Export from Access to Excel and back again?

[harix]

I create a temporay table which I want to export to Excel where I will do some complex calculations that I can't get done in Access (multiple references to previous records - tried 100's of ways to accomplish in Access - couldn't do it!) I have created macros that transfer data (from TempFile_tbl) to the spreadsheet, run calculations, then transfer back (TempFileNew_tbl) again using TransferSpreadsheet.

How can I set-up my Excel spreadsheet to automatically fill in the appropiate number of formulas to match the number of records. The # of records import - export will vary every time, 1- 500+ records.

I got it to work by using macros that deleted the imported data once it is exported to Access. But, the Excel spreadsheet has the formula columns set at 750 records and the imported data goes to a table that rejects blank spaces based on the Primary Key not having a value.

Obviously this is akward. Is there an easier way?

Thanks

 

[Ziggy1]

It sounds like a lot of work, have you tried just linking your spreadsheet to Access. Then use Append Query and or Make table queries to organize the data the way you want?

Ziggy