asked on 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.
Microsoft Access
Last Comment
Jenkins
ASKER
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Great! Thanks so much.
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