0

My procedure uses Access VBA to run a query to an external database, save the data to Excel, and save to a local folder. I've been using this same procedure with no issues for about year. Now after an O365 update it doesn't compile. I'm self taught and stuck at this point. I tried some updates resulting in a successful compile but in the end the procedure didn't accomplish anything.

Public Sub ExtractToXL()

    Dim conn As New ADODB.Connection
    Dim rstTD As New ADODB.Recordset
    Dim sql As String
    Dim xl As Excel.Application
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet
    
    Set xl = CreateObject("Excel.Application")
    Set wb = xl.Workbooks.Add()
    Set ws = wb.Sheets(1)
        
    conn.CommandTimeout = 0
    conn.Open "DSN=Teradata;"
    
    sql = "SELECT * FROM database;"
    
    rstTD.Open sql, conn
   
    For iCols = 0 To rstTD.Fields.Count - 1
        ws.Cells(1, iCols + 1).Value = rstTD.Fields(iCols).Name
    Next
        
    ws.Range("A2").CopyFromRecordset rstTD
     
    'save and cleanup
    ReportPath = Application.CurrentProject.Path & "\Reports\"
    wb.SaveAs (ReportPath & "Daily Report " & Format(DateAdd("d", -9, Date), "yyyymmdd") & "-" & Format(DateAdd("d", -3, Date), "yyyymmdd") & ".xlsx")
    wb.Close
    Set wb = Nothing
    Set ws = Nothing
    Set xl = Nothing
    
    rstTD.Close
    conn.Close
    Set rstTD = Nothing
    Set conn = Nothing

End Sub

When I run it, I receive: Compile error: Method or data member not found

It highlights ws.Cells

Bruce Pavkov
  • 23
  • 4
  • 2
    "I tried some updates resulting in a successful compile" - so does the code compile or not? – BigBen Nov 09 '20 at 15:15
  • The code as shown does not compile. If I make this change```ws("sheet1").Cells``` then I have to also update to ```ws("sheet1").Range``` but then I get a compile error for wb.SaveAs and I don't know how to resolve? – Bruce Pavkov Nov 09 '20 at 17:02
  • What references do you have checked under Tools > References? – BigBen Nov 09 '20 at 17:06
  • Visual Basics For Applications, Microsoft Access 16.0 Object Library, OLE Automation, Microsoft Office 16.0 Access database engine object, Microsoft Excel 16.0 Object Library, Microsoft ActiveX Data Objects 6.1 Library, Microsoft Office 16.0 Object Library – Bruce Pavkov Nov 09 '20 at 18:00
  • It appears the issue is resolved, although I'm not sure it makes sense. I copied all the code between Sub and End Sub into a new procedure I named "test" intended to try other options. But the code ran as is, no changes. So I tried my original procedure again (the code in question) and now it works? I have several Access programs for various reports and had to do this for each one. It's as if the full code was not being read until a change was made, then it refreshed and used all dimensions. – Bruce Pavkov Nov 10 '20 at 16:10
  • If this happens again, a full [Decompile](https://stackoverflow.com/a/3268188/3820271) will probably help. – Andre Mar 19 '21 at 17:58

0 Answers0