Please click 'Yes', if the post was helpful to you.
--
RyGuy
"KramerJ" wrote:
> Many thanks, expecially the quick reply. Reason for importing all worksheets
> from each workbook is to create a master Access table to automate the
> conversion process and for further database development. I have been out of
> the mainstream of development awhile so I am thankful for your assisstance.
> Regards, Joe
>
> "ryguy7272" wrote:
>
> > Import all files into one table (probably not a good idea). But anyway...
> > Map to the folder that the files are in now:
> > below, I called ithe folder Import, loke this:
> > strPath = "C:\Import\"
> >
> > Sub ImportAllExcelFiles()
> > On Error GoTo Err_F
> > Dim strPathFile As String, strFile As String, strPath As String, strSpec As
> > String
> > Dim strTable As String, ynFieldName As Boolean
> > ynFieldName = False
> > strPath = "C:\Import\"
> > 'strSpec = "NameOfImportSpecification" ' Put your name here
> > strTable = "tablename"
> > strFile = Dir(strPath & "*.xls")
> > Do While Len(strFile) > 0
> > strPathFile = strPath & strFile
> > DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable,
> > strPathFile, ynFieldName
> > ' Uncomment out the next code step if you want to delete the file after it's
> > imported
> > ' Kill strPathFile
> > strFile = Dir()
> > Loop
> > Exit_F:
> > Exit Sub
> >
> > Err_F:
> > MsgBox Err.Number & " " & Err.Description
> > Resume Exit_F
> >
> > End Sub
> >
> > If you want to import to several Tables, and you name the Tables as the code
> > runs, try this:
> >
> > Function ImportFiles()
> > Dim strPathFile As String, strFile As String, strPath As String
> > Dim strTable As String
> > Dim blnHasFieldNames As Boolean
> > blnHasFieldNames = True
> > strPath = "C:\Import\"
> > strFile = Dir(strPath & "*.xls")
> > Do While Len(strFile) > 0
> > strPathFile = strPath & strFile
> > 'Name the table
> > strTable = InputBox("Enter table name for file """ & strPathFile & """")
> > DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
> > strTable, strPathFile, blnHasFieldNames
> > strFile = Dir()
> > Loop
> > MsgBox "Done with Import"
> > End Function
> >
> > Call the function this way:
> > Private Sub Command0_Click()
> > Call ImportFiles
> > End Sub
> >
> > Regards,
> > Ryan---
> >
> > --
> > RyGuy
> >
> >
> > "KramerJ" wrote:
> >
> > > How do I import into Access 2003 a collection of Excel files each containing
> > > multiple work sheets.
|
Similar Threads
