Excel Macro Did Work Now Doesn't Loop All Worksheets

[Guest]

I had to change Excel Macro to reference different column.
That is now working, but only if I launch the macro from Excel.
I was able to launch it from Access and everything worked properly before
adding
the new columns.
Now it only works on first worksheet, and won't loop through the remaining ws.
This is the code from within Access

Public Function CleanClinic()
Dim appXL As Excel.Application
Set appXL = New Excel.Application
appXL.Visible = False
MsgBox "Please Wait While Clinic List Updates"
DoCmd.Hourglass True
appXL.Workbooks.Open "N:\Shared\ExcelTest.xls"
MsgBox "Almost Done"
appXL.ActiveWorkbook.Save
appXL.Quit
MsgBox "Update Complete"
DoCmd.Hourglass False
End Function

I am going loony toons trying to figure what's wrong.
Help please and thank you.

 

[Ken Snell [MVP]]

Your posted code does no looping through any worksheets in an EXCEL file.

Why do you think that this code should do that?

 

[Guest]

Told you I was going looney. Here is code from Macro.
This works if launched from excel but not if from Access.
The excel macro is in workbook "ExcelTest.xls"
The Function in Access is called "CleanClinic".
I invoke the function from an Access macro with states "RunCode" and I entered
CleanClinic().

Sub ProcessWorkbook()
Dim bk As Workbook, sh As Worksheet
Dim rng As Range, lastrow As Long
Set bk = Workbooks.Open("N:\Shared\Copy of ClinicTECList.xls")
For Each sh In bk.Worksheets
lastrow = sh.Cells(Rows.Count, 8).End(xlUp).Row
For i = lastrow To 1 Step -1
Set rng = sh.Cells(i, 8)
If Not IsEmpty(rng) Then
If IsDate(rng) Then
If rng < Date Then
rng.EntireRow.Delete
End If
End If
End If
Next
Next
End Sub

 

[Ken Snell [MVP]]

Post the ACCESS code that calls this EXCEL macro; include all the code that
opens the EXCEL workbook etc. too from ACCESS.

Define "doesn't work". What happens? error message? what does the error
message say? nothing happens? etc.
--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

This is in Access.

Public Function CleanClinic()This is the Excel macro:
Sub ProcessWorkbook()When I launch the Access code it does not do anything.
When I launch the excel macro from the macro workbook, it works.
I don't know what else to tell you. There is no error message.
Also, it seems, that when I open the Excel macro workbook and it states, this
wb contains macros, enable or disable, and I click enable, it doesn't do
anything either. But, if I open the actual code page and click "run", then
it works.
Any ideas?

 

[Ken Snell [MVP]]

Your ACCESS code does not have a step to actually run the EXCEL macro.

 

[Guest]

How do I do that?
And why, won't it run when I open the Excel workbook and click "enable
macro", but only if I go into the macro page and click "run", it works?

 

[Ken Snell [MVP]]

To have EXCEL run a macro automatically when you open the workbook, you need
to assign the macro to the Workbook_Open event. The "enable macro" button
that you click when you open the file is just a security level setting that
you're giving to EXCEL; it doesn't run a macro.

In ACCESS, you must add a step in your code that tells EXCEL to run the
macro.

appXL.Run "ExcelTest.xls!NameOfExcelMacro"

--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

Thank you, I have it working now.

To have EXCEL run a macro automatically when you open the workbook, you need
to assign the macro to the Workbook_Open event. The "enable macro" button
that you click when you open the file is just a security level setting that
you're giving to EXCEL; it doesn't run a macro.

In ACCESS, you must add a step in your code that tells EXCEL to run the
macro.

appXL.Run "ExcelTest.xls!NameOfExcelMacro"