Import / Export Multiple Files & Tabs

[maggiemago3]

Hi there,


I am trying to write some code to import multiple files (of unknown names) with multiple tabs/worksheets (of unknown names) into 1 Access table. The files will all be located in the same location and will be in the same format (amongst files and tabs/worksheets). Alternatively, at a future point, I will need to export this table, into 1 file, with the same number tabs and tab names in which I've recieved them. My idea is that, as I import the files, I need to create a column that will include the tab name, so that when I export at a later date, I can use that column to recreate those tabs, using the field data as the tab names. I am new to SQL and have started hunting and pecking for codes, but could really use the advice of a pro. Anything and everything helps.

Thanks!

[June7]

Sounds very complicated.

For example code to import/export Excel, review links in last post of: http://forums.aspfree.com/microsoft-...el-414974.html

Adding fields to table not a good idea. There is a limit of 255 fields allowed in table.

[maggiemago3]

Adding fields to the file wouldn't pose a problem as there are only about 30 existing fields and it is a file that is used as a standard form so will not change, or will change only minimally in the future.

The aspfree link brings me to a page regarding exporting forms from access to excel. That's not exactly what I am looking for, but thanks for the site, I will take a look around to see if something can help me.

I really need to be able to import/consolidate into an access table from multiple excel files with multiple worksheets, then do my thing w/ them (queries and such) and be able to export them out into a file, which can break out the worksheet tabs in the same way that I received them (only in one master file, containing the tabs)

[June7]

I said to look at the links in the last post of that thread.

And here is code sample for listing files in a folder http://www.allenbrowne.com/ser-59.html.

Instead of writing file name to a listbox, you could have import code.

[ssanfu]

You might also check out Ken Snell's site:
http://www.accessmvp.com/kdsnell/EXCEL_MainPage.htm

[maggiemago3]

Thanks, this site has what would be an excellent start to my project, however when I try to run the code, nothing happens. Any suggestions? I changed the path to my file name and changed the password setting to vbNullString. I entered it into a module as a function and tried to run it using a macro runcode function name. I don't know what's stopping it from doing anything, as the debugger doesn't even come up

Import Data from All Worksheets in All EXCEL
Files in a single Folder into Separate Tables via TransferSpreadsheet
(VBA)

Generic code to import the data from
all worksheets in all EXCEL files in a single folder. Each worksheet's data will
be imported into a separate table whose name is 'tbl' plus the worksheet name
plus an integer value that represents a "counter" for the workbooks
(e.g., "tblWorksheetName1").

Dim blnHasFieldNames As
Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean
Dim intWorkbookCounter
As Integer
Dim lngCount As Long
Dim objExcel As Object, objWorkbook As
Object
Dim colWorksheets As Collection
Dim strPath As String, strFile As
String
Dim strPassword As String

' Establish an
EXCEL application object
On Error Resume Next
Set objExcel =
GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set
objExcel = CreateObject("Excel.Application")
blnEXCEL = True
End
If
Err.Clear
On Error GoTo 0

' Change this next
line to True if the first row in EXCEL worksheet
' has field
names
blnHasFieldNames = False

' Replace
C:\MyFolder\ with the actual path to the folder that holds the EXCEL
files
strPath = "C:\MyFolder\"

' Replace
passwordtext with the real password;
' if there is no password,
replace it with vbNullString constant
' (e.g., strPassword =
vbNullString)
strPassword = "passwordtext"

blnReadOnly =
True ' open EXCEL file in read-only
mode

strFile = Dir(strPath &
"*.xls")

intWorkbookCounter = 0

Do While strFile <>
""

intWorkbookCounter = intWorkbookCounter + 1

Set
colWorksheets = New Collection

Set objWorkbook =
objExcel.Workbooks.Open(strPath & strFile, , _
blnReadOnly, ,
strPassword)
For lngCount = 1 To
objWorkbook.Worksheets.Count
colWorksheets.Add
objWorkbook.Worksheets(lngCount).Name
Next lngCount

' Close the EXCEL file without saving the file, and clean up the
EXCEL objects
objWorkbook.Close False
Set objWorkbook =
Nothing

' Import the data from each worksheet
into a separate table
For lngCount = colWorksheets.Count To 1
Step -1
DoCmd.TransferSpreadsheet acImport,
acSpreadsheetTypeExcel9, _
"tbl" &
colWorksheets(lngCount) & intWorkbookCounter, _
strPath
& strFile, blnHasFieldNames, _
colWorksheets(lngCount)
& "$"
Next lngCount

' Delete the
collection
Set colWorksheets = Nothing
'
Uncomment out the next code step if you want to delete the
' EXCEL
file after it's been imported
' Kill strPath & strFile

strFile = Dir()

Loop

If blnEXCEL = True Then
objExcel.Quit
Set objExcel = Nothing

[maggiemago3]

I figured it out! I was missing a backslash in my path name, thank you so much for the help and direction that you had given me !!!