0
Private Sub TransferDataToAsccess()
    Dim strXlPathFile As String, strXlFile As String, strXlPath As String
    Dim strAccessTable As String
    Dim blnHasFieldNames As Boolean

    'Change this next line to True if the first row in EXCEL worksheet has field names

    blnHasFieldNames = True

    'Replace C: \Documents\ with the real path to the folder that contains the EXCEL files 

    strXlPath = ThisWorkbook.Path & "\"

    'Replace tablename with the real name of the table into which the data are to be imported

    strAccessTable = "Tabell1"
    strXlFile = Dir(strXlPath & "*.xlsm")

    ' Transfer data from Excel to Access

    Do While Len(strXlFile) > 0

        strXlPathFile = strXlPath & strXlFile
        DoCmd.TransferSpreadsheet(acImport, acSpreadsheetTypeExcel9, strAccessTable, strXlPathFile, blnHasFieldNames)
        strXlFile = Dir()

    Loop

End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
Hacan
  • 1
  • Is the posted code the code you use right now? – Storax Mar 06 '22 at 15:21
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. – Community Mar 06 '22 at 20:24
  • If you want user to select folder path, use FileSystemObject dialog. – June7 Mar 06 '22 at 20:33
  • You want code behind Excel? Excel VBA does not have DoCmd.TransferSpreadsheet. Apparently, you are trying to use Access VBA code in Excel. Why don't you have code behind Access and import data (which is what the original code you copied is intended for)? http://accessmvp.com/KDSnell/EXCEL_MainPage.htm Why is Excel even involved? – June7 Mar 06 '22 at 22:56
  • If you want Excel to push data using TransferSpreadsheet, need to declare and set an Access object https://stackoverflow.com/questions/16161865/using-excel-vba-to-export-data-to-ms-access-table – June7 Mar 06 '22 at 23:07

0 Answers0