VBA printing to PDF works for me but not colleague

[Frenchfancy]

This is my first post so I apologize if I use the wrong terminology.

Using various posts from this forum amongst others I have set up an order form with an input page and a VBA to generate the order form. Once generated and confirmed there is another VBA which prints the form to paper and saves to PDF as well as collating the order for reference.

When I run this VBA it works just fine, but when my colleague runs it, the pdf opens but doesn't save. We are both working from the same dropbox file, and the PDF is set to save to the same place. We need it to save as we then send the PDF to the supplier and keep the paper copy for our files.

What could be the problem? We have checked the macro options on excel and they are the same on both computers.

 

[6StringJazzer]

When asking for help with a VBA problem, always include the VBA code you are asking about.

there is another VBA which prints the form to paper and saves to PDF as well as collating the order for reference.

Please go to this code, copy this entire Sub, and paste it into a post. Then in the post, select the code and click the "VBA" button in the edit controls to mark it as code.

 

[Frenchfancy]

Here is my code.

Sub Print_B_d_Com()
'
' Print bon de commande and save history
'

'
    Sheets("Bon de commande").Select
    Range("D1:G40").Select
    Selection.PrintOut Copies:=1, Collate:=True
    Sheets("Bilan BdC").Select
    ActiveSheet.ListObjects("Tableau6").Range.AutoFilter Field:=3
    Range("C4:E100").Select
    Sheets("Bon de commande").Select
    Range("D12:F100").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Bilan BdC").Select
    Range("C4").Select
    ActiveSheet.Paste
    ActiveSheet.ListObjects("Tableau6").Range.AutoFilter Field:=3, Criteria1:= _
        "<>"
    Range("A5:E100").Select
    Application.CutCopyMode = False
    Selection.Copy
    Feuil8.Select
    Range("A" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Bon de commande").Select

' print to PDF

With ActiveSheet.PageSetup
   .Orientation = xlPortrait
   .PrintArea = "$D$1:$G$45"
   .Zoom = False
   .FitToPagesTall = False
   .FitToPagesWide = 1
   End With
   
   ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:="commande " & ActiveSheet.Range("G1").Value, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=False, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
    
    Sheets("Bon de commande").Select
    
    
End Sub

 

[6StringJazzer]

The code does not explicitly define a folder to save the PDF.

when my colleague runs it, the pdf opens but doesn't save

If the PDF opens, it was created and saved. It cannot be opened if it could not be saved, because Excel doesn't open it, it simply sends a request to Windows to use the default PDF viewer to open the file in the given location. I expect that the file was saved, but saved in a different location than what you expected.

My suggestion would be to determine where you want to save the file and make that explicit in the export call.

Also I suggest getting rid of all the Select and Active references and qualify references explicitly. It is very difficult to keep track of what is Active at any given moment, and also unreliable as it can be easily changed without identifying every place that is affected. I do not have the ability to test this without your file.

Sub Print_B_d_Com()

' Print bon de commande and save history
    
    Sheets("Bon de commande").Range("D1:G40").PrintOut Copies:=1, Collate:=True
    Sheets("Bilan BdC").ListObjects("Tableau6").Range.AutoFilter Field:=3
    
    Sheets("Bon de commande").Range("D12:F100").Copy Destination:=Sheets("Bilan BdC").Range("C4")
    
    Sheets("Bilan BdC").ListObjects("Tableau6").Range.AutoFilter Field:=3, Criteria1:="<>"
    
    Sheets("Bilan BdC").Range("A5:E100").Copy
    With Feuil8
      .Range("A" & .Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
    
' print to PDF

   With Sheets("Bon de commande").PageSetup
      .Orientation = xlPortrait
      .PrintArea = "$D$1:$G$45"
      .Zoom = False
      .FitToPagesTall = False
      .FitToPagesWide = 1
   End With
   
   Sheets("Bon de commande").ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=ThisWorkbook.Path & "\commande " & Sheets("Bon de commande").Range("G1").Value, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=False, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
    
    Sheets("Bon de commande").Select ' optional, needed only if this sheet is not active when the macro starts
    
End Sub