PDF's won't open in Sharepoint/Teams but will in C:drive Documents folder

[Mr_Ragweed2]

Hello all and Happy Upcoming New Year. Trying to wrap up a project that has generated a few threads. Thank you to all who have helped me along the way. I have a macro that takes .xlxs files from a desktop folder from a user and saves them as pdf's to specific sharepoint folders on Teams. My issue is that the pdf's in Teams won't open. If i go to the Documents folder on the C;Drive the pdfs are also there (somehow) and open just fine. My code is below.

Option Explicit

Private Sub CommandButton1_Click()

'ZSync Form

    Dim sourceFolderPath As String
    Dim oldName As String
    Dim newName As String
    Dim wb As Workbook
    Dim wb2 As Workbook
    
    Dim FSO As Object
    Dim SourceFolder As Object
    Dim File As Object
    
    Application.ScreenUpdating = False
    
    Dim MyName As String
    MyName = Environ$("Username")
        
    sourceFolderPath = "C:\Users\" & MyName & "\OneDrive - Mycompany Inc\Desktop\Propane Forms"
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set SourceFolder = FSO.GetFolder(sourceFolderPath)
    
    '12-26 12:30am this mostly works. files go to correct folders as pdfs and are deleted. "ZSync" is left alone.
    ' problem is that the pdfs wont open in Teams but will open on the C:drive....
    'maybe leave as .xlsx files on Teams to edit?
    
    On Error Resume Next
    
    For Each File In SourceFolder.Files
    
        oldName = File.Name
    
    newName = Left(oldName, Len(oldName) - 5)
    
    If oldName Like "*ZSync*.xlsm" Then
    Exit Sub
    
    ElseIf oldName Like "*SWO*" & ".xlsx" Then
        Set wb2 = Workbooks.Open(File)
        Range("A5").Select
        ActiveSheet.SaveAs fileName:= _
            "https://abcd.sharepoint.com/teams/mycompany/Shared%20Documents/General/Crystal,%20Mary,%20Anna/Propane%20Forms/Propane%20Service%20Work%20Orders" _
             & "/" & newName & ".pdf"
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF
        ActiveWindow.Close
        Kill "C:\Users\" & MyName & "\OneDrive - mycompany\Desktop\Propane Forms\" & File.Name
        
        ElseIf oldName Like "*TMS*" & ".xlsx" Then
        Set wb2 = Workbooks.Open(File)
        wb2.SaveAs fileName:= _
            "https://abcd.sharepoint.com/teams/mycompany/Shared%20Documents/General/Crystal,%20Mary,%20Anna/Propane%20Forms/Tank%20Movement%20Sheet/" _
            & newName & ".pdf"
            wb2.ExportAsFixedFormat Type:=xlTypePDF
        ActiveWindow.Close
        Kill "C:\Users\" & MyName & "\OneDrive - mycompany Inc\Desktop\Propane Forms\" & File.Name
        
    End If
 
    Next File
    
    Set SourceFolder = Nothing
    Set FSO = Nothing
    
End Sub

Based on every example i have found in this forum and others, it looks like my syntax should be correct. I have used code before that converts a .xlsx file to a pdf but both locations were on the C:Drive. The difference in that code is i was using ChDir. Ex below;



Dim MyName As String
MyName = Environ$("Username")

ChDir "C:\Users\" & MyName & "\OneDrive - Mycompany Inc\Desktop\Propane Forms"

    ActiveWorkbook.SaveAs Filename:= _
       "C:\Users\" & MyName & "\OneDrive - Mycompany Inc\Desktop\Propane Forms" & "\" & newfile & ".pdf"
'etc

I'm pulling my hair out. I even tried jpg format with exact same results as i first mentioned. Any help here is greatly appreciated. Thanks!

 

[Mr_Ragweed2]

I found a solution. I'm not smart enough to actually explain it, but what i think happened is that i hadn't included all the document properties AND my order of operations may have been wrong. In the above code i used "SaveAs" before i used "ExportAsFixedFormat". In most examples i found, the order was reversed. I had tried reversing the operation alone (but without adding the document properties.) Also, i had tried adding the document properties piece at the end of the original version but that alone did not work. It took doing both things at the same time to make it go. Whatever the reason, the new code below works for me.

So basically this:

ActiveSheet.SaveAs fileName:= _
            "https://abcd.sharepoint.com/teams/mycompany/Shared%20Documents/General/Crystal,%20Mary,%20Anna/Propane%20Forms/Propane%20Service%20Work%20Orders" _
             & "/" & newName & ".pdf"
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF

Was changed to this:

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:= _
            "https://abcd.sharepoint.com/teams/my company/Shared%20Documents/General/Crystal,%20Mary,%20Anna/Propane%20Forms/Propane%20Service%20Work%20Orders" _
             & "\" & newName & ".pdf", _
             Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

I would still like to understand this more if anyone cares to add a comment/explanation.
Thanks to all who have read this thread. Hopefully it helps someone else along the way.

 

[JEC]

After ExportAsFixedFormat you need the filename to save it

So below is not enough.

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF

In your updated code you are doing it correctly with the filename included.

 

[Mr_Ragweed2]

Thank you very much! You have helped explain things to me in other threads as well and i really appreciate that.