• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 403
  • Last Modified:

Macro Enabled Workbook

I have a VBA that I did that loops though a list, updates a worksheet, with the new information, saves a copy based on the update and loops until it has cycled through a range of values.  There is only one problem with this.  It saves the new file as macro-enabled.  Is there a sub I can add to save as a regular file?
0
Jenedge73
Asked:
Jenedge73
  • 10
  • 7
  • 2
  • +1
1 Solution
 
Shanan212Commented:
When you are saving, change the extension as

.xls

eg:

activeworkbook.SaveAs("NAME OF FILE" + .xls")
0
 
Jenedge73Author Commented:
Thanks, however it doesn't work with a particular add-in I'm using.  Is there a way i could open every file in all the folders and sub-folders, save it as a regular excel file and delete the macro enabled copy?
0
 
Shanan212Commented:
That is possible. However, you would then loose the VBA inside those macro enabled copy.

Your best bet is to create a macro for every folder, run it from inside that folder to go though every excel file...save it as xls and leave the macro-enabled as it is for backup reasons.
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
Jenedge73Author Commented:
The add-on im using breaks all links to the source data and that makes the saved copy kinda like a hard copy rendering the macro usless.  I do, however, maintain the orginal with the macro.
0
 
p912sCommented:
Attached is a module I use to remove code from excel and word.

Function RemoveMyCode()
    'delete all code & forms
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule
        
    'word
    'Set VBProj = ActiveDocument.VBProject
    
    'excel
    Set VBProj = ActiveWorkbook.VBProject
        
    For Each VBComp In VBProj.VBComponents
        If VBComp.Type = vbext_ct_Document Then
            Set CodeMod = VBComp.CodeModule
            With CodeMod
                .DeleteLines 1, .CountOfLines
            End With
        Else
            VBProj.VBComponents.Remove VBComp
        End If
    Next VBComp
End Function

Open in new window

To use this you need to "Trust access to the VBA project object module" in Excel.

  File --> Options --> Trust Center --> Trust Center Settings --> Macro Settings

HTH

Scot
0
 
Jenedge73Author Commented:
Thanks.   Do the files have to be open or can i just direct it to perform the action on a folder.  This will save all .xlsm, within a specified folder to .xlsx and delete the .xlsm copy?
0
 
GrahamSkanCommented:
You can save a macro-free copy by specifying the file format parameter

ActiveWorkbook.SaveAs "C:\MyFolder\MyBook.xlsx", xlOpenXMLWorkbook
0
 
p912sCommented:
This code should go in your master to strip the code when you do the save as, which could all be part of the same routine.

You're really trying to solve two problems here; yes?

  1. Strip code out of workbooks as you save them from now on.
  2. Remove code from existing saved workbooks.

Or is it only #2?

Scot
0
 
Jenedge73Author Commented:
Thats the issue.  The add-on that I'm using saves the copy, I'm just directing my current VBA to perform the add-on function.  In order for me to convert the file to .xlsx and to maintain the correct format the add-on function has to save the file first.  Is there a way i can  convert every file within folder and subfolders to [same file name].xlsx and delete the old xlsm file.
0
 
p912sCommented:
You want to strip the code and rename from *.xlsx to *.xlsm for all the files in a particular folder?

The add-in does the save, and then you need to remove the code and rename?

Scot
0
 
Jenedge73Author Commented:
Yes.  The add-on is a global software product.  It distributes the end product to a folder.  I just want to change the properties of every file it saves.
0
 
GrahamSkanCommented:
This will do it for all the workbooks in a particular folder:

Option Explicit

Sub ConvertWorkbooks()
    Dim wbk As Workbook
    Dim strFile As String
    
    Const strFolder = "C:\MyFolder\"
    
    Application.DisplayAlerts = False
    strFile = Dir$(strFolder & "*.xlsm")
    Do Until strFile = ""
        Set wbk = Workbooks.Open(strFolder & strFile)
        wbk.Saved = False
        wbk.SaveAs Replace(wbk.FullName, ".xlsm", ".xlsx"), xlOpenXMLWorkbook
        wbk.Close
        Kill strFolder & strFile
        strFile = Dir$()
    Loop
End Sub

Open in new window

0
 
p912sCommented:
GrahamSkan ~ very nice. Looks like what I was going to offer this morning... :)

Scot
0
 
Jenedge73Author Commented:
Will this work on the subfolders as well?
0
 
Jenedge73Author Commented:
I copied the code into a module.  but when i execute nothing happens.  I also changed the
Const strFolder = "C:\MyFolder\"
to a specified folder
0
 
p912sCommented:
When you changed this line to the your folder to you include the "\" at the end?

was: "C:\MyFolder\"

you: "C:\MyActualFolder\"

I've tested the code and it works great!

As for will this do subfolders in the parent folder, not as written.
0
 
Jenedge73Author Commented:
It does, it works great.  Would it be a simple modification for it to do sub folders?
0
 
p912sCommented:
Simple - no.
0
 
Jenedge73Author Commented:
Thanks
0
 
p912sCommented:
Not that I want to complain, but a little recognition and points split seemed appropriate here since I helped you thru this... yes it was GrahamSkan's code, but this seems like a joint effort.
0
 
Jenedge73Author Commented:
I'm sorry, I wasn't paying attention and I didn't know you could divvy them up.  Yes, i do appriciate the help.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 10
  • 7
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now