Excel Data in many Sheets - Import to Access

[Andre]

I need some advise on how to do this.

I have an Excel workbook that contains one sheet for each Profit Center.
(About 100 profit centers)

Each sheets contains the P&L for the profit center, and the sheets are named
according to the profit center.

I want to automate importing this data to a relational set of tables.

How can I automate, having automation read the data from each sheet in the
specified Excel workbook.

I though about linking each workbook and then using VBA to extract and
transform the data, but that will take ages.

Any ideas welcome

Thanks
Andre

 

[Guest]

Andre,
Here's a couple of examples for you to consider. In the first, each of your
sheets acts as a Table for you do work with.

Steve

Public Sub ExcelOpenWbAsTable(strFile As String)
'Enter strFile as: "c:\temp\Book1.xls"
On Error GoTo ProcError
Dim db As DAO.Database
Dim tdf As DAO.TableDef

'leave the "Excel 5.0" in the next line, even for newer versions
Set db = DBEngine(0).OpenDatabase(strFile, False, False, "Excel
5.0;HDR=NO;IMEX=2;")
For Each tdf In db.TableDefs
Debug.Print tdf.NAME 'show sheet name
Next

ProcExit:
Set db = Nothing
Exit Sub

ProcError:
Select Case Err.Number
'Case 3265 'Application-defined or object-defined error
' Resume Next
Case Else
MsgBox "Unanticipated error: " & Err.Number & " " & Err.Description
Stop 'if you stop here F8 will take you to the problem line
Resume 0
End Select
End Sub

Public Sub ExcelImportSheet()

'Import a particular Excel Sheet and Range

DoCmd.TransferSpreadsheet acImport, 8, "table2", "c:\temp\book1.xls",
False, "Sheet3!A1:G20"

'Remember the !

End Sub

 

[Andre]

Steve,
that was just what i needed.
Thanks

Andre

Andre,
Here's a couple of examples for you to consider. In the first, each of your
sheets acts as a Table for you do work with.

Steve

Public Sub ExcelOpenWbAsTable(strFile As String)
'Enter strFile as: "c:\temp\Book1.xls"
On Error GoTo ProcError
Dim db As DAO.Database
Dim tdf As DAO.TableDef

'leave the "Excel 5.0" in the next line, even for newer versions
Set db = DBEngine(0).OpenDatabase(strFile, False, False, "Excel
5.0;HDR=NO;IMEX=2;")
For Each tdf In db.TableDefs
Debug.Print tdf.NAME 'show sheet name
Next

ProcExit:
Set db = Nothing
Exit Sub

ProcError:
Select Case Err.Number
'Case 3265 'Application-defined or object-defined error
' Resume Next
Case Else
MsgBox "Unanticipated error: " & Err.Number & " " & Err.Description
Stop 'if you stop here F8 will take you to the problem line
Resume 0
End Select
End Sub

Public Sub ExcelImportSheet()

'Import a particular Excel Sheet and Range

DoCmd.TransferSpreadsheet acImport, 8, "table2", "c:\temp\book1.xls",
False, "Sheet3!A1:G20"

'Remember the !

End Sub