Automating Excel Export to Access

  • Thread starter Thread starter Bryan
  • Start date Start date
B

Bryan

Hi.

I've recently been given a dirty excel workbook with lots
of different sheets. My task is to import each worksheet
into Access, the only problem is that each worksheet has
different columns and even different names.

What I'm looking to do, is run a macro which will create a
new database (adding new columns when they are found) and
then importing the data. The 'Get External Data' command
nearly works - only it can only import one worksheet at a
time, and then it creates a new table for each sheet.

Any ideas?

Thanks for your time
Bryan
 
Hi Bryan,

Try using VBA and TransferSpreadsheet as below. Probably you should think of importing every sheet into its own table and use Append Queries to add the data from the tables:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Employees", "c:\Temp.xls", True, "Sheet3!A1:ZZ999"

Depending on the version of your Excel file (97 until 2002 = 8) you will have to change the acSpreadsheetTypeExcel8 (= default value, not necessary to enter) to acSpreadsheetTypeExcel3, 4, 5, 7, 9 10, etc.

True : if your Excel File contains column headings set it to True (or -1), else False (or 0)

"Sheet3!A1:ZZ999" : Sheet name (presuming it's "Sheet3") and range on sheet to import. If you specify a sheet you have to add a range on the sheet.

If you want to find out the 3rd sheet in your file you will need some more VBA: ( have a look at Access help - GetObject and CreateObject)

'************************************
Private Sub MySheetImport()
Dim XLapp As Object
Dim XLFile As String
Dim XLSheet As String
Dim XLRange As String
Dim TableName As String
Dim z As Integer
Dim SheetCount As Integer

Set XLapp = GetObject(, "Excel.Application")

XLapp.Visible = True 'Excel is visible!! or if False not visible!!
XLFile = "c:\temp.xls" 'Your File
TableName = "Employees" 'Table to import into
XLRange = "!a1:z10" 'Specifies the area to be imported

Set XLwb = XLapp.Workbooks.Open(XLFile) 'Opens your file in Excel

'if you want to import all sheets in your Excel file into one table use the following 6 lines of code

'if you need only e.g. sheet 3, remove the for-next construct, keep the 3 lines of code within and change the code from .Sheets(z).Name
' to .Sheets(3).Name

SheetCount = XLapp.ActiveWorkbook.Sheets.Count 'Gives you the total number of sheets
For z = 1 To SheetCount
XLSheet = XLapp.ActiveWorkbook.Sheets(z).Name 'get name of sheet number z
XLSheet = XLSheet & XLRange 'add range to sheetname
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, TableName, XLFile, True, XLSheet
Next z

MsgBox "Imported Successfully "

XLapp.Quit
Set XLapp = Nothing
Set XLwb = Nothing

End Sub
'*****************************
 
Thanks BerHav

The code is working well so far, managing to import the
spreadsheets into seperate tables. Although it does hang
on one sheet - it has 65,000 blank rows in, which Access
tries to import. Not sure if that can be sorted though.

All that's left is to merge all the tables into one!

Thanks again.
Bryan
-----Original Message-----
Hi Bryan,

Try using VBA and TransferSpreadsheet as below. Probably
Click to expand...
you should think of importing every sheet into its own
table and use Append Queries to add the data from the
tables:
DoCmd.TransferSpreadsheet acImport,
Click to expand...
acSpreadsheetTypeExcel8, "Employees", "c:\Temp.xls",
True, "Sheet3!A1:ZZ999"
Depending on the version of your Excel file (97 until
Click to expand...
2002 = 8) you will have to change the
acSpreadsheetTypeExcel8 (= default value, not necessary to
enter) to acSpreadsheetTypeExcel3, 4, 5, 7, 9 10, etc.
True : if your Excel File contains column headings set it
Click to expand...
to True (or -1), else False (or 0)
"Sheet3!A1:ZZ999" : Sheet name (presuming it's "Sheet3")
Click to expand...
and range on sheet to import. If you specify a sheet you
have to add a range on the sheet.
If you want to find out the 3rd sheet in your file you
Click to expand...
will need some more VBA: ( have a look at Access help -
GetObject and CreateObject)
'************************************
Private Sub MySheetImport()
Dim XLapp As Object
Dim XLFile As String
Dim XLSheet As String
Dim XLRange As String
Dim TableName As String
Dim z As Integer
Dim SheetCount As Integer

Set XLapp = GetObject(, "Excel.Application")

XLapp.Visible = True 'Excel is visible!! or if False not visible!!
XLFile = "c:\temp.xls" 'Your File
TableName = "Employees" 'Table to import into
XLRange = "!a1:z10" 'Specifies the area to be imported

Set XLwb = XLapp.Workbooks.Open(XLFile) 'Opens your file in Excel

'if you want to import all sheets in your Excel file into
Click to expand...
one table use the following 6 lines of code
'if you need only e.g. sheet 3, remove the for-next
Click to expand...
construct, keep the 3 lines of code within and change the
code from .Sheets(z).Name
' to .Sheets(3).Name

SheetCount = XLapp.ActiveWorkbook.Sheets.Count
Click to expand...
'Gives you the total number of sheets
For z = 1 To SheetCount
XLSheet = XLapp.ActiveWorkbook.Sheets(z).Name 'get name of sheet number z
XLSheet = XLSheet & XLRange 'add range to sheetname
DoCmd.TransferSpreadsheet acImport,
Click to expand...
acSpreadsheetTypeExcel8, TableName, XLFile, True, XLSheet
 
Back
Top