Importing From Excel to Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have quesiton regarding my problem with Acess. My boss recieves reports
monthly in Excel formatting. He wants me to in turn create a database in
Access with a certain workbook. (which I have done already) this Excel
workbook has about 8 different worksheets in them. Would someone explain to
me how to make that one database without seperating each worksheet or is that
possible? He wants the reports he receives to be fed into the database that
I created in Access with updates. I need to know how to do this. I am
confused on how the information gets fed from Excel to make updates in
Access. I hope this makes since. Please help someone. Thank you!
 
Importing each of the worksheets separately is no problem at all. The
biggest question you have here is "What is the format of the reports in the
worksheets?"

The reason being is that Access will have to have all the data in columns.
If there are cells in the worksheet that are descriptive, but do not line up
in a column of data with headers, then it will have problems importing it.
For example, this will work
Account Jan Feb Mar Apr
12334 101 232 9 87
12449 393 11 82 15
etc

It will not work well if there is like the above with a totals line added:
Totals 494 243 97 102

Or if it looks like this, it will be a problemL
Account Report
Feb 22, 2006
Account Jan Feb Mar Apr
12334 101 232 9 87
12449 393 11 82 15

Now, even if it has some of the above, there is a way around it. That is to
create a range in the excel sheet that includes only the column headers and
column data excluding any totals.
When you do an import into access, the TransferSpreadsheet method has a
Range argument which can be the sheet name and/or a range name. So you can
define a range and use the TransferSpreadsheet to pull it in.
The only thing that will cause this problems if there are subtotals in the
middle of the column data.

Good Luck.
 
Back
Top