Sep 25 2023 09:32 AM
Sep 25 2023 09:32 AM
I have a worksheet that is set out as a report - users pick a customer from a drop down list and the rest of the report is populated based on cell contents on many other worksheets.
I am trying to export these cells to a .pdf file so that they can be shared with stakeholders and I've got the vba working when the workbook 'lives' in my C: drive, but its a team tool, and therefore, 'lives' on Microsoft Teams. I'm using the VBA:
'Create and assign variables
Dim ws As Worksheet
Dim rng As Range
Set ws = Sheets("Sheet4")
Set rng = ws.Range("b2:ai38")
'Save a range as PDF
rng.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=Range("b2") & " Report" & "_" & Format(Now(), "yyyymmdd_hhmmss") & ".pdf"
and when the workbook is on my C drive, when I run the report, it creates a .pdf with the filename of the customer and a timestamp in the same location that the workbook resides.
When I upload it to Teams, I get a 400 error as I assume, it doesn't know where to save the file.
What can I add to this VBA so that it brings up the SaveAs box, ideally pre-populated with the filename and .pdf extension. I've tried using
Application.getsaveasfilename InitialFilename:=Range("b2") & " Report" & "_" & Format(Now(), "yyyymmdd_hhmmss") & ".pdf"
but not having any luck at all. Any help would be gratefully received.
Thanks in advance,
Sep 25 2023 04:41 PMSolution
The Application.GetSaveAsFilename method is intended to return a value, but your syntax cannot receive a value. It makes me wonder what you are expecting the method to do.
Use syntax that captures a value, such as:
Dim vntFileSpec As Variant ' vntFileSpec = Application.GetSaveAsFilename(InitialFileName:=Range("b2") _ & " Report" & "_" & Format(Now(), "yyyymmdd_hhmmss") & ".pdf") If vntFileSpec = False Then '...the user decided not to save. Exit the procedure or do ' whatever else is appropriate. Else 'Use vntFileSpec in your <workbook>.SaveAs or <worksheet>.SaveAs method 'or <range>.ExportAsFixedFormat method or whatever. End If
Sep 26 2023 02:54 AM