Avatar of Jenkins
Jenkins
Flag for United States of America 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.
Microsoft Access

Avatar of undefined
Last Comment
Jenkins

8/22/2022 - Mon
Mark Edwards

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
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],[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

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
Mark Edwards

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Jenkins

ASKER
Great! Thanks so much.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck