extract data from Multiple workbooks

[mirage10]

Hi,
I have multiple work books in one folder, All these work books have the same format, they will have same column names A,B but each work book will have different values.
I want to get all this data into a new work book called "abc"
I want to present data in one of the following 2 ways

1. Each work books will be a sheet in this new file abc with all the data in it.

or

2. Ideally I would like to have data in only one work sheet in file abc,
say I have 2 files in this folder S1 and S2

S1 - A B
123 xy
234 yz

file S2- A B
nji 12
fgj 45
jkl 780

in this file abc i want the data from one file to be shown
after the other(like shown above)


I was trying to accomplish this using 1st method.

I have the following code, THe problem is this code does not fetch any data. It just runs with no errors.

while debugging

sSQL from the vb code was:
<code>
SELECT S.A,S.BFROM `C:\TRIAL\S2`.`Sheet1$` S
</code>
and

<code>
sConn is ODBC;DSN=Excel Files;DBQ=C:\TRIAL\S2.xls;DefaultDir=C:\TRIAL;DriverId=790;MaxBufferSize=2048;PageTimeout=5;
</code>

Pl. suggest what might be going wrong here.
THanks

<CODE>
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 5/31/2005 by adh
'

Dim fs, f, f2, fc, s
Dim sConn, sSQL, sPath2, sWorkbook2, p2
sPath2 = "C:\TRIAL"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder("C:\TRIAL")
Set fc = f.Files
For Each f2 In fc

p2 = InStrRev(f2.Name, BS)
sWorkbook2 = Split(Right(f2.Name, Len(f2.Name)), ".")(0)

sConn = "ODBC;"
sConn = sConn & "DSN=Excel Files;"
sConn = sConn & "DBQ=" & sPath2 & "\" & sWorkbook2 & ".xls;"
sConn = sConn & "DefaultDir=" & sPath2 & ";"
sConn = sConn & "DriverId=790;"
sConn = sConn & "MaxBufferSize=2048;"
sConn = sConn & "PageTimeout=5;"

sSQL = "SELECT S.A,S.B"
sSQL = sSQL & "FROM `" & sPath2 & "\" & sWorkbook2 & "`.`Sheet1$` S "

Sheets("Sheet2").[A1].Value = sSQL
Sheets("Sheet2").[A2].Value = sConn

With ActiveSheet.QueryTables(1)
.Connection = sConn
.CommandText = sSQL
End With
Next
End Sub
</CODE>



------------------------------------------
A Man can be only as happy.. as he makes up his mind to be

 

[SkipVought]

Hi,

You'ld be better off using ADO and using the CopyFromRecordset method where you specify the next empty cell on the output sheet.

here's some sample code....

Sub GetNomen(sPN As String)
    Dim sConn As String, sSQL As String
    Dim rst As ADODB.Recordset, cnn As ADODB.Connection
    
    Set cnn = New ADODB.Connection
    
    cnn.Open Workbooks("Personal.xls").Sheets("sysParms").[A010PROD_ConnectString]
    
    Set rst = New ADODB.Recordset
    
    sSQL = "SELECT PM.Nomen "
    sSQL = sSQL & "FROM FPRPTSAR.Part_Master PM "
    sSQL = sSQL & "WHERE PM.PART_ID='" & sPN & "' "
    
'    Sheets("sysParms").Range("SQL_Code").Value = sSQL
        
    rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
                          
    rst.MoveFirst
    lNextRow = [A1].CurrentRegion.Rows.Count + 1

    Cells(lNextRow, "A").CopyFromRecordset rst

    rst.Close
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
End Sub

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}