Wrong data type?

[Sam Hayler]

I keep getting an error message telling me

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

But it won't tell me which expression, or which argument.

However, I've isolated the problem to this line:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tblDataSefram,
xlsht, True, "xlsht!a17:f17"

And for reference, this is where xlsht comes from:

Set xlApp = CreateObject("Excel.Application")
Set xlWbk = xlApp.Workbooks.Open(Reports!rptTest![Source Data Location],
ReadOnly:=True)
Set xlsht = xlWbk.Worksheets("Sheet1")

Somebody help me please!!!

Thanks,
Sam

 

[Allen Browne]

Should the table name be in quotes?

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"tblDataSefram", "C:\MyFile.xls", True, "xlsht!a17:f17"

 

[Sam Hayler]

Maybe, but if so, that's not the only thing, because I'm still getting the
same error. I've tried everything I can think of, quotes, no quotes,
brackets... Every time I get the same error...

Should the table name be in quotes?

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"tblDataSefram", "C:\MyFile.xls", True, "xlsht!a17:f17"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I keep getting an error message telling me

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

But it won't tell me which expression, or which argument.

However, I've isolated the problem to this line:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
tblDataSefram,
xlsht, True, "xlsht!a17:f17"

And for reference, this is where xlsht comes from:

Set xlApp = CreateObject("Excel.Application")
Set xlWbk = xlApp.Workbooks.Open(Reports!rptTest![Source Data
Location],
ReadOnly:=True)
Set xlsht = xlWbk.Worksheets("Sheet1")

Somebody help me please!!!

Thanks,
Sam

 

[Sam Hayler]

Right, I've got rid of one error message only to encounter another. My code
is currently:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"tblDataSefram", strpath, True, "a17:f1558"

where strpath holds a string with the filename and path. But I now get an
error message that says:

"This table contains cells that are outside the range of cells defined in
this spreadsheet"

But I've specified the exact range of cells to import, so I don't know what
it's playing at now.

Thanks,

Sam

Should the table name be in quotes?

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"tblDataSefram", "C:\MyFile.xls", True, "xlsht!a17:f17"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I keep getting an error message telling me

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

But it won't tell me which expression, or which argument.

However, I've isolated the problem to this line:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
tblDataSefram,
xlsht, True, "xlsht!a17:f17"

And for reference, this is where xlsht comes from:

Set xlApp = CreateObject("Excel.Application")
Set xlWbk = xlApp.Workbooks.Open(Reports!rptTest![Source Data
Location],
ReadOnly:=True)
Set xlsht = xlWbk.Worksheets("Sheet1")

Somebody help me please!!!

Thanks,
Sam

 

[Allen Browne]

Not sure what the issue with the range is.

If someone else has a suggestion, please comment.