Building a query without using a query table

[Glory]

http://www.excel-vba.com/vba-code-2-12-SQL.htm

I need to be able to run an SQL query without generating any result in my Excel worksheet. That's because I'll be returning so many rows, I won't be able to fit them onto a single sheet... I need to Inner Join multiple tables and then reference the result like an external database to create a Pivot Table.

eg:

Select * From C:\DOCUMENTS AND SETTINGS\DESKTOP\Book1.xls`.'Sheet1$'
UNION ALL C:\DOCUMENTS AND SETTINGS\DESKTOP\Book2.xls`.'Sheet1$'

All I need to know is how to modify code like what's at the link above to build a table that I can work with using SQL through VBA.

Can anyone help with that?

 

[xenou]

Unfortunately, no. You can't join recordsets.

As far as compiling the data in a text file, that should work. You just have to be sure that the data is all "good clean data" when its saved/appended or your queries will go bad. Might be slow working with text files though.

What kind of data is this? Why so many different workbooks to begin with?

 

[Glory]

If you mean what I think you mean when you say clean data (no commas or quotes) then yeah, this is as clean as it gets. There are spaces and hyphens... now that I think of it there are forward slashes for dates (will that be a problem for reading from .CSVs with ADO?).

The place I work for needs to be able to extract information from a database. They extract all the information at once for lots of tests. It's impossible from where I'm sitting to choose not to get all the information from each test, and there are dozens of them.

So that's why there's so many workbooks... we can only get data for six tests in each workbook.

Norie: What kind of latency are we talking about to generate a new .MDB file? Would it really be that much simpler to work with?

 

[Glory]

Is it true that you can't extract data from other directories?

 

[jkpieterse]

If your Excel source workbooks stay the same, you can have a single MDB file in which you create links to all the tables from all the workbooks you need.
Then you can either write the query using MSQuery from Excel, or (easier) use Access to create the query you need and then from Excel pull in the data from that query using the Pivot table wizard, external data option.

 

[Glory]

That's what I'm considering as an alternative if all else fails.

But I already have code that creates a pivot table from a CSV file, and the looping code that I need to grab the data from all the workbooks.

All I need now is the answer to the blue question above, and the code to create a CSV file with VBA.

 

[Norie]

What context are you asking the blue question in?
I can't see why there would be a problem with having the data in different directories.

It would easier if they where in the same directory I suppose.

As for the latency of importing the data into Access, it's hard to tell.

There are a lot of factors, eg are the files located locally.

Finally, I tried creating a csv file using ADOX but it can't be done.

Why not just create a dummy Excel worksheet and save it as CSV.

 

[Glory]

set a = Workbooks.Add
a.SaveAs("new.CSV")
a.Close

?

How should I write to that file?

The blue question is an entry on MSDN that says it's impossible to import from one directory to another. My file fails when it's in some other directory, but succeeds when it's in the same directory as the original workbook (where the ADO code is being initiated).

Can spaces in the file name make a difference?

 

[Norie]

That's why I asked about context.

Did you post a link for the MSDN article?

I can't seem to see one if you did.

 

[Glory]

Post #33: http://support.microsoft.com/kb/241471

The data file won't import if it's not in the same directory as the destination workbook. I would use ACE if I could, but it's Excel 03.

How do I populate a CSV file multiple times in the course of a single macro using ADO? (eg, so that nothing is overwritten)

 

[Norie]

As far as I can see that article is for Visual FoxPro.

Is that what you're using?