Argthtjtr

Below mentioned sample procedure use to read data from input excel files saved in a folder. The SQl query has join operations from few tables (sheets) which are part of reportWorkbook. After running this procedure successfully on some files at some stage it throws an error '[Microsoft][ODBC Excel Driver] Too few parameters. Expected 11.' for next input file.

Sub RunQueryOnInputFiles()

Dim fldr            As Folder

Dim fil             As File

Dim oConn           As ADODB.Connection

Dim oRs             As ADODB.Recordset

Dim tmpWkb          As Workbook

Dim reportWorkbook  As Workbook

Dim sSql            As String



Set reportWorkbook = Workbooks.Add

reportWorkbook.Save tmpFilePath



'Loop through all files in input folder



    For Each fil In fldr.Files                                              

        Set tmpWkb = Workbooks.Open(fil.Path)

        tmpWkb.Sheets(1).Range("A1").CurrentRegion.Copy

        reportWorkbook.Sheets("TmpSheet").Range("A1").Paste xlPasteValues   

'Paste input data

        tmpWkb.Close SaveChanges:=False



        Set oConn = OpenConnection(reportWorkbook.FullName)

        sSql = GenrateSQL("SOME QUERY FROM SHEET [TmpSheet$]")              

'Genrate SQL query from [TmpSheet$]`

        Set oRs = GetRecordset(sSql, oConn)

        reportWorkbook.Sheets("XYZ").Range("A1").CopyFromRecordset oRs

        CloseConnection oConn

    Next fils



End Sub

I debug the problem separately using below mentioned procedure

Sub testing()

    Dim sSql As String

    Dim x As ADODB.Connection

    Dim r As Object

    Dim fl As Object

    sSql = "SELECT * FROM [TmpSheet$A1:U4]"

'considering reportWorkbook is in scope it is same workbook

    Set x = OpenConnection(reportWorkbook.FullName)    

    GetRecordset sSql, x

    Set r = GetRecordset(sSql, x)

    For Each fl In r.Fields

        Debug.Print fl.Name

    Next

End Sub

Here, fl.Name is returning the names which belongs to previous file which is already processed and data on TmpSheet is already been overwritten. Running the same testing() procedure after a few minutes shows correct fields which are available in Tmpsheet.

It seems that connection cannot be refreshed. Please suggest some method which can enforce connection to be refresh in run time.

From the post of your abbreviated code, the challenge you are facing is querying the very workbook you are processing which always reflects the snapshot of last saved instance.

Consider incorporating a Workbook.Save method after processing each Excel file within the loop.

For Each fil In fldr.Files                                              

    ...



    reportWorkbook.Save

Next fils

Additionally, consider querying the current workbook in the loop to avoid the overhead of opening the file and copying and pasting values.

For Each fil In fldr.Files                                              

    ' QUERY CURRENT WORKBOOK

    Set oConn = OpenConnection(fil.Path)

    sSql = GenrateSQL("SELECT * FROM [Sheet1$]") 

    Set oRs = GetRecordset(sSql, oConn)

    reportWorkbook.Sheets("TmpSheet").Range("A1").CopyFromRecordset oRs

    oRs.Close()

    CloseConnection oConn



    ' QUERY MASTER WORKBOOK

    Set oConn = OpenConnection(reportWorkbook.FullName)

    sSql = GenrateSQL("SOME QUERY FROM SHEET [TmpSheet$]")                      

    Set oRs = GetRecordset(sSql, oConn)

    reportWorkbook.Sheets("XYZ").Range("A1").CopyFromRecordset oRs

    oRs.Close()

    CloseConnection oConn



    reportWorkbook.Save

Next fils

Additional Notes:

• It appears you are overwriting data in XYZ sheet with each iteration of loop if that is the intention. Add or change sheet to avoid this overwriting.




• If your sheets have headers, be sure to copy recordset starting in A2 and loop field names starting in A1 (like your testing() subroutine). Or use static headers in row 1.




• For bottom solution to query looped workbook, if sheet is not known in advance you will need to open document in loop to get sheet name and pass it into first SQL query.