Copy diff workbooks into one workbook without opening the workbooks

[navb]

Could anyone please help me on this ? :
I have somewhere around 25 similar workbooks which I want to merge in a single sheet. I will only import 1 sheet (say sheet1) from each of those workbooks.I want to do this without opening any of the workbooks as this will save lot of time. Can this be done ? I have heard you can do this by using some ADO connection and then querying the excel database but dont know how to do this. Any help will be greatly appreciated !!

 

[navb]

I must also mention that the above code takes 35 secs to process 30 worksheets each having around 10k records !!! thats very impressive indeed. Thanks !!! I appreciate. .csv remains the last hurdle i guess

 

[bertie]

Ok, just a quick response to see if this works for you.

You have the connection string for csv files. So all you need to do is ajust how you read in the RecordSet.

Currently we have:

[FONT=Times New Roman][SIZE=3]'open the recordset[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]Set adoRs = New ADODB.Recordset[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]adoRs.Open "SELECT * FROM [[COLOR=red]Sheet1[/COLOR]$];", adoConn, adOpenStatic, adLockReadOnly[/FONT][/SIZE]

csv files only have one worksheet when opened in Excel. We can use a variable to extract the sheet name.

[COLOR=red]sSheetName [/COLOR]= Sheets(1).Name

And use the variable to read in the RecordSet.

[FONT=Times New Roman][SIZE=3]'open the recordset[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]Set adoRs = New ADODB.Recordset[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]adoRs.Open "SELECT * FROM [COLOR=red]" & s[/COLOR][COLOR=red]SheetName[/COLOR], adoConn, adOpenStatic, adLockReadOnly[/FONT][/SIZE]

NB The data is read from the first sheet in the workbook. This may affect how your Excel data is read in. If you think this is the case, copy, paste and rename the entire procedure; and run only for csv files.

 

[navb]

shall i change

sSheetName = Sheets(1).Name</pre>
to

sSheetName = Mid(aFiles(i), InStrRev(aFiles(i), "\") + 1, Len(aFiles(i)))

??

 

[bertie]

I have to admit that SQL is not my strong point, but it won't be the file extension.

If it doesn't refer to the sheet, sSheetName may have to refer to the full file path. i.e., something like

sSheetName = aFiles(i)

 

[Norie]

This will work for CSV files.

Option Explicit
Sub test()
Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim ws As Worksheet
Dim strFileName As String
Dim strPath As String
 
    Set cn = New ADODB.Connection
 
    Set rst = New ADODB.Recordset
 
    strFileName = "CSVfile.csv"    
    strPath= "C:\"

    With cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source=" & strPath & ";" & _
                            "Extended Properties=""text;HDR=Yes;FMT=Delimited"";"
        .Open
    End With
 
    rst.Open "Select * From [" & strFileName & "]", cn
 
    Set ws = Worksheets.Add
 
    ws.Range("A1").CopyFromRecordset rst
 
    rst.Close
    cn.Close
 
    Set rst = Nothing
    Set cn = Nothing
 
End Sub

For CSV files the data source isn't the file itself, it's the folder the file is in.

 

[navb]

adoRs.Open "SELECT * FROM [Sheet1$];", adoConn, adOpenStatic, adLockReadOnly

Can we use some conditions like select * from [Sheet1$] where Range("A") = "Europe" ......?
I need to filter data based on region....region is in column A.

 

[Norie]

Do you know the header/name of the column/field you want to filter by?

If you do:

Dim strSheetName As String
Dim strSQL As String
Dim strFieldName as String
 
strSheetName = "Sheet1"
strFieldName = "NameOfFieldToFilterBy"
 
strSQL = "SELECT * FROM [" & strSheetname &"$] "
strSQL = strSQL & " WHERE [" & strFieldName & "]='Europe'"

If you don't know the name of the field/column then it could still be possible but you'd have to find a way to determine the name.

The only way I can think of doing that at the moment is to actually open the query, or another query that only returns the field names.

I'm not sure if the latter is easily possible, though in some databases you can use certain types of queries to get infomation.

There is another way to do it using ADOX via the Catalog object.

Obviously if you do know the name of the field then no need for any of the above to get it.

 

[navb]

Hi,
There is no problem when I am using the following query :

col_Name = "Net Proceeds (Settle)"
strFieldName = "Settle Date"

adoRs.Open "SELECT SUM([" & col_Name & "]) FROM [" & sSheetName & "] WHERE [" & strFieldName & "]= #3/15/2011#", adoConn, adOpenStatic, adLockReadOnly

Here date is harcoded and sSheetName contains the worksheet name

But how do I modify the code if I want to pass a variable that contains the date value . When I am using the following :

settlement_date = "3/15/2010"

adoRs.Open "SELECT SUM([" & col_Name & "]) FROM [" & sSheetName & "] WHERE [" & strFieldName & "]= settlement_date ", adoConn, adOpenStatic, adLockReadOnly

its giving me the error :
" No value given for one or more parameters "

please help !

 

[Norie]

You seem to have a lost quote in that code, but that's not the problem.

Try enclosing the date in #s just like it is in the hard coded example.

Something like this perhaps.

strSQL = "SELECT SUM([" & col_Name & "]) "

strSQL = strSQL & " FROM [" & sSheetName & "] "

strSQL = strSQL & " WHERE [" & strFieldName & "]=#" & settlement_date & "#"

If that doesn't work there might be other ways to do it.

 

[navb]

adoRs.Open "SELECT SUM([" & col_Name & "]) FROM [" & sSheetName & "] WHERE [" & CURR_NAME & " ] =" & s_currency, adoConn, adOpenStatic, adLockReadOnly"

this code is giving error as s_currency is a string variable.
If I explicitly mention the currency name as for e.g

adoRs.Open "SELECT SUM([" & col_Name & "]) FROM [" & sSheetName & "] WHERE [" & CURR_NAME & " ] = 'EUR' , adoConn, adOpenStatic, adLockReadOnly"

it works.

how to get this resolved ?