converting excel sheet into pdf sometimes work sometimes not

dss28

New Member
Joined
Sep 3, 2020
Messages
36
Office Version
  1. 2007
Platform
  1. Windows
I have created a macro based program and in that i am using a code to convert excel sheet into pdf.
The program is now saved on shared folder on the computer network for use by all.
The vba code to convert to pdf works fine from my computer when the .xlsm file was only on my computer and also on the network.
From other shared locations, all program works fine but only this code does not work in that it gives messages that the file is created and saved in the location but actually there is no file saved as pdf.
can someone review the code and guide so that it can work on the shared location / server.
thanks


VBA Code:
Sub PDFCreate_Click()

Dim wsA As Worksheet
Dim wbA As Workbook
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
 
'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
  strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"

strName = wsA.Range("A1").Value _
          & " - " & wsA.Range("A2").Value _
          & " - " & wsA.Range("A3").Value
    

'create default name for savng file
strFile = strName & ".pdf"
strPathFile = strPath & strFile

'export to PDF in current folder
    wsA.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=strFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    'confirmation message with file info
    MsgBox "PDF file has been created: " _
      & vbCrLf _
      & strPathFile

exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not create PDF file"
    Resume exitHandler
End Sub
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
1,969
Try modifying this portion Filename:=strFile to
VBA Code:
Filename:=strPathFile

Bye
 
Solution

dss28

New Member
Joined
Sep 3, 2020
Messages
36
Office Version
  1. 2007
Platform
  1. Windows
thanks a lot
it resolved my issue.
regards,
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
1,969
Good...
Indeed if you specify only the filename, the file will be saved in the "current" path; if you specify path + finename your file will always be in the expected position.

Bye
 

Watch MrExcel Video

Forum statistics

Threads
1,118,800
Messages
5,574,399
Members
412,590
Latest member
Velly
Top