Import Excel Files

[davidjearly]

Hi,

I am trying to append the data I have stored in Excel format into an Access table using the following code:

Private Sub Command0_Click()
  If MsgBox("This will open the Excel folder for spreadsheet imports.  Continue?", vbYesNoCancel) = vbYes Then
Dim i As Integer
  Dim tblStr As String
    Dim varItem As Variant
i = 1
tblStr = "Patients"
      With Application.FileDialog(msoFileDialogFilePicker)
         With .Filters
           .Clear
           .Add "All Files", "*.*"
         End With
             .AllowMultiSelect = True
             .InitialFileName = "c:\"
             .InitialView = msoFileDialogViewDetails
                    If .Show Then
                      For Each varItem In .SelectedItems
                        For i = 1 To Len(varItem)
                          If IsNumeric(Mid(CStr(varItem), i, 1)) Then
                            tblStr = tblStr & Mid(CStr(varItem), i, 1)
                          End If
                        Next i
                        If Right(CStr(varItem), 4) = ".xls" Then
                          DoCmd.TransferSpreadsheet acImport, , tblStr, CStr(varItem), True
                            i = i + 1
                              DoCmd.OpenTable tblStr, acViewNormal, acReadOnly
                                MsgBox "Data Transferred Successfully!"
                                  DoCmd.Close
                                    tblStr = ""
                        End If
                      Next varItem
                    DoCmd.Close
                    End If
      End With
  End If
End Sub

The table and excel file have the same column structure, but when I import the records, hundreds of blank rows are being randomly inserted between the records.

Any ideas?

Thanks!

 

[namliam]

They are not random blank rows... The DoCmd.TransferSpreadsheet will pick up anything that excel thinks (may) contain(s) data.

Simply remove the rows from excel and you will not find them in Access...
The reason for this is that someone has been "down there" editing data at some point or has copied over an older spreadsheet "clear contents" or something simular leaving a remnent of "there was data there" in excel.

Not random, perfectly normal... Either remove the rows or Delete any rows with all nulls in access...

I hoped that cleared your worries...

 

[davidjearly]

Thanks for your reply, but that does not help.

They are random blank rows.

My datafile in Excel has no blank rows or formatting and I have made sure of this. No one uses that file other than myself, and I am 100% sure of this.

Going in to the Access table and deleting all the blank rows is not an option. This has to be user friendly.

 

[DCrake]

Have you tried to do it manually from the Get External Data > import?

Do you still get blank rows?

can you post a copy of the spreadsheet?

David

 

[namliam]

Thanks for your reply, but that does not help.

They are random blank rows.

My datafile in Excel has no blank rows or formatting and I have made sure of this. No one uses that file other than myself, and I am 100% sure of this.

Going in to the Access table and deleting all the blank rows is not an option. This has to be user friendly.

Sorry but ... wrong...

Have you tried removing all the rows between your data and the last row in Excel?? This is the exact thing i have had wonderings about... Removing the empty rows to reset Excel (delete row) has worked for me everytime...

 

[greebo]

I have an application which imports excel files submitted from wordwide sources. although the format is supposed to be fixed, people play with them so you can never be sure...

I always import each file into a new table (standard name) then run an access query to delete the unused lines, move the data to the final destination then delete the import table. Using a standard name for the import table makes life easier.

This app is run hands free by windows scheduler and is almost bug free - I currently have an open thread on formatting excel columns before the import!

 

[namliam]

Format's do nothign in excel... when transfering data thru it... Format is a display thing, doesnt change a value! Or the look that value gets in Access...