I went a little crazy with the variables but it was to make this as clear as possible. The debug lines will help you figure out what is happening if you need to step through the code. IntFileCounterIndex = intFileCounterIndex + 1ĭebug.Print "Free file name: " & strFileName
#VBA PRINT SELECTION FREE#
' Increase the counter as we have not found a free file yet. ' This file path does not currently exist. StrTestPath = strSaveDirectory & strFileBaseName & ".pdf" ' Build test path base just on base name to see if it exists. StrTestPath = strSaveDirectory & strFileBaseName & Trim(Str(intFileCounterIndex)) & ".pdf" ' Build test path base on current counter exists. StrFileBaseName = Trim(rngNamedCell.Value)ĭebug.Print "File Name will contain: " & strFileBaseName StrSaveDirectory = Environ("USERPROFILE") & "\Desktop\"ĭebug.Print "Saving to: " & strSaveDirectory with filename = GetFileName(Range("A1")) Function GetFileName(rngNamedCell As Range) As Stringĭim strSaveDirectory As String: strSaveDirectory = ""ĭim strFileName As String: strFileName = ""ĭim strTestPath As String: strTestPath = ""ĭim strFileBaseName As String: strFileBaseName = ""ĭim strFilePath As String: strFilePath = ""ĭim intFileCounterIndex As Integer: intFileCounterIndex = 1
#VBA PRINT SELECTION CODE#
In your code you could just replace the lines filename = Application. If that already exists then try AwesomeData2.pdf and so on. If the cell contains "AwesomeData" then we will try and create a file in the current users desktop called AwesomeData.pdf. The value of that cell will be the base file name. Use the comments in the code to help understand what is happening. Hopefully this is self explanatory enough. Worksheets("RentalCalcs").Range("A1:N24").Select Worksheets("Rental").Range("A1:N24").Selectįilenamerentalcalcs = Path & "\" & Sheets("RentalCalcs").Range("O1") 'ThisWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select Path = CreateObject("WScript.Shell").specialfolders("Desktop")įilenamerental = Path & "\" & Sheets("Rental").Range("O1") I have linked the code to a commandbutton on the Rental Sheet - Private Sub CommandButton1_Click() I have changed the code and references and it now works. Worksheets("RentalCalcs").Range("A1:N24").ExportAsFixedFormat Type:=xlTypePDF, _ Worksheets("Rental").Range("A1:N24").ExportAsFixedFormat Type:=xlTypePDF, _ Title:="Select Path and Filename to save") Here is the code I have so far: Sub PrintRentalForm()įilename = Application.GetSaveAsFilename(InitialFileName:="", _įileFilter:="PDF Files (*.pdf), *.pdf", _ 2, 3 in the filename based on the copy number?**
#VBA PRINT SELECTION PDF#
If multiple copies of the same sheet are saved/printed to PDF can each copy have a number eg.Is there a way that the PDF file can create a folder on the users desktop and save the file with a file name based on specific cells in the sheet?.There are two things I want to do though: I have a code that prints a selected area in a worksheet to PDF and allows user to select folder and input file name.
0 kommentar(er)
