VBA SQL code to O365 RRS feed

  • Question

  • Hello,

    I have a O365 workbook with two sheets which are "AMZ" and "invoice".

    What I want to do is using VBA code  below  to extract data to sheet("invoice").

    But it can't work, the error message is  "No value given for one or more required parameter".

    However, I create a excel 2016 workbook and copy this VBA code.  It works very well.

    I have no idea why and how to fix it cause I am not vey good at VBA, I hope someone  can teach me how to fix the code in o365.  Many thanks. 

    My code:

    Dim cnn As Object, rs As Object, SQL As String, stropen As String

    Set cnn = CreateObject("ADODB.connection")
    Set rs = CreateObject("ADODB.Recordset")

    stropen = "provider=Microsoft.ACE.OLEDB.12.0;"

    stropen = stropen & "extended properties=Excel 8.0;Data source="

    stropen = stropen & ActiveWorkbook.FullName
    MsgBox stropen

    cnn.Open stropen

    SQL = "SELECT time,newsku,quantity,productsales  FROM [AMZ$]"
    SQL = SQL & "WHERE type='Order' and fulfillment='Amazon'"

    MsgBox SQL

    Set rs = cnn.Execute(SQL)


    Dim K As Integer
    For K = 1 To rs.Fields.Count
       ActiveSheet.Cells(2, K) = rs.Fields(K - 1).Name
       'MsgBox ActiveSheet.Cells(2, K).Text


    ActiveSheet.Range("A2").CopyFromRecordset rs

    Set rs = Nothing
    Set cnn = Nothing

    Saturday, September 28, 2019 8:12 AM

All replies

  • If you set the cnn and open it like this, does it work for you?

    With cnn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & ActiveWorkbook.FullName & ";" & _
        "Extended Properties=""Excel 12.0 Xml;HDR=yes"";"
    End With

    Subodh Tiwari (Neeraj) sktneer

    Sunday, September 29, 2019 4:25 AM