Dim fDialog As Office.FileDialog Dim varFile As Variant Dim Path As String ' Set up the File Dialog. Set fDialog = Application.FileDialog(msoFileDialogFilePicker) With fDialog ' Allow user to make multiple selections in dialog box fDialog.AllowMultiSelect = False ' Set the title of the dialog box. fDialog.Title = "Please Select An Excel Sheet" ' Clear out the current filters, and add our own. fDialog.Filters.Clear fDialog.Filters.Add "Excel Sheets 2003", "*.xls" fDialog.Filters.Add "Excel Sheets 2007", "*.xlsx" '.Filters.Add "All Files", "*.*" ' Show the dialog box. If the .Show method returns True, the ' user picked at least one file. If the .Show method returns ' False, the user clicked Cancel. If fDialog.Show = True Then 'fDialog.SelectedItems Path = fDialog.SelectedItems(1) 'Importing the Data From Excel Sheet to access database Dim WrksheetName As String Dim i As Integer Dim xl As Object Set xl = CreateObject("Excel.Application") xl.Visible = True xl.Workbooks.Open Path With xl .Visible = True With .Workbooks(.Workbooks.Count) For i = 1 To .Worksheets.Count WrksheetName = .Worksheets(i).Name DoCmd.TransferSpreadsheet (acImport), acSpreadsheetTypeExcel97, WrksheetName, Path Next i End With End With Set xl = Nothing Me.txt_Path.SetFocus Me.txt_Path.Text = Path Else MsgBox "You clicked Cancel in the file dialog box." End If End With
For i = 1 To .Worksheets.Count
For i = 2 To .Worksheets.Count
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)