[AccessD] Using Excel from Access

Hey Marty,
Thanks for your code and a great insight to the properties of Excel. I am
going to rename one of my excel files and experiment today.
Another example of the high level of willingness to help and the degree of
expertise on our list!!!

Best,

Bob


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of MartyConnelly
Sent: Monday, July 14, 2003 10:35 PM
To: Access Developers discussion and problem solving
Subject: Re: [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

>
>


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com