Solved

Using VBA to Save As Using Macro Enabled WBs

Posted on 2013-01-28
12
696 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

911 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now