Transfer specific columns from Excel to specific columns in Access Table

Question

I am attempting to transfer specific columns from an excel document to specific columns to a table in my Access database. For example my excel sheet might contain the following:

Date  Last  First  Gender   Month  School  Grade 
10/1  Rode  Danny  Male     Sept.  Ro ISD     10 
10/2  Dode  Manny  Male     Sept.  Ro ISD     11

And My Student Table for my database may contain the fields:

Type Source Phone Email Last First School Major School Grade

I only wish to export the excel columns labeled: Last, First, School, Grade and put them in their respective columns in my student table. I've looked at the DoCmd.TransferSpreadSheet in VisualBasic through access but am unsure if this is a solution or if there is another way. Any help would be greatly appreciated as it would savce me soooo much time! Thanks!

Answer 1

Consider directly querying from Excel worksheet as both can interface to the Jet/ACE SQL Engine. Below assumes your worksheet's data begins in A1 with column headers. Integrate below action query in a VBA DAO/ADO call or directly place it in Access' query design (SQL mode).

INSERT INTO myAccessTable ([Last], [First], [School], [Grade])
SELECT [Last], [First], [School], [Grade] 
FROM [Excel 12.0 Xml;HDR=Yes;Database=C:\Path\To\Excel\File.xlsx].[SheetName$]

Answer 2

There are many ways to accomplish this. A quick method would be to import all of the data from the Excel spreadsheet into a table (i.e. "MyExcelImportTable"). Then you could Create and run a query that would append just the data you want.

INSERT INTO MyStudentTable(Last, First, School, Grade)
SELECT Last, First, School, Grade FROM MyExcelImportTable