cannot reference sheet name when importing excel

[kobiashi]

hi

so im trying to import an excel sheet into access, i want to be able to select the sheet and range, i have tried various different ways to reference the sheet but i just cannot get it to work.

does anybody have any suggestions please

Private Sub btnCreateInServiceIssues_Click()
'On Error GoTo btnCreateInServiceIssues_Click_Err

    Dim SheetName As String
    Dim TableName As String
   ' On Error GoTo ErrorHandler
    'disable ms access warnings
    
    SheetName = "Sheet(8)!A2:H15"
    TableName = "Table_RunIns"
    DoCmd.SetWarnings False
    
    DoCmd.RunSQL "DELETE * FROM Table_RunIns"

    'load spreadsheet
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, TableName, selectFile(), SheetName, True
    're-enable ms access warnings
    DoCmd.SetWarnings True

here is my code so far, the sheet is always in the same location in the workbook, but the name has spaces in it, and im not sure if this is effecting the code,

so the error i get when it runs is

run time error 2498

an expression you entered is the wrong data type for one of the arguments.

ive removed the variable "SheetName" from the argument, and it works, but i dont know how else to reference the sheet name

ive tried "('mf020118 Run In')!A2:h15"
"'mf020118 Run In'!A2:H15"

none of them work

 

[CJ_London]

try putting your parameters in the right order - hasfieldnames comes before range, not after as you have it

Also advise to disable warnings until you have the code working as required

 

[Ranman256]

sheet name is the last parameter:

docmd.TransferSpreadsheet acExport ,acSpreadsheetTypeExcel12,vQry, vFile, true,vSheetName

 

[kobiashi]

yeh i spotted that but still not luck

i think it may have something to do with the name of the sheet, as it has spaces in it, but as it is not my sheet, i cannot change the name of the sheet, i have tried referencing the sheet as "Sheets(8), but still no joy.

my other option, but im not usre how to achieve it, is rename the sheet in the run time

 

[CJ_London]

I've just tried with a space in the sheet name, worked no problem.

However I don't think you can refer to a sheet by it's index if that is what you are trying to do

 

[kobiashi]

ok, please can you show me how you referenced it,

i did it like this

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, TableName, selectFile(), False, "'mf020118 Run In'!A2:J50"

 

[CJ_London]

other than hardcoding the table name and path/file name, exactly as you have it - but I used the equivalent of "mf020118 Run In!A2:J50" (did not use single quotes)

Have you enabled setwarnings to see the error messages

 

[kobiashi]

what a dumbass i am, that must have been the only way i didnt try