Solved

Using VBA to Save As Using Macro Enabled WBs

Posted on 2013-01-28
12
719 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 46

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 46

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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
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 46

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 46

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

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.

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

837 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