HELP! - Import Loop

[auujxa2 via AccessMonster.com]

Currently, I have my code loop through subfolders, import all .xls files into
one table. I'd like to add the filename to a field (F50), so I can
distinguish between where the records came from. Here is my code. The SQL
statement I have doesn't assign the "myFile" as I thought it would. I
figured myFile was still defined since I put it in before it looped to the
next file. Please help! (The issue I'm having is at the very bottom of this
code)

Private Sub Form_Open(Cancel As Integer)

DoCmd.SetWarnings False

'Imports every excel workbook from all subfolders into temp table
Call ListFilesToTable("F:\Advertising Planning\Limited Time Restore\Limited
Time Offer Templates\Spring 08", "*.xls", True)

'appends newly created files to master email table
DoCmd.OpenQuery "NewFileThisWeekQry"
'appends deleted or renamed files to master email table
DoCmd.OpenQuery "LastWeekFileRenameOrDeletedQry"

DoCmd.OpenQuery "ClearLastWeeksDataQry"

'appends last weeks data to lastweeks table
DoCmd.OpenQuery "CreateLastWeeksTblQry"

'clears this weeks tables
DoCmd.OpenQuery "ClearThisWeeksTblQry"
DoCmd.OpenQuery "ClearThisWeekTmpTblQry"

'Imports this weeks workbooks as This Weeks table
Dim dbs As Database, rst As Recordset
Dim myPath As String
Dim myCount As Variant
Dim myFile As String
Dim SQL As String

myCount = 0
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("Files")

With rst
Do While Not rst.EOF
myCount = myCount + 1
myPath = rst.Fields("FPath")
myFile = rst.Fields("FName")
SQL = "UPDATE ThisWeekTempTbl " & _
"SET ThisWeekTempTbl.F50 = myFile " & _
"WHERE ThisWeekTempTbl.F50 Is Null"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"ThisWeekTempTbl", myPath & myFile
DoCmd.RunSQL SQL
rst.MoveNext
Loop
rst.Close
End With

 

[Steve Sanford]

"myFile" has to be concantated to the UPDATE SQL string

The first thing is to comment out or delete any lines (in this sub) that are
like this:

DoCmd.SetWarnings

Here is the modified code to replace your loop:
(watch for line wrap)

'---snip--------
With rst
Do While Not rst.EOF
myCount = myCount + 1
myPath = rst.Fields("FPath")
myFile = rst.Fields("FName")
SQL = "UPDATE ThisWeekTempTbl"
SQL = SQL & " SET F50 = '" & myFile & "'"
SQL = SQL & " WHERE F50 Is Null"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"ThisWeekTempTbl", myPath & myFile
CurrentDb.Execute SQL
rst.MoveNext
Loop
rst.Close
End With
'-----snip----------

I would recommend taking the time to write error handler code instead of
using

DoCmd.SetWarnings False


HTH

 

[auujxa2 via AccessMonster.com]

"Filename" is populating field F50 instead of the actual filename from field
"FName"

"myFile" has to be concantated to the UPDATE SQL string

The first thing is to comment out or delete any lines (in this sub) that are
like this:

DoCmd.SetWarnings

Here is the modified code to replace your loop:
(watch for line wrap)

'---snip--------
With rst
Do While Not rst.EOF
myCount = myCount + 1
myPath = rst.Fields("FPath")
myFile = rst.Fields("FName")
SQL = "UPDATE ThisWeekTempTbl"
SQL = SQL & " SET F50 = '" & myFile & "'"
SQL = SQL & " WHERE F50 Is Null"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"ThisWeekTempTbl", myPath & myFile
CurrentDb.Execute SQL
rst.MoveNext
Loop
rst.Close
End With
'-----snip----------

I would recommend taking the time to write error handler code instead of
using

DoCmd.SetWarnings False

HTH

Currently, I have my code loop through subfolders, import all .xls files into
one table. I'd like to add the filename to a field (F50), so I can

[quoted text clipped - 53 lines]

rst.Close
End With

 

[auujxa2 via AccessMonster.com]

Sorry Steve, I forgot yesterday I tried inputting "Filename" as the default
value in the table to see if that worked. I was desperate!!!! Your new
concatenated code worked. Thank you so much. I appreciate it.

You da man

"Filename" is populating field F50 instead of the actual filename from field
"FName"

"myFile" has to be concantated to the UPDATE SQL string

[quoted text clipped - 36 lines]