Opening pdf now shows message box

[ipbr21054]

I am using the supplied code below.

When i run the code i now see the message shown as per screen shot supplied.
The pdf is saved & i see no other messages or errors so not sure why this message is being shown.

Adobe Reader is used.
I go to the path as mentioned & the saved pdf is there & i am able to open it no problem.

Anybody have info on this please or do you see an error in the code.

Thanks

Private Sub CommandButton1_Click()
  Dim sPath As String
  Dim strFileName As String
  Dim sh As Worksheet
  
  If TextBox1 = "" Then
  MsgBox "YOU DID NOT ENTER A CUSTOMERS NAME", vbCritical, "NO NAME ENTERED ON SHEET"
  TextBox1.SetFocus
  Exit Sub
    
  End If
  
  With ThisWorkbook.Worksheets("PRINT LABELS")
    .Range("B3") = Me.TextBox1.Text ' ENTERS CUSTOMERS NAME TO WORKSHEET
    .Range("E3") = Format(DateSerial(CLng(Me.cboYear.Value), Me.cboMonth.ListIndex + 1, Me.cboDay.Value), "long date")
  End With
  Unload PrinterForm
                                                                             
  With ActiveSheet
    If .Range("AB1") = "" Then
      MsgBox "NO CODE SHOWN TO GENERATE PDF", vbCritical, "NO CODE ON SHEET TO CREATE PDF"
      Exit Sub
    End If
    strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\" & .Range("B3").Value & " " & Format(.Range("E3").Value, "dd-mm-yyyy") & ".pdf"
    .Range("A1:K23").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
    MsgBox "PDF HAS NOW BEEN GENERATED", vbInformation + vbOKOnly, "GENERATE PDF FILE MESSAGE"
    
  End With
  
        sPath = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\"
        strFileName = sPath & Range("B3").Value & " " & Format(Range("E3").Value, "dd-mm-yyyy") & ".pdf"

  If Dir(strFileName) <> vbNullString Then
    ActiveWorkbook.FollowHyperlink strFileName
    Else
    MsgBox ("not found")
  End If
    
 
End Sub

 

[ipbr21054]

Just an update as ive noticed something.
When i first run the code above and save file as HUGH CLEGG 123 it saves fine & i can open pdf & read it.
I then do the same process & save as HUGH CLEGG 456 i now see that the 123 file has been replaced with the 456 file.
Obviously this cant / shouldnt happen

 

[ipbr21054]

New updated code.
This fixes the overwrite issue.

Pdf does not print & i now see the Msgbox NOT FOUND

Private Sub CommandButton1_Click()
  Dim sPath As String
  Dim strFileName As String
  Dim sh As Worksheet
  
  If TextBox1 = "" Then
  MsgBox "YOU DID NOT ENTER A CUSTOMERS NAME", vbCritical, "NO NAME ENTERED ON SHEET"
  TextBox1.SetFocus
  Exit Sub
    
  End If
  
  With ThisWorkbook.Worksheets("PRINT LABELS")
    .Range("B3") = Me.TextBox1.Text ' ENTERS CUSTOMERS NAME TO WORKSHEET
    .Range("E3") = Format(DateSerial(CLng(Me.cboYear.Value), Me.cboMonth.ListIndex + 1, Me.cboDay.Value), "long date")
  End With
  Unload PrinterForm
                                                                             
  With ActiveSheet
    If .Range("AB1") = "" Then
      MsgBox "NO CODE SHOWN TO GENERATE PDF", vbCritical, "NO CODE ON SHEET TO CREATE PDF"
      Exit Sub
    End If
    strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\" & .Range("B3").Value & " " & .Range("AB1").Value & " " & Format(.Range("E3").Value, "dd-mm-yyyy") & ".pdf"
    .Range("A1:K23").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
    MsgBox "PDF HAS NOW BEEN GENERATED", vbInformation + vbOKOnly, "GENERATE PDF FILE MESSAGE"
    
  End With
  
        sPath = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\"
        strFileName = sPath & Range("B3").Value & Range("AB1").Value & " " & Format(Range("E3").Value, "dd-mm-yyyy") & ".pdf"


      If Dir(strFileName) <> vbNullString Then
    ActiveWorkbook.FollowHyperlink strFileName
    Else
    MsgBox ("NOT FOUND")
  End If

 
End Sub

 

[RoryA]

Remove these two lines:

        sPath = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\"
        strFileName = sPath & Range("B3").Value & Range("AB1").Value & " " & Format(Range("E3").Value, "dd-mm-yyyy") & ".pdf"

 

[ipbr21054]

Morning,

I have delete as advised.

I still see the message about pdf
This line is yellow when i debug, no file is printed

ActiveWorkbook.FollowHyperlink strFileName

 

[RoryA]

That makes no sense. That line won't be running unless the file exists, since Dir checked for it.

 

[ipbr21054]

Strange but it happens.

A question for you.
This piece of the code, tell me is it trying to open the pdf to look at it OR open to then print ?

      If Dir(strFileName) <> vbNullString Then
    ActiveWorkbook.FollowHyperlink strFileName
    Else
    MsgBox ("NOT FOUND")
  End If

I ask becuse the file is generated fine & saved fine.
I dont then need to view it so just print the pdf in question

 

[RoryA]

That will only open it. It has nothing to do with printing at all. If all you want is a printout, why don't you just print the sheet and skip the whole pdf creation?

 

[ipbr21054]

Ok then i have no reason to open it.

So as everything works then i only need to now print the file just save.
Some info if it helps at all.

Worksheet is called PRINT LABELS
Cell B3 is customers name.
Cell AB1 is part number.
Cell E3 is todays date.

Wi the above info the file is saved like so.
IAN PARSONS 2FF30C 17-05-2023.pdf

The path for this folder where all the pdf are saved is as follows.
C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF

Can we do something to then replace this below with a printable file code please as all works apart from now printing the pdf in question

      If Dir(strFileName) <> vbNullString Then
    ActiveWorkbook.FollowHyperlink strFileName
    Else
    MsgBox ("NOT FOUND")
  End If

 

[RoryA]

Why do you need the PDF at all if all you want is a printout?