Import Excel File using a Macro

[smakkiee]

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

[ssanfu]

Ken Snell has an excellent site about EXCEL Workbook Files - both importing and exporting. See if this site helps you:
http://www.accessmvp.com/kdsnell/EXCEL_Import.htm

[smakkiee]

Ken Snell has an excellent site about EXCEL Workbook Files - both importing and exporting. See if this site helps you:
http://www.accessmvp.com/kdsnell/EXCEL_Import.htm

Hi Sanfu,

I actually got my code from that site but there are some things that I need on top of it that I listed in the requirements. Any thoughts?

[The VBA Man]

smakkiee, looks like you have a lot of criteria for the target Excel data. have you considered doing things the other way around? i.e. instead of starting at Access, start at Excel. select the rows you want to import, then go to Access, and import only those "active" rows from the "active" sheet

[smakkiee]

smakkiee, looks like you have a lot of criteria for the target Excel data. have you considered doing things the other way around? i.e. instead of starting at Access, start at Excel. select the rows you want to import, then go to Access, and import only those "active" rows from the "active" sheet

The reason I don't want to use excel is because the current database is using excel and its too slow. That's why I am converting it to Access. Along with this, I can't start with Excel because the spreadsheet I am importing is not going to be the same spreadsheet every time I need to update it.

[ssanfu]

From what I see:
-The code should allow me to choose whether the first row has fields Done
-Browse the Excel file (cannot be specific because this excel file may change) Done
-Choose the Excel file and specific sheet to transfer (cannot be specific because this sheet may change) Done
-Skip 'n' rows in order to get to the data of the excel table............ Can not be Done using Transferspreadsheet (AFAIK)
-Transfer the sheet into an access table Done
-Delete original file that was imported Done

To be able to specify rows to skip, you will have to use other methods to import the data. (VBA or multiple queries/tables)

[lynthel]

When I apply the same code in my access under a button I created "Import Macro", I get a compile error "Sub or Function not defined" on the "strPath = BrowseFolder(strBrowseMsg)" line...can someone explain why?