Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Saving as a macro enabled workbook using VBA code

right ho,

may be incredibly straightforward this (I hope it is) I'm using excel 2007 - i've created a macro enabled template file, and I'm wanting to include some code that will allow me to automatically save in a folder location using the name from a cell, but I still want to be able to use macros in the saved spreadsheet, i assumed it would be a simple case of changing the file extension to .xlsm, but this returns an error, i can get the file to save as a standard workbook, so i'm a bit lost - below is the code i've been using

Sub SaveBook()
Dim sFile As String
sFile = Range("J8") & ".xlsx"
ActiveWorkbook.SaveAs Filename:="O:Mobile PlantGeneralBudgeting & part expenditure" & sFile
End Sub

any help most appreciated!

Cheers


Post your answer or comment

comments powered by Disqus
I have a 2-worksheet Excel workbook (Excel 2007 and 2010) that serves as a template for users to enter collected data into. Each data set requires a unique file name (for tracking), so I wrote the macro below. Each time a user collects data, they open the template workbook and it directs them to immediately save the file into a specific directory. The code for that is as follows:

Private Sub Workbook_Open()
  ' Written by Authors
  ' Last updated 02/23/2011

    ' Open a Message Box informing the user that they MUST save the workbook before use.
    MsgBox ("This workbook MUST be saved to the I: drive before use.")

    Dim Show_Box As Boolean
    Dim Response As Variant

    ' Set the Show_Dialog variable to True.
    Show_Box = True

    ' Begin While loop.
    While Show_Box = True

         ' Show the Save File Instructions Input Bbox.
         Response = InputBox("User Instructions", _
              "Save File Formatting Instructions")

         ' Check to ensure the user entered a file name.
         If Response = "" Then
         Else
              ' Test to make sure an entry was made.
              If Response <> "" Then
                   ' Set the path on the I: drive to save the file to
                   MyPath = "Specified Path"
                 
                   ' Set the format of the saved file as a macro-enabled workbook
                   ActiveWorkbook.SaveAs Filename:=MyPath & "" & Response, FileFormat:=51
                   Show_Box = False
              Else
              End If
         End If
  
    ' End the While loop.
    Wend
  
End Sub
I know the FileFormat should be set to 52 for a macro-enable workbook, but I have it set to 51 for now because if the saved file is macro-enabled (as I need it to be), when it is opened later it will run the macro again asking for a file name. There are other macros I plan to insert into the template workbook for use in the saved macro-enabled workbook, so I need to disable the macro above once the new workbook is saved.

I thought there may be code that could be inserted before the Message Box appears (perhaps an If Then statement) that would check the Active Workbook file name against the name of the template workbook. If they are the same, the macro will run because the filename match means the template was opened. If the Active Workbook does not match, then the macro is skipped. I tried a couple things but had no luck. Any suggestions or guidance would be greatly appreciated.

Hi all

I have the following code, which simply saves a workbook as a new workbook with a date stamp. However, I want to save it as a macro free workbook and I prefer not to get that dialog box warning if i want to save as a mcro free workbook. Any suggestion please?


	VB:
	
 Save_as_new_workbook() 
     '\\\\\\\\\\\
     ' ********* SAVE AS A NEW WORKBOOK *************
     '///////////////////////////////////////////////
    Dim C As String, directory As String 
    Windows(MasterFileName).Activate 
    sheets("Save").Select 
    C = Range("d2").Value 
    directory = Range("A2") 
    ActiveWorkbook.SaveAs Filename:=directory & C & ".xls", _ 
    FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ 
    ReadOnlyRecommended:=False, CreateBackup:=False 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello everyone,

because the file is sent to a lot of different persons, my boss wanted my to protect it and shut down the ribbons, formula bar...so it looks similar on every pc.
I used 'Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"' and a few more.
Now my problem:
Most people save it as macro-free workbook, so if they start it again, all the macros are disabled.
Is there any possibility to allow just saving as a marco enabled workbook?

Thanks in advance,

the Intern

I have been editing the following code so that it will work a little better for my needs, but now it keeps saying that the following features can not be saved in a macro free workbook * VB Project. Other than 4 command buttons that use codes to print, I don't need anything from the VB Project to be saved with the sheet.

Sub cmdSaveReport_Click()
Application.ScreenUpdating = False
ActiveSheet.Select
ActiveSheet.Copy
ActiveSheet.Range("Type_Of_Call").Select
ThisFile = Range("FileName").Value
'ActiveSheet.SaveAs Filename:="upstairsshareddocsIncident Reports2010 Incident Reports" & ThisFile &
".xls"
ActiveSheet.SaveAs Filename:="C:UsersUserDocumentsFire Departmentdummy reports" & ThisFile &
".xls"
Application.ScreenUpdating = True
ActiveWorkbook.Close
'Call RemoveRequestNumber
End Sub
I am using excel 2007 but in compatability mode, (since the computer that the end users work with are all 2003). The file I am working on is a .xls file and I am saving it as the same.
If I select yes to save as a macro free file the code finishes and works fine.
If anyone has any ideas on why this error is coming up I would appreciate it.

Hi all,

please can someone help me....

I need a macro ie when a user opens a macro enabled workbook,he should be asked for a username and password to access workbook.
Administrator should have right to create users with reset password rights

thanks

Why can't I record "flag for follow up" as a macro? Is there some VBA code I
can use to summon it?

I have read several articles on VB code to change the color of the worksheet tab based upon a cell value, is there a way to do this right in the worksheet, and not have to code VB? I ask this because otherwise I will have to save as a macro enabled workbook, allow vb code to run, allow maros, and all that other maco security stuff.

Any ideas would be appreciated!

Thanks in advance.

I created a excel file (that was originally a Macro-Enabled Worksheet) that I wanted other users in my company to replicate but not be able to change the original document. This file contains several macros. One of these macros is connected to a "Save to Server" button which saves the file with the name that the user enters into a cell in the worksheet (cell b4). It saves the document to a specified folder on our server (this folder is dependent on the intials placed in cell H4).

Long story short, basically when I ran some trial runs on the original excel document (the macro enabled document), it works to perfection. However, since I didn't want the users to be able to touch the original I saved that document as aMacro-Enabled Excel Template to the server (where it will be stored). I closed it up, then reopened it (as the typical user will) to test the document and I got the following error message:

"The following features cannot be saved in macro-free workbooks:

- VB Project

To save a file with these features, click No, and then choose a Macro-enabled file type in the File Type List.

To continue saving as a macro-free workbook, click Yes."

This is slightly confusing as I would think that if it is a Macro-enabled template that when you open said template file, it would open a macro-enabled worksheet. Not to mention, the macro I have written adds the extension xlsm to the document, so I would think this too would fix it. I have even changed by excel options to automatically save all excel files as macro-enabled documents and added the folder where the Macro-enabled template is being held on the server as a trusted location.

When I click "No" on the above mentioned error message, I get a Run-time error '1004: VB projects and XLM Sheets cannot be saved in macro-free workbook.

I guess my biggest question is why am I getting this error with the Macro-Enabled Template? Here is my code for the Macro to save the workbook:


	VB:
	
 SaveButton_Click() 
    If Range("e3") > 0 Then 
        MsgBox "Please Specify A file Name and/or Superintendent", vbOKOnly, "Warning!" 
        Range("b4").Select 
    Else 
        Dim sfile As String 
        Dim uname 
        sfile = Range("B4").Value & ".xlsm" 
        uname = Range("H4").Value 
        ActiveWorkbook.Save 
        ActiveWorkbook.SaveAs Filename:="S:Proposal Worksheets2011 Worksheets" & uname & "" & sfile 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I am trying to also think ahead as I know we still have some users on 2003 Excel, so I am not sure how this will affect them (when trying to save a document as xlsm) and I also wouldn't mind adding code to automatically create a subfolder in the directory for the initials that the user inputs. Any help would be greatly appreciated!

I have a gigantic excel file with multiple macros and 10+ tabs. I feel that the file has become corrupt. Excel will not let me save the file as a macro-enabled workbook but instead saves the file where I put it. When I go back to find it the icon for the file is a blank sheet of paper and then I have to specify that the file should be run in Excel. Why does this happen? It will not let me save the file as any other type of file (ie: 2003, 2007). When I go to "save as" the only file type it will let me save as is "Excel Files". It will also not always recognize the new macros that I have built, but still recognizes the old macros I built before the problem started to occur.

Any ideas as to what the problem is and what a solution might be?

Thanks in advance.

My codes image a table in Office 2003 excel template with macro enable, after
I update to Office 2007, I get this error message on closing the workbook:
The following features cannot be saved in macro-free workbooks:
VB project
To save a file with these features, click No, and then choose a
macro-enabled file type in the File Type list.
To continue saving as a macro-free workbook, click Yes.

Is there a way I can suppress this message programmatically? Or using Office
2007 policy?
It seems that Office 2007 does not allow to save as .xlsx with macro.
Thanks for your help.

[Solved]
I am using the function: Application.OnTime TimeValue to run a specific macro every day at 4:00 PM, but at 4:00 PM the macro tries to launch another macro but is disabled by Excel even though the macro security is set to allow it to run. If I run the “Sample_Macro_Name” manually, it runs just fine. However, as soon as I use the another macro to launch the “Sample_Macro_Name” at a specific time, when the that time occurs, instead it returns the error message:

"Cannot run the macro Sample_Macro_Name() The macro may not be available in this workbook or all macros may be disabled”

Excel version: Office 2010
Macro security: set to enable all macros to be run.
Workbook is saved as a macro enabled workbook in a “trusted file location” on my local drive.

I have tried placing the 2nd bit of code, the macro, in standard modules, worksheet modules, and ThisWorkbook module, but in every case the macro will not fire with the Application.OnTime function, but will always fire when I manually launch it. It is as if Microsoft does not allow timed macros to fire off at a certain time due to security, yet it can be manually launched.

1. Code in the ThisWorkbook module:


Private Sub Workbook_Open()
  'Runs a macro at 4:00 PM
   Application.OnTime TimeValue("16:00:00"), "Sample_Macro_Name()”
End Sub


2. Then in a regular module the code:


Sub Sample_Macro_Name()
  [Macro code goes here]
End Sub
Folks, I appreciate your wise guidance.

Hi All,
I Have a macro enabled workbook and i am trying to set it that only people with the password can open the file, I can set the password for modifying the file but when i set it for opening the file, i get this message;

"This workbook contains Excel 4.0 macros or Excel 5.0 modules. If you would like to password protect or restrict permission to this document, you need to remove these macros."

But i cannot remove these macros as they are integral to the sheet...!!!

Please help!!!

I have a 2007 worksheet that I save as a macro enabled file in 2007. It has many tabs, with about 15 tabs feeding into 5 intermediate tabs. The 5 intermediate tabs then feed into 1 tab that summarizes everything. There are a few more tabs for other purposes within this same worksheet.

Before I can do these summaries, I export an expenditure report from our accounting software, massage it to remove unnecessary rows and columns, and do other things using one macro. This is one of the tabs in this worksheet.

After I'm done, I send it to one person who uses 2003. But before I send the worksheet to this person, I delete the macro. Then I save the worksheet as a 2003.

The other person can open the file with his 2003 and everything looks normal. But when he saves it, links show "#NAME?" Links are to various tabs but all tabs are in this same worksheet; there are no links to another worksheet.

The formula in those cells that show this error still look the same, e.g., in the Summary tab cell B74 the formula would look like =SUM(Alaska:Idaho!B74), but the cells themselves show this error.

What are possible solutions to this?

does anyone know how to share a workbook with VBA codes??

The code below forces a new, unsaved instance in Excel (opened from a sharepoint menu on our intranet) to save as a macro-enabled workbook, but I cannot get it to open the "Save As" window to the correct path.

The issue is when they save, it defaults to their local My Documents folder, but we want this to save to whatever sharepoint (web folder) they last navigated to when they opened the new instance. That is exactly the path it opens to if I remove this code, so it's in there somewhere.

(Without this code, although it wants to save in the correct web folder, it tries to save as an xlsx by default, which is a nuisance to the users...who are also unwilling to just change the type to xlsm in the save as dialog.)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  Dim varFileName As Variant
  If SaveAsUI = True Then
    Cancel = True
    Application.EnableEvents = False
    varFileName = Application.GetSaveAsFilename("somefilename.xlsm", " Excel Macro Enabled Workbook (*.xlsm),
*.xlsm,", 2)
    If varFileName = vbFalse Then
      Cancel = True
    Else
      Me.SaveAs varFileName, 52
    End If
    Application.EnableEvents = True
  End If
End Sub


Is there a way to have a single workbook default to save as a xlsm
(macro-enabled) file type instead of xlsx when the template it was created
from was a xltm (macro-enabled) template?

Currently it brings up a message about saving macros in a macro-free
workbook. Most of our uses will not even read/understand this and will just
save the workbook without the macros.

We would prefer not to change the default save type for workbooks from xlsx
if we don't have to.

Hi all,

I have a macro enabled workbook, and I want it to
prevent the user from saving the file in any way. Canon anyone pls tell me how to disable the save and save as functionality using vba in the Auto_Open subroutine?

Thanks in advance.

Tino XXL

Hi,

I am using a loop to send an email to each relevant person from a range of email addresses. Each time the loop runs a temporary file is created, which I then save afterwards which a unique name for each individual. This was working fine until I attempted to amend the RangetoHTML function to use a macro enabled template rather than just an ordinary workbook. Now the email generated is blank and I can't understand why.


	VB:
	
 Range) 
     ' Changed by Ron de Bruin 28-Oct-2006
     ' Working in Office 2000-2010
    Dim fso As Object 
    Dim ts As Object 
    Dim TempFile As String 
    Dim TempWB As Workbook 
     
    TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" 
     
     'Copy the range and create a new workbook to past the data in
    Application.DisplayAlerts = False 
    rng.Copy 
    [B]Set TempWB =
Workbooks.Add(template:="[URL="file://dartgroup.plcdata1Jet2FlightOperationsJet2FltOpsEFBChange"]dartgroup.plcdata1Jet2FlightOperationsJet2FltOpsEFBChange[/URL]
RequestsChangeTemplate.xltm") 
    [/B]    With TempWB.Worksheets("Sheet1") 
    .Cells(2, 1).PasteSpecial Paste:=8 
    .Cells(2, 1).PasteSpecial xlPasteValues, , False, False 
    .Cells(2, 1).PasteSpecial xlPasteFormats, True, False, False 
    .Cells(2, 1).Select 
    Application.CutCopyMode = False 
    On Error Resume Next 
    .DrawingObjects.Visible = True 
    .DrawingObjects.Delete 
    On Error Goto 0 
End With 
Application.DisplayAlerts = True 
 
 
 'Publish the sheet to a htm file
With TempWB.PublishObjects.Add( _ 
    SourceType:=xlSourceRange, _ 
    Filename:=TempFile, _ 
    Sheet:=TempWB.Worksheets("Sheet1").Name, _ 
    Source:=TempWB.Worksheets("Sheet1").UsedRange.Address, _ 
    HtmlType:=xlHtmlStatic) 
    .Publish (True) 
End With 
 
 'Read all data from the htm file into RangetoHTML
Set fso = CreateObject("Scripting.FileSystemObject") 
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) 
RangetoHTML = ts.ReadAll 
ts.Close 
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _ 
"align=left x:publishsource=") 
 
 'Define the unique file name and save the document in the user's folder.
Dim TempSaveAs As String 
 
TempSaveAs =
"[URL="file://dartgroup.plc/data1/Jet2FlightOperations/Jet2FltOps/EFB/Change"]dartgroup.plcdata1Jet2FlightOperationsJet2FltOpsEFBChange[/URL]
Requests" & Me.txtcvalue.Value & "" & "EFB" & Me.txtConfigNo.Value & " " & Me.txtTitle.Value 
 
 
TempWB.SaveAs (TempSaveAs) 
TempWB.Close 
 
 
 'Delete the htm file we used in this function
Kill TempFile 
 
Set ts = Nothing 
Set fso = Nothing 
Set TempWB = Nothing 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I have changed a few bits and pieces from Ron de Bruin's original code, but it is using the template (in bold) which has caused it to stop working. The debugger doesn't highlight any lines, it just doesn't populate the body of the email.

This is the code I am using for the email creation:


	VB:
	
 
Dim OutMail As Object 
Dim cell As Range 
Dim rng As Range 
Dim CellRng As Range 
Dim cvalue As String 
Dim TempHyperlink As String 
 
Application.ScreenUpdating = False 
Set OutApp = CreateObject("Outlook.Application") 
Set rng = TempSheet.Rows("1:16") 
 
With ThisWorkbook.Worksheets("Log") 
    Set CellRng = .Range(.Cells(iRow, 6), .Cells(iRow, 26)) 
End With 
 
On Error Goto cleanup 
For Each cell In CellRng 
     
     
     
    If cell.Value Like "?*@?*.?*" Then 
        txtcvalue.Value = Left(cell.Value, WorksheetFunction.Search("@", cell.Value, 1) - 1) 
        TempHyperlink =
"[URL="file://dartgroup.plcdata1Jet2FlightOperationsJet2FltOpsEFBChange"]dartgroup.plcdata1Jet2FlightOperationsJet2FltOpsEFBChange[/URL]
Requests" & Me.txtcvalue.Value 
        Set OutMail = OutApp.CreateItem(0) 
        On Error Resume Next 
        With OutMail 
            .To = cell.Value & vbNewLine 
            .Subject = Me.txtTitle.Value 
            .ToDoTaskOrdinal = DeadlineDate 
            .TaskDueDate = DeadlineDate 
            .TaskStartDate = Me.txtIssueDate.Value 
            .FlagStatus = 2 
            .FlagRequest = strFlagRequest 
            .FlagIcon = 6 
            .HTMLBody = EmailContent & "    Record  - Please complete the relevant Department Specific Information Fields " &
RangetoHTML(rng) & "
" & "
" & "
" & "Should you require further information regarding the Change Request prior to providing this feedback, please see" & Me.txtOriginator.Value & " prior to the deadline." .Send End With On Error Goto 0 Set OutMail = Nothing End If Next cell cleanup: Set OutApp = Nothing
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks,

Maria.

I have created a spreadsheet that I want to send to various end users. The workbook contains 3 sheets. The first sheet is a welcome screen explaining the use of the spreadsheet. Additionally, I have written a macro assigned to a smiley that will take the user to the desired location on the second sheet. Certain cells are are also locked and the workbook is password protected to prevent altering. I have saved the workbook as a macro enabled file. However, when the file is opened, it says the macro is not contained in the workbook. I am sure it is something simple I have overlooked.

I would appreciate any help.

thx,
Stumped

Hi
I wish to save a macro enabled template file to a macro enabled file in D:Invoice2012 with a file name equivalent to the value of cell k3 in sheet 1.
I tried the following macro but ended up with a error

Sub saveFileMacro()
FullFileName = Application.GetSaveAsFilename("D:Invoice2012" & Format(Range("K3").Value) &
".xlsm", _
    "Excel Macro-Enabled Workbook (*.xlsm),*.xlsm", 2, "Save File As")
If FullFileName <> vbNullString Then
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
        FullFileName, FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close
Else
MsgBox "File not saved"
End If
End Sub
It works only till the "save as" screen and once I press save, it shows error "Runtime error '1004'. This extension cannot be used with selected file type and so on..
Originally I got this macro from http://www.excelforum.com/excel-prog...-template.html, customized it a little based on my requirement but still ended up with an error.

A little help would be much appreciated.

Thanks
Anand

I have an excel file that is distributed within my organization. That file contains certain worksheets that the end-users do not need to see or make changes to, so in the VBA project, I change those files to "VeryHidden". Then I protect the workbook's VBA code by going to Tools->VBAProject- Project Properties-> Protection tab and adding a password. This worked great as it allowed all the other sheets to function as intended, but didn't allow the users to access the sheets that were hidden.

However, we recently upgraded to Excel 2007, and the password protection is stripped whenever the file is saved with the extension .xlsx. I can save it as a "macro-enabled" file with the extension .xlsm, but all that needs to be done to remove the password is to save it with the .xlsx extension again. Is there something that can be done in Excel 2007 to protect VBA code, or am I doing something completely wrong?

This is my first post here, so please let me know if I've done something wrong!

Is there any way, I can unprotect a excel workbook using vba macro.

Thanx

Thank you in advance for looking at this problem. I have patched together some VBA code to run a simple counter used to tally the number of patients seen in a day. The intention of this counter is to provide and accurate count as well as storing the data for historically purposes. In the edit mode everything runs great, even the data save function which also zero’s the cells. I have tried many different ways to get it to autorun or even save it as a macro. My problem is that I do not have a good understanding of this, or even how to make this program work from a desktop shortcut. Ideally, I would like to click a desktop shortcut and only have the userform pop up so data can be updated/entered. When the user closes the userform, the form closes and stores the data. The method that I have chosen to save the data is not very efficient, but functional. I am also interested having the data store on a single worksheet whereas; the data is simply stored in each column by date. This would definite facilitate graphing data and comparing our historical encounters. This part of the project may have to be done later. I am using Excel2003. . I am active duty navy and have limited resources here at sea. Any help you can provide is greatly appreciated.

Hi,
I have written one macro which will change background color of some of the rows of one excel file, and save it with the changes. This excel file is having some macros which will run automatically every time we open this. That means it is a micro enabled sheet (.xlsm). But while saving it, with below code:
Application.DisplayAlerts = False
Application.ScreenUpdating = False
objExcelSrc.ActiveWorkbook.SaveAs fileName:= _
MrkSrcFile, FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
one “Microsoft Excel” error message is displaying. The error message says:
“The following Features cannot be saved in macro-free workbooks: .VB Project. To save a file with these features, click No, and then choose a macro-enabled file type in the File Type List. To Continue saving as a micro – free workbook, Click Yes”.
If I click “Yes”, my macro is running fine. If NO, it’s asking to change the format and save it manually.
So, I have two options to suppress the message or to click the default option Yes so that my macro will run. But I am not able to handle this with the below two options.
Below are the codes I have already tried, but not able to handle. Please guide me on this.
'---control moving out
'objExcelSrc.ActiveWorkbook.SaveAs fileName:= _
'MrkSrcFile, FileFormat:= _
'xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
'---control moving out
'objExcelSrc.ActiveWorkbook.SaveAs fileName:=
'MrkSrcFile , FileFormat:= _
'xlOpenXMLWorkbookMacroDisabled, CreateBackup:=False
'---control moving out
'objExcelSrc.ActiveWorkbook.SaveAs fileName:= _
'MrkSrcFile, CreateBackup:=False
'Application.DisplayAlerts = True
'currentWorkSheetSrc.SaveAs MrkSrcFile
'Application.DisplayAlerts = False
'currentWorkSheetSrc.SaveAs fileName:= _
'MrkSrcFile, FileFormat:= _
'xlOpenXMLWorkbook, CreateBackup:=False
'--51 pop ups,56-hang,50-not working
'objExcelSrc.ActiveWorkbook.SaveAs fileName:= _
'MrkSrcFile, FileFormat:=FileFormatNum, CreateBackup:=False
currentWorkSheetSrc.Application.Quit
'MsgBox (j)
Application.ScreenUpdating = True
Thanks
Amarendra


No luck finding an answer? You could always try Google.