Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Using VBA to Save As Using Macro Enabled WBs

Posted on 2013-01-28
12
Medium Priority
?
775 Views
Last Modified: 2013-02-02
Hi Experts,  :)

Wanted to use the "Save As" feature to create a copy of a workbook in a different location.

I have a Master workbook which will contain over 20 sheets... Of these sheets the end user would need to view only 2 ..  I was figuring creating a copy in a different location would be the easiest way to remove the other sheets the end user do not need.

I've attached a sample workbook with the following code in module:  modCopyWB

Sub SaveNew()
     
    Dim sFileName As String, SFilePath As String
     
    sFileName = ThisWorkbook.Name
    SFilePath = "C:\Temp\" 
     
    Application.ScreenUpdating = False
     
    On Error GoTo errcather
     
    Sheets(Array("RPV", "RPV (2)")).Copy
     
    On Error GoTo 0
     
    ActiveWorkbook.SaveAs Filename:=SFilePath & sFileName

    ActiveWorkbook.Close Savechanges:=False
     
     
errcather:
    MsgBox "One or more the worksheets does not exist in this workbook"
     
    Application.ScreenUpdating = True
     
End Sub

Open in new window


The problem I'm having is when it's time to save the workbook it states that I can't save it due to my workbook containing Macros.  It's advising me that I can't save the VB project as a non-macro workbook.  But the name I'm asking it to be named in the code is the same as my current 'macro' enabled workbook with the ".xlsm" extension.

Why doesn't the code recognize or accept the naming convention?  Is there a fix?

The "save as" workbook should have the same name as the Master workbook just at a different location where the end users will have READ ONLY access.

Thank you in Advance!
EE-Sample-WB.xlsm
0
Comment
Question by:"Abys" Wallace
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38827974
Dim  FileExtStr As String
Select Case YourWBName.FileFormat
                Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
                Case 52:
                    If .HasVBProject Then
                        FileExtStr = ".xlsm": FileFormatNum = 52
                    Else
                        FileExtStr = ".xlsx": FileFormatNum = 51
                    End If
                Case 56: FileExtStr = ".xls": FileFormatNum = 56
                Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
                End Select
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 38827993
I don't know if it has anything to do with this, but I typically use the save or saveas vba events to do my saves.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38828006
Update my code above and add.

Dim FileFormatNum As Integer

and the following at the end

SaveAs YourPathAndFileName & FileExtStr, _
                FileFormat:=FileFormatNum
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 16

Expert Comment

by:kmslogic
ID: 38828179
Bottom line is that if you save in current Excel formats (2007 or later) you cannot save macros in a file that with an xlsx extension.  

You can get around this by saving the document as an earlier XLS format.
0
 

Author Comment

by:"Abys" Wallace
ID: 38828373
@MartinLiss ... where would I place your recommendation?  My VB training wheels are still on.  :)  Do I create a separate module and call your code in my original?

Or do I include it somewhere within my code?  

Thank you for your help~
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38828470
Replace line 16 with the following. Note: your sFileName should NOT already have a file extension.

Select Case YourWBName.FileFormat
    Case 51
         FileExtStr = ".xlsx": FileFormatNum = 51
    Case 52
         If .HasVBProject Then
           FileExtStr = ".xlsm": FileFormatNum = 52
         Else
           FileExtStr = ".xlsx": FileFormatNum = 51
         End If
    Case 56
          FileExtStr = ".xls": FileFormatNum = 56
     Case Else
          FileExtStr = ".xlsb": FileFormatNum = 50
    End Select
ActiveWorkbook.SaveAs Filename:=SFilePath & sFileName & FileExtStr, _
                FileFormat:=FileFormatNum

Open in new window

0
 

Accepted Solution

by:
"Abys" Wallace earned 0 total points
ID: 38828495
Thank you everyone for your help and attention to my question...  I worked out a solution by recording a macro while saving my "macro-enabled" book as a different name.  

In my code I had to change:

    ActiveWorkbook.SaveAs Filename:=SFilePath & sFileName

Open in new window

to

    ActiveWorkbook.SaveAs Filename:=SFilePath & sFileName, FileFormat _
        :=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Open in new window

EE-Sample-WB.xlsm
0
 

Author Comment

by:"Abys" Wallace
ID: 38828510
@MartinLiss  I'm getting a compile error at

.HasVBProject

Open in new window


in this portion of the code

    Case 52
         If .HasVBProject Then
           FileExtStr = ".xlsm": FileFormatNum = 52
         Else
           FileExtStr = ".xlsx": FileFormatNum = 51
         End If

Open in new window


I created a macro to attempt to determine what was missing and now my issue is resolved with the following extra code at my original line 16:

FileFormat :=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False 

Open in new window

0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38828525
Add a With ActiveWorkbook at the start of my code and End With and the end of my code.
0
 

Author Comment

by:"Abys" Wallace
ID: 38828563
@MartinLiss after modifying I ran your code which still saved it as a "non-Macro" enabled workbook.

Sub SaveNew()
     
    Dim sFileName As String, SFilePath As String
     
    sFileName = "EE Sample WB_Report"
    SFilePath = "T:\ErrorData\Reporting_Analysis\RepII Reports\"
     
    Application.ScreenUpdating = False
     
    On Error GoTo errcather
     
    Sheets(Array("RPV", "RPV (2)")).Copy
     
    On Error GoTo 0

With ActiveWorkbook
Select Case .FileFormat

    Case 52
         If .HasVBProject Then
           FileExtStr = ".xlsm": FileFormatNum = 52
         Else
           FileExtStr = ".xlsx": FileFormatNum = 51
         End If
    Case 56
          FileExtStr = ".xls": FileFormatNum = 56
     Case Else
          FileExtStr = ".xlsb": FileFormatNum = 50
    End Select
    
ActiveWorkbook.SaveAs Filename:=SFilePath & sFileName & FileExtStr, _
                FileFormat:=FileFormatNum

End With

    ActiveWorkbook.Close Savechanges:=False
     
     
errcather:
    MsgBox "One or more the worksheets does not exist in this workbook"
     
    Application.ScreenUpdating = True
     
End Sub

Open in new window


The code gave my copy workbook the ".xlsx" extension because it stopped with the 1st case... Case 51 and then went to "end select"

I attempted to modify by removing "case 51" to let it attempt at "case 52" if-then-else statement but it skipped this step as well and went to case 50 giving it the ".xlsb" extension.

Again thank you for your assistance but my fix is working so I'll just work with that.  :)

Kindest Regards
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 38828607
I think martin should get points for this question.
0
 

Author Closing Comment

by:"Abys" Wallace
ID: 38846411
after changing my line 16 to include the following:

FileFormat :=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

 My copy saved in the correct format.  Appreciate everyone's assistance
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question