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]

All I know is that's the exact error message I'm getting... and moving the data into the same directory as the destination workbook stops the error from occurring.

Does another explanation leap to mind? I'd rather not encounter the issue again.

 

[Norie]

Not without further information, which is something that has been missing from this whole thread.

Now you seem to have code and you are having problems with it - for that we need to see the code.}

 

[Glory]

Yeah well, you know, I'm trying to do as much of the leg work as I can.

...I didn't change anything in my earlier code. It just randomly started working. That's always a little ominous.

Okay, well then I'm only left wondering how to populate a .CSV file w/ VBA. Ruddles pointed out the FileSystemObject object.

Do I have to use the "WriteLine" method to write every single cell value and comma out manually? Or is there a better way?

 

[Norie]

What earlier code?

As far as I can recall, and I'll check later, there's not a whole lot of code going on in this thread.

You still aren't giving us much information at all - if you were too I've got a feeling you'd get a lot more concrete help.

Probably not the whole code but enough to be going on with - perhaps even a link or two to threads/articles here or elsewhere that deal with what you want.

I think I know what I would do.

1 Save all the relevant data as CSV using VBA to the same directory, with an appropriate filename for each - that might be important.

2 Now with ADO set up a connection to the folder where those files are.

3 When you do that each CSV file should get treated like a separate table in a database.

4 Write SQL to combine all the data into one file.

I've never done that, it might not be appropriate for your situation but it might work.

PS To get the code for 1 you can use the macro recorder.

 

[Norie]

Well I came up with this.

I created 3 worksheets each with 3 columns of data.

The data in each worksheet was different but the data type the same:

ColumnA - Field1 (Integer)
ColumnB - Field2 (Text)
ColumnC - Field3 (Date)

I saved them as 3 separate CSV files (WB1Table, WB2Table, WB3Table) into the same directory 'C:\Databases\'.

Then with this code I combined all the data, adding an extra field to identify the source of each record.

Option Explicit

Sub CVSTest()
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strDBFolder As String
Dim strSQL As String

    Set conn = CreateObject("ADODB.Connection")

    Set rst = CreateObject("ADODB.Recordset")
 
    strDBFolder = "C:\Databases\"
 
    strSQL = "SELECT * ,'WB1' AS Source FROM WB1Table.csv"
 
    strSQL = strSQL & " UNION " & "SELECT * ,'WB2' AS Source FROM WB2Table.csv"
 
    strSQL = strSQL & " UNION " & "SELECT * ,'WB3' AS Source FROM WB3Table.csv"
 
    conn.Provider = "Microsoft.Jet.OLDEDB.4.0)"
    
    conn.Properties("Data Source") = "C:\Databases\"
 
    conn.Properties("Extended Properties") = "text;HDR=YES;FMT=Delimited"

    conn.Open    ' "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                 "Data Source=" & strDBFolder & ";" & _
                 "Extended Properties=""text;HDR=YES;FMT=Delimited"""
 
    rst.Open strSQL, conn, adOpenStatic, adLockOptimistic, adCmdText
 
    Range("A1").CopyFromRecordset rst
 
End Sub

Don't know if that's of any use but it was definitely easier than I though it would be - worked first time, honest.

 

[Glory]

I can't copy to my sheet. It's more than 65k rows of data. But I can create a PivotTable from a CSV file without any (apparent) difficulty. I haven't messed around with it to track down any problems.

Mostly what I need now is an idea about how to write to a CSV file without overwriting anything already inside the CSV file (assuming all headers are identical).

Then I'll be set to go. That way I can use just one CSV file to group together all the dat a from the scattered XLS files. I can't control those XLS files. They come to me, and I have to work with them. I want to make them simpler, turn them all into one CSV file.

I'm sure it's possible... probably even relatively simple, but I don't have a clue where to start, except I keep reading TextStream.

I can't get any intellisense on that.

 

[Norie]

If you are talking about the code I posted don't you realise it's just an example.

You don't need to copy the recordset to a worksheet.

You could use it for something else.

Can't think of anything specific but 'pivot' something... keeps on coming to mind.

 

[xenou]

Tell your manager (?) that you need Excel 2010. You'll have a million rows in Excel 2010 and you can just copy the data in without any further trouble.

 

[Glory]

xenou: I think I'd actually rather get it done this way. I want to learn how to do it (and I'm already nine-tenths of the way to being done). Besides, this place does everything on the cheap. They might give me what I need, but lots of other people won't have the same machines, and the program needs to work on theirs too.

Norie: I can't control how this data comes to me, and if I have to open every .xls workbook to save them all as .csv files, it will take upwards of two mintues every time this program runs. That's why I've been asking for an ADO method to 'push' data (the way you said not to do), populating an existing .csv file.

I need a method that will not overwrite the file with each fresh iteration; that way I can fill one .csv from multiple .xls files. The code I've already written will create a pivot table from that single external source.

 

[Norie]

Glory

I'm sorry you've really lost me now.

How exactly are you getting this data?

CSV files or Excel files?