I need to create a Macro in Access that will allow me to import an excel file into an access database.
The requirements are:
-The code should allow me to choose whether the first row has fields
-Browse the Excel file (cannot be specific because this excel file may change)
-Choose the Excel file and specific sheet to transfer (cannot be specific because this sheet may change)
-Skip 'n' rows in order to get to the data of the excel table
-Transfer the sheet into an access table
-Delete original file that was imported
I attached what i have so far but it's missing two requirements: Skipping rows and being able to choose a specific sheet instead of just a folder that contains the excel file.
Hope you can help!
Thanks
Private Sub btnUpdate_Click()
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean
blnHasFieldNames = False
strBrowseMsg = "Select the folder that contains the EXCEL files:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If
strTable = "EMT"
strFile = Dir(strPath & "\*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & "\" & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames
Kill strPathFile
strFile = Dir()
Loop
End Sub