Run Excel Macro within Access - to Hide Rows...

[jwkolker]

I am trying to open a file and hide all rows where the value in the cell in column A is NOT "1" - this is what I have written but it is not working - (it works as a macro within Excel but I need to call if from an Access Module)... take a peek:

Public Function testhide()

Dim XL As Object
Dim xlCellTypeFormulas As Property

***This may be where the problem is *****

Set XL = CreateObject("Excel.Application")
      
XL.Workbooks.Open "MyFileName.xls"
      
'Hides all rows that are empty
               
XL.Application.Visible = True
****This line is here so I can see the macro do it's thing
I will remove it after it is all working***
     XL.Application.Goto Reference:="fronthome"
     XL.Columns("A:A").Select 'Assuming print markers there
     XL.Selection.SpecialCells(xlCellTypeFormulas, 1).Select
     XL.Selection.EntireRow.Hidden = True
     
End Function

Any help would be greatly appreciated.

JoKo
John Kolker
Programmer
jwkolker@comcast.net

 

[scking]

The automation code for Excel will run from any Office application. The procedure below is written in Outlook. It uses the opening event to check for a meeting with a specific title, then runs the Excel macros. As you can see the primary issue is to identify the add-in that the macros reside in and then merely .RUN the macro. My addin is called PERSONAL_ENHANCED.Xla. It does however run a macro that is resident in Excel and does not run a macro from commands using automation code.

Public Sub RunReports()

Dim myXlApp As Excel.Application
Dim myBook As Excel.Workbook
Dim pathSep As String
Dim libItem As String
Dim strActiveBook As String
Dim strMacro As String
Dim Item As Integer

If UBound(Appts) < 0 Then
Exit Sub
End If

On Error Resume Next

Set myXlApp = GetObject(Class:=&quot;Excel.Application&quot
If myXlApp Is Nothing Then
Set myXlApp = New Excel.Application
End If

pathSep = myXlApp.PathSeparator
libItem = myXlApp.StartupPath & pathSep & &quot;PERSONAL_ENHANCED.Xla&quot;
myXlApp.Workbooks.Open filename:=libItem
myXlApp.Workbooks.Add
Set myBook = myXlApp.ActiveWorkbook
myXlApp.Visible = True

' Find out if this is already identified
' you only want to run it once

For Item = 0 To UBound(Appts)
Debug.Print Appts(Item).Subject
strMacro = Appts(Item).Subject
Do While InStr(1, strMacro, &quot; &quot > 0
strMacro = Mid$(strMacro, 1, InStr(1, strMacro, &quot; &quot + 1)
Loop
strMacro = &quot;PERSONAL_ENHANCED.XLA!&quot; & strMacro
myXlApp.Run Macro:=strMacro
Appts(Item).myAppt.ReminderSet = False
Appts(Item).myAppt.Save
Next Item

End Sub
-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------