Import data from a specfic Worksheet from Excel

[daredan]

Hi,

I found the source code on https://stackoverflow.com/questions/...ccess-using-vb which works basically fine for what I am looking for as long as the source file just contains one worksheet with data to import.

Option Compare Database
Option Explicit

Private Sub cmdImportExcel_Click()

' Requires reference to Microsoft Office 11.0 Object Library.
   Dim fDialog As FileDialog
   Dim varFile As Variant

   ' Clear listbox contents.
   'Me.FileList.RowSource = ""

   ' Set up the File Dialog.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

   With fDialog

      .AllowMultiSelect = False
      .Filters.Add "All Files", "*.*"

      If .Show = True Then

         'Loop through each file selected and add it to our list box.
         For Each varFile In .SelectedItems
         ' Label3.Caption = varFile

         Const acImport = 0
         Const acSpreadsheetTypeExcel9 = 8

         'This gets the sheets to new tables
         GetSheets varFile

         Next
         MsgBox ("Import data successful!")
         End If
End With
End Sub


Sub GetSheets(strFileName)
   'Requires reference to the Microsoft Excel x.x Object Library

   Dim objXL As New Excel.Application
   Dim wkb As Excel.Workbook
   Dim wks As Object

   'objXL.Visible = True

   Set wkb = objXL.Workbooks.Open(strFileName)

   For Each wks In wkb.Worksheets
      DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
            "tblData", strFileName, True, wks.Name & "$"
   Next

   'Tidy up
   wkb.Close
   Set wkb = Nothing
   objXL.Quit
   Set objXL = Nothing
End Sub

However, the source file where I want to get the data from a worksheet called 'TestData' consists of multiple worksheets. The existing code doesn't cater for that need and runs into an error when run:





I tried to rename the part with wks.Name & "$" to wks.Name & "TestData" but unfortunately it doesn't seem as simple as I hoped, as this results in:



So your help would be appreciated on how to alter the code, that it just points to the worksheet I want to import.

Thanks!

[Welshgasman]

Your tables have to match what you are importing?
Both table names and fields.

[daredan]

In this simplified testing example the fields match.


The Excel file is 'TestDB.xlsx' with 'TestData' worksheet to be imported/appended into 'tblData' table in the database. Again, the existing code works, if there is just ONE worksheet.

So my idea, as this has to work for an Excel file with multiple sheets, is to only fetch just this one sheet (in this case 'TestData') and append it to 'tblData'.

[CJ_London]

are you sure you are using the correct excel type? acSpreadsheetTypeExcel9 (=8 which is access 2000 i.e. .xls), current spreadsheets use 9 or 10

'F1' in your error implies you have specified no headers, although I can see you have set to true

suggest for debugging purposes change acimport to aclink and see what you get

Note you do not need to specify acSpreadsheetTypeExcel9 as a constant - as it is already a constant in the access library

And why are you looping through the workbook? If you only need data from the one worksheet, just specify it. At the moment your code is trying to import from all worksheets

[daredan]

@CJ_London:
I'm using the acSpreadsheetTypeExcel9 simply because it came over with the copy/paste code from the site I referred to in my prior post as this is currently the foundation for my solution. I changed that to 12 to not run into related issues.

Yes, the 'F1' is related to the fact, when I change the source file to have another (blank) worksheet.

I'm currently looping through the workbook because of copy/paste of the original code and unfortunately my attempt to stop this and point only to one specific worksheet failed due to lack of VBA knowledge. That's the part I described in my initial post, where I simply tried to replace wks.Name & "$" with wks.Name & "TestData". If I could guidance on the related syntax, I trust, my problem should be as good as solved.


Just to sum it up again on the highest level: I want to get Excel data from the 'TestData' worksheet of an Excel file consisting of multiple worksheets and append that data in the 'tblData' in Access.

[daredan]

FYI - I tried altering the code to remove the loop and point only to the 'TestData' worksheet, but failed:

[daredan]

At least I found now a working syntax in a worksheet...

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblData", strFileName, True, "TestData$a1:c3"

...with the downside that this only works for a static range. This is at least good enough to continue working, however it would be nice to have the last row calculated dynamically.

[Micron]

I think this could be simplified a lot but at the moment it's confusing. It seems that
- a dialog loop isn't required because multiselect is set to No
- you don't need to open a workbook to do the transfer - or does someone need to see the sheet names?
- one error says TestData, the other TestDat so something is amiss there
- there might be an issue with the acSpreadsheetType specified here - check https://docs.microsoft.com/en-us/off...ferspreadsheet
- as mentioned, constants aren't required either, but that's a small matter at present

We can't see what's being passed to GetSheets and maybe that's part of the problem. I'm thinking just open the dialog (assuming you even want to do that) choose the file and that will return the path. Then just do the transfer using that path value and correct import specs. The 2nd sub really isn't needed either.

EDIT - posted before I saw new post. If using automation, should be able to determine the range if you don't want the whole sheet to import into Access. Might be easier to use a named dynamic range, which you'd have to create in Excel. A named dynamic range might look like

=OFFSET(DATA!$B$6,0,0,COUNTA(DATA!$B:$B),1) and its scope can be a sheet or a workbook. I'm fuzzy on the scope so it's something you'd probably have to research.
That formula gets all the values from A6 to the end of the used range in A.

[daredan]

I agree Micron and sorry for the confusion on the naming conventions here, that unfortunately came up the more I work with the issue here. Also I am pretty sure by now that the source code isn't really good or at least not good enough for the simple task I am after. But it was the best starting option I have found for someone who's simply not good enough to code this from scratch after browsing through lots of related solutions.

Anyway I tried cleaning up the code to the best of my knowledge and it still works:

Private Sub cmdImportExcel_Click()

'Requires reference to Microsoft Office 11.0 Object Library.
Dim fDialog As FileDialog
Dim varFile As Variant

'Set up the File Dialog.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

With fDialog
    .AllowMultiSelect = False
    .Filters.Clear
    .Filters.Add "Excel Spreadsheets", "*.xls, *.xlsx, *.xlsm, *.xlsb"

    If .Show = True Then

         'Loop through each file selected and add it to our list box.
         For Each varFile In .SelectedItems
             GetSheets varFile 'This gets the sheets to new tables
         Next
         MsgBox ("Import data successful!")
    End If
End With
End Sub

Sub GetSheets(strFileName)
   'Requires reference to the Microsoft Excel x.x Object Library
   Dim objXL As New Excel.Application
   Dim wkb As Excel.Workbook
   Dim wks As Object
   
   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tblData", strFileName, True, "TestDat$A1:C3"
   
   'Tidy up
   Set wkb = Nothing
   objXL.Quit
   Set objXL = Nothing
End Sub

a dialog loop isn't required because multiselect is set to No

Agree, however I would appreciate help on this one. Removing/commenting out resulted in the VBA to fail.

you don't need to open a workbook to do the transfer - or does someone need to see the sheet names?

Agree, I removed that part of the code successfully.

one error says TestData, the other TestDat so something is amiss there

Sorry, I edited previous posts to align.

there might be an issue with the acSpreadsheetType specified here

I changed that to the 'latest' acSpreadsheetTypeExcel12Xml. I couldn't find any other issues, which might be related.

as mentioned, constants aren't required either, but that's a small matter at present

Agree, I removed those completely from the code successfully.


So the only thing that I couldn't resolve yet on how to specify the range, which would be 'Table1' in Excel, dynamically (e.g. "TestDat$Table1", "TestDat$[Table1]" didn't work.

EDIT1: @Micron: I'll be looking into the OFFSET approach

EDIT2: I adapted to run the code on my bigger production database (830 rows per import, 8 columns) with the same underlying assumptions as in my small test case. The code ran successfully, however no data was not appended, although it should have (matching structure, no primary key issues). How would I optimize the error handling to not prompt me with a 'Import data successful!' in this case?

[CJ_London]

point only to the 'TestData' worksheet, but failed

because you didn't include the $

[daredan]

because you didn't include the $

Thanks, that's related to my post #6. I have corrected that in post #7 when I found the correct syntax.

[davegri]

If you would like to select the worksheet from a dropdown on a form, this code will set up the combobox with the worksbook's sheets as the rowsource.

Public Function fcnListWorksheets(ByVal pWorkBook As String)
    Dim objExc As Object
    Dim objWbk As Object
    Dim objWsh As Object
    Set objExc = CreateObject("Excel.Application") ' late
    Set objWbk = objExc.Workbooks.Open(pWorkBook)
	Me.cboInitialsheet.RowSource = vbNullString
	For Each objWsh In objWbk.Worksheets
		Me.cboInitialsheet.AddItem objWsh.Name
	Next
    On Error Resume Next
    Set objWsh = Nothing
    objWbk.Close
    Set objWbk = Nothing
    objExc.Quit
    Set objExc = Nothing
End Function

[Micron]

Here's what I cobbled together using OP's code. Note that one could either use a For Next loop to loop over the single item collection, or one could simply refer to the collection index if there will only ever be one item due to the multi select being disabled. I also cannot vouch for the accuracy of the posted name of the range that follows the True parameter - it looks odd to me. In one way the code looks as long as the first example, but that's because I added an error handler. However, I removed
- the call to the other sub
- that sub
- the loop
- the automation and variant references

Option Compare Database
Option Explicit

Private Sub cmdImportExcel()
Dim fDialog As FileDialog
Dim varFile As Variant
Dim strFileName 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 acImport, acSpreadsheetTypeExcel12Xml, "tblData", strFileName, True, "TestDat$A1:C3"
       'shorter version, or could use line continuation characters
       DoCmd.TransferSpreadsheet 0, 9, "tblData", strFileName, True, "TestDat$A1:C3"
    Else
       MsgBox "Import selection was cancelled."
       Exit Sub
    End If
End With

MsgBox "success message goes here"

exitHere:
Set fDialog = Nothing
Exit Sub

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

End Sub

Seems that a new wrinkle has been raised - how to know if zero rows were imported. The only way I know of is to count the table records before and after.

[CJ_London]

Seems that a new wrinkle has been raised - how to know if zero rows were imported.

use a query rather than transferspreadsheet

dim db as dao.database
dim sqlStr as string

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
msgbox db.recordsaffected & " Records Imported"

[daredan]

@davegri: Thanks for bringing this option up. However the Excel files which are subject to import will all have the same structure. So I can reliably link to a specific worksheet.
@Micron&CJ_London: Thank you a lot for your help. I will try incorporating it first thing tomorrow trying both options.