Import Excel Spreadsheet Into Access

[jo15765]

Their is a spreadsheet downloaded from a server daily. I want to through VBA create a table in access 2000 and import the spreadsheet (could be .xls or .xlsx) into the newly created table.

This is what I was working with --- not working properly, but I think *fingers crossed* it's close to it


Function ImportSpreadsheet
Dim filename, table, worksheetName As String
Dim hasFieldNames As Boolean
Dim ws As Integer

bhasFieldNames = False

table = "TestingImportThroughVBA"
worksheetName = "Sheet 2"
filename = "C:\Testing\TestWorksheet.xls"

For ws = 1 to 1
Do While Len(filename) > 0
DoCmd.TransferSpreadsheet acImport, acSpreadshetTypeExcel9, table, filename, hasFieldNames
Loop
Next ws
End Function

[mancubus]

first you dont need loops here.
remove them leaving one line, try:

DoCmd.TransferSpreadsheet acImport, acSpreadshetTypeExcel9, table, filename, hasFieldNames, worksheetName & "$"

dont forget $ sign after worksheet name.

it's just an adoption to below code.
below is a working code that i'm currently using to import multiple sheets (xl sheet name = acc table name) with Office 2010 by deleting current Access tables.
as i don't have Office 2000 installed on my pc, i can't test the possible Office 2000 adoption of the code.
afaik you can't directly import data from upper versions of Office programs.


tutorial:
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm



Sub import_multi_XL_tbl()
Dim wsList As Variant
Dim xlFile As String
Dim i As Integer

wsList = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6")
xlFile = "C:\MyFolder\MySubFolder\MyXLFile.xlsm"

With DoCmd
.SetWarnings False
For i = LBound(wsList) To UBound(wsList)
On Error Resume Next
.RunSQL "DROP TABLE " & wsList(i)
.TransferSpreadsheet _
acImport, _
acSpreadsheetTypeExcel12Xml, _
wsList(i), _
xlFile, _
True, _
wsList(i) & "$"
On Error GoTo 0
Next
.SetWarnings True
End With
End Sub

[jo15765]

@Mancubus ty for that!

What would I need to do if I wanted to import into a already created table and the spreadsheet I am importing headers do not match table headers (i.e. I need to provide field mappings)?

[mancubus]

welcome.

same. when you run the code by changing the worksheet name it will append the rows from new sheet to existing table.

just make sure that the new worksheet has the same field (column) structure.

[edwards142]

Deleted by Moderator.
Reason: No help except an outside link to a non member commercial site