save userform as pdf file to Microsoft Teams folder

[Mr_Ragweed2]

Howdy,
I have a userform that i currently can print. I would like to print and save from the "print button" on the form. I'm assuming it would save as a pdf but maybe an image is easier? Doesn't matter to me. I would like to save it to a specific folder in Microsoft Teams.
my code for printing is below. (It is not specific to my printer as it allows many users to print to their default printer.)

If Application.Dialogs(xlDialogPrinterSetup).Show Then
    UserForm1_MyUserformName.PrintForm
    Else
    Exit Sub
End If

I have a cell to reference for giving a unique name to each file: Sheet("Special Sheet").Range("D1")
All users would be saving to the same file:
"https://blah blah.sharepoint.com/:f:?r/teams/blah blah/Shared%20Documents/General/Blah%20Apps/abc%20Orders?csf=1&web=1&e=xW6Z7s"

Any help is greatly appreciated. Thank you very much!

 

[Mr_Ragweed2]

Update. Sorry for any ambiguity. I am not looking for someone to just do this for me. I hope i didn't imply that. I am trying to find my own solution.
Here is the code i am trying so far, it is adapted from a code that saves a workbook as a pdf to a sharepoint teams folder. Again, i am trying to save a screenshot (i guess) of my userform as a pdf (i guess) to a sharepoint teams file.

Dim strPath As String, strFName As String
Dim currDate As String, fullPath As String

strPath = "https://blah blah.com/:f:/r/teams/blah blah/Shared%20Documents/General/blah%20Apps/blah%20Orders?csf=1&web=1&e=xW6Z7s"
strFName = Sheets("Combined data").Range("D1").Value
strFName = Left(strFName, InStrRev(strFName, ".") & -1 & "-")
currDate = Format(Now, "dd-mm-yyy-hhmmss")
fullPath = strPath & strFName & currDate & ".pdf"

UserForm1_MyNameOfForm.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fullPath, Quality:=xlQualityStandard, _
    IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False

I am getting an "method or data member not found" error on the last line. "ExportAs FixedFormat" is highlighted with the error.
I think i'm getting close?

 

[Mr_Ragweed2]

I am getting nowhere with my own efforts. I am begging for assistance. Every version of code i find does not seem to adapt and i cant help but think the issue is much smaller than i am making it.

 

[John_w]

I am getting an "method or data member not found" error on the last line. "ExportAs FixedFormat" is highlighted with the error.

Because ExportAsFixedFormat isn't a userform property.

You have to Alt+PrintScreen to copy the active window to the clipboard, paste it to a worksheet and save that as a PDF. This is done by following userform CommandButton1 control:

Private Sub CommandButton1_Click()

    Dim PDFfile As String
    Dim tempSheet As Worksheet
    
    PDFfile = ThisWorkbook.Path & "\Userform.pdf"
    
    Application.SendKeys "(%{1068})"
    DoEvents
    
    Set tempSheet = Worksheets.Add
    With tempSheet
        .Range("A1").PasteSpecial
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        Application.DisplayAlerts = False
        .Delete
        Application.DisplayAlerts = True
    End With
    
    MsgBox "Created " & PDFfile, vbInformation

End Sub

 

[Mr_Ragweed2]

A lot to unpack here.
I am sometimes getting a "paste special method of range class failed" error on the line below. And "sometimes" getting the error doesn't make sense 'cause it should either work every time or error every time - i know. I have tried troubleshooting this part but can't find any constant. I check the affected sheets on the workbook and manually reset them - ie making sure to delete added sheets when code errors out early.

.Range("A1").PasteSpecial

But it does save a copy to my desktop. Actually a shortcut to my desktop on One Drive. When i copy that link, it is different from the address i hard coded to save it to.
Also it does not give it the name i specified. Here is the code:

Dim strPath As String, strFName As String
Dim currDate As String, fullPath As String

strPath = "https://mycompany.sharepoint.com/:f:/r/teams/MyDeptFolder/Shared%20Documents/General/Sales%20Apps/Seed%20Orders/"
strFName = Sheets("Combined data").Range("D1").Value
currDate = Format(Now, "dd-mm-yyy-hhmmss")
fullPath = strPath & strFName & currDate & ".pdf"

'Sheets("Combined data").ExportAsFixedFormat Type:=xlTypePDF, Filename:=fullPath, Quality:=xlQualityStandard, _
'    IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False
    
   Dim PDFfile As String
    Dim tempSheet As Worksheet
    
    PDFfile = ThisWorkbook.Path & "\Userform1MasterSeedOrderForm.pdf"
    
    Application.SendKeys "(%{1068})"
    DoEvents
    
    Set tempSheet = Worksheets.Add
    With tempSheet
        .Range("A1").PasteSpecial
        .ExportAsFixedFormat Type:=xlTypePDF, fileName:=PDFfile, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        Application.DisplayAlerts = False
        .Delete
        Application.DisplayAlerts = True
    End With
    
    MsgBox "Created " & PDFfile, vbInformation

 

[Mr_Ragweed2]

another issue. The userform i am saving has an image that has been loaded onto it from a spreadsheet. When i run this line:

IgnorePrintAreas:=False

i get the userform only. When i change it to TRUE, i get the image only.
Also, regardless of which version i use, the pdf does not fir on 1 page. When i print it, it fits easily. The dimensions of the userform are Height 495 and Width 595. The dimensions of the image window are Height 342 and width 708. Not sure how the image fits, but it does very nicely. The zoom of the userform is 80.

 

[John_w]

I am sometimes getting a "paste special method of range class failed" error on the line below.

Try replacing it with:

.Activate
DoEvents
.Paste

You might need a short delay before the Paste:

Application.Wait DateAdd("s", 1, Now)

Also it does not give it the name i specified.

The code is specifying fileName:=PDFfile. Change it to your variable, i.e. fileName:=fullPath and change the MsgBox line accordingly.

The userform i am saving has an image that has been loaded onto it from a spreadsheet. When i run this line:

IgnorePrintAreas:=False

i get the userform only. When i change it to TRUE, i get the image only.

Sorry, I don't know about that. The SendKeys should be copying the whole userform to the Clipboard as a single image.

Also, regardless of which version i use, the pdf does not fir on 1 page.

With the image (shape) of the userform on the temporary sheet, experiment with reducing its size (record a macro) or setting the Page Layout margins (record a macro) and incorporate the code into the macro.

 

[Mr_Ragweed2]

Thank you for your response. I made the changes above. See below:

Dim PDFfile As String
    Dim tempSheet As Worksheet
   
    'PDFfile = ThisWorkbook.Path & "\Userform1_MasterSeedOrderForm.pdf"
    PDFfile = ThisWorkbook.Path & "\haha.pdf"
    'PDFfile = fullPath
    Application.SendKeys "(%{1068})"
    DoEvents
    
    Set tempSheet = Worksheets.Add
    With tempSheet
        '.Range("A1").PasteSpecial
        .Activate
        DoEvents
        Application.Wait DateAdd("s", 1, Now)
        .PasteSpecial
        .ExportAsFixedFormat Type:=xlTypePDF, fileName:=fullPath, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        Application.DisplayAlerts = False
        .Delete
        Application.DisplayAlerts = True
    End With
    
    MsgBox "Created " & fullPath, vbInformation

Now i am getting a "Document not saved. The document may be open, or an error may have been encountered when saving" error... on this line:

.ExportAsFixedFormat Type:=xlTypePDF, fileName:=PDFfile, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

(Havent tried to work on the true/false thing yet or the size issue. Totally on board with the recoding macro to resize.)

 

[Mr_Ragweed2]

the size issue is puzzling. Sometimes the capture does include the image, but usually not. Sometimes it's an entire screen shot of my monitor, usually it's not. Sometimes the size is really big, and sometimes not.
I have closed and reopened the workbook multiple times to try and get the same result but rarely are the results the same.
I did record a macro on size adjustment and using those factors/percentages is how i discovered the inconsistency.

 

[Mr_Ragweed2]

I will probably post the sizing issues as a separate thread once we figure out how to capture the userform as a pdf and get to save at sharepoint/teams successfully.