Access import of Excel calculations data

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

Guest

Im not entirely sure this is possible but i've been issued with the following
task:

Create a website (in this case i'll be doing it in asp) to interface with a
very large scale excel calculator (12 meg of financial calculators, very
complicated, not feasible to re-write into access manually)

I need a way to either
a) Directly import all the calculations etc into access so access
essentially can do exactly what this spreadsheet does (dont think this is
possible)

b) get access to directly interface with this, and run calculations by
placing data in specific fields (then obtaining data from the results) and
saving this output into a temporary table within the access database so it
can be accessed (both input and output) via a webpage.

c) i've looked into the ODBC connector for excel, but as far as i can tell
it will only insert / pull out data within a static table, wheras this is
more of an application (no specific table layout) which performs complex
calculations.

First week at the new job, this is the fun and challenging stuff ;) (maybe
less fun...)
 
Hi Luke,

It's probably possible to move all the calculations into Access but it
would mean a major re-appraisal of the whole application. So your (b) is
probably the best approach for now.

Here's some stuff that should get you going:

Sub GetValuesFromExcel()
'Demonstrates taking values from particular cells in
'Excel workbook and putting them into Access table
'using automation and recordset operations

Dim xlApp As Excel.Application
Dim wbkW As Excel.Workbook
Dim rsR As DAO.Recordset
Dim F As DAO.Field

Set xlApp = CreateObject("Excel.Application")
Set wbkW = xlApp.Workbooks.Open("D:\Folder\Filename.wks", , True)
Set rsR = CurrentDb.OpenRecordset("TableName")

rsR.AddNew
With wbkW.Worksheets("SheetName")
'get values from cells and put them in fields in the recordset
'or vice versa!
rsR.Fields(1).Value = .Cells(5, 1).Value
rsR.Fields(2).Value = .Cells(5, 3).Value
rsR.Fields(3).Value = .Cells(7, 6).Value
End With
rsR.Update

rsR.Close
Set rsR = Nothing
Set wbkW = Nothing

'close this and any other workbooks that may have opened
Do While xlApp.Workbooks.Count > 0
xlApp.Workbooks(1).Close False
Loop
xlApp.Quit
Set xlApp = Nothing
End Sub


Set value of single cell in worksheet using a query:

Dim strSQL As String
Dim dblValue As Double

strSQL = "UPDATE [Excel 8.0;HDR=NO;Database=C:\Book1.xls;]" _
& ".[Sheet1$B4:B4] SET F1=" & dblValue & ";"
CurrentDB.Execute strSQL, DBFailOnError




Methods for Transferring Data to Excel from Visual Basic
http://support.microsoft.com/default.aspx?scid=kb;en-us;247412

Sample Excel automation
http://www.mvps.org/access/modules/mdl0006.htm

Run Excel macros through Automation
http://www.mvps.org/access/modules/mdl0007.htm

Importing Excel spreadsheets from code
http://www.mvps.org/access/general/gen0008.htm

Using Automation to Create and Manipulate an Excel Workbook (Q210148)
http://support.microsoft.com/?id=210148

Exporting an Excel worksheet as a CSV file
http://www.mvps.org/access/modules/mdl0046.htm

There's a white paper and code samples including tutorials available
from Microsoft at
http://support.microsoft.com/?id=253235
 
Back
Top