Solved

Using VBA to Save As Using Macro Enabled WBs

Posted on 2013-01-28
12
683 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 45

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 45

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 45

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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 45

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

747 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

12 Experts available now in Live!

Get 1:1 Help Now