Link to home
Create AccountLog in
Avatar of Jenkins
JenkinsFlag 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.
Avatar of Mark Edwards
Mark Edwards
Flag of United States of America image

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.
Avatar of Jenkins


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.
Avatar of Mark Edwards
Mark Edwards
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Jenkins


Great! Thanks so much.