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.

 

[tomasm]

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.

Can you explain a little more on how you link and append? Are you deleting the linked table after running the append query?

I have been doing this by changing the source of a linked table, then appending from that linked table to a local table. The problem with doing it this way is that once the source Excel file is moved or deleted, Access will eventually re-create that file in the same folder location with the same filename. The contents of the workbook will be blank.

Set Db = CurrentDb
Set td = Db.TableDefs("linkedTable")
td.connect = "Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=" & varFile
td.RefreshLink

 

[Ken Sheridan]

When importing from Excel, the data will rarely be structured in a way correct for Access, in which data is stored in a set of correctly normalized related tables. The attached little demo file illustrates how data imported from Excel can be recast into a correct predefined structure by executing a series of 'append' queries in a specific order. The rule of thumb is that data must be imported into the referenced (one-side) table in each one to many or one to one relationship type before data is imported into the relevant referencing (many-side) table.

The demo allows you to step through the process query by query, with a brief explanation of each stage in the process as the query is executed.

The above does presuppose that the Excel data is structured in such a way that it can be imported into a single non-normalized table in Access. If this is not the case you might need to import separate ranges independently of each other, and then decompose each imported table separately.

 

[Isaac]

I think Access will often import the entire section of Excel that corresponds with the Excel worksheet object's .UsedRange property.
So if people have put data and formatted a section and then delete data out of it, but it remains with some formatting, Access might import that and remember you have no guarantee of the sort order.

 

[CJ_London]

I think Access will often import the entire section of Excel that corresponds with the Excel worksheet object's .UsedRange property.

I agree

I tend to just use sql rather than importing to effectively a temporary table. But totally agree that if multiple tables are to be appended to, you need to start with the primary table(s) first to identify new primary keys.

 

[Pat Hartman]

I have been doing this by changing the source of a linked table, then appending from that linked table to a local table. The problem with doing it this way is that once the source Excel file is moved or deleted, Access will eventually re-create that file in the same folder location with the same filename. The contents of the workbook will be blank.

I don't use the method you used to link to the workbook. I'm pretty sure that is why you are having this problem. I usually have a dialog that allows the user to pick a specific file and then use TransferSpreadsheet to link to it. If you move the file later, the link will no longer work.

If this is a one time only effort, I will probably delete the link after I finish using it. If it is a recurring process, I leave the link so that the name of the linked table stays available and all of my later uses of this link to different files, still use the same queries because the linked table name never has to change.

Sorry to take so long but I had surgery on Friday and haven't really checked in since then. Everything is fine and I'm still exhausted but otherwise up and around.

 

[MarkK]

Hey @Pat Hartman, best wishes on a complete and speedy recovery!
Mark

 

[Pat Hartman]

Thanks Mark. The older you get the more stressful any surgery is. Even out-patient surgery is debilitating. But, the pain was never terrible. I just needed a lot of naps these past few days. My brother, who has a ton of vacation time accrued, came to spend a week with me in case I had trouble and that was awesome. Almost good enough to put the surgery out of my mind completely. So, my brother got to spend time with my daughter and granddaughters also so all of us were happy. One of my granddaughters is going to spend 3-4 months in London later this year and my brother and I decided to take a trip to visit her. That means I may get to meet some of the AWF UK members also. Uncle Gizmo will contact all the UK crowd as it gets closer to the time (probably October or November) and hopefully arrange a get-together in London if there's any interest.