Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 512
  • Last Modified:

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.
0
slamond
Asked:
slamond
  • 6
  • 5
1 Solution
 
David LeeCommented:
Hi, slamond.

This isn't possible.  HTM pages don't support macros.
0
 
slamondAuthor Commented:
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.).
0
 
David LeeCommented:
What's the goal of writing the macros into a HTM file?  They aren't going to execute from there.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
slamondAuthor Commented:
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.
0
 
David LeeCommented:
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.
0
 
slamondAuthor Commented:
Yes, macros DO run from with an HTM file that was created in Excel.
I assume you think that I am referring to HTML files that were created in Frontpage, etc.

The solution that I found (in Microsoft's Excel 2003 Manual) is to save the macro to the Personal Macro Workbook. Macros that are saved to this workbook, in the Record New Macro dialog box, are available when ANY workbook is open. So, when I open the workbook with the HTM file extension, the macro is available.
0
 
David LeeCommented:
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?
0
 
slamondAuthor Commented:
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.
0
 
David LeeCommented:
So the users don't use any of the macros, just you, right?  
0
 
slamondAuthor Commented:
Correct.
The macros are just for me to use when updating the data,
0
 
David LeeCommented:
Got it.  Thanks for the explanation.  
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now