Import data from a specfic Worksheet from Excel

[ssanfu]

@daredan,



You have lots of good advice - I thought I add Ken's site....

For examples of import code, you might check out "Ken's ACCESS Examples and Information for Working With EXCEL Workbook Files"
I've used it several times to help with code for importing Excel files.

Info about Excel Spread Sheet Types
AcSpreadSheetType

[daredan]

@ssanfu: Thank you, that is surely a good source to bookmark as it covers a wide range of scenarios.

@Micron: Your code worked flawlessly on the small test database without any error messages.
On the big database (again, same principle, but more columns/rows to import) it also imports all the data (829 records), but there Access creates the following table after the import:


@CJ_London:
I tried incorporating your code into the existing one...

Private Sub cmdImportExcel_Click()
Dim fDialog As FileDialog
Dim varFile As Variant
Dim strFileName As String
Dim db As DAO.Database
Dim sqlStr As String

On Error GoTo errHandler
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

With fDialog
    .AllowMultiSelect = False
    .Filters.Clear
    .Filters.Add "Excel Spreadsheets", "*.xls, *.xlsx, *.xlsm, *.xlsb"
    If .Show Then
       strFileName = .SelectedItems(1)
       MsgBox strFileName 'disable/remove this line when OK
       'DoCmd.TransferSpreadsheet 0, 9, "tblData", strFileName, True, "TestData$"
       sqlStr = "INSERT INTO tblData (ItemID, Description, Price)" & _
        " SELECT * FROM (SELECT * FROM [TestData$] AS xlData IN '" & strFileName & "'[Excel 12.0;HDR=yes;IMEX=2;ACCDB=Yes])"
       db.Execute sqlStr

    Else
       MsgBox "Import selection was cancelled."
       Exit Sub
    End If
End With

'MsgBox "success message goes here"
MsgBox db.RecordsAffected & " Records Imported"

exitHere:
Set fDialog = Nothing
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

End Sub

...but get an error message when running it.


Also tried to keep the code together in the if...else statement to potentially trigger a different behavior, but it ended with the same error.

[Micron]

If you Dim an object variable (create a space in memory for it) then attempt to write a line that uses that object then you forgot one crucial part - you put nothing in the space/variable. It's like getting an empty bucket (akin to empty variable) and trying to water the plants with it.
SET db = CurrentDb
right before/after SET fdialog (just to keep things together)

Also un-set when done: Add Set db = Nothing after Set fdialog = nothing
You're not using varFile?

[Micron]

Re: name autocorrect failures
Don't know if that's because the name you're trying to use for an object (table/field/form/etc.) has changed or (more likely) your source data headers (or perhaps other values) contain leading spaces. I'd start with looking for leading spaces in Excel column headers.

[CJ_London]

...but get an error message when running it.

my bad, you need to include

set db=currentdb

before you execute

and this really needs to go just after

'MsgBox "success message goes here"
MsgBox db.RecordsAffected & " Records Imported"

because you would get an error if the sql was not executed because the .show has been cancelled

edit: recommend for debugging you put debug.print sqlStr after you have created it.

Then you can copy the string from the immediate window to the sql window of a new query - then run it to make sure it works as intended

[daredan]

Thanks a lot to you all, especially @Micron and @CJ_London. Without your guidance I would still have to redirect my DB users to the 'External Data / New data source / From File / Excel' menu entry.

@Micron: Yes, I must have had some leading/trailing/excessive spaces in the headers. After cleaning that part up in Excel, no further autocorrect failure was produced.
@Micron & @CJ_London: Now either of the solution approaches is working. I kept CJ's active, as this gives a confirmation to the user on how many records were imported.

This is the final code for my production database:

Private Sub btnImportData_Click()

Dim fDialog As FileDialog
Dim strFileName As String
Dim db As DAO.Database
Dim sqlStr As String

On Error GoTo errHandler
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
Set db = CurrentDb

With fDialog
    .AllowMultiSelect = False
    .Filters.Clear
    .Filters.Add "Excel Spreadsheets", "*.xls, *.xlsx, *.xlsm, *.xlsb"
    If .Show Then
       strFileName = .SelectedItems(1)
       MsgBox strFileName
       sqlStr = "INSERT INTO tblTransaction (YearMo, Entity, Acct, ActDKK, FcDKK, ActLocCur, FcLocCur, Comment)" & _
        " SELECT * FROM (SELECT * FROM [DB$] AS xlData IN '" & strFileName & "'[Excel 12.0;HDR=yes;IMEX=2;ACCDB=Yes])"
       db.Execute sqlStr
       MsgBox db.RecordsAffected & " Records Imported"
    Else
       MsgBox "Import selection was cancelled."
       Exit Sub
    End If
End With

exitHere:
Set fDialog = Nothing
Set db = Nothing
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

End Sub

[Micron]

The appreciation is nice, but why are you still declaring the variant and not using it?

you would get an error if the sql was not executed because the .show has been cancelled

The exit sub/function statement prevents that?

[CJ_London]

jut be aware that your code will fail on this line if the user does not select a file

MsgBox db.RecordsAffected & " Records Imported"

it should be just after


db.Execute sqlStr

and before the Else

[daredan]

@Micron: Sorry, missed that part. This one goes back to the legacy source script. As it has no use, I simply deleted it from the script and updated the related final code above.
@CJ_London: Thanks, I have amended it now.

[daredan]

Sorry (and not sure if this is the correct way) to open up a closed thread, but a little follow-up question has derived during introducing this to my team.

Would it be simple to allow multi-selection and therefore multi-import files into the database?

I've tried to simply amend this part...

.AllowMultiSelect = True

...which will allow multi-selection, however still only one file will be imported.

[Micron]

Follow the example here?
https://docs.microsoft.com/en-us/off...ion.filedialog

Basically, add varFile to declarations section and loop over selected items and execute code for each as required.

[daredan]

Thank you Micron.
I tried incorporating the code, but it gives me a compile error.


Code updates are in red:

Private Sub btnImportData_Click()

Dim fDialog As Office.FileDialog
Dim strFileName As String
Dim db As DAO.Database
Dim sqlStr As String
Dim varFile As Variant

Me.FileList.RowSource = ""

On Error GoTo errHandler
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
Set db = CurrentDb

With fDialog
    .AllowMultiSelect = True
    .Title = "Please select one or more files"
    .Filters.Clear
    .Filters.Add "Excel Spreadsheets", "*.xls, *.xlsx, *.xlsm, *.xlsb"
    If .Show = True Then
        For Each varFile In .SelectedItems
            Me.FileList.AddItem varFile
            strFileName = .SelectedItems(1)
            sqlStr = "INSERT INTO tblTransaction (YearMo, Entity, Acct, ActDKK, FcDKK, ActLocCur, FcLocCur, Comment)" & _
            "SELECT * FROM (SELECT YearMo, Entity, Acct, ActDKK, FcDKK, ActLocCur, FcLocCur, Replace([Comment1],Chr(10),Chr(13) & Chr(10))" & _
            "As Comment FROM [DB$] AS xlData IN '" & strFileName & "'[Excel 12.0;HDR=yes;IMEX=2;ACCDB=Yes])"
            db.Execute sqlStr
            MsgBox strFileName & vbCrLf & db.RecordsAffected & " records imported"
        Next
     Else
       MsgBox "Import selection was cancelled."
       Exit Sub
    End If
End With

exitHere:
Set fDialog = Nothing
Set db = Nothing
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

End Sub

[daredan]

Ok, I've come one step further when I found https://www.599cd.com/tips/access/150405_filedialog/ describing the solution in a bit more detail.

So I have created an empty form with a listbox control named 'FileList' and assigned the code to the command button there.

Running the procedure now, gives me the following error:

[CJ_London]

the notes to the code says

'This example illustrates how to use the FileDialog object to display a dialog box that allows the user to select one or more files. The selected files are then added to a listbox named FileList.'

So you need a listbox named FileList

[CJ_London]

Running the procedure now, gives me the following error:

Self evident isn't it? listboxes have rowsources - you are presumably now passing a csv list which is not sql, but a value list