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?

 

[Glory]

I need to put a bunch of .xls files into one .csv file.

Ruddles mentioned that I might have to use the method FileSystemObject.WriteLine. I'd prefer not to if it means I have to write the data one element at a time.

If Excel can create a .csv file through its SaveAs dialog, there must be some other method to "Insert Into" one.

 

[xenou]

Possibly:
1) save the .xls as a .csv
2) ReadAll from the csv using the FileSystemObject.ReadAll method
3) Append to the master csv using the FileSystemObject.Write method

Personally, I should simply add the data to an Access table. For this you only need to open Access then use File | Import Data. This part can be scripted too but you'd be well served at first to do it "by hand" to become familiar with what is happening. Probably I would not even be using Excel (or Excel pivot tables) at all. Access should be quite suitable to get whatever you need from the data - in my experience anyway.

 

[Glory]

We need printable pivot tables to manually verify a bunch of information.

I'm trying to avoid opening the .xls files because that takes a long time.

Will it be necessary to manually insert the comma delimiter after every "Write" statement has been executed by including it in the string to be written, along with the value of the cell?

Open MyCSV.csv For Append As #1
 
For i1 = 1 to LastUsedRow
 
    For i2 = 1 to LastUsedColumn
 
        Cells(r, c) = a
 
        FileSystemObject.Write a & ","
 
    Next
 
Next

Like that?

 

[xenou]

Yes it would be necessary.

Your code above is using two different and incompatible IO statements:
This is a VBA Input/Output Function (OPEN statement)

Open MyCSV.csv For Append As #1

This is a FileSystemObject Input/Output Function (WRITE statement)

FileSystemObject.Write a & ","

I'm not sure where you're getting your examples from - it seems as if you are making it up as you go along. You have to step back and learn the basics - first, write a simple one liner. Then when you understand the basics of writing a file, work up to writing individual elements from an array. You're trying to do too much at once.

-------------------------------------------

We need printable pivot tables to manually verify a bunch of information.

I'll bet I could verify the same data with an aggregate query in Access, sans pivot table. Indeed, for what it's worth you can also use pivot tables in Access.
The point is first to collect and prep your data. Then (and only then) to run the reports on it - which can be in any shape or form you like (Access query, Access Pivot table, export to Excel, pivot table in Excel sourced from Access, etc. etc.)

 

[Norie]

You want to avoid manually opening Excel files, but looping through a file line by line is OK?

 

[Norie]

Oops, tried to edit but it timed out while I was testing.

Anyway, the code I posted earlier can be adapted to append one (or more) csv files to another, existing file.

All that's needed for that is a simple INSERT query.

PS I agree with xenou, Access might be capable of giving you the information you want. Excel isn't the only place you can create things like pivot tables.

 

[Glory]

You want to avoid manually opening Excel files, but looping through a file line by line is OK?

Only if it's faster.

I have learned a lot since the start of this thread.

I can now use ADO to extract data from closed workbooks (which allowed me to finish a project I stalled out on back in January)...

...I have totally reworked a bunch of query tables I've been working with for a long time using what I've learned about SQL through researching what's been written here...

...and I've gotten the spur to get a handle on .csv's and Access. All kinds of ideas are coming to mind that weren't there a week ago.

This is why I'm eager to get on this site. What I gain here makes work fun.

Thanks.

 

[xenou]

Generally yes, opening a csv with Excel will be faster than reading it line by line. Ditto the other way, saving an Excel file as a csv will be faster than writing it line by line.

I'm glad your making progress. I may have wanted to steer towards Access instead but you should get good results with the csv's if the files are well structured and aren't getting any bad data in them. Access would complain about bad data (such as text in a number field) - a csv will happily accept whatever you give it. So you have to be careful with your data.