By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,039 Members | 1,831 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,039 IT Pros & Developers. It's quick & easy.

strange issue...

P: n/a
This code is to import bunch of excel file into access as a single
table. table name is same as file name. it works for the first file
and then breaks where i have Set TDF = DB.TableDefs(TempFile)

It's in yellow , when i move my mouse over it i can see the table name
and i can table was imported.

can anyone please point me to right direction ?
Dim strFile As Variant
Dim strPath As String
Dim DB As DAO.Database
Dim TDF As DAO.TableDef
Dim FLD As DAO.Field
Set DB = CurrentDb()

strPath = "C:\_ IS Help\Common EE\"
strFile = Dir(strPath & "*.xls")

Do While Len(strFile) 0
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8,
Left(strFile, Len(strFile) - 4), strPath & strFile, True
'Dim tempfile As String
Dim TempFile As Variant
TempFile = Left(strFile, Len(strFile) - 4)
MsgBox TempFile

Set TDF = DB.TableDefs(TempFile)
With TDF
.Fields.Append .CreateField("FileName", dbText)
End With

DoCmd.RunSQL "UPDATE " & (Left(strFile, Len(strFile) - 4)) & " SET
Filename = '" & strFileName & "'

strFile = Dir
Set TDF = Nothing

Loop
MsgBox "Import Completed"

Feb 20 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Feb 20, 5:38 pm, qar...@gmail.com wrote:
'Dim tempfile As String
Dim TempFile As Variant
TempFile = Left(strFile, Len(strFile) - 4)
MsgBox TempFile

Set TDF = DB.TableDefs(TempFile)
With TDF
.Fields.Append .CreateField("FileName", dbText)
End With

What is the error message you are getting? And are you sure the table
you see named in breamode is actuall in the DB?

Feb 21 '07 #2

P: n/a
Quick Recap:
1. this vba import batch excel files from specific directory into
individual tables
2. individual tables are name after the file was imported
3. a field is created in the new table called "FileName"
4. This field is pupulated with the name of the excel file
5. the new data then imported into another giant table called "ALL"

This line had to be added and now it's all working...

DB.TableDefs.Refresh
Private Sub cmd3_Click()

Dim strFile As Variant
Dim strPath As String
Dim DB As DAO.Database
Dim TDF As DAO.TableDef
Dim FLD As DAO.Field
Set DB = CurrentDb()

strPath = "C:\_ IS Help\Common EE\"
strFile = Dir(strPath & "*.xls")

Do While Len(strFile) 0
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8,
Left(strFile, Len(strFile) - 4), strPath & strFile, True
DB.TableDefs.Refresh
Dim TempFile As Variant
TempFile = Left(strFile, Len(strFile) - 4)
MsgBox TempFile

Set TDF = DB.TableDefs(TempFile)
With TDF
.Fields.Append .CreateField("FileName", dbText)
End With

DoCmd.RunSQL "UPDATE " & TempFile & " SET Filename = '" & TempFile
& "';"
DoCmd.RunSQL "INSERT INTO [ALL] SELECT * FROM " & TempFile & " ;"

strFile = Dir
Set TDF = Nothing

Loop
MsgBox "Import Completed"

End Sub

Feb 21 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.