Extra fields using TransferSpreadsheet

[dgmoore]

I have a table in Access03 that I populate using the
TransferSpreadsheet method. This is a repeating process - I clear the
table and then repopulate from a particular spreadsheet using
TransferSpreadsheet. Fairly frequently I get an error "No field
F[number] in table" and the process halts.

The field number in the error message varies from F22 to F30 or so on
different attempts, but the kicker is this: there is no data in any
spreadsheet column beyond 20. Does anybody know where this might be
coming from?

Thanks
Dave

 

[tina]

Excel has an annoying way of "seeing" columns and/or rows that were used at
one point, in a worksheet, but no longer. i have no idea why this happens.
try opening your Excel file, go to the worksheet in question, press
Ctrl+Home to take you to the top/left cell in the sheet, then press Ctrl+End
to take you to what Excel sees as the bottom/right cell in the sheet. if the
active cell is further out than column 20 (that would be column T, i think),
there's your culprit. try moving to the "real" bottom/right cell in the
sheet, then pressing and holding the Shift key while you press Ctrl+Home.
this will highlight the section so you can copy/paste it into a new, blank
worksheet - which should get rid of the "extra" columns.

hth

 

[John Nurick]

It happens because Excel by default attempts to import the worksheet's
UsedRange, which is (loosely speaking) the rectangle from A1 to the last
cell that contains data, or that has been given a cell format. See
http://www.contextures.on.ca/xlfaqApp.html#Unused for more, including
how to reset the UsedRange.

Alternatively you can (in Excel) define the table you want to import as
a named range, or (in Access) pass the actual range you want as the
Range argument of TransferSpreadsheet, e.g. "Sheet1$A1:F100".

Excel has an annoying way of "seeing" columns and/or rows that were used at
one point, in a worksheet, but no longer. i have no idea why this happens.
try opening your Excel file, go to the worksheet in question, press
Ctrl+Home to take you to the top/left cell in the sheet, then press Ctrl+End
to take you to what Excel sees as the bottom/right cell in the sheet. if the
active cell is further out than column 20 (that would be column T, i think),
there's your culprit. try moving to the "real" bottom/right cell in the
sheet, then pressing and holding the Shift key while you press Ctrl+Home.
this will highlight the section so you can copy/paste it into a new, blank
worksheet - which should get rid of the "extra" columns.

hth

I have a table in Access03 that I populate using the
TransferSpreadsheet method. This is a repeating process - I clear the
table and then repopulate from a particular spreadsheet using
TransferSpreadsheet. Fairly frequently I get an error "No field
F[number] in table" and the process halts.

The field number in the error message varies from F22 to F30 or so on
different attempts, but the kicker is this: there is no data in any
spreadsheet column beyond 20. Does anybody know where this might be
coming from?

Thanks
Dave

 

[John Nurick]

Corrected version of my previous message:

It happens because *Access* by default attempts to import the
worksheet's UsedRange, which is (loosely speaking) the rectangle from A1
to the last cell that contains data, or that has been given a cell
format. See http://www.contextures.on.ca/xlfaqApp.html#Unused for more,
including how to reset the UsedRange.

Alternatively you can (in Excel) define the table you want to import as
a named range, or (in Access) pass the actual range you want as the
Range argument of TransferSpreadsheet, e.g. "Sheet1$A1:F100".

Excel has an annoying way of "seeing" columns and/or rows that were used at
one point, in a worksheet, but no longer. i have no idea why this happens.
try opening your Excel file, go to the worksheet in question, press
Ctrl+Home to take you to the top/left cell in the sheet, then press Ctrl+End
to take you to what Excel sees as the bottom/right cell in the sheet. if the
active cell is further out than column 20 (that would be column T, i think),
there's your culprit. try moving to the "real" bottom/right cell in the
sheet, then pressing and holding the Shift key while you press Ctrl+Home.
this will highlight the section so you can copy/paste it into a new, blank
worksheet - which should get rid of the "extra" columns.

hth

I have a table in Access03 that I populate using the
TransferSpreadsheet method. This is a repeating process - I clear the
table and then repopulate from a particular spreadsheet using
TransferSpreadsheet. Fairly frequently I get an error "No field
F[number] in table" and the process halts.

The field number in the error message varies from F22 to F30 or so on
different attempts, but the kicker is this: there is no data in any
spreadsheet column beyond 20. Does anybody know where this might be
coming from?

Thanks
Dave

 

[dgmoore]

Great answers! Thanks to both Tina and John.

Dave

 

[tina]

you're welcome