We have a macro that builds an SQL Query, sends it off to the Unix Server where it is executed and then the text file copied back to the User’s temp directory where it is imported into a Worksheet using QueryTables.

The user often has a list of items to import and because of a size limitation on the SQL Query it has to be sent numerous times get all the data for the list.

The Workbook has its own menu items, created by the workbook each time it is opened by reading through the VB Modules and building the menu and deleting them each time it is closed.

Occasionally, the QueryTables will error out on the .refresh command in the middle of multiple queries. For some reason Excel is not recognizing the text file, and interestingly enough after this happens other work books cannot be opened and the “Ctrl Alt W” shortcut to open Word also becomes non-functional, no errors are indicated they just don’t work.

Using the Excel Menu to import the text file also fails although it doesn’t show any errors. Closing the workbook and re-opening it fixes the problem.

Something needs to be reset, I’ve added a statement to run through any QueryTables associated with the Worksheet and delete them but that hasn’t solved the problem.

The Macro has been modified to handle the error and import the text file by opening it and reading it in line by line but this is considerable slower and doesn’t fix the problem of not being able to open other workbooks.

Other workbooks can be created but not opened.

Any suggestions? The users are getting frustrated, they have some huge queries and only one error invalidates them. I’m way beyond my expertise and need some help.

Thanks
Ed