Access loses 1 row and 1 col when importing spreadsheet

  • Thread starter Thread starter Douglas J. Steele
  • Start date Start date
D

Douglas J. Steele

One of the parameters in the TransferSpreadsheet method is "hasfieldnames":
set it to False if you want the first row to be treated as data, rather than
field names. If you know the actual size, you can specify a range to import,
rather than letting Access guess.
 
We've been using a macro to import Excel spreadsheets into an access 2002
database. After 5 months of this we found an error on a report. It turns out
that access is ignoring the last row and last column of the spreadsheet. If
the spreadsheet has 12 columns and 1000 rows the table will have 11 columns
and 998 rows of data (one row is converted to field names). This happens
when using Transferspreadsheet in a macro and in VBA. The same result is
produced when using the wizard to get external data. We also discovered that
if we open the spreadsheet and save it (even without making a change) the
missing row and column will be imported correctly. By
the way, the spreadsheets come via internet from a third party. We have no
control over what they use to create it. We'd greatly appreciate any ideas.
Thanks.



We're running Access 2002 SP3 Excel 2002 SP3 VB 6.3
 
I can think of one possible cause. Let's test it:

1) Take one of the workbooks in the state in which it reaches you, open
it in Excel, and if necessary switch to the problematic worksheet.

2) Hit Alt+F11 to open the VBA editor, and in the editor hit Ctrl+G to
switch to the immediate pane.

3) Type
Activeworkbook.Activesheet.UsedRange.Select
hit Enter to execute the statement.

4) Switch back to the main Excel window.

My hypothesis is that the last row and last column will not be in the
selected area. If this is the case, it means that the system that is
creating the workbook is doing so in a way that causes or allows the
sheet's UsedRange property to get out of step with the actual range
occupied by the table.

If that's the problem, the solution is to modify the other system: if
its creators care about quality and data integrity they will be happy to
do so.

Meanwhile, the workround is to open and save the workbook before
importing (either manually or under program control).
 
sounded great but it didn't work.all rows and columns were selected
But Thanks. I appreciate that you took the time to respond.f you have
anyother ideas I'd love to hear them
 
The fact that the data imports properly after you've opened and saved
the workbook makes it almost certain that the problem is in the way the
other system creates the workbook. So I'd take it up with the owners of
the other system.

Here's a little VBA procedure that opens a workbook, does something
innocuous to it, and saves it. Maybe if you call this before
TransferSpreadsheet it will work round the problem for you.


Sub OpenAndSaveWorkbook(FileName As String)
Dim oBook As Object 'Excel.Workbook

'Open workbook
Set oBook = GetObject(FileName)
'Do something that will make Excel think a save is needed
With oBook.Worksheets(1).Cells(1, 1)
.Font.Name = .Font.Name
End With
oBook.Close True
Set oBook = Nothing
End Sub


sounded great but it didn't work.all rows and columns were selected
But Thanks. I appreciate that you took the time to respond.f you have
anyother ideas I'd love to hear them
Click to expand...
 
Back
Top