Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    file naming and printing (2000)

    i have some code here which is suppose to make a file name from some required fields
    select 2 pages and print them selecting a printer and find a folder based on a rangename and save the pdf there
    The 2 problems I am having is
    making the save name?
    I have declared 3 varibles vcompanyname, location, varea. These will all be used to name the file with "Treatement" as the first part of the name so ex.
    "Treatment Comp NW Bluesky.xls"


    and finding the correct folder to save in based on the company name?
    folder location example based on the the above companyname= G:2003NEW2005CompT.Charts & Reports


    Option Explicit

    Sub chartspdf ()
    Dim fso As Object
    Dim fld As Object
    Dim subfld As Object
    Dim strPath As String
    Dim strFile As String
    Dim oSh As Worksheets

    vcompanyname = range("companyshort")
    vLocation = range("location")
    varea = range("area")

    vPrinter = application.printer

    'Directory to look for company folder
    strPath = wsh.Range("G:2003NEW2005"&vcompanyname&"" _
    &"T.Charts & Reports")

    Application.ScreenUpdating = False
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fld = fso.GetFolder(strPath)

    'select worksheets
    oSh("Job","Detail").select

    'Print a pdf and savename at proper location
    vsavename = "Treatment"&" "&vcompanyname&" "&vlocation&" "&varea
    'print the pdf
    Application.ActivePrinter = "Acrobat PDFWriter on LPT1:"
    Sheets(vPrintSheets).PrintOut Copies:=1, Collate:=True
    'name pdf
    'using v savename

    MsgBox = "Chart complete"


    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: file naming and printing (2000)

    See <post:=322,094>post 322,094</post:> for code you can use to set the file name for the next PDF file to be created. In your situation, you'd use something like

    strPath = "G:2003NEW2005" & vcompanyname & "T.Charts & Reports"
    SetPDFFileName strPath & "" & vsavename
    Application.ActivePrinter = "Acrobat PDFWriter on LPT1:"
    Sheets(vPrintSheets).PrintOut Copies:=1, Collate:=True

  3. #3
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: file naming and printing (2000)

    Thanks for your Reply
    I made some changes and am getting closer but having a problem with declaing the varibles and not sure what that problem is?

    Option Explicit

    Sub chartspdf()
    Dim fso As Object
    Dim fld As Object
    Dim subfld As Object
    Dim strPath As String
    Dim strFile As String
    Dim vsavename As String


    vcompanyshort = worksheets("INPUT").Range("CompanyShort").Value
    vWellLocation = worksheets("INPUT").Range("Location").Value
    varea = worksheets("INPUT").Range("Area").Value
    vsavename = "Treatment "&vcompanyshort&" "&vLocation&" "&varea

    vsavename = worksheets("INPUT").Range("c13").Value = Range("companyshort")
    'Directory to look for company folder
    strPath = "G:2003New2005" & vcompanyshort & "T.Charts & Reports"

    Application.ScreenUpdating = False
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fld = fso.GetFolder(strPath)

    'print the pdf
    vPrintsheets = worksheets("Job Summary", "Job Details").Select
    Application.ActivePrinter = "Acrobat PDFWriter on LPT1:"
    Set PDFFileName = strPath & "" & vsavename
    Sheets(vPrintsheets).PrintOut Copies:=1, Collate:=True

    MsgBox = "Chart complete"


    End Sub

  4. #4
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: file naming and printing (2000)

    Sorry I have attached the code with the registry part as well

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: file naming and printing (2000)

    1) You don't do anything with the variables fso, fld, subfld and strFile (you set some of them, but don't use them for anything).

    2) You haven't declared the variables vcompanyshort, vWellLocation, varea, vLocation, vPrintsheets and PDFFileName.

    3) You can't use Set to assign a string value, Set is only for object variables. Moreover, you don't use PDFFilename for anything,

    4) You can't assign a value to MsgBox.

    5) The line vsavename = Worksheets("INPUT").Range("c13").Value = Range("companyshort") makes no sense. It is not clear what you want. Moreover, you had already assigned vsavename in the previous line.

    6) The line vPrintsheets = worksheets("Job Summary", "Job Details").Select makes no sense. You cannot select a worksheet and then assign a variable. Moreover, worksheets("Job Summary", "Job Details").is not valid.

  6. #6
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: file naming and printing (2000)

    thanks for the reply,
    Sorry I miss a few things <img src=/S/doh.gif border=0 alt=doh width=15 height=15> I am just learning VBA and dont have much of a programming background.

    I have declared varibles now and cleared up unused ones

    I have it compiling now <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    and it runs

    But id like to beable to name the PDF vsavename and dont know how I can get this to work for me?? You said something about SetPDFfilename in your first POSt what does that do?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: file naming and printing (2000)

    You haven't copied the SetPDFFileName procedure from the post I referred to higher up in this thread into your code. You must do that. Then call it like this:
    <code>
    SetPDFFileName strPath & "" & vsavename
    </code>
    (NOT as <code>Set PDFFileName = ...</code> !)

    SetPDFFileName sets the file name used by the Acrobat PDFWriter "printer".

  8. #8
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: file naming and printing (2000)

    Thanks alot Hans,

    Sorry if I make you a little fusrated,

    It works now

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: file naming and printing (2000)

    Does the error occur on the line Sheets(vPrintsheets).PrintOut Copies:=1, Collate:=True ?
    My guess is that a duplicate filename occurs, but it's hard to say. Try the following:
    - Click somewhere in ChartsPDF.
    - Press F8 to start executing the code step by step. Each F8 will execute one instruction.
    - Hover the mouse pointer over the name of a variable in the code to see its value.
    - You can switch to other applications while the code is paused, for example to Windows Explorer.
    - Try to find out where the conflict arises.

  10. #10
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: file naming and printing (2000)

    I do get an error when I ran a test on line


    SetPDFFileName strPath & "" & vsavename


    which says Cannot Print; File is Already in use or Locked
    The file has not be already created or locked, could this be because of the registry code?

  11. #11
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: file naming and printing (2000)

    Have you tried using
    'go to directory
    ChDir strpath & ""
    'set sheets
    vPrintsheets = Array("Job Summary", "Job Details")
    'set printer
    Application.ActivePrinter = "Acrobat PDFWriter on LPT1:"

    'input save name
    Application.SendKeys (vsavename)
    'print
    Sheets(vPrintsheets).PrintOut Copies:=1, Collate:=True

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: file naming and printing (2000)

    I haven't tried anything, since I don't have Acrobat.

  13. #13
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: file naming and printing (2000)

    Long piece of code moved to attachment by HansV

    Hi All,

    I have go this print code to work and its shown below.
    since icould not get the same name to work i have stored it to the clipboard so ctrl v will be a fast way of filling in the savename.

    I have one problem though at the end of the code at :
    Sheets(Array("Job Summary", "Job Details")).Select
    SetPDFFileName "" & vsavename
    ActiveWindow.SelectedSheets.PrintOut ActivePrinter:="Acrobat PDFWriter on LPT1:"

    i cannot print both selected sheets at once, rather it prints the first sheet in the array and then when thats complete print the other sheet seperate trying to make two pdf files when 1 pdf file should have 2 of the selected sheets in it. Does anyone know how I can make the code print the to selected sheets into one file?

    Thanks

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: file naming and printing (2000)

    Does the code and responses to the thread <post:=362,199>post 362,199</post:> help at all?

    I do not use Acrobat so can not test anything...

    Steve

  15. #15
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: file naming and printing (2000)

    No, i found that Adobe only has this problem with some workbooks, because my code now works fine on a different test file
    This adobe is quite a stuburn piece of software

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •