All geek questions in one place

Import Excel spreadsheet in Access using VBA

I am trying to import an Excel spreadsheet into Access using simple VBA code. The problem I ran into is that there are 2 sheets in the Excel file and I need a second sheet that needs to be imported. Is it possible to specify the required sheet in VBA code?

Private Sub Command0_Click() Dim dlg As FileDialog Set dlg = Application.FileDialog(msoFileDialogFilePicker) With dlg .Title = "Select the Excel file to import" .AllowMultiSelect = False .Filters.Clear .Filters.Add "Excel Files", "*.xls", 1 .Filters.Add "All Files", "*.*", 2 If .Show = -1 Then StrFileName = .SelectedItems(1) DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "COR Daily", StrFileName, True Else Exit Sub End If End With End Sub

Should I set StrFileName to 'StrFileName'&'.Worksheetname' ? Is this the correct naming scheme for this?

sort of:

 StrFileName = StrFileName & ".WorkSheetName"
+6
source share
1 answer

Pass the sheet name with the Range DoCmd.TransferSpreadsheet Method parameter. See the “Worksheets in a Range Parameter” section at the bottom of this page.

This code imports from a worksheet named "temp" into a book called "temp.xls" and stores the data in a table named "tblFromExcel".

 Dim strXls As String strXls = CurrentProject.Path & Chr(92) & "temp.xls" DoCmd.TransferSpreadsheet acImport, , "tblFromExcel", _ strXls, True, "temp!"
+11
source

Source: https://habr.com/ru/post/897247/

More articles:


All Articles