Issue with Importing from SQL Server

[newnoise]

I have a macro that extracts data from SQL Server and pastes it into a worksheet in Excel. The problem is that once the data are pasted into the Excel worksheet, Excel's "find" function no longer works (i.e., I press CTRL+F, search for text that I know appears in the data, and the search returns nothing). Does importing data from SQL Server do something to the data to change how it can be accessed/searched within Excel?

Here's the code:

Please Login or Register  to view this content.

The problem occurs when I try to run the other macro (named "Customers"). Within that macro, there's a command to search through the spreadsheet for a column title and then record that column number as a variable. When that code runs, Excel returns a "Run-Time 91" error: "Object variable or With block variable not set."

When I exit the macro and attempt to find the text myself (outside the macro), the CTRL+F search box returns no results, even though I *see* the text on screen that I'm searching for.

Any help would be appreciated.

[Norie]

What are you searching for?

Can you give some examples?

Have you tried filtering?

[newnoise]

I'm just searching for a column header. Here's the code of the second macro in question:

Please Login or Register  to view this content.

The problem occurs at this point in the code:

c_orderno = Cells.Find(What:="orderno", After:=ActiveCell, LookIn:=xlFormulas, _
Lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Column

I get a run-time 91 error. Any idea what's going on?

[Norie]

There's a few things that could cause problems.

If you want to find the columns for particular fields you could try this.

Please Login or Register  to view this content.

If that doesn't work can you upload a workbook with just the field names?

By the way, you could use criteria in the query so that the rows/records you don't want aren't actually imported.

Then you wouldn't need the second code.

[newnoise]

I think I figured out what the problem is, but I don't know how to fix it. Basically, the problem is that the "QueryTables.Add" command hasn't had a chance to fully populate the table before the macro proceeds to its next step. The spreadsheet just says "Getting data..." when the "Find" command is being run, so there's no column header for it to find. Here's what the screen reads:

screenshot.png

So, my question now: Is there a way to make sure that the data from the SQL pull are fully populated before macro proceeds? I've tried using a "wait" or "pause" function to have the macro hold for 10 seconds or so to give it time to populate, but it appears that the "background refresh" of the SQL pull doesn't happen untli AFTER the macro has finished running.

Any help would be appreciated!

[Norie]

Change the query so you don't need the second part of your code.

[newnoise]

That's certainly a reasonable suggestion, but there is a portion of the macro that I haven't been able to figure out in SQL but know how to do in Excel. The data in question have unique entity ID's, some of whom also have spouses whose unique ID's appear in the same table. I use macro code in Excel to make sure that only one spouse per couple is included in the table. I'm not sure how to do the same thing using SQL.