How-to Save VBA Macros in an HTM file?
I routinely use macros (recorded and then edited in the VB Editor and often assigned to buttons) but I have noticed that when I create them in a file that is saved as a webpage (HTM file extension) I lose the macros when I reopen the file.
I'm opening all files using Frontpage 2002, in case that makes a difference.
Is there a workaround for this?
As background: I like to save the files as HTM since most of the worksheets are being viewed by others either via our Intranet or via the Internet. And they can read them with their browser (as opposed to the XLS file type that will cause Excel to open unnecessarily).
I have no trouble working with macros in other files (saved as XLS). It seems to be a problem related to the file type.
I'm opening all files using Frontpage 2002, in case that makes a difference.
Is there a workaround for this?
As background: I like to save the files as HTM since most of the worksheets are being viewed by others either via our Intranet or via the Internet. And they can read them with their browser (as opposed to the XLS file type that will cause Excel to open unnecessarily).
I have no trouble working with macros in other files (saved as XLS). It seems to be a problem related to the file type.
ASKER
The workaround that I thought of is to store the macros in the old-fashioned format that I formerly used in Lotus-123, where the macro is written line by line and then referred to as a named-range.
Problem: I can't remember how to write macros this way. (I vaguely remember in Lotus-123 each line would start "/FO..." (File, Open, etc.).
Problem: I can't remember how to write macros this way. (I vaguely remember in Lotus-123 each line would start "/FO..." (File, Open, etc.).
What's the goal of writing the macros into a HTM file? They aren't going to execute from there.
ASKER
I can record the macro and execute it from within the file, I just can't save it.
I assume because VBA code cannot be saved in a file with the HTM extension.
So, I should be able to save the older/line-by-line version of the macro if I can learn the formatting.
I assume because VBA code cannot be saved in a file with the HTM extension.
So, I should be able to save the older/line-by-line version of the macro if I can learn the formatting.
You can execute a macro from within a spreadsheet because Excel supports macros. HTML files DO NOT support macros. Even if you find a way to save the macro code in the HTML file it isn't going to run from the file.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Maybe I didn't understand the issue. I took the question to be how to include macros in/with an HTML file in such a way that other employees could access the files and macros without having to open Excel. Did I misunderstand or are you saying that when you or anyone else opens the HTM file in your browser from a web site that the macros work in the browser?
ASKER
I created an Excel file that contains a few different worksheets/pages and saved it on our Intranet with the file extension HTM. In this way it can be presented to users as a hyperlink and they can open it and read each page (by clicking the sheet name at the botttom) using their browser.
Since the data sometimes has to be resorted and the cell formats fixed after the sort (such as color-coded cell backgrounds) I prefer to use a macro. The macro resorts the data and then copies and Paste Special/Formats from a format template stored in one of the other sheets.
Saving the macro in the Personal Worksheet solved my problem.
Since the data sometimes has to be resorted and the cell formats fixed after the sort (such as color-coded cell backgrounds) I prefer to use a macro. The macro resorts the data and then copies and Paste Special/Formats from a format template stored in one of the other sheets.
Saving the macro in the Personal Worksheet solved my problem.
So the users don't use any of the macros, just you, right?
ASKER
Correct.
The macros are just for me to use when updating the data,
The macros are just for me to use when updating the data,
Got it. Thanks for the explanation.
This isn't possible. HTM pages don't support macros.