CODEWITHSUNDEEP
excelvbams-access
Cyril Gonsalves
Cyril Gonsalves

Reputation: 43

Run time error 424 object required docmd transfersheet import

I am facing this issue with below code:

    Dim strPathFile As String, strFile As String, strPath As String
    Dim strTable As String
    Dim blnHasFieldNames As Boolean
    blnHasFieldNames = True

    strPath = "C:\Report\ImportFile\"
    strTable = "Ex_Table"

    strFile = Dir(strPath & "*.xlsx")

          strPathFile = strPath & strFile
          Debug.Print strPathFile
          DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
                strTable, strPathFile, blnHasFieldNames

All I am trying to do it to import a Sheet in Sample.xlsx in access database. I went through many posts but couldn't find the answer which will resolve this issue.

I am using Excel 2016 and Access 2016.

Access DB Path & Name - C:\Report\Database\dbname.accdb

Upvotes: 1

Views: 361

Answers (1)

user4039065
user4039065

Reputation:

You're trying to work on an XLSX workbook but acSpreadsheetTypeExcel9 (resolved as 8) is the Microsoft Excel 2000 format specifier (XLS). An .XLSX workbook requires either acSpreadsheetTypeExcel12 (e.g. 9) for Microsoft Excel 2010 format or more preferably the acSpreadsheetTypeExcel12Xml (e.g. 10) for Microsoft Excel 2010/2013/2016 XML format (.xlsx, .xlsm, .xlsb) workbooks.

AcSpreadSheetType enumeration

Upvotes: 1

Related Questions