[SOLVED] SQL query interrupted by a for loop and closing/saving files

**nErD** · 01-09-2012, 05:04 AM

Dear members,

My queries simply do not wish to be executed when combined with a for loop or if a file is closed an saved after the query function (see the code below):

Please Login or Register  to view this content.

I have tried puttig the query code in different areas of the original code and using the Wait method. Nothing works. I need to take item numbers, send them as a query and receive a corresponding price, which is saved into an Excel sheet. This needs to be done for 2 up to 100 (!) item numbers. Each item number is a seperate query; I have not combined them into a single query as you never know how many item numbers you have. It can be 2 one day, 50 tomorrow.

In MATLAB I have successfully used 1000 queries in a for loop with zero issues. I don't understand why the queries (1) take so much time with VBA and (2) why they get interuppted by surrounding events. Any thoughts?

**snb** · 01-09-2012, 06:10 AM

assuming a querytable in AF1:

Please Login or Register  to view this content.

**nErD** · 01-09-2012, 06:52 AM

snb:

I changed your code to the following, to try to make it work:

Please Login or Register  to view this content.

I still get a run time error (91) for
[ For Each cl In TO_Sheet_CTO.Columns(2).SpecialCells(2)
[/code]

EDIT: I got your code to run, but it does not fix the inherent problem of the query being interrupte, i.e. the info from the database is not stored in the sheet of interest

**Kyle123** · 01-09-2012, 08:03 AM

why not skip the loop?

Please Login or Register  to view this content.

**nErD** · 01-09-2012, 08:35 AM

Double Post [EDIT]

**nErD** · 01-09-2012, 08:47 AM

Kyle: that did the trick! And made my day :D

The only problem remaining is executing code after the query, i.e. (1) saving and closing the file, and deleting the AF column when done (the data in column AF is copied into column S).

Also, t captures (the cpumn ttitle at B1 and (2) does not get return a value for all item numbers.

**Kyle123** · 01-09-2012, 09:19 AM

Can't you use your existing code for saving and closing the file etc?

1. Does it matter if the title is captured? The query will just return no results for it.
2. Not sure what you mean by this, are you saying that not all item numbers are included in the SQL string or no results are returned?

**nErD** · 01-09-2012, 09:37 AM

Kyle: Writing while tired = typos. My apologies.

I used a messagebox to see if t captured all item numbers; it did. I solved the title issue with changing the range to ("B2:B" LastRow). The saving the file issue can be solved saving manually afterwards (keeping the files open). My last issue remains:

* When using the sql query above, only 28 values are returned (always), even though you have more item numbers than that. It does not seem like the values change either (ex. using code with different item numbers yeild the same result as with the previous item numbers).

**Kyle123** · 01-09-2012, 09:50 AM

does the query work if you entire it directly into the DB?

Take the full Query string generated and see what it returns directly in the DB

**nErD** · 01-10-2012, 04:02 AM

Kyle: yep, the item numbers exist in the system.

EDIT: there was a problem relating to the DB not allowing for (1) repeated item numbers and (2) space in the item numbers; it has been solved manually.

To further complicate things; there are several sheets that need to be checked, i.e. you get the code working for the last sheet, but the first sheets' query events got stopped.

I have solved this by forcing (hate doing that) the user to click a button at each sheet to execute the queries one by one, without interruptions.

Final code:

Please Login or Register  to view this content.

I will now mark the thread as solved.

[SOLVED] SQL query interrupted by a for loop and closing/saving files

LinkBack

Thread Tools

Rate This Thread

Display

[SOLVED] SQL query interrupted by a for loop and closing/saving files

Re: SQL query interrupted by a for loop and closing/saving files

Re: SQL query interrupted by a for loop and closing/saving files

Re: SQL query interrupted by a for loop and closing/saving files

Re: SQL query interrupted by a for loop and closing/saving files

Re: SQL query interrupted by a for loop and closing/saving files

Re: SQL query interrupted by a for loop and closing/saving files

Re: SQL query interrupted by a for loop and closing/saving files

Re: SQL query interrupted by a for loop and closing/saving files

Re: SQL query interrupted by a for loop and closing/saving files

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions