docmd.transferspreadsheet

[ianclegg]

Can anybody pointme in the right direction? I need to link or import a spreadsheet into access for processing. The worksheet that I want is on the secondtab in excel and is called worksheet, the firsttab is called JobProgress.

My existing code is as follows


DoCmd.TransferSpreadsheet acImport, acspreadsheetexcel9, "TCardforinput", fgetfile, hasfieldnames = True

Thans in anticipation


Regards

Ian Clegg

 

[boblarson]

Something like this:

DoCmd.TransferSpreadsheet acImport, acspreadsheetexcel9, "TCardforinput", fgetfile, True,"'Worksheet!A1:G102"

By the way, you don't include HasFieldNames in there if you are in the spot where True goes, it is just TRUE. If you are using it as an argument without using blank commas to fill options you aren't using then it would be:

HasFieldNames:=True

But you don't need it in this case.

Also, I wouldn't have a worksheet named worksheet. That may cause some unintended side-effects with code.

 

[ianclegg]

Thanks for the prompt reply Bob,

I have amended the code as you suggested, I now get the message Microsoft jet db could not find the object spreadsheet$A1:AC85. Ensure that the object exists.

The tabs on the workbook are as follows

Job progress, spreadsheet,tma,timesheet

my code now reads

fgetfile, True, "'spreadsheet!A1:AC85"


Any clues ?

regards

Ian

 

[ianclegg]

Fixed It !

the code needed Range:="spreadsheet!A1:AA600" after true,


It now works,

Thanks for all the help & pointing me in the right direction

Regards

Ian Clegg