You can use an Access "append query" with your worksheet as its data source. That way you can import only the columns you need, and you can also extract the date from the workbook file name and use it for a query parameter.
In this example, I import only one XLSX file. My Access destination table, "FD_Worksheet_master", includes a Date/Time field named "file_date". And the Excel data I want to import is in a worksheet named "Sheet1".
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim astrPieces() As String
Dim dteFileDate As Date
Dim strDir As String
Dim strFile As String
Dim strInsert As String
strDir = "C:\Users\hans\Documents\"
strFile = "FD Worksheet 01 06 2016.xlsx"
If Not strDir Like "*\" Then
strDir = strDir & "\"
strInsert = "INSERT INTO FD_Worksheet_master (file_date, Annual, Monthly, Hourly)" & vbCrLf & _
"SELECT [which_date] as file_date, xl.Annual, xl.Monthly, xl.Hourly" & vbCrLf & _
"FROM [Excel 12.0 Xml;HDR=YES;IMEX=2;DATABASE=" & strDir & strFile & "].[Sheet1$] AS xl;"
astrPieces = Split(strFile, " ")
' Note: I assumed "01 06 2016" is "mm dd yyyy" format.
' If actually "dd mm yyyy", swap the order of astrPieces(2) and astrPieces(3)
dteFileDate = DateSerial(Val(astrPieces(4)), astrPieces(2), astrPieces(3))
Set db = CurrentDb
Set qdf = db.CreateQueryDef(vbNullString, strInsert)
qdf.Parameters("which_date").Value = dteFileDate
You may need to change the IMEX value; if 2 doesn't work, try 1.
Many thanks Hans! After a very small modification to your code, I managed to get it to work perfectly for me. It is now placing the date from the Excel file name into the 'file_date' field within the 'FD_Worksheet_master' table for every row in the original spreadsheet. From here, do you know how I would import the columns 'K', 'N', 'AO', and 'AP' from Sheet1 of the worksheet? I'm very new to MS Access VBA, so please forgive my lack of ability!
I used HDR=YES in the query's connection to the workbook because you had True for the HasFieldNames parameter with TransferSpreadsheet. So that means the field/column names/headers should be present in the first row of the worksheet. Look up the names for those columns and substitute those where I had xl.Annual, xl.Monthly, xl.Hourly in my query.
If I misunderstood your situation, and you don't have names/headers in the first spreadsheet row, make another query which is just SELECT * with your working FROM clause and HDR=NO. When you run that query, Access will assign names ... such as F1, F2, etc. ... to the columns. Find out which names correspond to the columns you want, and use those names in the main query.
Again Hans, your solution worked perfectly. I added my column names to the code and it brought everything in as I wanted.I will post my final code below. Thank you!