[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 782
  • Last Modified:

Using VBA to Save As Using Macro Enabled WBs

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
"Abys" Wallace
Asked:
"Abys" Wallace
  • 5
  • 4
  • 2
  • +1
1 Solution
 
Martin LissOlder than dirtCommented:
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
 
ScriptAddictCommented:
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
 
Martin LissOlder than dirtCommented:
Update my code above and add.

Dim FileFormatNum As Integer

and the following at the end

SaveAs YourPathAndFileName & FileExtStr, _
                FileFormat:=FileFormatNum
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
kmslogicCommented:
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
 
"Abys" Wallaceself employedAuthor Commented:
@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
 
Martin LissOlder than dirtCommented:
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
 
"Abys" Wallaceself employedAuthor Commented:
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
 
"Abys" Wallaceself employedAuthor Commented:
@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
 
Martin LissOlder than dirtCommented:
Add a With ActiveWorkbook at the start of my code and End With and the end of my code.
0
 
"Abys" Wallaceself employedAuthor Commented:
@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
 
ScriptAddictCommented:
I think martin should get points for this question.
0
 
"Abys" Wallaceself employedAuthor Commented:
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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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