linking cells in Excel to Access (2002 – XP)

Topic

New Reply

Hello all,

I am having a devil of a time. I have years of data in Excel spreadsheets and would like to be able to manipulate the data in Access. I cannot figure out a good way to do it. I have tried the Excel feature “Convert to MS Access” but I just get the error message that the database cannot be created.

A brief explanation of what I have and what I want:

Have:
Individual (by month) workbooks containing a spreadsheets for each day of each year for seven years. With one additional spreadsheet that consolidates all the totals from each day.
Basic layout of the sheets has not changed from the beginning.

Want:
To link, import, something, the data from cells in the total (consolidation sheets) spreadsheets to cells in an Access database. The purpose of this being I want to create a sales forecaster. This forecast would be based on historical sales by day of week, with customer counts and daily temperatures.

Is this an overwhelming thing? I have been copying and pasting from the Totals spreadsheets to a new spreadsheet and then I will just copy and paste from the new spreadsheet into an Access table. But there has got to be a way to do this automatically or with less time.

TIA

Bret

Reply | Quote

Replies

I’m not clear on whether you want to convert the individual month spreadsheets to Access or only the totals. Do you still intend to keep the daily stuff in Excel or are you talking about switching over to an Access application for the whole thing?

Reply | Quote

Hello Charlotte,

Right now I would like to just use the totals. I don’t really care that I convert the entire worksheets.

Eventually I would like to move all of this to Access. I like the idea of being able to use the data that I collect and I have decided that I was wrong to use Excel for this purpose. It requires to much manual manipulation to be very useful. I need to create forms that resemble the Excel Spreadsheets we use so as to not create angst for my managers when they do their daily paper work.

Thanks Charlotte

Reply | Quote

Try importing into Access instead of exporting from Excel: create a new blank database, then use File | Get External Data | Import… to get the data into Access. To do this successfully, your Excel tables must either start in cell A1 of a worksheet, or have a name assigned to them.

Reply | Quote

Hello Hans,

When you say the the ” or have a name assinged to them”, do you mean, name the cell in Excel. For example instead of cell d4 give it the name “temperature”?

The problem I have had with importing into Access is that the spreadsheets are laid out in the fashion of the original paper form that was filled out by hand every night. This puts label and data mixed together in columns and rows.

Thanks Hans

Reply | Quote

By “name”, I meant a name assigned to a range as a whole in Excel: select a range, then type the name in the cell address box in the formula bar, or select Insert | Name | Define…

If you want to import data into Access, they must be in table form, i.e. rows = records and columns = fields. For example:

ID	Company	Product	Price
1	Acme	Widget	$9.98
2	Acme	Bolt	$0.49
3	HAL	PC	$1999.00

If the data is in another form, you will have to get it into table form first, or write code to process it while importing into Access.

Reply | Quote

I think I am starting to see what you are saying. My “totals” spreadsheet is in columns and rows, the problem there has been the fact that I am using two or three rows at the top for the labels.

I will try importing just the totals sheet. In the past have seen the other spreadsheets displayed on the import screen and I knew they wouldn’t work.

Thanks Hans

Reply | Quote

You can name an entire range on a spreadsheet and it doesn’t need to start at A1. Just start with the upper left corner of your totals data and name that entire range something like “TotalsData”. Then you can import that range into Access. If you actually used two or three rows for column labels rather than wrapped cells, you will need to make sure the row closest to the data itself is given a unique name and that you include only that row in your data range. Otherwise, the labels will get imported as well and all your data will come over as text because of the format of the labels. Make sure you let Access know that the first row of your data range if field names or it will assign such useful names as Field1, Field2, etc.

Reply | Quote

Thanks Charlotte and Hans,

This has been real helpful. Now I just need to spend some time and then later I won’t have to dig quite so hard to find the answers.

Bret

Reply | Quote