Macro PDF With Variable For Username And Cell Text FileName

[SandidgeHayLLC]

Hello, I found a macro online which almost worked as I was needing. But I haven't been able to change it to
save where I need or with the cell info as the filename. I'm being bluntly honest this language is foreign
to me and while i have tried to understand it and make it do what I need the results have been terrible. 
Goals: variable for windows username as part of save location <even possible?
Cell value as part of save file name when export as pdf macro pressed.
My goal is this --> c:\users\*the username for windows*\folder1\folder2\folder3\folder4\folder5\*folder name matching contents of cell t4*\summary sheet.pdf"

The macro code included below.
The file attached *plan to save macro as part of file instead of on each pc

Code: Select all

REM  *****  BASIC  *****

Sub Main

End Sub


sub export_pdf
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$A$1:$AB$52"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
dim args2(2) as new com.sun.star.beans.PropertyValue
  Dim Cel        As Object
  Dim Werkblad    As Object
  Dim Werkmap    As Object
  
  Werkmap=ThisComponent
  Werkblad=Werkmap.Sheets.getByName("SUMMARY")
  Cel=Werkblad.getCellByposition(19,3) 'T4


args2(0).Name = "URL"
args2(0).Value = "file:///C:/***The Windows Username***/***SubDir1***/***SubDir2***/***SubDir3***/**SUBDIR4**/**SUBDIR5***/"& Cel.getString(T4)/"Summary Sheet"&".pdf"
args2(1).Name = "FilterName"
args2(1).Value = "calc_pdf_Export"
args2(2).Name = "FilterData"
args2(2).Value = Array(Array("UseLosslessCompression",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Quality",0,90,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ReduceImageResolution",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("MaxImageResolution",0,300,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("UseTaggedPDF",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("SelectPdfVersion",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ExportNotes",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ExportBookmarks",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("OpenBookmarkLevels",0,-1,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("UseTransitionEffects",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("IsSkipEmptyPages",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("IsAddStream",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("FormsType",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ExportFormFields",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("HideViewerToolbar",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("HideViewerMenubar",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("HideViewerWindowControls",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ResizeWindowToInitialPage",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("CenterWindow",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("OpenInFullScreenMode",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("DisplayPDFDocumentTitle",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("InitialView",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Magnification",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Zoom",0,100,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("PageLayout",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("FirstPageOnLeft",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("InitialPage",0,1,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Printing",0,2,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Changes",0,4,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("EnableCopyingOfContent",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("EnableTextAccessForAccessibilityTools",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ExportLinksRelativeFsys",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("PDFViewSelection",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ConvertOOoTargetToPDFTarget",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ExportBookmarksToPDFDestination",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("_OkButtonString",0,"",com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("EncryptFile",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("DocumentOpenPassword",0,"",com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("RestrictPermissions",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("PermissionPassword",0,"",com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Selection",0,,com.sun.star.beans.PropertyState.DIRECT_VALUE))

dispatcher.executeDispatch(document, ".uno:ExportToPDF", "", 0, args2())


end sub

[RoryOF]

Please do not capitalise every letter - it makes your posting nearly impossible to read. I have corrected your post to proper sentence case.

[SandidgeHayLLC]

Please do not capitalise every letter - it makes your posting nearly impossible to read. I have corrected your post to proper sentence case.

Well, I am sorry you do not like the way I normally always type. I actually type much quicker when doing that and I read it much easier also.
Don't worry I also write by hand the same way. Sorry to be such a bother to you.

[musikai]

deleted

[musikai]

ok, the line could like this

Code: Select all

args2(0).Value = converttourl("file:///C:/Users/" & environ("USERNAME") & "/Google Drive/Driver SYNC Files/Truck-19/Load Paperwork/" & Cel.getString() & "/Billing Sheet"&".pdf")

It will work, if the Computer has a C drive.

Attached is your file with this modified line. (It already had the code embedded in the file, so it should work when you press the yellow button.)

[Villeroy]

It will work, if the Computer has a C drive.

It should not work. You have to decide if you convertToURL("C:\path with spaces\and backslashes\blah.ods") a normal system path or not. file: is the protocol part of an URL. Anything starting with file: and with slashes instead of backslashes is an URL already.

[musikai]

Thanks! It works but this nicer and correct:

Code: Select all

args2(0).Value = converttourl("C:\Users\" & environ("USERNAME") & "\Google Drive\Driver SYNC Files\Truck-19\Load Paperwork\" & Cel.getString() & "\Billing Sheet"&".pdf")