Random Errors When Macro Is Run In A Bat-file

  • Hello all!

    I dont know if this is the right forum for this, but I this is the best folrum I've found, so I'd thought I'd give it a shot anyway.

    The scnario is that I'm running loads of SQL-queries in a bat-file (using the isql-command). After that has been done I want to run Excel to format the result of the queries, and save it as a excel file on a networkplace. The SQL-queries are working fine, and the macro is working fine, when run seperatly, but since I cant predict how long the SQL-queries will take I have to either give it an extra long time before I start with the Macro, or run then in a chain from the bat-file.
    When I do that I usually end up with random errors in Excel. Most usually it is "Not enough memory to display completly". This error is shown before the actual workbook is loaded, and therefore no macro has been loaded either so there's nothing I can do from there.
    When I click OK the macro runs as should be though, but since it should be run at 5 in the morning it will just stand there until I come into work each day, and have loads of complaint that they havent got their figures yet.

    I was wondering if any of you know how to cuircumvent this/solve the problem?
    I have checked out Microsoft, and googled the errors loads of times in diffrent way, but havent found anything solid that works yet.

  • Re: Random Errors When Macro Is Run In A Bat-file

    i'm not to sure if this would help you out or even solve your problem
    you 'could' save your .bat file on a network and then have the user's PC open a Micorsoft Schedule to the batch file....

    then add in echo's for "has the batch completed" yes/no... it would require user interaction but the chain would work...

  • Re: Random Errors When Macro Is Run In A Bat-file

    The batfile looks something very similar to this:
    (not Excel, still Code-tag??)

    CD Querys
    For %%1 in (*.SQL) Do Echo %%1 | isql -E -i %%1

    And this is where I would like to include

    CD Macros

    User interaction doesnt work. The computer literacy here isnt at top so to speak. We've had a couple having hard time using a mouse, and the files should be ready by the time they come in to work in the morning.

  • Re: Random Errors When Macro Is Run In A Bat-file

    If your report doesn't take too long to work...

    set up another batch file and set a windows schedule to run 15 min's before people start work...

    I've personally not tried to run the next chain of events after one has finished

  • Re: Random Errors When Macro Is Run In A Bat-file

    Thanks for the quick replys... :)

    I tried to have like that, and gave it ample time (I thought), but as the queires takes longer and longer the more the month progress it becomes more difficult to predict how long is enough, specially with requests on customized reports that will require even more queries, and thereby increasing the time.

    I would like to have it all in one bat-file so that it becomes indipendant of the time-issue.
    As I see at the moment, the only way I can do that is by building a small application in a programing language that does what the bat-file does, but more smart so to speak, but I just felt that's a little bit overkill for the task at hand.

    Thanks anyway guys... :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!