append file name to a table

G

GEORGIA

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!!
 
K

Ken Snell MVP

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).
 
G

GEORGIA

Hi,
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/
Click to expand...
 
K

Ken Snell MVP

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/
 
G

GEORGIA

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 said:
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 said:
Hi,
Click to expand...
Click to expand...
 
K

Ken Snell MVP

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 said:
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 said:
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 said:
Hi,
on # 4, how would i do a calculated field to show the file name?
Thanks


:

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/


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!!
Click to expand...
Click to expand...
Click to expand...
 
G

GEORGIA

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 said:
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 said:
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 said:
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/



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


:

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/


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!!
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell MVP

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 said:
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 said:
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 said:
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

:

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/



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


:

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/


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!!
Click to expand...
Click to expand...
Click to expand...
 
G

GEORGIA

oh I GOT IT! THANK YOU SO MUCH!
Ken Snell MVP said:
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 said:
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 said:
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/


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

:

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/



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


:

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/


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!!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

run code from Module via a macro 6
Import File Information 0
Importing multiple Files 10
how to import multiple Excel files into multiple Access tables? 9
Import, Rename .csv files with date created as field 2
importing from Excel To Access 2
Multiple .CSV into One DB 12
Move Files after import 1

Top