Previous Thread

8/18/2006 9:01:51 AM    First record is always blank
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.



8/18/2006 6:06:30 PM    Re: First record is always blank
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. 
 
On Fri, 18 Aug 2006 09:01:51 -0700, jAdams 
 
<jAdams@discussions.microsoft.com> wrote: 
 
-- 
 
John Nurick [Microsoft Access MVP] 
 
Please respond in the newgroup and not by email.