Avatar of Stephen Byrom
Stephen Byrom
Flag for Ireland

asked on 

Appending Excel range to Access table

Hi again,
Still struggling to get this right.

Managed to get the Excel macro to run while in Access, but can't get the correct syntax to import the "usedrange" from sheet "SageDump" to my table.
Here's the code
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim strFileName As String
Dim ImportRange As Range

strFileName = (GetMyPath() & "Interface2.xlsm")

    Set objXL = New Excel.Application
    Set objWkb = objXL.Workbooks.Open(strFileName, , False, , "password")

'run excel macro
    objXL.Run "Upload2Dbase"

'import the data from sheet named "SageDump"
     ImportRange = objWkb.Worksheets("SageDump").UsedRange
     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
     "tblDeliveriesDue", strFileName, True, ImportRange
'destroy objects
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing

Open in new window

As I say, still struggling with Object variable or With block not set.
As always, any help is much appreciated.
Microsoft AccessMicrosoft Excel

Avatar of undefined
Last Comment
Gustav Brock

8/22/2022 - Mon