Import Excel file in Database using VBA (learning purpose)

[SachAccess]

Hi,

I am trying to import an Excel file in Database using VBA. I have copied code from below URL. Code is working fine.
However have few doubts about this code.

Access - Import Data From Excel Spreadsheet Into Access Database With DoCmd | VBAmacros.net

While importing data, first row after the table field is blank, not able to understand the reason.
Also, data is not imported in the same serial order as it is.
Please note, just trying to learn, please help only if you get time.

'https://vbamacros.net/macros/access-import-data-from-excel-spreadsheet-into-access-database-with-docmd
Option Compare Database
Private Sub Select_File_Click()
    ' Set variables
    Dim strTableName As String
    Dim strFileName As String
    Dim blnHasHeadings As Boolean
   
    'Set data
    strTableName = "tblCustomers"
    strFileName = "Y:\MS Access\Dummy_Data.xlsb"
    blnHasHeadings = True
    ' Import data
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, strTableName, strFileName, blnHasHeadings
    MsgBox "Excel file imported!", vbInformation
End Sub

 

[theDBguy]

Hi. To help us understand the result you're describing, please consider posting a sample Excel file that shows that behavior when you use this code.

 

[SachAccess]

Hi @theDBguy thanks for the help. Posting is not allowed here, apologies.
Post importing.
First row is fields (column headers from Excel)
Second row is blank.
From third row of DB, entire excel worksheet is imported correctly but not in original serial order.
Am not able to understand why first row is coming as blank while importing.

 

[CuriousGeo]

The reason your first row in your imported data is blank is because in your Excel spreadsheet, you say "Second row is blank". The import VBA is importing as it should. If you don't want the blank row, either delete it in the spreadsheet, or build into your import code not to import blanks or nulls.

 

[The_Doc_Man]

From third row of DB, entire excel worksheet is imported correctly but not in original serial order.

When you do the import into Access, is a primary key defined? If so, the datasheet view of the table might actually be an internal form that will honor the key if one is defined. (In which case, the table isn't sorted - but that implicit form IS sorted.) Does it look like the data got sorted based on the key?

If there is no such key, then I'm not sure why the order would change. Can you tell whether a sort occurred based on a field within the table/worksheet?

 

[Pat Hartman]

What you are looking at in the Excel window is not necessarily how the data is stored in the file and your import from Excel would be referencing the file, not the opened spreadsheet. It is probable that at some point AFTER the data was entered, someone sorted it so you see the sorted version in Excel but in Access, you get the raw data.

If order is important, add a sequence number to the Excel file and import that also. You can sort on it once the data is imported.

 

[SachAccess]

Hi,

Thanks everyone for the help. It seems there was some issue with the Excel file.
It seems invisible records were present in Excel. Cleared the blank rows in Excel and re-tried, now the code is working fine.

Private Sub Select_File_Click()
    Dim dlg As FileDialog
    Set dlg = Application.FileDialog(msoFileDialogFilePicker)
    
    '+ Delete if table is already present in DB
    tableName = "MyImportedExcel"
    If Not IsNull(DLookup("Name", "MSysObjects", "Name='" & tableName & "'")) Then
        DoCmd.SetWarnings False
        DoCmd.Close acTable, tableName, acSaveYes
        DoCmd.DeleteObject acTable = acDefault, tableName
        DoCmd.SetWarnings True
    End If
    '= Delete if table is already present in DB
    
    With dlg
        .Title = "Select the Excel file to import"
        .AllowMultiSelect = False
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xlsb", 1
        .Filters.Add "All Files", "*.*", 2
        
        If .Show = -1 Then
            strFileName = .SelectedItems(1)
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "MyImportedExcel", strFileName, True
        Else
            Exit Sub
        End If
    End With
End Sub

 

[theDBguy]

Hi. Glad to hear you got it sorted out. That is why I asked for a sample Excel file. I suspected the problem could be in there.

 

[Pat Hartman]

It seems invisible records were present in Excel.

Excel remembers. It remembers because it cannot determine which empty rows you want to keep for formatting and which are no longer needed.

You can control this easily using good practice.

When you select a bunch of rows in Excel and press the delete key, you are telling Excel to keep the rows but remove the contents.
When you select a bunch of rows in Excel and select delete from the ribbon, you are telling Excel to delete the rows as well as the contents.

For those who import from Excel, you frequently run into these phantom rows because the people who use Excel like separating their data and they don't understand the effect this blank stuff has on Access so the Access people have to protect themselves.

I do it by not importing directly from Excel to a permanent table. I link to Excel and then run an append query. That allows me to ignore the blank rows as well as do a little cleanup and validation on the way in.

 

[SachAccess]

Hi @Pat Hartman sir thanks a lot for the help. Was away hence delayed reply. Apologies.

 

[standenman]

Excel remembers. It remembers because it cannot determine which empty rows you want to keep for formatting and which are no longer needed.

You can control this easily using good practice.

When you select a bunch of rows in Excel and press the delete key, you are telling Excel to keep the rows but remove the contents.
When you select a bunch of rows in Excel and select delete from the ribbon, you are telling Excel to delete the rows as well as the contents.

For those who import from Excel, you frequently run into these phantom rows because the people who use Excel like separating their data and they don't understand the effect this blank stuff has on Access so the Access people have to protect themselves.

I do it by not importing directly from Excel to a permanent table. I link to Excel and then run an append query. That allows me to ignore the blank rows as well as do a little cleanup and validation on the way in.

Could you explain a little more how you link to an excel worksheet in Access rather than import to temp table?

 

[Pat Hartman]

The command to link is the same as the command to import. The difference is one of the arguments. Look up TransferSpreadsheet for all the options.

Just FYI, linking won't solve the phantom row/column issue so you would need to ignore those rows in your append query that transfers the data to the permanent table.

Also a warning, the option suffixed with "Xml" is the option to choose for the latest version of Excel. "True" says the sheet has column headers. It is best to just read the help entry so you understand better what the options are.

docmd.TransferSpreadsheet acLink,acSpreadsheetTypeExcel12Xml,"tablename", "filename", True

 

[standenman]

I see. Thank you Pat.