Update a column in excel sheet using SQL in VBA RRS feed

  • Question

  • I need to update a column(Item) in the excel sheet (POLines), for which I have written an update query

    But, I'm unable to execute it

    This code shows error on execute line "Automation error"

    I tried other ways to execute, but to no success.

    Following is the code:


    Dim cn As ADODB.Connection
    Dim objCommand As ADODB.Command

    Set objCommand = New ADODB.Command

    Dim szConnect, szSQL As String

    szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & folderpath & working_file & ";" & _
        "Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

    Set cn = New ADODB.Connection

    cn.Open szConnect

    szSQL = "Update [POLines$] Set Item=5" ' its a sample query but logically right

    With objCommand
    .ActiveConnection = cn
    .CommandText = szSQL
    End With

    PS: the variables are defined in the code.

    • Edited by Xavier03 Thursday, November 15, 2018 2:01 PM
    Thursday, November 15, 2018 1:56 PM