CODEWITHSUNDEEP
excelvbams-access
Ricardo de la Cruz
Ricardo de la Cruz

Reputation: 37

VBA - Method or data member not found when opening an excel file from access

I have a small application that connects to a SQL server and downloads some of the data to Excel. It works fine in my computer but in the computer of one of my coworkers I get a "Method or Data member not found" error when trying to change the content of a cell in the excel spreadsheet.

This is the code that fails in my coworker computer, the line that has the error is the last line:

Public Function ExportRequest(strSupplier As String, intSupplier As Integer, _
    strOutPath As String, iEmpty As Integer, strManager As String, _
    ichkVolume As Integer, iframeVolume As Integer, ichkMatNum As Integer) As String

   On Error GoTo err_Handler
   
   ' Excel object variables
   Dim appExcel As Excel.application
   Dim wbk As Excel.Workbook
   Dim wks As Excel.Worksheet
   Dim wksvol As Excel.Worksheet
   Dim wsRange As Excel.Range
   Dim wksvolRange As Excel.Range
   
   Dim sTemplate As String
   Dim sTempFile As String
   Dim sOutput As String
      
   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset
   Dim sSQL As String
   Dim sDateFrom As String
   Dim sDateTo As String
   Dim lRecords As Long
   Dim iRow As Integer
   Dim iCol As Integer
   Dim iFld As Integer
   Dim intYear As Integer
   
   Const cStartRow As Byte = 3
   Const cStartColumn As Byte = 1
      
   intYear = year(Now)
      
   DoCmd.Hourglass True
   
   ' set to break on all errors
   application.SetOption "Error Trapping", 0
   
   ' start with a clean file built from the template file
   
   sTemplate = ap_GetConfig("NegTemplatePath") & ap_GetConfig("NegTemplate")
   
   sOutput = strOutPath & "\Price Template " & strSupplier & "-2020" & ".xlsm"
   
   If Dir(sOutput) <> "" Then Kill sOutput
   FileCopy sTemplate, sOutput
   
   ' Create the Excel Applicaiton, Workbook and Worksheet and Database object
   Set appExcel = Excel.application
   Set wbk = appExcel.Workbooks.Open(sOutput)
   wbk.Sheets("Price List").Visible = xlSheetVisible
   Set wks = appExcel.Worksheets("Price List")
   wks.UnProtect Password:="irishstout"
   wks.Cells(1, 2) = strSupplier

Hopefully someone has run into something similar?

Upvotes: 0

Views: 579

Answers (1)

Parfait
Parfait

Reputation: 107567

In MS Office object model, all methods, properties, and events follow a hierarchical structure. Usually, the Application object usually initializes top level items. All others would derive from its descendants. Therefore, the Excel.Application would not contain properties for worksheets or ranges. See examples below.

Excel

  • Methods: Application > Workbooks > Add / Open ...

  • Properties: Application > Workbook > Worksheets / Queries / Path ...

  • Events: Application > Workbook > Activate / BeforeClose ...

    Set appExcel = New Excel.Application
Set wbk = appExcel.Workbooks.Open("C:\Path\MyWorkbook.xlsx")
Set wks = wbk.Worksheets("My Sheet")

wks.Name = "My New Name"

Word

  • Methods: Application > Add / Open ...

  • Properties: Application > Document > Paragraphs / Table / Bookmarks ...

    Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Open("C:\Path\MyDocument.docx")
Set wdPara = wdDoc.Paragraphs(1)

wdDoc.Tables.Add(NumRows=3, NumColumns=3)

Access

  • Methods:

    • Application > OpenCurrentDatabase > CurrentDb ...
    • Application > DoCmd > OpenForm / OpenReport ...

  • Properties:

    • Application > CurrentDb > TableDefs / QueryDefs ...
    • Application > CurrentProject > AllForms / AllReports ...

  • Events: Application > CurrentProject > AllForms > Form > AfterUpdate / BeforeClose ...

    Set appAccess = New Access.Application
Set accFile = appAccess.OpenCurrentDatabase("C:\Path\MyDatabase.accdb")
Set db = accFile.CurrentDb()

appAccess.DoCmd.OpenForm("My Form")

Upvotes: 1

Related Questions