View original (French)

[VBA] Save a copy without macros

Solved
Elboubdelapampa Posted messages 153 Registration date   Status Member Last intervention   -  
 jojo -
Hello,
I have an Excel workbook with macros. I would like the macro to save a copy of the file without the macros at the end of the automated process.
Since I have chart sheets with links, I can't just copy and paste the data into other workbooks.
Is it possible to save a workbook without the macros?

Thank you in advance

Configuration: Windows XP / Firefox 3.6.7

2 answers

  1.
    Elboubdelapampa Posted messages 153 Registration date   Status Member Last intervention   7
     
    Thank you, I tried, the Save as works well, but at the line
    With ActiveWorkbook.VBProject, I get the error message
    Run-time error '1004'
    The 'VBProject' method for the '_Workbook' object failed
    This can be translated as:
    Error '1004'
    The 'VBProject' method for the '_Workbook' object has failed

    Any idea?
    1
    1. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
       
      Try the solution from MichDenis
      Menu / Tools / Macros / Security / Trusted Editors /
      both checkboxes must be checked.
      0
    2. Elboubdelapampa Posted messages 153 Registration date   Status Member Last intervention   7
       
      It was that: only one was activated.
      Thank you for the time you took, I had been working on the problem for a while.
      0
    3. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
       
      I just did two Google searches for you... So basically it only took me a few seconds. The hardest part was finding answers that would satisfy you and not just the first link that came up.
      Good luck with the rest and don't hesitate to come back.
      0
    4. jojo
       
      Good evening forum! I need help!
      I want to use the code above but I'm struggling;
      I want to create a copy of my workbook and save it in a directory (under a different name) while removing the macros; without closing my working workbook. The destination workbook should be closed for another person. In short, I want to save my workbook without the macros.
      1
  2. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
     
    Hello,
    Maybe a solution here from Frédéric Sigonneau, Laurent Longre, just that!!!
    The code: 
    sub SaveAsWithoutMacros() Dim NomSource$, CheminDest$, NomDest$ Dim VBC As Object NomSource = "EssaiSaveAs.xls" 'to adapt CheminDest = "C:\WindowsTemp" 'to adapt NomDest = "Essai.xls" 'to adapt Workbooks(NomSource).SaveAs CheminDest & NomDest With ActiveWorkbook.VBProject 'this part of the code is from Laurent Longre For Each VBC In .VBComponents If VBC.Type = 100 Then With VBC.CodeModule .DeleteLines 1, .CountOfLines .CodePane.Window.Close End With Else: .VBComponents.Remove VBC End If Next VBC 'LL End With Application.Quit SendKeys "%O" end sub

    --
    Best regards,
    -- Every problem has its solution. If there is no solution, where is the problem? --
    0