First record is always blank

[Guest]

Hi,

I have 3 tables and I populate each by importing 3 spreadsheets. The 3
spreadsheets are basically set up the same way and on all 3 spreadsheets the
1st row contains column headings. The spreadsheets are created via VBA by
extracting data from a series of formatted spreadsheets that are submitted
from different divisions (budgeting data) to new spreadsheet files that are
saved/closed and then imported.

For some reason with one of the tables (same table every time) when I import
the corresponding spreadsheet using either the Get External Data - Import
option from the toolbar or the TransferSpreadsheet method in VBA the first
row is always a blank record.

I have examined the spreadsheet which always give me the blank record and
compared it to the other two spreadsheets that import correctly and I can see
no reason why this is happening. As I said all 3 spreadsheets are created
the exact same way and the VBA code is the same other that what cells are
referenced. I have tried recreating the Access table from scratch to see if
there was something goofy with the table but that wasn’t the problem either.

If anybody has any ideas and can point me in the right direction to solving
this problem it would be greatly appreciated.

Thanks,

J.A.

 

[Guest]

Problem solved!! Thanks Anyway

 

[John Nurick]

Hi JA,

I assume there are no blank rows within the data in the problematic
worksheet. But here's something else that could account for it:

Excel has a concept of a UsedRange, basically a rectangular area in a
worksheet starting (usually) at A1 and including every cell that's ever
had data in it. When you import a table, Access by default imports the
entire UsedRange. But if data has been deleted from cells at the bottom
of the table on the worksheet, the UsedRange is not necessarily updated
- and in that situation Access will try to import the empty row(s).

To reset the UsedRange you need to delete the actual cells (or rows or
columns) that contain (or once contained) data. It's not enough to clear
them (delete the data they contain). Alternatively you can explicitly
adjust the UsedRange in Excel VBA.

But if this is the source of your empty records, it may mean that
there's a problem with the system that's creating the worksheets - which
case may affect the integrity of your data.

 

[John Nurick]

What did it turn out to be?

Problem solved!! Thanks Anyway