append file name to a table

  • Thread starter Thread starter GEORGIA
  • Start date Start date
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!!
 
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).
 
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...
 
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/
 
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...
 
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...
 
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...
 
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...
 
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...
 
Back
Top