Need an example of importing from Excel to Access

[M Skabialka]

A user has asked that some of his spreadsheets be combined into an Access
database. Each workbook has multiple sheets, with most of them being charts
using data from the first worksheet in each workbook. These graphs are then
copy/pasted into PowerPoint. Can that be done from Access also?

Also, one spreadsheet comes from an outside source and a new one comes in
every two weeks, replacing the old one. There are five header rows, some of
which are merged vertically. The columns are rearranged with each new
version of the spreadsheet based on a delivery date for the item in the
column, and data in the column changes also, as does the number of columns
and rows. The first six columns are static.
I think that each two weeks I will have to delete a table in Access and
replace it with data imported from the newest spreadsheet, but have never
worked with Excel data before, and also don't know how merged cells will
effect anything - these contain the item ID number which will be critical.
Each column merges a different number of cells from 2 to 5 cells.
Does any of the Access guru's have an example database showing how to import
from Excel? Currently it will be Office 2003, but soon will be 2007. The
code will have to work with either. I have done imports with text delimited
data but this is entrely new to me.

Mich

 

[Richard]

Mich,

Take a look a Ken's site for some code ideas.

http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

You may have to build your database around the .Xls your importing. If you
can match the column heads to your table field names it might work. I import
3-4 spreadsheets a week from different sources, I have to tweak the column
heads and delete unnecessary data but it can be done.

hth
Good luck
Richard

 

[M Skabialka]

The spreadsheet is set up exactly opposite of what importing is expecting.
Instead of a column of VehicleIDs, these are in a row, with info in the rows
beneath, so I need to be able to look at the spreasheet cell by cell and
analyze the data to put it into an Access table. I'm probably going to have
to start at the top of each column and work down to get one record for
Access.

All of the import examples I have seen so far assume each Excel row is a
Access row, while for this application each spreadsheet column is an Access
row.
The spreadsheet is created by another entity which we have no control over.

Any more ideas on how I can import this Excell spreadsheetsheet bi-weekly
into Access?

Mich

 

[Clif McIrvin]

My approach would be to attempt a series of steps to see if they get
you where you want to go, then begin to develop VBA code to repeat the
process.

Step 1 would be to discover if the Excel Transpose function will
successfully transpose the data into the row / column format that Access
expects to find.

Step 2: can that data be imported, or is some editing necessary?

Step 3: Can that editing be automated, or will it require human decision
making?

Step 4: Develop Macros in Excel to prepare the data for importing.

Step 5: Develop COM Automation code in Access to do the same thing as
Step 4 but now in code behind a form in Access.


Good luck! and come back here with specific questions as you need
assistance.