Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.
Import All Excel Worksheets to Separate Tables in Access
From Wiki
It is possible to import all worksheets without knowing the names by using automation.
- Sub GetExcel()
- 'Requires reference to the Microsoft Excel x.x Object Library
- Dim strFileName As String
- Dim objXL As Excel.Application 'Object
- Dim wkb As Excel.Workbook
- Dim wks As Object
- Set objXL = CreateObject("Excel.Application")
- 'objXL.Visible = True
- strFileName = "C:\Docs\LTD.xls"
- Set wkb = objXL.Workbooks.Open(strFileName)
- For Each wks In wkb.Worksheets
- DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
- wks.Name, strFileName, True, wks.Name & "$"
- Next
- 'Tidy up
- wkb.Close
- Set wkb = Nothing
- objXL.Quit
- Set objXL = Nothing
- End Sub
30Jun08 Caveat: In my experience, this sort of approach will truncate all text strings at 255 characters. It seems the only reliable method, if your colleagues insist on treating Excel as a word processor *ahem* is to use Automation and explicitly acquire an Excel.Range for each cell.
I would merrily accept correction on the point. --smitty1e
In reply: If the memo-type data is in or below the 27th row (tested in Access 2000, row 1= header), then data will be imported into a text field. This is a problem with all Excel imports in that the data type of the import field is determined by the first few rows. A way around is to import into an existing table. Truncation can also occur if you have not got a recent version of the Jet driver (http://support.microsoft.com/kb/239114).
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.