Import Excel file in Database using VBA (learning purpose) (2 Viewers)

[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?

 

[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.

 

[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?

 

[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.

 

[MarkK]

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

 

[tomasm]

Thanks for the reply and I hope you are doing well today.

I am trying to think of why I decided to save a linked table object and use .Connect and .RefreshLink to change the source instead of using TransferSpreadsheet. But I can't remember the reason.

It could be that I was seeing different ways of checking if a table exists before attempting to delete it (like post #7 above) and decided to use .Connect and .RefreshLink to avoid that. But I don't need to check if the table exists, and I want Access to raise an error and stop running code if somehow the temporary linked table doesn't exist when the delete line executes. And if somehow the delete fails, and the next time the sub runs and TransferSpreadsheet creates "tempLinkedTable1", that's fine too because I'll get an error on the append query.

This is working fine so far with my tests today:

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12Xml, "tempLinkedTable", varFile, True
'run append query and other things here
Db.TableDefs.Delete "tempLinkedTable"

 

[tomasm]

Again, all you need to do is to always use TransferSpreadsheet to link to the same named table. Then your queries won't break. You can first check the linked table using code that has an error trap so you can suppress the error message and just delete the link if it already exists. The reason for leaving the link table between uses is so that Access won't break your queries by changing all the column names to Expr1, Expr2, etc if you C&R while the link is missing.

If I leave the linked table saved in Access then I'll be back to my problem of Access creating blank Excel workbooks where the linked file used to be (that seems like a bug to me). The Excel files that I import come from a network folder and someone eventually moves those files. I could copy the file to a folder on my PC prior to running the import code, but I have no use for those files after I import them. And my revised code is working well so far and I'm not seeing any mysterious, blank Excel files appearing.

I did a compact and repair this morning, then ran my code and everything is fine. I'm looking at my append query now and it's still good. I think if I opened it in Design view that would break it. I avoid Design view for that reason and I'm glad Access finally allows me to go straight to SQL view from the navigation pane.

 

[The_Doc_Man]

Best wishes for recovery, Pat. And I admit to a bout of jealousy. But right now, my dear wife is recovering from her dislocated shoulder so we can't travel across the pond anytime soon anyway.

 

[Ken Sheridan]

If I leave the linked table saved in Access then I'll be back to my problem of Access creating blank Excel workbooks where the linked file used to be (that seems like a bug to me). The Excel files that I import come from a network folder and someone eventually moves those files. I could copy the file to a folder on my PC prior to running the import code, but I have no use for those files after I import them. And my revised code is working well so far and I'm not seeing any mysterious, blank Excel files appearing.

If you take a look at the DecomposerDemo file that I attached to an earlier reply in this thread, you'll see that, rather than linking to the Excel file, it imports the data from it into a predefined MasterTable, which is then decomposed into a set of predefined normalised tables. If, when selecting the Excel file in the dialogue I change its name to that of a non-existent file, this simply raises a File Not Found error, which can be handled in the code. No new empty Excel file is created. As the MasterTable table and the tables into which it is decomposed will then remain as empty tables in the Access file, the 'append' queries which carry out the decomposition are safe from any adverse consequences.

 

[tomasm]

If you take a look at the DecomposerDemo file that I attached to an earlier reply in this thread, you'll see that, rather than linking to the Excel file, it imports the data from it into a predefined MasterTable, which is then decomposed into a set of predefined normalised tables. If, when selecting the Excel file in the dialogue I change its name to that of a non-existent file, this simply raises a File Not Found error, which can be handled in the code. No new empty Excel file is created. As the MasterTable table and the tables into which it is decomposed will then remain as empty tables in the Access file, the 'append' queries which carry out the decomposition are safe from any adverse consequences.

The demo looks like it would be useful for that particular scenario where normalization is needed. In my case, my module to import from Excel is separate from the rest of my database and it's purpose is only to modify an Excel file and then export a new version of it back to Excel. I don't save any data from it or do any normalization. The module and related lookup tables and queries are really not related to the rest of my database. It's just convenient to have this Excel file modification tool included as a button on one of my forms.

I don't know when Access was creating an empty Excel file after the file was moved or deleted. Maybe on startup or compact and repair. Deleting the linked table each time solved that problem.