Free Microsoft Excel 2013 Quick Reference

Vba to save xla file Results


I am looking for VBA code to save my Xla file after running the code.
Actually i want to delete code from a Xla file after one run. If i open a new workbook and re run the macro its working. To avoid that i want to save Xla file permanently to all workbooks.

I have a large macro set that I had in a stand-alone spreadsheet and then switched focus to the spread that i wanted the macro to actually work on (i.e. all VBA was stored in a read-only spreadsheet and it operated on a separate spreadsheet). I got sick of having to have two spreadsheets open, so I saved the old macro sheet as an excel add-in. However, all the keyboard shortcuts that I used to use (e.g. Ctl+a to start the splash screen) are still attached, and since it's now an add-in, anytime i want to highlight all cells in a sheet, my splashscreen launches. Since it's an .xla file, I can't get into the same macro options screen to get rid of them. Where is this information kept in the .xla file?


I have an excel add-in file (.xla) that is password protected (I know the password). The file contains excel sheets that I want to view. When I open the .xla, the add-in is properly added to the excel menu bar but the file itself does not open. If I rename the file as .xls, I am still unable to open the file. I can see the modules and sheet names listed in VBA but can't figure out how to get to see the sheets in excel.

Thanks for your help.

When say user1 develops the report and saves, the
functions are saved to the xla file using an absolute address.

The directory depends on where the excel is installed. Below is an example
of a function saved.

='C:Program Filesmicrosoft
officeOFFICE11xlstartConsolidationAddin.xla'!ge tValue($B$1,"DESCRIPTION","LINE_ITEMS",B16)

If user1 sends this report workbook to another report developer, user2 whose
excel installation is different from the original developer, The report
cannot used unless the absolute address is mass replaced on all the
worksheets of the workbook.

For this to work we need to select each sheet in the workbook and then
remove the 'C:Program Filesmicrosoft
officeOFFICE11xlstartConsolidationAddin.xla' from all the functions for
each cell.

Can any one please help me to resolve this issue.

We want that when the user saves the report, it must be saved with the
relativbe address i.e only the function name
'getValue($B$1,"DESCRIPTION","LINE_ITEMS",B16)' i.e. instead of storing the
complete address in each cell.




You don't have to keep an xls copy. You can open an xla file in Excel just
as you can an xls file. Just make sure you uninstall the add-in before
opening it. The file won't be visible in Excel, but you cane edit the code
in the VBIDE as normal.

As for keeping a copy on your local drive, surely your network drives are
backed up, so there is no need? I keep many add-ins at work on a network
drive, and keep development versions on there which I update and then
promote to production. Never any problems.




"Chuckles123" > wrote in message
> Our OS is WXP-Pro. We are using an .xla file on a shared network drive
> to store macros. As a backup, I am "trying" to keep a matching .xls
> file on my local drive. Also, to make changes in the VBA Code without
> executing the macros, I have to be able to open the file, obviously.
> I'm probably crazy, but it seems like I am getting inconsistent results
> when saving code changes. This is the procedure I use: when getting
> undesired results in the spreadsheet, I will "step-thru" the code, find
> and correct the mistakes, and then click-on the "Save" icon in the
> Editor; I then shut down the Editor, and click-on the "Save" icon in
> the spreadsheet -- herein lies the rub, apparently, the code changes
> just made are sometimes not saved in my .xls copy of the shared .xla
> file.
> I have been using the same prefix for the shared .xla file and my local
> xls file -- I am thinking about making them different.
> Help!
> --
> Chuckles123
> ------------------------------------------------------------------------
> Chuckles123's Profile:
> View this thread:


When my Auto_Open() kicks off I need to be able to establish the file name.
The reason for this is that when I'm designing my Excel Add in my file name
uses the .xls extension so i refer to my file as FileName.xls but when I want
to save my file as an Excel Add-in, the file extension changes to .xla, thus
I need to update my reference in the code. Therefore it would good if I can
obtain the file name when the Auto_Open() starts, then I don't have to
manualy change the reference.

Any ideas on how to accomplish this would be create.


Using 2003

I would like to add a keyboard short-cut to a macro in an .xla file.

Of course, I tried it in Excel but .xla macros do not show up in the
dropdown box.

Is there a better/shorter way than saving the .xla file to .xls file
applying the shortcut then re-saving as an .xla file etc.



Excel 2003 on a LAN:

I have VBA code stored in two .xlA files (neither is installed as an 'AddIn' in Excel): one macro fires at 9 am and the other fires at 5 pm. The 5 pm macro imports data from an accounting server and updates two .xlS data files and saves them using constant filenames (the .xlS files contain zero VBA code). The 9 am macro, using the preceding day's data files plus data imported from another server, crunches all the data and sends out e-mails based on the values in specified cells. All of this usually works.

However, there has occasionally been a problem with a macro crashing (a pop-up error message indicating that a 'read-only' file cannot be accessed <-- this really means that it cannot be saved). Of course, this only occurs on the .xlS files since the .xlA files are never saved (when the macros are executed). However, when revising code in an .xlA file and subsequently attempting to save such file, I occasionally get the same message; this is true even though the .xlA macro will run just fine when the .xlA file is opened on an unintended 'read-only' basis (regardless of the True/False setting of 'Application.DisplayAlerts=?').

I am currently in a testing mode; my ultimate goal is to locate these macros and data files on an Autosys server -- therefore, zero pop-ups. I am not sure what causes the above sickness, but here are the symptons: the .xlS file or the .xlA file is "tied up" on my network login, even though when I go into 'Windows Task Manager - Processes', EXCEL.EXE is not listed; also, no change in "tied up" status when powering the machine down and re-powering and re-logging-in to our LAN; the file can be copied but it cannot be deleted. The only solution has been to call our LAN Administrator and request that the "tie up" be severed; he does so by making a few mouse clicks, I guess. (I think he may be getting tired of my requests.)

I think this situation may even be caused by "stepping through" a macro, allowing the macro to open various files, and then shutting the macro down without allowing the macro to close files and shut-down Excel (is it good form to close all files prior to shutting-down Excel in a macro?).

I have coded another macro that opens each of these files, saves each of these files, and then closes each of these files; currently, the macro sends me an e-mail if all procedures are completed OK and it sends me an e-mail if one or more of the files could not be saved, including an error message and the name(s) of the files that could not be saved. I have scheduled this macro to fire at 9:10 am and 5:10 pm (shortly after each of the other two macros).

Can anyone shed any light on this situation?

Thanks in advance,



I've just written my first VBA code, and I want to share this code with my collegues in the form of Excel add-in. xla format. (and they will use the same code in different files) However when I save the file as .xla, and open this .xla file in another .xls file (drag and drop save x0la to another xls file), it doesn't work. I believe that this is due to the fact that my xla added as another vba project, instead of a module within vba project of my xls file.

How can I convert my code to an add-in that will add itself into the open file's vba project hiearachy as a new module and work there within that hieararch, rather than making a new filename.xla VBAProject?

Sorry if I couldn't explain myself, this is my first code and I'm quite unfamiliar with the jargon.



I support an excel file that has a bunch of vba code behind including calls to msquery. The customer used this excel file by opening the original pulling in data for a specific client and than saving the file to a different name. The result is she has multiple copies of the file.

It currently has two problems:
I need to change the code in one of the subroutines to fix a known problem. This of course involves opening up the Visual Basic editor (from the excel file), finding the code to change and making the code change.We have just upgraded to Office 2003 and MSquery is looking for xlquery.xla which is no longer needed in 2003. The problem this causes and its solutions are described at Microsoft Support here. I have done the quick fix which is putting the xlquery.xla where excel is looking for it, but I want to do the correct fix which involves running the following code in every workbook that accesses xlquery.xla

    Dim n As Name 
    For Each n In ActiveWorkbook.Names 
        If n.Visible = False And InStr(1, n.Name, "QUERY", _ 
        vbTextCompare) > 0 And InStr(1, n.Name, _ 
        "Query_from", vbTextCompare) = 0 Then 
        End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
and than making sure you save the file as office 2003.Both of these things are much to complicated and time consuming for my customer to do for each individual excel workbook.

… Which leads me to my question: Is there a way to automate this?



Hi Everyone,

Got a quick question - is there a quick way in locating the XL add-in files? I can see there is a couple of active add-ins in the VBA project window. However, I couldn't remember where these add-ins were saved in my existing PC. I would like to save these add-ins in a floppy & transfer it to another PC.

The add-ins I've got are ATPVBAEN.XLA & FUNCRES.XLA.

Thanks for your reply.


I have a Excel file with lots of small small macro's and i want to share it with my collegues.

I first copied the file to network and created a short cut and pasted the shortcut to my collegues machine in xlstart folder.

When I want to make updations. i had to ask them to close the excel and re-open

Is there anyway by which whom so-ever wants can edit and append code and still the latest version is available to all.?

I tried saving as xla but then the macro's are unable to access unless you have a menu or command button to fire them. since there is lot of macro's creating menu bar is also tedious task

Any one please help me?



When saving a worksheet as csv, i need the delimiter to be a semicolon. When I manually save_as, this works fine, doing is in VBA, it saves with a comma as delimiter. I cannot find where i can set this value or a parameter to change. Anybody know? Sounds like an "international standards" problem.

Second is that i actually need it to a .txt file so I would really like to rename the .csv to .txt (or be able to save as .txt with a semicolom as separator) in VBA (first closing it of course, but as the module is an xla this shouldn't be a problem).

Anybody know this?

Von Pookie's HTML Maker FAQ
Last updated: 07 July 2009

07 July 2009: Downloads are currently unavailable for Colo's site. If you would be interested in testing the new HTML Maker designed specifically for the MrExcel forum, please see this thread:

PLEASE NOTE: This FAQ was written while we were using phpbb forum software. I have not checked these instructions for accuracy with the new vBulletin software we are currently using. It is possible that some items listed here may no longer apply.

Please note the following:
This is not the FAQ for the VBHTML Maker nor the YaBB Table Maker.These directions were written using Windows 2000 and XP, and Excel 2002 and 2003. I do not (currently) know the information for Macs or any other versions of Windows or Excel.

How can I show a small image of my worksheet in my post?
You can simply download the handy HTML Maker add-in! The HTML Maker is available for download at Colo's site. Currently unavailable

I have downloaded the .zip file. What is the next step?
You may need to download an extraction program such as WinZip to open the .zip file and extract the add-in file. For now, just extract the HtmlMaker.xla file to your desktop. Once you have this, you no longer need the .zip file.

Next, you need to locate the Addins folder on your computer. There are several places you may see a folder with this name. From the Excel help file: "Add-ins are stored by default in one of the following places:
The Library folder or one of its subfolders in the Microsoft OfficeOffice folder.The Documents and Settingsuser nameApplication DataMicrosoftAddIns folder."If you're uncertain which folder to use, open Excel, go to the Tools menu and select Add-ins. In the dialog that will display, click the Browse button to see the path for the AddIns folder it is using. Once you've located this, you can simply drag and drop the HtmlMaker.xla file into it.

Note: If you cannot find the Application Data folder, you probably just need to change a setting on your computer.Open Windows Explorer (or any folder) and select Folder Options from the Tools menu.On the View tab, look for "Hidden files and folders" under the Advanced SettingsMake sure the option "Show hidden files and folders" is selected, click OK, and look for that pesky Application Data folder again.

I have added the file to the AddIns folder, but I am unable to activate it in Excel!
Start Excel, go to the Tools menu and select Add-Ins. You should see a dialog box with a list of available add-ins, with a checkbox next to each item. Simply find the one labeled Htmlmaker, check the box next to it and click OK. You should now have a new menu between the Window and Help menus called HTML. You have now installed and activated the HTML Maker, and should be ready to go!

How do I use this add-in?
Using the HTML Maker to post a "snapshot" of your worksheet on the message board is relatively simple.
[list=1][*]Make sure you have enabled HTML in your board profile.Click the "profile" link--which is in the group of text links at the top-right of every page of the board.In the list of preferences, make sure the option for "Always allow HTML" is set to Yes.Click the "Submit" button to save any changes to your profile[*]Make sure the HTML Maker add-in is active (see "I've added the file to the AddIns folder, but I can't figure out how to activate it in Excel!").[*]Go to the HTML menu and select "Convert sheet to HTML"[*]Select the range you want to copy
Please select only the range (preferably with little to no formatting) that is crucial to the question you are asking.[*]Click OK[*]On the message box that pops up, click "yes" (I honestly have no clue what this does, but I always click yes and it works just fine)[*]Another message box will appear, letting you know how many characters the HTML Maker code will use, and how many characters that are remaining for you to use in typing the rest of your message. Click OK.[*]A new browser window will open, displaying what your copied cells will look like when posted to the board.[*]In this new window, click the "Copy code to clipboard" button.
If this does not work...[list=a][*]If you are running Windows XP with Service Pack 2 installed, make sure you allow popups on the page (they will be blocked by default) and try clicking the button again[*]As an alternative:In this same window click the button that is on the page that is labeled "View Source." This is located right next to the "Copy to Clipboard" button.Copy the entire code that is displayed there.[/list:o:4e41f3da2f][*]Come back to the board where you are entering your message, and put your cursor in the area where you type the body of your message.[*]Press Ctrl+V in the area where you type. If you don't know HTML, it will look like a lot of gibberish. That's normal, don't worry about it. You can still edit and add to your message if needed at this point. Do not edit/add to the code from the HTML Maker.[*]When you are finished, click the Submit button to post your message. The table should appear in your post just as it did on the browser page you copied the code from.
Do not preview a message containing HTML Maker code. You will not be able to use the buttons on the preview page to submit your message.Do not quote a post using HTML Maker code. You will receive errors (when quoting a message, simply edit out any extra HTML Maker code).[/list:o:4e41f3da2f]Back to top

Known Issues & Errors

I installed the HTML Maker add-in, but the "HTML" menu you say should appear on the menu bar is not there.
It is possible that you have installed the wrong add-in. This FAQ is for Colo's HTML Maker, NOT the VB HTML Maker or YaBB Table Maker. Making sure you have installed the correct add-in is the first step.

If you have confirmed that you installed Colo's HTML Maker correctly, make sure you have enabled the add-in. Go to ToolsAdd-Ins, click the checkbox for the HTML Maker and click OK. The menu should then appear.

If you have performed both of the previous steps and the menu is still not appearing, post your questions in the Questions About This Board forum.

I followed the directions, but after posting the page is not displaying correctly.
You can either edit the post yourself and remove ALL of the HTML code, or report the post by clicking the "Report" button () on the post to notify the moderators of the problem. A moderator will edit the post to remove any/all HTML Maker code so the thread will be able to display correctly.

As to why the page displayed strangely in the first place, it's usually one of two things: either you didn't follow the directions and copied the source of the page the HTML Maker generated instead of using the "view source" button on the page (yes, there is a difference and we can tell from the code if this is the case), or you have inadvertently posted incomplete HTML Maker code--which is much more common.

HTML requires both opening and closing tags; if you do not post the entire code, the closing tags are not included and the board cannot interpret the code correctly.

When using an HTML Maker example in your post make sure that you have copied all of the generated code and that it is pasted into the message in its entirety. There is also a character limit for each message (but to be completely honest, I have no idea what that limit actually *is*). If you try to use multiple examples within a single post, you can hit the limit quickly and the end of the code could be cutoff.

Error: "Could not load an object because it is not available on this machine"
You may be missing a file called ietimer.ocx, which is required. Click here to go to Colo's site and check if the file is already installed on your computer. This link will also automatically install the file for you if it is needed.
The file is also available for download from Microsoft: After downloading the file, it must be registered with regsvr32. The easiest way to do this is to right-click the file and select Install from the context menu.

Error: "Compile error." or "Can't find project or library..."
Open the Visual Basic Editor by pressing Alt+F11. Once in the editor, select the HTMLMaker in the Project window then go to the Tools menu and select References.
If there are any references that start with "MISSING," uncheck them and don't forget to save.
More information on this error can be found in this thread:

Error: "Error Number: 91, Object Variable or with Block variable not set..."
Click OK to close the dialog box with the error and go to Tools->Macros->Security. Click the Trusted Sources tab and check the box next to "trust access to Visual Basic Project," and click OK.

In Excel 2007, the directions are slightly different:
Click the Office Button and go to Excel Options->Trust Center->Trust Center Settings->Macro Settings->Developer Macro Settings. Then you can Check the "Trust access to the VBA project object model" box and click OK.
(Thanks to Bradlee_S for posting this, because I would have never thought to add it otherwise)

Error: "Runtime error: 5 Invalid procedure call or argument."
When this error occurs, the following line will be highlighted in the debugger:
Open the Visual Basic Editor (Alt+F11) and go to Tools->Options. Select the General tab and make sure "Break on unhandled
errors" is selected for "Error Trapping."

First mention of this error (and solution) can be found in this thread:

I am unable to view formulas after clicking the formula link in the posted example
There are two or more posts in the thread with HTML Maker examples, which could cause a conflict in the Javascript code being used. There is no real "fix" for this issue that I know of. The only real workarounds are to not post a message using the HTML Maker code in a thread already using an example elsewhere, or if you absolutely need to show how the data is set up, use the HTML Maker to show your sheet and indicate what formulas are in what cells within your message. Users viewing the examples in Firefox may notice this problem as well. (I have this issue with Firefox no matter what, personally).

I am unable to copy the generated code to the clipboard.
As was discovered by board member Parry: "If you have installed Windows XP Service Pack 2, a Pop-Up blocker is installed [in Internet Explorer] as part of the pack and by default this will block active content.

When you select your cells and run HTML Maker it will create an HTML page of your image as expected, but the pop-up blocker will prevent you copying the code when you click the button to send Source to the clipboard. Simply click on the pop-up security information bar, then select Allow Blocked Content and you can now copy the HTML to the clipboard."

Back to top

[list][*]This add-in is simply to show a small "snapshot" of the selected area of your spreadsheet. However, some things will not display when posting an HTML Maker example, such as:Drawings and objects (i.e., items created from the Drawing toolbar)Clip art and other imagesCharts and graphsConditional formatting[/list:o:4e41f3da2f]Questions concerning the HTML Maker should be posted in the Questions About This Board forum.
If you have not used the HTML Maker add-in before, please post in the Test forum before making your actual post to make sure the example will display correctly. If any posts with HTML maker code end up "breaking" the page (including posts in the Testing forum), they will be edited by a moderator or administrator to remove the problem-causing HTML code from your post or the post itself will be deleted.

Find an error or something I missed?
Click to send me a private message


Trying to understand an excellent procedure posted by Dave Petersen in

How do I test the flow of the code (variables etc)? When I run the
code by "saving" a file, the VBE window does not to appear to get
focus, nor can I pause the code.

Hopefully and most likely the answer is simple? The code is below.

As is, the code does not stop Excel from quering "Do you want to save
....." I would like to have the code intercept the XL standard queries
if possible. BTY, the code below is saved in an xla file which in turn
is "linked" as a XL Addin.

Also, the file is saves as .xlk which is not really a problem as I
assume that xlk is the default XL "Backup" file suffix (which would
explain where in the code the "k" came from).


Eagle One

************************************************** ***********

Option Explicit
Public WithEvents xlApp As Excel.Application
Private Sub Workbook_Open()
Set xlApp = Application
End Sub
Private Sub Workbook_Close()
Set xlApp = Nothing
End Sub
Private Sub xlApp_WorkbookBeforeSave(ByVal Wb As Workbook, _
ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim myFileName As Variant
Dim testStr As String
Dim resp As Long

myFileName = Wb.FullName

If SaveAsUI Then
myFileName = Application.GetSaveAsFilename _
(InitialFileName:=Wb.FullName, _
filefilter:="Excel file, *.xls")
If myFileName = False Then
Cancel = True
Exit Sub
testStr = ""
On Error Resume Next
testStr = Dir(myFileName)
On Error GoTo 0

resp = vbYes
If testStr = "" Then
'do nothing
resp = MsgBox(Prompt:="Overwrite Existing File?", _
If resp = vbNo Then
Cancel = True
Exit Sub
End If
End If
End If
End If

'do the actual save
With Application
.StatusBar = "Saving " & myFileName
.DisplayAlerts = False
.EnableEvents = False
End With

On Error Resume Next
Wb.SaveAs myFileName, FileFormat:=xlWorkbookNormal,
If Err.Number 0 Then
MsgBox "Something went wrong. File not saved" & vbLf _
& Err.Number & "--" & Err.Description
MsgBox "Saved as an xl workbook as: " & myFileName
End If
With Application
.StatusBar = False
.DisplayAlerts = True
.EnableEvents = True
End With

Cancel = True 'we did the work, don't let excel do it again.


End Sub

How do I register or expose a function (in a Visual Basic program created
with the Excel Visual Basic editor and saved as an .xla file) so that it is
visible when I try to use the Macro manager to assign and use it as a Macro?

I created the program and it runs fine from the VBA editor--I just can't get
to it once I load it via the .xla mechanism--it doesn't show up in the
selection window.

Excel 2003

I have VBA code stored in an .xlA file (not installed as an 'AddIn' in Excel); the code opens up an .xlS file and does a lot of data manipulation and importing data from other files; at one point, it opens an .xlT file (there is code stored in 'ThisWorkbook' module using 'BeforeSave' Private Sub). A specified worksheet is copied from the workbook that was 'Active' immediately prior to opening of the .xlT file to the .xlT file; then, a 'SaveAs' command saves the modified .xlT file as an .xlS file with a new filename. At this point in time, the spreadsheet becomes 'invisible' (only reference on Taskbar is VBA code), although it is still open (I am using two monitors to step thru the code; VBA on the right monitor and spreadsheet on the left monitor.).

The macro continues to process code correctly, but can someone explain this behavior.

Thanks for a response,



I'm building my first Add-In, which I'm using to gather all Sub's that I've created through my small VBA writting career.

The thing is that when I write new code and some reason exit Excel and forget to save the XLA, obviously the code is lost.

I've used:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
End Sub
but obviously, if I write garbagge or delete precious code, all is lost.

Is there a way for Excel to ask me if I want to save the XLA file, like it does when changes are made to a common worksheet?

I've tried the Save Dialogs but they all ask for new file name. I just want a simple "do you wish to save blabla.xla?" question...

Thank you!


I have the followinf issue:
When I create a macro that uses dediczted Library references and that I
save it in a xla file then I have issues for deployment because the
library references still refer to the old directory I had on my
development PC.

How can I make sure (automatically) that the References point to the
libraries in the correct deployment directory ?


I have a macro with 10 modules stored in an .xlA file (this is not
installed in Excel using Tools - Add-Ins). The purpose of Module 1 is
to open, on each business day, a workbook (it has only one worksheet
with zero formulas, only strings and numbers; the preceding day's
version is replaced), make a few minor changes, and save it to a shared
drive (same path as the file that was opened); also, this file is
attached to an Outlook e-mail and sent to an user group. It is
desirable that when users open the e-mail and open the attachment, that
the window be "maximized"; also, it is desirable that the shared drive
workbook window be "maximized" when it is opened using Windows Explore
and that, when both of these workbooks are shut down without the user
having made any changes, that the user not receive the pop-up question
- "Do you want to save the changes you made ...?".

In the 'ThisWorkbook' module of my .xlA file, I have the following

Option Explicit
Private Sub Workbook_Open()
Application.WindowState = xlMaximized
End Sub

Near the end of Module 1, I have the following code:

ActiveWindow.WindowState = xlMaximized
SaveWorkBook oWBookOutPut

The following applies to both the e-mail attachment and to the file
stored on the shared drive:
I am getting inconsistent results; most of the time, the new workbook
opens in "xlNormal" size. Sometimes I am getting the "Do you want to
save ...?" prompt and sometimes not when shutting down the file without
making any changes; furthermore, when viewing the question prompt and
selecting "No", and then re-opening each of the workbooks, sometimes I
will not get the question prompt -- subsequent re-openings behave
exactly the same.

Lastly, I exported Module 1 and made it a freestanding macro. It
behaves perfectly each time I run it. Does anyone have any ideas as to
what is happening?

Thanks for a response,