Stop VBA code running on copy of workbook...

[Stu Dapples]

I have created a workbook which contains all the code I need to take figures, do calcs, generally make my quoting life easy - even to the point it will save a copy of the workbook to a specified directory and use the individual quote number as it's filename... Happy Days!

Unfortunately, on the file that is generated, when it is opened, it runs the "clear sheets" script I wrote and bins all the info

So, I used the code here to set the Save As and it works fine, I assumed that by setting the filetype I could use "xlWorkbookNormal" to stop the code...

I have had it working but now it simply saves the workbook as a "file" which I can't open!

Can anyone tidy up my syntax to get it save to the path, with the filename as set in the string but in a format which will prevent the macros running (or get rid of them completely) in the the copy version?

Private Sub EndOfQuote()
Dim fPath As String, fName As String

Sheet9.Activate


  fPath = "\\svwa-fs1\orwin_man\Change Management\2019 Projects\UK Service Centre\Quotes\"
  fName = Sheet1.Range("B1").Value
 
  ActiveWorkbook.SaveCopyAs (fPath & fName)


End Sub

 

[DanteAmor]

Try this

Sub EndOfQuote()
  Dim fPath As String, fName As String
 
  Application.DisplayAlerts = False
  Application.ScreenUpdating = False
 
  fPath = "\\svwa-fs1\orwin_man\Change Management\2019 Projects\UK Service Centre\Quotes\"
  fName = Sheet1.Range("B1").Value & ".xlsx"
  Sheets.Copy
  ActiveWorkbook.SaveAs fPath & fName, FileFormat:=xlOpenXMLWorkbook
  ActiveWorkbook.Close False
End Sub

 

[dmt32]

Hi,

With SaveCopyAs you cannot change the file format but one way maybe would be to use SaveCopyAs to make the copy & then open that file & use SaveAs to change to required file format

Untested but maybe something like following

Private Sub EndOfQuote()
    Dim fPath As String, fName As String
    Dim WkBk As Workbook

'51 = xlOpenXMLWorkbook (without macro's in 2007 >, xlsx)
'52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007, xlsm)


    With Application
        .ScreenUpdating = False: .EnableEvents = False: .DisplayAlerts = False
    End With

  fPath = "\\svwa-fs1\orwin_man\Change Management\2019 Projects\UK Service Centre\Quotes\"
  fName = Sheet1.Range("B1").Value

    ActiveWorkbook.SaveCopyAs (fPath & fName)
  
    Set WkBk = Workbooks.Open(fPath & fName, False, False)

    With WkBk
        .SaveAs fPath & fName, xlOpenXMLWorkbook
        .Close False
     End With
   
     With Application
        .ScreenUpdating = True: .EnableEvents = True: .DisplayAlerts = True
    End With

End Sub

Suggestion should preserve the original file remaining open but may need some adjustment but hopefully, give you an idea to resolve your issue

Dave

 

[Stu Dapples]

Try this

Sub EndOfQuote()
  Dim fPath As String, fName As String

  Application.DisplayAlerts = False
  Application.ScreenUpdating = False

  fPath = "\\svwa-fs1\orwin_man\Change Management\2019 Projects\UK Service Centre\Quotes\"
  fName = Sheet1.Range("B1").Value & ".xlsx"
  Sheets.Copy
  ActiveWorkbook.SaveAs fPath & fName, FileFormat:=xlOpenXMLWorkbook
  ActiveWorkbook.Close False
End Sub

Perfect! Many thanks for taking the time

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.