Import data from Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In my Access 2000 database I sometimes import data from MS Excel files.
For that purpose I have a form with a control button with the following
OnClick event:

Dim strSQL As String
Const Q = "'"

strSQL = "INSERT INTO MyTable ( BranchID, Date, Amount) " & _
"SELECT BranchID, Date, Amount " & _
"FROM [ExcelSheet$] IN " & Q & Me.FilePath & Q & " [EXCEL 5.0;]"

CurrentDb.Execute strSQL, dbFailOnError
End If

It works fine.

Now I need to make something alike, but instead of "BranchID" value from
ExcelSheet a constant should be inserted, e.g. "01".
Simply speaking:
- field "BranchID" should be always filled with "01" value;
- field "Date" should take values from "Date" column in Excel File;
- field "Amount" should take values from "Amount" column in Excel File;

Is it possible?
Thanks in advance!
 
In my Access 2000 database I sometimes import data from MS Excel files.
For that purpose I have a form with a control button with the following
OnClick event:

Dim strSQL As String
Const Q = "'"

strSQL = "INSERT INTO MyTable ( BranchID, Date, Amount) " & _
"SELECT BranchID, Date, Amount " & _
"FROM [ExcelSheet$] IN " & Q & Me.FilePath & Q & " [EXCEL 5.0;]"

CurrentDb.Execute strSQL, dbFailOnError
End If

It works fine.

Now I need to make something alike, but instead of "BranchID" value from
ExcelSheet a constant should be inserted, e.g. "01".
Simply speaking:
- field "BranchID" should be always filled with "01" value;
- field "Date" should take values from "Date" column in Excel File;
- field "Amount" should take values from "Amount" column in Excel File;

Is it possible?
Click to expand...

2nd line:
SELECT '01' as F1, Date, Amount "
 
Back
Top