[AccessD] Using Excel from Access

Bob Heygood wrote:

>Hey to the group,
>
>I am working with a new client. they asked me to provide a front end to some
>Excel sheets. I told them it should not be too difficult. Of course I asked
>them why the whole project could not be moved to Access and was told "the
>CFO is very comfortable w/Excel", a common response according to discussions
>at our local Access group breakfasts. They did agree that once I completed
>the project, they would not change anything in the sheets (oh yeah!).
>Well most of it is just a matter of linking to the sheet and treating like a
>table. But one of the tasks is to recreate a viewable/printable version of
>one of the sheets and not let the user see the calcs behind any of the
>cells.
>
>Now for the question:
>Can I get a hold of the calculation/formula behind a given cell in a sheet
>thru automation?
>
>Once that is accomplished, I think I can supply a solution for them.
>
>TIA,
>
>Bob Heygood
>  
>
Something like this

Sub listformula()
'set reference to specific Excel object library as below or use
' Set xlapp = CreateObject("excel.application")
'If Err.Number Then
'     MsgBox "Excel not installed or not registered for Automation"
'     Exit Sub
'  End If


' Excel object Variables
Dim xlapp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim Cell As Excel.Range
Dim sh As Excel.Worksheet
Dim nm As Excel.Name

' DAO Object variables
Dim rst As DAO.Recordset
Dim db As DAO.Database

' Other variables
Dim lngNumRows As Long
Dim lngNumCols As Long
Dim intI As Integer

Set xlapp = New Excel.Application
xlapp.Visible = True
' Change to the correct path for your xls file
Set xlbook = xlapp.Workbooks.Open("c:\Excel\Loan Calc.xls")
' Change to the correct name of your worksheet
Set xlsheet = xlbook.Worksheets("Amortization Table")

lngNumRows = xlsheet.UsedRange.Rows.Count
Debug.Print lngNumRows
lngNumCols = xlsheet.UsedRange.Columns.Count
Debug.Print lngNumCols

Dim FName As String
Dim Fnum As Long

FName = "C:\Excel\Export2.txt"
Fnum = FreeFile
Open FName For Output As Fnum
For Each sh In xlbook.Worksheets
 For Each nm In sh.Names
        Debug.Print nm.Name & vbTab & nm.RefersTo
    Next nm

For Each Cell In xlsheet.UsedRange.Cells
        If Cell.HasFormula Then
         ' Debug.Print Cell.Address & vbTab & Cell.Formula
          Print #Fnum, Cell.Address & vbTab & Cell.Formula
        '424 error possible
        End If
    Next Cell
 Next sh
Close Fnum

'
'
'xlbook.Save
xlbook.Close
xlapp.Quit
Set xlapp = Nothing
Set xlbook = Nothing
Set xlsheet = Nothing
Set db = Nothing
Set rst = Nothing
End Sub

>  
>