Reference a specific cell when importing an Excel file into Access

dbfromnewjersey used Ask the Experts™
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.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mark EdwardsChief Technology Officer
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.
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()
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],[AccessField2], [AccessField3], [AccessField4], [AccessField5]"
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

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.
Chief Technology Officer
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.
Great! Thanks so much.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial