Dynamic connections to Excel

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

Guest

I want use data from a Access-2003 application that is transfered and
processed by a pre-existing Excel spreadsheets. In other words, all that
Excel does is process the data. The modified data is returned and displayed
to the user via subform. I want to keep the user completly inside the
application enviroment. I have tried every method posted here, methods
including SQL's INSERT INPUT. But there were issues with the FROM- path
length being to long. I have many a hour of VBA and VB under my belt so I'm
scarced of any code someone might sugguest. Or is there a ActiveX control
available that might preform this task?
 
I want use data from a Access-2003 application that is transfered and
processed by a pre-existing Excel spreadsheets. In other words, all that
Excel does is process the data. The modified data is returned and displayed
to the user via subform. I want to keep the user completly inside the
application enviroment. I have tried every method posted here, methods
including SQL's INSERT INPUT. But there were issues with the FROM- path
length being to long. I have many a hour of VBA and VB under my belt so I'm
scarced of any code someone might sugguest. Or is there a ActiveX control
available that might preform this task?
Click to expand...

It sounds as if what you need to do is
1) create an Excel.Application object

2) use this to open the workbook as an Excel.Workbook object (let's call
it oBook)

3) poke the data you want to process into the appropriate ranges and
cells. For recordsets I'd probably use Excel's Range.CopyFromRecordset
Dim rsR as DAO.Recordset
Set rsR = db.OpenRecordset("SELECT blah...", dbOpenSnapshot)
oBook.Sheets("MySheet").Cells(2,2).CopyFromRecordset rsR
and for individual cells
oBook.Sheets("MySheet").Cells(1,20).Formula = "99.35"
Better yet, assign names to the cells and ranges you need to manipulate.

4) finally retrieve the results from the workbook. If they consist of a
recordset to be displayed in a subform, it might be sufficient to set
the subform's recordsource to a query on the appropriate range, e.g.
SELECT * FROM
[Excel 8.0;HDR=Yes;database=C:\TEMP\AddrNew3.xls;].[MyRange]
;
but maybe you need to import the results and store them in a table in
the database.
 
Back
Top