Error using TransferSpreadsheet to import an Excel spreadsheet

0
votes

I have a routine that helps me locate a the row of word in a standardized worksheet. Based on the location of the word - for example, I search in column 'A' It finds the word on row 7.

I now know that I can use the range A8:M14 as the data I want to import into my table, so I created a function 'GETBASELINE' that would just return that string - "A8:M14"

So now I have a table called tbl_TEMP_Import with these fields

BASELINE|OCT|NOV|DEC|JAN|FEB|MAR|APR|MAY|JUN|JUL|AUG|SEP

I call it like so:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tbl_TEMP_Import", strPath, False, GetBASELINE(strPath, strSheet)

I get Error 2391 Field 'F1' doesn't exist in destination tbl_TEMP_Import

If I change it to 'True' for 'has field names' I get Error 3270 'Property Not Found'

I wish I could get better debugging. Doesn't seem too complex to accomplish this.

1
excelms-accessvba

1 Answers

0
votes

Answer with 'True' for 'has field names': You have to give the table the field heading as well... so the code for GETBASELINE must equal "A7:M8" ... (I'm not sure where 14 came from)

A7:M7 are Field names A8:M8 are 1st set of values

Answer with 'False' for 'has Field names': You must change the field names, because the call brings in temp field names, F1,F2 and so on Manually you can change the names. It is programmable as well.

In any event, once you call the script one way, be careful not to 'mix' them with each other! Delete one before you call the next