DoCmd.TransferSpreadsheet acSpreadsheet

Question

Hello,

I have used this code several times, but this time I am importing one spreadsheet from a workbook (containing 3 spreadsheets).  The worksheet name is "Import File", in a workbook called "Premium Accounting CD Transaction Report.xls"

Below is my code, can you please tell me where I would add the worksheet name:?

DoCmd.TransferSpreadsheet , acSpreadsheet, "tblImportPriorDayClaimsTransactions", "\DFS01\Shared\CFA\Vol5\Treasure\Cash_Mgr\1- Treasury Operations\4- Database\PBFS Reports\Import\Premium Accounting CD Transaction Report.xls", True

Thank you!

VickiC

Answer 1

Looks like you are missing something. The sysntax for the TransferSpreadsheet method is:

TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)

So you have a , after command which can throw things off. Try:

DoCmd.TransferSpreadsheet acImport, 8,"tblImportPriorDayClaimsTransactions", "\\DFS01\Shared\CFA\Vol5\Treasure\Cash_Mgr\1- Treasury Operations\4- Database\PBFS Reports\Import\Premium Accounting CD Transaction Report.xls", True, "Import File!"

TransferType = acImport

SpreadsheetType = 8  (or acSpreadsheetTypeExcel9 for Excel 200-2003)

TableName: tblImportPriorDayClaimsTransactions

Filename: ...\Premium Accounting CD Transaction Report.xls

HasFieldnames: True

Range: Import File

Use OA, not supported

Answer 2

Hi,

While searching other forums on the web, I found some additional code to try.  Here is what I have now:

DoCmd.TransferSpreadsheet , acImport, "tblImportPriorDayClaimsTransactions", "\\DFS01\Shared\CFA\Vol5\Treasure\Cash_Mgr\1- Treasury Operations\4- Database\PBFS Reports\Import\Premium Accounting CD Transaction Report.xls", , "Import File!", True

This looks like it wants to work, but now I am getting the following error message:

Run-time error '2391':

Field 'F1' doesn't exist in destination table

'tblImportPriorDayClaimsTransactions'

My spreadsheet range is columns A to E, and my table has five columns as well.  Any thoughts?

Thanks for any assistance!!

Vicki C

Answer 3

I was thinking that, if the constant was not recognized it would default to acImport so that's why it might have worked. But doesn't explain why it stopped working.

Answer 4

The TransferType argument is actually optional, Scott, with a default of acImport, so provided that TransferSpreadsheet is followed by a comma it's not necessary to include it.

I know very little of Excel, however, so am not sure what the answer to Vicki's question is.  The TransferSpreadsheet method does have a Range argument, which can take a range name, but whether it also takes a worksheet name I can't say.

Answer 5

Don't know that that ever worked. The first argument for TransferSpreadsheet is whether its export, import or link, so the correct constant should be acImport.