Reference a specific cell when importing an Excel file into Access
strSourceFieldsList = "MID([F1],1,6), MID([F1],7,50), [F2], [F3], 'November 19'"
In the above section of code, rather than having the month and year hardcoded as it is in the fifth field above, I'd like to instead grab the value from cell D10 of the Excel import file. Cell D10 is a header cell that always contains the month and year of the import file/report.
In the above section of code, rather than having the month and year hardcoded as it is in the fifth field above, I'd like to instead grab the value from cell D10 of the Excel import file. Cell D10 is a header cell that always contains the month and year of the import file/report.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hey dbfnj:
If you are using SQL to import, and you have the header row included in your import, you can modify the SQL statement to make it a "SELECT" query and use the querydef to get the name of any column name you want. See attached database. Run the import with headings. If it doesn't work for you, let me know what you're really trying to do.
Test-Database.accdb
If you are using SQL to import, and you have the header row included in your import, you can modify the SQL statement to make it a "SELECT" query and use the querydef to get the name of any column name you want. See attached database. Run the import with headings. If it doesn't work for you, let me know what you're really trying to do.
Test-Database.accdb
Thank you. I'll explain what exactly I'm trying to do. I tried modifying your code a bit on my own but no success, so here I am again.
This is your code for when the input file has no headers (additional comments from me follow your code):
Private Sub Command10_Click()
'// IMPORT EXCEL FILE WITHOUT HEADINGS:
On Error GoTo sub_err 'SET ERROR HANDLER.
Dim strImportTableName As String
Dim strTargetFieldsList As String
Dim strSourceFieldsList As String
Dim blnHasHeaders As Boolean
Dim strExcelFilePath As String
Dim strSheetName As String
Dim strImportRange As String
Dim strSQL As String
'New variable to hold period the Excel report pertains to
Dim HoldPeriod As String
strImportTableName = "tblMyAccessTable"
CurrentDb.Execute "DELETE * FROM [tblMyAccessTable]"
strTargetFieldsList = "[AccessField1],[AccessFie
strSourceFieldsList = "MID([F1],1,6), MID([F1],7,50), [F2], [F3], 'June'"
blnHasHeaders = False
strExcelFilePath = Me.txtSelectedFile & ""
strSheetName = Me.txtSheetName & ""
strImportRange = Me.txtRange & ""
strSQL = "INSERT INTO [tblMyAccessTable] ( " & strTargetFieldsList & " )"
strSQL = strSQL & vbCrLf & " SELECT " & strSourceFieldsList
strSQL = strSQL & vbCrLf & " FROM [Excel 12.0;HDR=" & IIf(blnHasHeaders = True, "YES", "NO") & ";Database=" & Me.txtSelectedFile & "].[" & strSheetName & "$" & strImportRange & "]"
CurrentDb.Execute strSQL
exit_sub: 'SINGLE POINT OF EXIT TO MAINTAIN CONTROL OF PROCESSING.
DoCmd.OpenTable "tblMyAccessTable"
Exit Sub
sub_err: 'ERROR HANDLER.
MsgBox Err.Description, vbCritical, "Error in " & Me.Name & ".Command1_Click() event:"
Resume exit_sub
End Sub
In the above, you'll see I hardcoded "June". As you can see, what that winds up doing is copying "June" down the entire Access table, which is basically what I'm after. I want the month of the report to be listed on every row in the table.
However, because this will be a recurring task and I will be dealing with different months, I'm hoping to grab the month name from the report itself and populate every row of the Access table just as hardcoding it does.
In concept (but I can't get it to work), I was thinking to run an additional or initial SQL statement that retrieves the month name from the report (the month name on the report always appears in cell B15) and to place it in a variable. Then, rather than hardcode the month like I did with June, I could replace it the variable name. That way, whatever month value is in the variable will be copied to every row of the table.
This is your code for when the input file has no headers (additional comments from me follow your code):
Private Sub Command10_Click()
'// IMPORT EXCEL FILE WITHOUT HEADINGS:
On Error GoTo sub_err 'SET ERROR HANDLER.
Dim strImportTableName As String
Dim strTargetFieldsList As String
Dim strSourceFieldsList As String
Dim blnHasHeaders As Boolean
Dim strExcelFilePath As String
Dim strSheetName As String
Dim strImportRange As String
Dim strSQL As String
'New variable to hold period the Excel report pertains to
Dim HoldPeriod As String
strImportTableName = "tblMyAccessTable"
CurrentDb.Execute "DELETE * FROM [tblMyAccessTable]"
strTargetFieldsList = "[AccessField1],[AccessFie
strSourceFieldsList = "MID([F1],1,6), MID([F1],7,50), [F2], [F3], 'June'"
blnHasHeaders = False
strExcelFilePath = Me.txtSelectedFile & ""
strSheetName = Me.txtSheetName & ""
strImportRange = Me.txtRange & ""
strSQL = "INSERT INTO [tblMyAccessTable] ( " & strTargetFieldsList & " )"
strSQL = strSQL & vbCrLf & " SELECT " & strSourceFieldsList
strSQL = strSQL & vbCrLf & " FROM [Excel 12.0;HDR=" & IIf(blnHasHeaders = True, "YES", "NO") & ";Database=" & Me.txtSelectedFile & "].[" & strSheetName & "$" & strImportRange & "]"
CurrentDb.Execute strSQL
exit_sub: 'SINGLE POINT OF EXIT TO MAINTAIN CONTROL OF PROCESSING.
DoCmd.OpenTable "tblMyAccessTable"
Exit Sub
sub_err: 'ERROR HANDLER.
MsgBox Err.Description, vbCritical, "Error in " & Me.Name & ".Command1_Click() event:"
Resume exit_sub
End Sub
In the above, you'll see I hardcoded "June". As you can see, what that winds up doing is copying "June" down the entire Access table, which is basically what I'm after. I want the month of the report to be listed on every row in the table.
However, because this will be a recurring task and I will be dealing with different months, I'm hoping to grab the month name from the report itself and populate every row of the Access table just as hardcoding it does.
In concept (but I can't get it to work), I was thinking to run an additional or initial SQL statement that retrieves the month name from the report (the month name on the report always appears in cell B15) and to place it in a variable. Then, rather than hardcode the month like I did with June, I could replace it the variable name. That way, whatever month value is in the variable will be copied to every row of the table.
OK. Sounds easy enough. Since you know what sheet the name is on and what cell it is in, you can create a select statement that just pulls that one cell and use it to set a recordset that you can get the report name from. See attached files. I've put the report name in Cell B2 of the Excel file. You can adjust the code to your specific situation.
Test-Database.accdb
TEST_FILE.xlsx
Test-Database.accdb
TEST_FILE.xlsx
Premium Content
You need an Expert Office subscription to comment.Start Free Trial