-1

newbie to VBA but have done some research and cannot find the answer to my problem anywhere. I have a Print to PDF sub in a module in my accounts program and, great it works when I create and save a 'Sales Invoice' sheet but when I activate the 'Part Exchange' sheet and save that it fails. The problem seems to be with the pathname and invoice number creation as I have included a message box to try and debug but the box is empty when I Step Through the Part Exchange Save Code. On the Worksheets I have a formula in cell B4 (=Cell("Filename") which I use to test which user is using the workbook. I do get a problem with this formula not updating when I change sheets sometimes.

Sorry but I cannot attach my workbook as it is private but I can attach a couple of pictures of the test worksheets

Sorry this is so long but I am really getting frustrated. A little knowledge is a dangerous thing!!!

ANY advice apart from 'Give Up' would be greatly appreciated.

Sales Invoice Sheet

1

Part Exchange Sheet

2

Sub PrintPDF()

   Call Save_PDF
End Sub


Function Save_PDF() As Boolean
    Dim PathName As String
    Dim UserName As String
    Dim Inv_Number As Integer
    Dim svas As String
'Find the user name from Active sheet filename
    UserName = Range("B4") 'Gets the Cell("Filename") from the active sheet
    MsgBox Range("B4")
    
' Choose File Save Name from 2 options of different user accounts
   If InStr(UserName, "C:\Users\Martyn") Then
       PathName = Range("B3")       'User Martyns path to either Sales Invoice folder or Purchase
                                    'Invoice folder, depending on the sheet
    Else
        PathName = Range("B2")      'User Adams path as above
    End If
    
     Inv_Number = Range("H8")       'Gets the Invoice Number
     svas = PathName & "\" & Inv_Number & ".pdf" 'Full path to save PDF Both sheets save paths are correct
       
    MsgBox Dir(svas) 'Shows what the save path is this is fine in Sales Invoice but blank in Part Exchange 
        
    If Dir(svas) <> vbNullString Then 'Checks if the File is existing THIS IS A PROBLEM ON PART EXCHANGE
        If MsgBox("There is already a file of that name, do you wish to replace it?", vbYesNo, "Warning") = vbNo Then GoTo EndMacro
    End If
    
    
' Show PDF
    On Error GoTo OpenPDFError 'Always defaults to this line and debug goes to the following line when saving PART ;
'EXCHANGE SHEET
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=svas, Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=True
    On Error GoTo 0
    
Save_File:
    MsgBox "A copy of this sheet has been successfully saved as a  .pdf  file: " & vbCrLf & vbCrLf & svas & _
       vbCrLf & "Please Review the .pdf document. If the document does NOT look good, adjust your printing parameters, and try again."
        
    Save_PDF = True
    GoTo EndMacro
    
OpenPDFError:
    MsgBox "Unable to save, there maybe an existing copy of invoice on file or open." & vbCrLf & "If you wish to" & _
    "overwrite this invoice then, please delet existing copy or close your PDF reader and try again", , "WARNING"
    
    Save_PDF = False
    
    
EndMacro:
End Function

Thanks Martyn

vimuth
  • 5,064
  • 33
  • 79
  • 116
Martyn
  • 1
  • 1
  • You really should stop relying on implicit ActiveSheet. [See here for some hints on how to do that](https://stackoverflow.com/q/10714251/445425) – chris neilsen Mar 09 '23 at 02:42
  • Thank you for the reply but I cannot see from your link how to determine which sheet the code is running in if I do not use ActiveSheet. As I'm new to VBA I am struggling to understand the code in your link. – Martyn Mar 09 '23 at 17:24

1 Answers1

0

The problem was related to a mistake I made.
The file address at B3 in the Part Exchange worksheet was typed incorrectly. Instead of copying and pasting the address from the address bar in Explorer I typed it in and missed the "20" from "2022-23".

Synapsis
  • 667
  • 1
  • 5
  • 19
Martyn
  • 1
  • 1