Import data from Pivot table in Excel?

cyborgoddess

Registered User.
Local time
Yesterday, 19:41
Joined
Sep 2, 2004
Messages
19
I'm posting this here because I'd love to be able to automate an export to excel, where I can use multiple sheets to calculate returns, then import the new numbers back into access as a new table. Is there a way to tell Access to import from a sheet where the fields are made up of 1) The row headers, 2) the column headers 3) the values in the row?
Date 01/01/01 01/01/02 01/01/03
ID
1 $10,000 $10,050 $11,000
2 $11,000 $12,050 $13,000
3 $10,000 $11,050 $12,000

so that the new table looks like:

ID Date Value
1 01/01/01 $10,000
1 01/01/02 $10,050
1 01/01/03 $11,000
2 01/01/01 $11,000
2 01/01/02 $12,050
2 01/01/03 $13,000


Thanks
 
The short answer, of course, is YES, you can transfer data from Access to Excel using Excel automation objects. However, the coding for what you ned is relatively cumbersome, but can be done. Bottom line: this will take some time to code, test, and recode.

I would look a this as a three step process. The first step, of course, is to create an excel sheet based on the Access recordset, using Access field headers as the Excel Sheet headers, then populating the rows as needed.

The next step is to create the additional columns in the Excel sheet, assingn formulae to each cell in the same row as current data, and execute the formulae.

The final step would be to import the data from the newly-modified Excel sheet back into Access.

I probably didn't say much that you didn't know. The best advice is to go see this example of automation:

Excel Automation Example

From there, you can determine the best course of action.

I hope you charge by the $$hour$$ :D
 
I've mastered steps 1 & 2 - no sweat, step 3 is what is baffling me. I'm going to try that code in excel - but I'm not sure I understand what it is doing.

Thanks for the quick response.

-A
 
still confused

I'm not sure how that is going to help me convert the data from crosstab to a "normal" table, though.

Is the idea that I rearrange the data in excel before I bring it back into access?
 

Users who are viewing this thread

Back
Top Bottom