CODEWITHSUNDEEP
sqlexceladodbrecordsetvba
botakelymg
botakelymg

Reputation: 59

Performing SQL queries on basic Excel 2013 worksheet as table using ADO with VBA triggers Errors

I'm developping modules on a client XLSm with 32-bits 2013 Excel.

I'd like to use datas on worksheet as if it is an Access table.

With a lot of difficulties, I think connection is now OK.

Still, I have error : 3001 Arguments are of wrong type, are out of acceptable range. Error that I cannot understand.

Here excerpts of VBA lines :

In addition, I added 20 lines in data Worksheet below the header line to permit to Excel to interpret for the type of each columns.

    varCnxStr = "Data Source=" & G_sWBookREINVOICingFilePath & ";" &   "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=15';"

    With conXLdb
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Mode = adModeShareExclusive
        .Open varCnxStr
    End With

    strSQL = "SELECT * "
    strSQL = strSQL & " FROM [ReInvoiceDB$B2B5072] inum "
    strSQL = strSQL & " WHERE inum.InvoiceNum LIKE '1712*' "
    strSQL = strSQL & ";"

    '>> TRIGGERs ERROR with the current Where Clause !!'
    adoXLrst.Open strSQL, conXLdb, dbOpenDynamic, adLockReadOnly, adCmdText

    If adoXLrst.BOF And adoXLrst.EOF Then
        'no records returned'
        GoTo Veloma
    End If

    adoXLrst.MoveFirst

    Do While Not adoXLrst.EOF
        'Doing stuff with row'
        adoXLrst.MoveNext
    Loop

    sHighestSoFar = adoXLrst(1).Value '> just to try for RecordSet : Codes are not completed...

    sPrefixeCURR = Mid(sHighestSoFar, 1, 4)
    Highest = CInt(Mid(sHighestSoFar, 5))

    '> Increment >'
    Highest = Highest + 1

    HighestStr = sPrefixeCURR & Format(Highest, "00")

    strGSFNumber = HighestStr

    adoXLrst.Close
    conXLdb.Close

Veloma: 
    On Error Resume Next
    Set adoXLrst = Nothing
    Set conXLdb = Nothing
Exit Sub

Etc.

Any idea about what seems be wrong ?

Thank you

Upvotes: 0

Views: 1563

Answers (2)

botakelymg
botakelymg

Reputation: 59

To give more details about the whole module to be implemented : it is to perform a Transaction unit.

This transaction will comprise 3 operations : get a max value from a column (Invoice number) to increment it, record the new number inside an Access table (by DAO), the same Excel file (by ADO) and generating document on HDD.

So it is aimed to use the Excel file as a table not as a file manipulated with Windows script or Excel VBA. My end user is disturbed by the pop-uping of an Excel opening file operation. As a developer, I'm feeling more comfortable with using SQL statements as much as possible inside Transaction session. Is that your opinion too ?

Upvotes: 0

iDevlop
iDevlop

Reputation: 25262

Below is an old example I have been using successfully. Note that the sheet name in the book are Sheet1 and Sheet2, but in the query I had to use sheet1$ and sheet2$. I noticed you had $ signs in the middle of your sheet names. perhaps that's the issue ? 

Sub SQLUpdateExample()
    Dim con As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set con = New ADODB.Connection
    con.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
           "DriverId=790;" & _
           "Dbq=" & ThisWorkbook.FullName & ";" & _
           "DefaultDir=" & ThisWorkbook.FullName & ";ReadOnly=False;"
    Set rs = New ADODB.Recordset
    Set rs = con.Execute("UPDATE [Sheet1$]  inner join [Sheet2$] on [Sheet1$].test1 = [Sheet2$].test1  SET [Sheet1$].test3 = [Sheet2$].test2 ")

    Set rs = Nothing
    Set con = Nothing
End Sub

Upvotes: 2

Related Questions