Vba code to save a worksheet as normal excel worksheet (without macros)

[kenny9002]

I currently have the following vba code that saves an excel worksheet as PDF:

' 1 Newly created file is saved as a PDF file
Path = "C:\Copy Invoices"
 
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
Path & "\" & sNewWorkbookName & " - " & [G5], Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False

I should be very grateful if the above code could be changed/tweaked for me so that, instead of saving the worksheet as PDF, it would save the worksheet as normal Excel worksheet (without macros), in the same location/folder as shown above. I use Excel 2007 - 2013.

Thanks all for your kind help, and Merry Christmas to you all.

Kenny

 

[mole999]

have a read here > https://www.rondebruin.nl/win/s5/win001.htm bearing in mind you want to change the workbook to one without macros that is currently running a macro, or you could use the routine to just save the woeksheet itself as the xlsx

 

[kenny9002]

Dear Mole999,

Thanks very much for your prompt and kind response to my Post.

I must confess that my knowledge of vba is still very basic. I got through to the link that you provided.

Unfortunately, due to my very basic knowledge, the options provided got me confused, hence I ask for your further kind help or that of any other forum member.

A kind friend provided me with the code in my Post #1 above. The code works OK to use it for saving as a PDF. However, there is need now to discontinue saving as PDF but as a normal Excel file without macros. I need it to complete a task that I currently have.

I should therefore be grateful if the code in my Post #1 can be tweaked or adjusted to meet that need. If saving the Excel worksheet as a normal Excel file (without macros) would require a code that is completely different altogether from that which I have shown in my Post #1 , I'd appreciate help with that, please. I wish I knew which of the options listed in the rondebruin link to choose that would meet my need.

I look forward to further help with this, please.

Thanks.

Kenny

 

[Fluff]

Untested, but try

' 1 Newly created file is saved as a PDF file
Path = "C:\Copy Invoices"
 
ActiveWorkbook.SaveAs FileName:=Path & "\" & sNewWorkbookName & " - " & [G5], FileFormat:=51

 

[kenny9002]

Thanks Fluff for your kindness and for your very helpful code.

Although it saved the worksheet as a macro-free Excel file, there are two problems, about which I need your help:

1. The main Invoice Sheet has the following command buttons - 'Save & Clear'; 'Exit'. Although the main Invoice form and details cover A1 to N52, the above command buttons reside in columns O to R.

And when I click on the 'Save & Clear' command button, and it eventually saves the worksheet, I find that the saved copy contains those two command buttons.

I would like it to save the worksheet, but without the command buttons appearing on the saved copies, please.

2. When I click on the 'Save & Clear' command button, I get a message box with the following message:

'The following features cannot be saved in macro-free workbook: .VB Project.
To save a file with these features, click No and then choose a macro-enabled file type in the file type list.
To continue saving as a macro-free workbook, click Yes.'

I have to click Yes for it to save as macro-free. Please is there any adjustment that can be made to your code to prevent above message and get it to save straightaway the copy as a macro-free fille?

Apart from these two issues, every thing else is fine, and I am grateful to you for your help.

Thanks very much.

Kenny

 

[Fluff]

Try

   Dim Shp As Shape
   Dim Pth As String
   
   For Each Shp In ActiveSheet.Shapes
      Shp.Delete
   Next Shp
   
   Pth = "C:\Copy Invoices"
   Application.DisplayAlerts = False
   ActiveWorkbook.SaveAs FileName:=Pth & "\" & sNewWorkbookName & " - " & [G5], FileFormat:=51
   Application.DisplayAlerts = True

 

[kenny9002]

Dear Fluff,

Thanks for your continued support.

The corrected code (ref your post #6 ) almost did the job except for some minor issue.

Meanwhile, I am sorry that I did not inform you that the body of the Invoice Sheet contains some Option buttons which have to be selected while completing the Invoice. These option buttons are required as part of the Invoice.

Therefore, what happens is that the following line of your code deletes all the 'command buttons' includiang the 'option buttons' in the body of the Invoice:

For Each Shp In ActiveSheet.Shapes
      Shp.Delete
   Next Shp

In the course of saving the worksheet as a macro-free file, I would like the code to exclude/delete the command buttons, but not the option buttons in the body of the Invoice. I believe that the word 'Shapes' in the code covers both the command buttons and option buttons. Please, is there anything that can be done to delete only the command buttons and not the option buttons in the body of the Invoice?

I have no doubt that this final tweak will completely solve my problem.

Again, thanks for all your help.

Kenny

 

[Fluff]

How about

Private Sub CommandButton1_Click()
'Ron de Bruin https://www.rondebruin.nl/win/s4/win002.htm

   Dim obj As OLEObject
   Dim Pth As String
   
   For Each obj In ActiveSheet.OLEObjects
      If TypeOf obj.Object Is MSForms.CommandButton Then
         obj.Delete
      End If
   Next
   
   Pth = "C:\Copy Invoices"
   Application.DisplayAlerts = False
   ActiveWorkbook.SaveAs FileName:=Pth & "\" & sNewWorkbookName & " - " & [G5], FileFormat:=51
   Application.DisplayAlerts = True
End Sub

 

[kenny9002]

Dear Fluff,

I am very sorry for bothering you this much with this problem which appears to be somewhat knotty. I have tried your code but can't seem to get it it to work. The code does not get rid of, from the saved copies, the buttons that control the macros.

I must add that your code, in your Post #6 above as follows:

Dim Shp As Shape
   Dim Pth As String
  
   For Each Shp In ActiveSheet.Shapes
      Shp.Delete
   Next Shp

which refers to the buttons as 'shapes' successfully got rid of the shapes which control the macros, and which serve, for me, as command buttons. The only unfortunate thing is that in doing so, it also got rid of the option buttons, which should be allowed to remain.

I created those 'command buttons' using the 'Insert Shapes' feature in my Excel 2007. The shapes look more elegant than either the 'Form' or 'ActiveX' command buttons.

Unfortunately, your most recent code (Post #8 ) does not delete any of the command buttons (whether created with shapes or with Form or ActiveX) from the saved copies.

As I searched some other forum, I came across a thread by someone who had a somewhat similar issue - although not completely the same as mine. The member indicated that he solved his own problem with the following vba code:

[B]'Delete the command button in new workbook[/B]
[B]With ActiveSheet[/B]
[B]    .Shapes("CBONew").Delete[/B]
[B]    .Shapes("CBOSave").Delete[/B]
[B]End With[/B]

I assume that the two buttons he has that control the macros are named 'New' and 'Save', respectively.

The two shapes that I have (which control macros, and which I want deleted from the saved copies of the Invoice) are named 'SAVE' and 'EXIT', respectively.

I tried to replicate his code - inserting the names of my own two shapes. But I keep getting 'Runtime Errors'.

I wish to retain the two beautiful shapes and having them serve as command buttons, and wish that there is a way your code (Post #6 ) could be tweaked to do the trick for me - or indeed any other code that can do it.

The whole thing is not only frustrating me, but I seem to be letting you share in my frustration - even at Christmas! Please, I sincerely apologize for the bother.

I remain eternally grateful to you for your continued help.

Kenny

 

[Fluff]

Try

Sub RemoveShapes()
   Dim Shp As Shape
   Dim Pth As String
   
   For Each Shp In ActiveSheet.Shapes
      If Not (Shp.Type = msoOLEControlObject Or Shp.Type = msoFormControl) Then Shp.Delete
   Next Shp

   Pth = "C:\Copy Invoices"
   Application.DisplayAlerts = False
   ActiveWorkbook.SaveAs FileName:=Pth & "\" & sNewWorkbookName & " - " & [G5], FileFormat:=51
   Application.DisplayAlerts = True
End Sub

You will need to set a reference.
In the VBE select tools > References > Microsoft Office xx.0 Object Library (make sure you check the box, rather than just select the line) > OK