Hi I have this code that will import multiple excle files into one table:

Private Sub Command3_Click()
Dim strFile As String
Dim strFolder As String

strFolder = Me.txtfilename.Value
strFile = Dir$(strFolder & "*.xls")
Do While Len(strFile) > 0

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTest",
strFolder & strFile, True

strFile = Dir$()
Loop

MsgBox "Finished", vbOKOnly

End Sub

There are over 50 excel files that are being imported. I have created a
column called "File_Name" in a table and would like to update it with file
name each time the excel is being imported so that I can refer back to where
the data came from. How would I go about doing so?

Thank you for help!!

You'll need a few extra steps.

One, use an interim table to receive the imported data from one spreadsheet
file.

Two, run a delete query on that interim table to delete all records from it
before you do the TransferSpreadsheet action for the file.

Three, do the TransferSpreadsheet action to the interim table.

Four, run an append query to copy the data from the interim table to the
permanent table, and add a calculated field to the append query that
provides the name of the file from which the data came (this can be done by
building the SQL statement dynamically in code for the append query, and
then executing the append query's SQL statement via code).

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


"GEORGIA" <(E-Mail Removed)> wrote in message
news72A4305-72C1-4D48-946B-(E-Mail Removed)...
> Hi I have this code that will import multiple excle files into one table:
>
> Private Sub Command3_Click()
> Dim strFile As String
> Dim strFolder As String
>
> strFolder = Me.txtfilename.Value
> strFile = Dir$(strFolder & "*.xls")
> Do While Len(strFile) > 0
>
> DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTest",
> strFolder & strFile, True
>
> strFile = Dir$()
> Loop
>
> MsgBox "Finished", vbOKOnly
>
> End Sub
>
> There are over 50 excel files that are being imported. I have created a
> column called "File_Name" in a table and would like to update it with file
> name each time the excel is being imported so that I can refer back to
> where
> the data came from. How would I go about doing so?
>
> Thank you for help!!

Hi,
on # 4, how would i do a calculated field to show the file name?
Thanks


"Ken Snell MVP" wrote:

> You'll need a few extra steps.
>
> One, use an interim table to receive the imported data from one spreadsheet
> file.
>
> Two, run a delete query on that interim table to delete all records from it
> before you do the TransferSpreadsheet action for the file.
>
> Three, do the TransferSpreadsheet action to the interim table.
>
> Four, run an append query to copy the data from the interim table to the
> permanent table, and add a calculated field to the append query that
> provides the name of the file from which the data came (this can be done by
> building the SQL statement dynamically in code for the append query, and
> then executing the append query's SQL statement via code).
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
> http://www.accessmvp.com/KDSnell/
>
>
> "GEORGIA" <(E-Mail Removed)> wrote in message
> news72A4305-72C1-4D48-946B-(E-Mail Removed)...
> > Hi I have this code that will import multiple excle files into one table:
> >
> > Private Sub Command3_Click()
> > Dim strFile As String
> > Dim strFolder As String
> >
> > strFolder = Me.txtfilename.Value
> > strFile = Dir$(strFolder & "*.xls")
> > Do While Len(strFile) > 0
> >
> > DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTest",
> > strFolder & strFile, True
> >
> > strFile = Dir$()
> > Loop
> >
> > MsgBox "Finished", vbOKOnly
> >
> > End Sub
> >
> > There are over 50 excel files that are being imported. I have created a
> > column called "File_Name" in a table and would like to update it with file
> > name each time the excel is being imported so that I can refer back to
> > where
> > the data came from. How would I go about doing so?
> >
> > Thank you for help!!
>
>
>

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTest",
strFolder & strFile, True

Dim strSQL As String
Dim dbs As DAO.Database
Set dbs = CurrentDb
strSQL = "INSERT INTO [RealTableName] ( " & _
"FieldName1, FieldName2, FieldName3, FileNameField )" & _
" SELECT FieldName1, FieldName2, FieldName3, " & _
"'" & strFile & "' FROM [InterimTableName]"
dbs.Execute strSQL, dbFailOnError
Set dbs = Nothing

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



"GEORGIA" <(E-Mail Removed)> wrote in message
news:80F626DF-4313-4697-BE99-(E-Mail Removed)...
> Hi,
> on # 4, how would i do a calculated field to show the file name?
> Thanks
>
>
> "Ken Snell MVP" wrote:
>
>> You'll need a few extra steps.
>>
>> One, use an interim table to receive the imported data from one
>> spreadsheet
>> file.
>>
>> Two, run a delete query on that interim table to delete all records from
>> it
>> before you do the TransferSpreadsheet action for the file.
>>
>> Three, do the TransferSpreadsheet action to the interim table.
>>
>> Four, run an append query to copy the data from the interim table to the
>> permanent table, and add a calculated field to the append query that
>> provides the name of the file from which the data came (this can be done
>> by
>> building the SQL statement dynamically in code for the append query, and
>> then executing the append query's SQL statement via code).
>>
>> --
>>
>> Ken Snell
>> <MS ACCESS MVP>
>> http://www.accessmvp.com/KDSnell/
>>
>>
>> "GEORGIA" <(E-Mail Removed)> wrote in message
>> news72A4305-72C1-4D48-946B-(E-Mail Removed)...
>> > Hi I have this code that will import multiple excle files into one
>> > table:
>> >
>> > Private Sub Command3_Click()
>> > Dim strFile As String
>> > Dim strFolder As String
>> >
>> > strFolder = Me.txtfilename.Value
>> > strFile = Dir$(strFolder & "*.xls")
>> > Do While Len(strFile) > 0
>> >
>> > DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
>> > "tblTest",
>> > strFolder & strFile, True
>> >
>> > strFile = Dir$()
>> > Loop
>> >
>> > MsgBox "Finished", vbOKOnly
>> >
>> > End Sub
>> >
>> > There are over 50 excel files that are being imported. I have created
>> > a
>> > column called "File_Name" in a table and would like to update it with
>> > file
>> > name each time the excel is being imported so that I can refer back to
>> > where
>> > the data came from. How would I go about doing so?
>> >
>> > Thank you for help!!
>>
>>
>>

i am getting an error message (too few parameters)

Dim strFile As String
Dim strFolder As String
Dim strSQL As String
Dim dbs As DAO.Database
strFolder = Me.txtfilename.Value
strFile = Dir$(strFolder & "*.xls")
Do While Len(strFile) > 0

DoCmd.RunSQL "DELETE tblTemp.* FROM tblTemp;"


DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTemp",
strFolder & strFile, True


Set dbs = CurrentDb

strSQL = "INSERT INTO [Accountpay] ( " & _
"FieldName1, FieldName2, FieldName3, FileNameField )" & _
" SELECT FieldName1, FieldName2, FieldName3, " & _
"'" & strFile & "' FROM [tblTemp]"

dbs.Execute strSQL, dbFailOnError
Set dbs = Nothing




strFile = Dir$()
Loop

"Ken Snell MVP" wrote:

> DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTest",
> strFolder & strFile, True
>
> Dim strSQL As String
> Dim dbs As DAO.Database
> Set dbs = CurrentDb
> strSQL = "INSERT INTO [RealTableName] ( " & _
> "FieldName1, FieldName2, FieldName3, FileNameField )" & _
> " SELECT FieldName1, FieldName2, FieldName3, " & _
> "'" & strFile & "' FROM [InterimTableName]"
> dbs.Execute strSQL, dbFailOnError
> Set dbs = Nothing
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
> http://www.accessmvp.com/KDSnell/
>
>
>
> "GEORGIA" <(E-Mail Removed)> wrote in message
> news:80F626DF-4313-4697-BE99-(E-Mail Removed)...
> > Hi,
> > on # 4, how would i do a calculated field to show the file name?
> > Thanks
> >
> >
> > "Ken Snell MVP" wrote:
> >
> >> You'll need a few extra steps.
> >>
> >> One, use an interim table to receive the imported data from one
> >> spreadsheet
> >> file.
> >>
> >> Two, run a delete query on that interim table to delete all records from
> >> it
> >> before you do the TransferSpreadsheet action for the file.
> >>
> >> Three, do the TransferSpreadsheet action to the interim table.
> >>
> >> Four, run an append query to copy the data from the interim table to the
> >> permanent table, and add a calculated field to the append query that
> >> provides the name of the file from which the data came (this can be done
> >> by
> >> building the SQL statement dynamically in code for the append query, and
> >> then executing the append query's SQL statement via code).
> >>
> >> --
> >>
> >> Ken Snell
> >> <MS ACCESS MVP>
> >> http://www.accessmvp.com/KDSnell/
> >>
> >>
> >> "GEORGIA" <(E-Mail Removed)> wrote in message
> >> news72A4305-72C1-4D48-946B-(E-Mail Removed)...
> >> > Hi I have this code that will import multiple excle files into one
> >> > table:
> >> >
> >> > Private Sub Command3_Click()
> >> > Dim strFile As String
> >> > Dim strFolder As String
> >> >
> >> > strFolder = Me.txtfilename.Value
> >> > strFile = Dir$(strFolder & "*.xls")
> >> > Do While Len(strFile) > 0
> >> >
> >> > DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
> >> > "tblTest",
> >> > strFolder & strFile, True
> >> >
> >> > strFile = Dir$()
> >> > Loop
> >> >
> >> > MsgBox "Finished", vbOKOnly
> >> >
> >> > End Sub
> >> >
> >> > There are over 50 excel files that are being imported. I have created
> >> > a
> >> > column called "File_Name" in a table and would like to update it with
> >> > file
> >> > name each time the excel is being imported so that I can refer back to
> >> > where
> >> > the data came from. How would I go about doing so?
> >> >
> >> > Thank you for help!!
> >>
> >>
> >>
>
>
>

I used generic field names in the SQL statement. You must replace the
generic field names with the real field names -- and you must include all
the fields that are in the temporary and permanent table.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


"GEORGIA" <(E-Mail Removed)> wrote in message
news:1C7EE6E7-88C4-4A3B-99B2-(E-Mail Removed)...
>i am getting an error message (too few parameters)
>
> Dim strFile As String
> Dim strFolder As String
> Dim strSQL As String
> Dim dbs As DAO.Database
> strFolder = Me.txtfilename.Value
> strFile = Dir$(strFolder & "*.xls")
> Do While Len(strFile) > 0
>
> DoCmd.RunSQL "DELETE tblTemp.* FROM tblTemp;"
>
>
> DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTemp",
> strFolder & strFile, True
>
>
> Set dbs = CurrentDb
>
> strSQL = "INSERT INTO [Accountpay] ( " & _
> "FieldName1, FieldName2, FieldName3, FileNameField )" & _
> " SELECT FieldName1, FieldName2, FieldName3, " & _
> "'" & strFile & "' FROM [tblTemp]"
>
> dbs.Execute strSQL, dbFailOnError
> Set dbs = Nothing
>
>
>
>
> strFile = Dir$()
> Loop
>
> "Ken Snell MVP" wrote:
>
>> DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
>> "tblTest",
>> strFolder & strFile, True
>>
>> Dim strSQL As String
>> Dim dbs As DAO.Database
>> Set dbs = CurrentDb
>> strSQL = "INSERT INTO [RealTableName] ( " & _
>> "FieldName1, FieldName2, FieldName3, FileNameField )" & _
>> " SELECT FieldName1, FieldName2, FieldName3, " & _
>> "'" & strFile & "' FROM [InterimTableName]"
>> dbs.Execute strSQL, dbFailOnError
>> Set dbs = Nothing
>>
>> --
>>
>> Ken Snell
>> <MS ACCESS MVP>
>> http://www.accessmvp.com/KDSnell/
>>
>>
>>
>> "GEORGIA" <(E-Mail Removed)> wrote in message
>> news:80F626DF-4313-4697-BE99-(E-Mail Removed)...
>> > Hi,
>> > on # 4, how would i do a calculated field to show the file name?
>> > Thanks
>> >
>> >
>> > "Ken Snell MVP" wrote:
>> >
>> >> You'll need a few extra steps.
>> >>
>> >> One, use an interim table to receive the imported data from one
>> >> spreadsheet
>> >> file.
>> >>
>> >> Two, run a delete query on that interim table to delete all records
>> >> from
>> >> it
>> >> before you do the TransferSpreadsheet action for the file.
>> >>
>> >> Three, do the TransferSpreadsheet action to the interim table.
>> >>
>> >> Four, run an append query to copy the data from the interim table to
>> >> the
>> >> permanent table, and add a calculated field to the append query that
>> >> provides the name of the file from which the data came (this can be
>> >> done
>> >> by
>> >> building the SQL statement dynamically in code for the append query,
>> >> and
>> >> then executing the append query's SQL statement via code).
>> >>
>> >> --
>> >>
>> >> Ken Snell
>> >> <MS ACCESS MVP>
>> >> http://www.accessmvp.com/KDSnell/
>> >>
>> >>
>> >> "GEORGIA" <(E-Mail Removed)> wrote in message
>> >> news72A4305-72C1-4D48-946B-(E-Mail Removed)...
>> >> > Hi I have this code that will import multiple excle files into one
>> >> > table:
>> >> >
>> >> > Private Sub Command3_Click()
>> >> > Dim strFile As String
>> >> > Dim strFolder As String
>> >> >
>> >> > strFolder = Me.txtfilename.Value
>> >> > strFile = Dir$(strFolder & "*.xls")
>> >> > Do While Len(strFile) > 0
>> >> >
>> >> > DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
>> >> > "tblTest",
>> >> > strFolder & strFile, True
>> >> >
>> >> > strFile = Dir$()
>> >> > Loop
>> >> >
>> >> > MsgBox "Finished", vbOKOnly
>> >> >
>> >> > End Sub
>> >> >
>> >> > There are over 50 excel files that are being imported. I have
>> >> > created
>> >> > a
>> >> > column called "File_Name" in a table and would like to update it
>> >> > with
>> >> > file
>> >> > name each time the excel is being imported so that I can refer back
>> >> > to
>> >> > where
>> >> > the data came from. How would I go about doing so?
>> >> >
>> >> > Thank you for help!!
>> >>
>> >>
>> >>
>>
>>
>>

now I am getting a sytax error on Insert INTO statement..:

Private Sub Command3_Click()
Dim strFile As String
Dim strFolder As String

strFolder = Me.txtfilename.Value
strFile = Dir$(strFolder & "*.xls")
Do While Len(strFile) > 0


DoCmd.OpenQuery "query1", acViewNormal, acEdit
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTemp",
strFolder & strFile, True

Dim strSQL As String
Dim dbs As DAO.Database
Set dbs = CurrentDb

strSQL = "INSERT INTO [AccountPay] ( " & _
"ChkRoutingNum, ChkAccountNum, Points, Escrow, Cost, Maintenance, Other,
VendorId, VendorName, ContractId, Description, CITAppNo, CustName, InvoiceNo
)" & _
"SELECT ChkRoutingNum, ChkAccountNum, Points, Escrow, Cost, Maintenance,
Other, VendorId, VendorName, ContractId, Description, CITAppNo, CustName,
InvoiceNo)" & _
"'" & strFile & "' FROM [tblTemp]"

dbs.Execute strSQL, dbFailOnError


Set dbs = Nothing


strFile = Dir$()

Loop


End Sub

I can't figured out. Help!
Thanks!

"Ken Snell MVP" wrote:

> I used generic field names in the SQL statement. You must replace the
> generic field names with the real field names -- and you must include all
> the fields that are in the temporary and permanent table.
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
> http://www.accessmvp.com/KDSnell/
>
>
> "GEORGIA" <(E-Mail Removed)> wrote in message
> news:1C7EE6E7-88C4-4A3B-99B2-(E-Mail Removed)...
> >i am getting an error message (too few parameters)
> >
> > Dim strFile As String
> > Dim strFolder As String
> > Dim strSQL As String
> > Dim dbs As DAO.Database
> > strFolder = Me.txtfilename.Value
> > strFile = Dir$(strFolder & "*.xls")
> > Do While Len(strFile) > 0
> >
> > DoCmd.RunSQL "DELETE tblTemp.* FROM tblTemp;"
> >
> >
> > DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTemp",
> > strFolder & strFile, True
> >
> >
> > Set dbs = CurrentDb
> >
> > strSQL = "INSERT INTO [Accountpay] ( " & _
> > "FieldName1, FieldName2, FieldName3, FileNameField )" & _
> > " SELECT FieldName1, FieldName2, FieldName3, " & _
> > "'" & strFile & "' FROM [tblTemp]"
> >
> > dbs.Execute strSQL, dbFailOnError
> > Set dbs = Nothing
> >
> >
> >
> >
> > strFile = Dir$()
> > Loop
> >
> > "Ken Snell MVP" wrote:
> >
> >> DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
> >> "tblTest",
> >> strFolder & strFile, True
> >>
> >> Dim strSQL As String
> >> Dim dbs As DAO.Database
> >> Set dbs = CurrentDb
> >> strSQL = "INSERT INTO [RealTableName] ( " & _
> >> "FieldName1, FieldName2, FieldName3, FileNameField )" & _
> >> " SELECT FieldName1, FieldName2, FieldName3, " & _
> >> "'" & strFile & "' FROM [InterimTableName]"
> >> dbs.Execute strSQL, dbFailOnError
> >> Set dbs = Nothing
> >>
> >> --
> >>
> >> Ken Snell
> >> <MS ACCESS MVP>
> >> http://www.accessmvp.com/KDSnell/
> >>
> >>
> >>
> >> "GEORGIA" <(E-Mail Removed)> wrote in message
> >> news:80F626DF-4313-4697-BE99-(E-Mail Removed)...
> >> > Hi,
> >> > on # 4, how would i do a calculated field to show the file name?
> >> > Thanks
> >> >
> >> >
> >> > "Ken Snell MVP" wrote:
> >> >
> >> >> You'll need a few extra steps.
> >> >>
> >> >> One, use an interim table to receive the imported data from one
> >> >> spreadsheet
> >> >> file.
> >> >>
> >> >> Two, run a delete query on that interim table to delete all records
> >> >> from
> >> >> it
> >> >> before you do the TransferSpreadsheet action for the file.
> >> >>
> >> >> Three, do the TransferSpreadsheet action to the interim table.
> >> >>
> >> >> Four, run an append query to copy the data from the interim table to
> >> >> the
> >> >> permanent table, and add a calculated field to the append query that
> >> >> provides the name of the file from which the data came (this can be
> >> >> done
> >> >> by
> >> >> building the SQL statement dynamically in code for the append query,
> >> >> and
> >> >> then executing the append query's SQL statement via code).
> >> >>
> >> >> --
> >> >>
> >> >> Ken Snell
> >> >> <MS ACCESS MVP>
> >> >> http://www.accessmvp.com/KDSnell/
> >> >>
> >> >>
> >> >> "GEORGIA" <(E-Mail Removed)> wrote in message
> >> >> news72A4305-72C1-4D48-946B-(E-Mail Removed)...
> >> >> > Hi I have this code that will import multiple excle files into one
> >> >> > table:
> >> >> >
> >> >> > Private Sub Command3_Click()
> >> >> > Dim strFile As String
> >> >> > Dim strFolder As String
> >> >> >
> >> >> > strFolder = Me.txtfilename.Value
> >> >> > strFile = Dir$(strFolder & "*.xls")
> >> >> > Do While Len(strFile) > 0
> >> >> >
> >> >> > DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
> >> >> > "tblTest",
> >> >> > strFolder & strFile, True
> >> >> >
> >> >> > strFile = Dir$()
> >> >> > Loop
> >> >> >
> >> >> > MsgBox "Finished", vbOKOnly
> >> >> >
> >> >> > End Sub
> >> >> >
> >> >> > There are over 50 excel files that are being imported. I have
> >> >> > created
> >> >> > a
> >> >> > column called "File_Name" in a table and would like to update it
> >> >> > with
> >> >> > file
> >> >> > name each time the excel is being imported so that I can refer back
> >> >> > to
> >> >> > where
> >> >> > the data came from. How would I go about doing so?
> >> >> >
> >> >> > Thank you for help!!
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

You have syntax errors in the way you've formatted the statement.

When I use your strSQL = step in the VBA Immediate Window, and use
"test.xls" for the strFile variable, I get this:

INSERT INTO [AccountPay] ( ChkRoutingNum, ChkAccountNum, Points, Escrow,
Cost, Maintenance, Other, VendorId, VendorName, ContractId, Description,
CITAppNo, CustName, InvoiceNo )SELECT ChkRoutingNum, ChkAccountNum, Points,
Escrow, Cost, Maintenance, Other, VendorId, VendorName, ContractId,
Description, CITAppNo, CustName, InvoiceNo)'test.xls' FROM [tblTemp]


See the )SELECT in the middle? Needs to have a space between ) and SELECT.

Also see how you've got the ) to the left of 'test.xls'? It needs to be ,
'test.xls') instead.

So try this:

strSQL = "INSERT INTO [AccountPay] ( " & _
"ChkRoutingNum, ChkAccountNum, Points, Escrow, " & _
"Cost, Maintenance, Other, VendorId, VendorName, ContractId, " & _
"Description, CITAppNo, CustName, InvoiceNo ) " & _
"SELECT ChkRoutingNum, ChkAccountNum, Points, Escrow, " & _
"Cost, Maintenance, Other, VendorId, VendorName, ContractId, " & _
"Description, CITAppNo, CustName, InvoiceNo, '" & _
strFile & "' FROM [tblTemp]"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



"GEORGIA" <(E-Mail Removed)> wrote in message
news:9714A9E9-620F-4253-869A-(E-Mail Removed)...
> now I am getting a sytax error on Insert INTO statement..:
>
> Private Sub Command3_Click()
> Dim strFile As String
> Dim strFolder As String
>
> strFolder = Me.txtfilename.Value
> strFile = Dir$(strFolder & "*.xls")
> Do While Len(strFile) > 0
>
>
> DoCmd.OpenQuery "query1", acViewNormal, acEdit
> DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTemp",
> strFolder & strFile, True
>
> Dim strSQL As String
> Dim dbs As DAO.Database
> Set dbs = CurrentDb
>
> strSQL = "INSERT INTO [AccountPay] ( " & _
> "ChkRoutingNum, ChkAccountNum, Points, Escrow, Cost, Maintenance, Other,
> VendorId, VendorName, ContractId, Description, CITAppNo, CustName,
> InvoiceNo
> )" & _
> "SELECT ChkRoutingNum, ChkAccountNum, Points, Escrow, Cost, Maintenance,
> Other, VendorId, VendorName, ContractId, Description, CITAppNo, CustName,
> InvoiceNo)" & _
> "'" & strFile & "' FROM [tblTemp]"
>
> dbs.Execute strSQL, dbFailOnError
>
>
> Set dbs = Nothing
>
>
> strFile = Dir$()
>
> Loop
>
>
> End Sub
>
> I can't figured out. Help!
> Thanks!
>
> "Ken Snell MVP" wrote:
>
>> I used generic field names in the SQL statement. You must replace the
>> generic field names with the real field names -- and you must include all
>> the fields that are in the temporary and permanent table.
>>
>> --
>>
>> Ken Snell
>> <MS ACCESS MVP>
>> http://www.accessmvp.com/KDSnell/
>>
>>
>> "GEORGIA" <(E-Mail Removed)> wrote in message
>> news:1C7EE6E7-88C4-4A3B-99B2-(E-Mail Removed)...
>> >i am getting an error message (too few parameters)
>> >
>> > Dim strFile As String
>> > Dim strFolder As String
>> > Dim strSQL As String
>> > Dim dbs As DAO.Database
>> > strFolder = Me.txtfilename.Value
>> > strFile = Dir$(strFolder & "*.xls")
>> > Do While Len(strFile) > 0
>> >
>> > DoCmd.RunSQL "DELETE tblTemp.* FROM tblTemp;"
>> >
>> >
>> > DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
>> > "tblTemp",
>> > strFolder & strFile, True
>> >
>> >
>> > Set dbs = CurrentDb
>> >
>> > strSQL = "INSERT INTO [Accountpay] ( " & _
>> > "FieldName1, FieldName2, FieldName3, FileNameField )" & _
>> > " SELECT FieldName1, FieldName2, FieldName3, " & _
>> > "'" & strFile & "' FROM [tblTemp]"
>> >
>> > dbs.Execute strSQL, dbFailOnError
>> > Set dbs = Nothing
>> >
>> >
>> >
>> >
>> > strFile = Dir$()
>> > Loop
>> >
>> > "Ken Snell MVP" wrote:
>> >
>> >> DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
>> >> "tblTest",
>> >> strFolder & strFile, True
>> >>
>> >> Dim strSQL As String
>> >> Dim dbs As DAO.Database
>> >> Set dbs = CurrentDb
>> >> strSQL = "INSERT INTO [RealTableName] ( " & _
>> >> "FieldName1, FieldName2, FieldName3, FileNameField )" & _
>> >> " SELECT FieldName1, FieldName2, FieldName3, " & _
>> >> "'" & strFile & "' FROM [InterimTableName]"
>> >> dbs.Execute strSQL, dbFailOnError
>> >> Set dbs = Nothing
>> >>
>> >> --
>> >>
>> >> Ken Snell
>> >> <MS ACCESS MVP>
>> >> http://www.accessmvp.com/KDSnell/
>> >>
>> >>
>> >>
>> >> "GEORGIA" <(E-Mail Removed)> wrote in message
>> >> news:80F626DF-4313-4697-BE99-(E-Mail Removed)...
>> >> > Hi,
>> >> > on # 4, how would i do a calculated field to show the file name?
>> >> > Thanks
>> >> >
>> >> >
>> >> > "Ken Snell MVP" wrote:
>> >> >
>> >> >> You'll need a few extra steps.
>> >> >>
>> >> >> One, use an interim table to receive the imported data from one
>> >> >> spreadsheet
>> >> >> file.
>> >> >>
>> >> >> Two, run a delete query on that interim table to delete all records
>> >> >> from
>> >> >> it
>> >> >> before you do the TransferSpreadsheet action for the file.
>> >> >>
>> >> >> Three, do the TransferSpreadsheet action to the interim table.
>> >> >>
>> >> >> Four, run an append query to copy the data from the interim table
>> >> >> to
>> >> >> the
>> >> >> permanent table, and add a calculated field to the append query
>> >> >> that
>> >> >> provides the name of the file from which the data came (this can be
>> >> >> done
>> >> >> by
>> >> >> building the SQL statement dynamically in code for the append
>> >> >> query,
>> >> >> and
>> >> >> then executing the append query's SQL statement via code).
>> >> >>
>> >> >> --
>> >> >>
>> >> >> Ken Snell
>> >> >> <MS ACCESS MVP>
>> >> >> http://www.accessmvp.com/KDSnell/
>> >> >>
>> >> >>
>> >> >> "GEORGIA" <(E-Mail Removed)> wrote in message
>> >> >> news72A4305-72C1-4D48-946B-(E-Mail Removed)...
>> >> >> > Hi I have this code that will import multiple excle files into
>> >> >> > one
>> >> >> > table:
>> >> >> >
>> >> >> > Private Sub Command3_Click()
>> >> >> > Dim strFile As String
>> >> >> > Dim strFolder As String
>> >> >> >
>> >> >> > strFolder = Me.txtfilename.Value
>> >> >> > strFile = Dir$(strFolder & "*.xls")
>> >> >> > Do While Len(strFile) > 0
>> >> >> >
>> >> >> > DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
>> >> >> > "tblTest",
>> >> >> > strFolder & strFile, True
>> >> >> >
>> >> >> > strFile = Dir$()
>> >> >> > Loop
>> >> >> >
>> >> >> > MsgBox "Finished", vbOKOnly
>> >> >> >
>> >> >> > End Sub
>> >> >> >
>> >> >> > There are over 50 excel files that are being imported. I have
>> >> >> > created
>> >> >> > a
>> >> >> > column called "File_Name" in a table and would like to update it
>> >> >> > with
>> >> >> > file
>> >> >> > name each time the excel is being imported so that I can refer
>> >> >> > back
>> >> >> > to
>> >> >> > where
>> >> >> > the data came from. How would I go about doing so?
>> >> >> >
>> >> >> > Thank you for help!!
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

oh I GOT IT! THANK YOU SO MUCH!
"Ken Snell MVP" wrote:

> You have syntax errors in the way you've formatted the statement.
>
> When I use your strSQL = step in the VBA Immediate Window, and use
> "test.xls" for the strFile variable, I get this:
>
> INSERT INTO [AccountPay] ( ChkRoutingNum, ChkAccountNum, Points, Escrow,
> Cost, Maintenance, Other, VendorId, VendorName, ContractId, Description,
> CITAppNo, CustName, InvoiceNo )SELECT ChkRoutingNum, ChkAccountNum, Points,
> Escrow, Cost, Maintenance, Other, VendorId, VendorName, ContractId,
> Description, CITAppNo, CustName, InvoiceNo)'test.xls' FROM [tblTemp]
>
>
> See the )SELECT in the middle? Needs to have a space between ) and SELECT.
>
> Also see how you've got the ) to the left of 'test.xls'? It needs to be ,
> 'test.xls') instead.
>
> So try this:
>
> strSQL = "INSERT INTO [AccountPay] ( " & _
> "ChkRoutingNum, ChkAccountNum, Points, Escrow, " & _
> "Cost, Maintenance, Other, VendorId, VendorName, ContractId, " & _
> "Description, CITAppNo, CustName, InvoiceNo ) " & _
> "SELECT ChkRoutingNum, ChkAccountNum, Points, Escrow, " & _
> "Cost, Maintenance, Other, VendorId, VendorName, ContractId, " & _
> "Description, CITAppNo, CustName, InvoiceNo, '" & _
> strFile & "' FROM [tblTemp]"
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
> http://www.accessmvp.com/KDSnell/
>
>
>
> "GEORGIA" <(E-Mail Removed)> wrote in message
> news:9714A9E9-620F-4253-869A-(E-Mail Removed)...
> > now I am getting a sytax error on Insert INTO statement..:
> >
> > Private Sub Command3_Click()
> > Dim strFile As String
> > Dim strFolder As String
> >
> > strFolder = Me.txtfilename.Value
> > strFile = Dir$(strFolder & "*.xls")
> > Do While Len(strFile) > 0
> >
> >
> > DoCmd.OpenQuery "query1", acViewNormal, acEdit
> > DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTemp",
> > strFolder & strFile, True
> >
> > Dim strSQL As String
> > Dim dbs As DAO.Database
> > Set dbs = CurrentDb
> >
> > strSQL = "INSERT INTO [AccountPay] ( " & _
> > "ChkRoutingNum, ChkAccountNum, Points, Escrow, Cost, Maintenance, Other,
> > VendorId, VendorName, ContractId, Description, CITAppNo, CustName,
> > InvoiceNo
> > )" & _
> > "SELECT ChkRoutingNum, ChkAccountNum, Points, Escrow, Cost, Maintenance,
> > Other, VendorId, VendorName, ContractId, Description, CITAppNo, CustName,
> > InvoiceNo)" & _
> > "'" & strFile & "' FROM [tblTemp]"
> >
> > dbs.Execute strSQL, dbFailOnError
> >
> >
> > Set dbs = Nothing
> >
> >
> > strFile = Dir$()
> >
> > Loop
> >
> >
> > End Sub
> >
> > I can't figured out. Help!
> > Thanks!
> >
> > "Ken Snell MVP" wrote:
> >
> >> I used generic field names in the SQL statement. You must replace the
> >> generic field names with the real field names -- and you must include all
> >> the fields that are in the temporary and permanent table.
> >>
> >> --
> >>
> >> Ken Snell
> >> <MS ACCESS MVP>
> >> http://www.accessmvp.com/KDSnell/
> >>
> >>
> >> "GEORGIA" <(E-Mail Removed)> wrote in message
> >> news:1C7EE6E7-88C4-4A3B-99B2-(E-Mail Removed)...
> >> >i am getting an error message (too few parameters)
> >> >
> >> > Dim strFile As String
> >> > Dim strFolder As String
> >> > Dim strSQL As String
> >> > Dim dbs As DAO.Database
> >> > strFolder = Me.txtfilename.Value
> >> > strFile = Dir$(strFolder & "*.xls")
> >> > Do While Len(strFile) > 0
> >> >
> >> > DoCmd.RunSQL "DELETE tblTemp.* FROM tblTemp;"
> >> >
> >> >
> >> > DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
> >> > "tblTemp",
> >> > strFolder & strFile, True
> >> >
> >> >
> >> > Set dbs = CurrentDb
> >> >
> >> > strSQL = "INSERT INTO [Accountpay] ( " & _
> >> > "FieldName1, FieldName2, FieldName3, FileNameField )" & _
> >> > " SELECT FieldName1, FieldName2, FieldName3, " & _
> >> > "'" & strFile & "' FROM [tblTemp]"
> >> >
> >> > dbs.Execute strSQL, dbFailOnError
> >> > Set dbs = Nothing
> >> >
> >> >
> >> >
> >> >
> >> > strFile = Dir$()
> >> > Loop
> >> >
> >> > "Ken Snell MVP" wrote:
> >> >
> >> >> DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
> >> >> "tblTest",
> >> >> strFolder & strFile, True
> >> >>
> >> >> Dim strSQL As String
> >> >> Dim dbs As DAO.Database
> >> >> Set dbs = CurrentDb
> >> >> strSQL = "INSERT INTO [RealTableName] ( " & _
> >> >> "FieldName1, FieldName2, FieldName3, FileNameField )" & _
> >> >> " SELECT FieldName1, FieldName2, FieldName3, " & _
> >> >> "'" & strFile & "' FROM [InterimTableName]"
> >> >> dbs.Execute strSQL, dbFailOnError
> >> >> Set dbs = Nothing
> >> >>
> >> >> --
> >> >>
> >> >> Ken Snell
> >> >> <MS ACCESS MVP>
> >> >> http://www.accessmvp.com/KDSnell/
> >> >>
> >> >>
> >> >>
> >> >> "GEORGIA" <(E-Mail Removed)> wrote in message
> >> >> news:80F626DF-4313-4697-BE99-(E-Mail Removed)...
> >> >> > Hi,
> >> >> > on # 4, how would i do a calculated field to show the file name?
> >> >> > Thanks
> >> >> >
> >> >> >
> >> >> > "Ken Snell MVP" wrote:
> >> >> >
> >> >> >> You'll need a few extra steps.
> >> >> >>
> >> >> >> One, use an interim table to receive the imported data from one
> >> >> >> spreadsheet
> >> >> >> file.
> >> >> >>
> >> >> >> Two, run a delete query on that interim table to delete all records
> >> >> >> from
> >> >> >> it
> >> >> >> before you do the TransferSpreadsheet action for the file.
> >> >> >>
> >> >> >> Three, do the TransferSpreadsheet action to the interim table.
> >> >> >>
> >> >> >> Four, run an append query to copy the data from the interim table
> >> >> >> to
> >> >> >> the
> >> >> >> permanent table, and add a calculated field to the append query
> >> >> >> that
> >> >> >> provides the name of the file from which the data came (this can be
> >> >> >> done
> >> >> >> by
> >> >> >> building the SQL statement dynamically in code for the append
> >> >> >> query,
> >> >> >> and
> >> >> >> then executing the append query's SQL statement via code).
> >> >> >>
> >> >> >> --
> >> >> >>
> >> >> >> Ken Snell
> >> >> >> <MS ACCESS MVP>
> >> >> >> http://www.accessmvp.com/KDSnell/
> >> >> >>
> >> >> >>
> >> >> >> "GEORGIA" <(E-Mail Removed)> wrote in message
> >> >> >> news72A4305-72C1-4D48-946B-(E-Mail Removed)...
> >> >> >> > Hi I have this code that will import multiple excle files into
> >> >> >> > one
> >> >> >> > table:
> >> >> >> >
> >> >> >> > Private Sub Command3_Click()
> >> >> >> > Dim strFile As String
> >> >> >> > Dim strFolder As String
> >> >> >> >
> >> >> >> > strFolder = Me.txtfilename.Value
> >> >> >> > strFile = Dir$(strFolder & "*.xls")
> >> >> >> > Do While Len(strFile) > 0
> >> >> >> >
> >> >> >> > DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
> >> >> >> > "tblTest",
> >> >> >> > strFolder & strFile, True
> >> >> >> >
> >> >> >> > strFile = Dir$()
> >> >> >> > Loop
> >> >> >> >
> >> >> >> > MsgBox "Finished", vbOKOnly
> >> >> >> >
> >> >> >> > End Sub
> >> >> >> >
> >> >> >> > There are over 50 excel files that are being imported. I have
> >> >> >> > created
> >> >> >> > a
> >> >> >> > column called "File_Name" in a table and would like to update it
> >> >> >> > with
> >> >> >> > file
> >> >> >> > name each time the excel is being imported so that I can refer
> >> >> >> > back
> >> >> >> > to
> >> >> >> > where
> >> >> >> > the data came from. How would I go about doing so?
> >> >> >> >
> >> >> >> > Thank you for help!!
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>