TRANSFERSPREADSHEET DATA TO ACCESS TABLE

[Lyda_Faye]

I am using the TransferSpreadsheet command in a macro. I have 25 fields in my EXCEL spreadsheet and 25 fields in my ACCESS table with fieldnames. The spreadsheet is to be transferred to the table. But the error message states that there is a field that does not exists in the table called 'F26'.
NO, it does not exists because I don't have 26 fields.

Please tell me why I keep getting this message:

"...Field 'F26' doesn't exists in destination table."


Here's my statement:

Import, 8, "ODATA", "H:\LYDIA\OSHA\TEST_OSHA\OSHA_REPORTS.xls", Yes

I want to specify a range name but not sure how to do that without getting an error. There is a named range (LASTUSED) in my spreadsheet that contains the used range in the spreadsheet but I don't know how to use it in my ACCESS statement. I tried ODATA!LASTUSED for the

#VALUE!

 

[LTunnicliffe]

Sounds silly but verify that the named range "ODATA" is only 25 fileds (columns) big.

From there I don't know.

LT

 

[Ziggy]

Your access table probably has an ID field giving 26 fields

 

[Dugantrain]

Hey, I just realized that I'm now a "Board Master"! Awesome! Anyway, I had a similar problem, except mine was "F1". So to get around this, I just created a temp table in my database, named each field F1, F2, F3, etc. and then it transferred fine. After the transfer, I'd run an Append Query to get it into the table that it was supposed to be in. Pretty barbaric way of doing it, but I was at my wit's end with TransferSpreadsheet and was just ready to get the thing working.

*Edit* After running the Append Query, I'd run a Delete Query to remove the data out of the Temp Table. I called all of these procedures in one command button and used docmd.set warning false so that it would do all of this in one big swoop.

 

[Lyda_Faye]

Thank you. This is what I did:

Dim oApp As Object
Set oApp = CreateObject("Excel.Application")
oApp.Workbooks.Open Filename:="H:\DIR1\OSHA\TEST_OSHA\OSHA_REPORTS.xls"
oApp.Application.Visible = True
wsRange = oApp.Worksheets("ODATA").Cells(2, 25).Value

DoCmd.TransferSpreadsheet acImport, 8, _
"ODATA", "H:\LYDIA\OSHA\TEST_OSHA\OSHA_REPORTS.xls", True, wsRange


This opens the worksheet then pulls the cell that contains the range in it. The range is then moved to wsRange. wsRange is used in the syntax for the transfer. iT WORKS!

 

[Dugantrain]

That looks like pretty good code there. I think I might start going with something like this instead of using temp tables all of the time (all of my projects come directly from Excel Hell, so finding better ways of importing/exporting denormalized data is always a huge plus).