Hi guys,
I have a problem when I run this script. don't know what the problem. Debugger gives me Run-time error 1004 and when I stop debugger excel crashes.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ActSheet As Worksheet
Dim ActBook As Workbook
Dim CurrentFile As String
Dim NewFileType As String
Dim NewFile As String
Dim pdfName As String
Dim i As Integer, j As Integer
If SaveAsUI Then Cancel = True
Application.ScreenUpdating = False ' Prevents screen refreshing.
Application.DisplayAlerts = False
CurrentFile = ThisWorkbook.FullName
NewFileType = "Excel Files 2007 (*.xlsx), *.xlsx," & _
"Excel Files 1997-2003 (*.xls), *.xls," & _
"All files (*.*), *.*"
NewFile = Application.GetSaveAsFilename( _
InitialFileName:=ThisWorkbook.Path & "\" & ActiveSheet.Range("F6") & " Invoice " & ActiveSheet.Range("B11"), _
fileFilter:=NewFileType)
If NewFile <> "" And NewFile <> "False" Then
ActiveWorkbook.SaveAs FileName:=NewFile, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
pdfName = ThisWorkbook.Path & "\PDF Archive\" & ActiveSheet.Range("F6") & " Invoice " & ActiveSheet.Range("B11")
If Dir(pdfName & ".pdf") <> "" Then
If Dir(pdfName & " copy.pdf") <> "" Then
i = 1
j = 1
Do While i = 1
If Dir(pdfName & " copy (" & CStr(j) & ").pdf") <> "" Then
j = j + 1
i = 1
Else
i = 2
End If
Loop
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
pdfName & " copy (" & CStr(j) & ").pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
End If
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
pdfName & " copy" & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
End If
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
pdfName & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Set ActBook = ActiveWorkbook
Workbooks.Open CurrentFile
ActBook.Close
End If
Application.DisplayAlerts = False
End Sub
Highlighted code is
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
pdfName & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
-> OpenAfterPublish:=False
Can you help, please? truly don't know what the problem tnx
Bookmarks