CODEWITHSUNDEEP
excelms-accessvbams-access-2010
alpha_nom
alpha_nom

Reputation: 331

Transfer specific columns from Excel to specific columns in Access Table

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!

Upvotes: 7

Views: 11200

Answers (3)

Parfait
Parfait

Reputation: 107757

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$]

Upvotes: 5

AndrewBanjo1968
AndrewBanjo1968

Reputation: 115

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

Upvotes: 5

SeanC
SeanC

Reputation: 15923

DoCmd.TransferSpreadSheet will only transfer a whole sheet. There are a couple of ways you could transfer what you need:

  1. Copy the required columns to a second sheet, and transfer that sheet

  2. Save the data as a CSV, and import the CSV, where you can specify columns

Upvotes: 2

Related Questions