E
eshirley
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
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