MS Excel 2000 Changes Menu Macro on Save As

[jonbrown]

I am trying to create a macro in MS Excel 2000 that saves a spreadsheet as HTML then reopens the original file.
I have, I beleive managed to get the required functionality working OK.

The problem is that having attached the macro to a custom menu, once the macro has been run, the attached macro now points to the HTML version of the file. This means that running the macro attempts to open the html file, which either does not exist or if it does prevents a saveas because the file is already open.

Hope this makes sense!!

Does anyone know of a way to prevent this happening?

Thanks

Jon

 

[jonbrown]

If anyone is interested, I solved this myself by creating the following functionality:

Sub Save()
Module1.My_Save
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
For Each bar In Application.CommandBars
Set cb = bar
If cb.Name = "My_Menu" Then
cb.Delete
End If
Next
End Sub

Private Sub Workbook_Open()
Run ("ResetMenu&quot
End Sub

Modules:
Sub ResetMenu()
On Error GoTo ErrorTrap

' if the menu already exists - delete it
For Each bar In Application.CommandBars
Set cb = bar
If cb.Name = "My_Menu" Then
cb.Delete
End If
Next

' rebuild the menu
Set MenuBar = Application.CommandBars _
.Add(Name:="My_Menu", Position:=msoBarTop)

With MenuBar
'.Protection = msoBarNoProtection
.Visible = True
End With


Set newItem = CommandBars("My_Menu&quot.Controls.Add(Type:=msoControlButton, ID:=1)
With newItem
.Caption = "Save"
.FaceId = 0
.OnAction = "My_Save"
.Style = msoButtonCaption
End With
'MsgBox ("Should Have Finished&quot

ErrorTrap:
If Err.Description <> &quot;&quot; Then
MsgBox (Err.Description)
End If
End Sub

Sub My_Save()
On Error GoTo Err_Save

If ActiveWorkbook.Name = &quot;MyWorksheet.xls&quot; Then
' Save the active workbook
ActiveWorkbook.Save
' get the path and name of the workbook for future use
fName = ActiveWorkbook.FullName
'save the active workbook as HTML
ActiveWorkbook.SaveAs Filename:=&quot;\\MyPath\page.htm&quot;, FileFormat:=xlHtml
'reopen the original file
Application.Workbooks.Open Filename:=fName
Workbooks(&quot;MyWorksheet.xls&quot.Activate
' close the HTML version
Workbooks(&quot;page.htm&quot.Close SaveChanges:=False
Else
MsgBox (&quot;Active Workbook is &quot; + ActiveWorkbook.FullName)
Exit Sub
End If
Run (&quot;MyWorksheet.xls!ResetMenu()&quot

Err_Save:

MsgBox (Err.Description)
Exit Sub

End Sub