Get PDF filename from Excel sheet on printout

I have an Excel sheet that actually is a letter.
On the same sheet I have a button that get the next address from a database (in Excel) and the contents on the sheet changes automatically.

I'm supposed to print out (actually save) the letter as an PDF-file (Acrobat installed) and what happens is that the PDF-file is saved to a specific folder on the HD. All this is very simple part from one thing. I have to manually change the name of the PDF-file before I save it, otherwise it will use the same name every time and I want it to pick up the unique name from a cell on the sheet (that changes every time I run the macro "Next  customer").

What I want is a sub that "print out" to a PDF-file and grab the name for the file from a range called "FileName".  Additionally I might add the sub "Next  customer" to the end of this sub and then pop-up a MsgBox asking if I want to print out this one aswell.

I started to look at:

expression.PrintOut(From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFileName)

But I got stuck at "PrToFileName" and expressions etc.

Does anyone have a solution to my problem ?

Johan
Sweden
LVL 4
OkkiAsked:
Who is Participating?
 
calacucciaCommented:
What's better, adding the line with the enter key will even do the 'Click OK' for you:

Sub TestPrintOut()
TestName = "MyPDFFileName.pdf"
SendKeys (TestName)
SendKeys ("{ENTER}")
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub

Calacuccia
0
 
criCommented:
You can not, at least not according to VBA/Excel help:

"....PrintToFile   Optional Variant. True to print to a file. Microsoft Excel prompts the user to enter the name of the output file. There's no way to specify the name of the output file from Visual Basic...."

The only work-around I can think of is that the PDF file is renamed _afterwards_ by the macro, but as the PrintToFile dialog does not seem to have a default too, you would need to take care to input the same name everytime. Is this acceptable ?
0
 
criCommented:
Ooops, do you happen to have Excel 2000 ? In this case wait for an expert who does too.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
OkkiAuthor Commented:
Cri:  Yes, I have 2000 but I was kind of hoping that this file could be used successfully on a computer running Office 97 as well.

"The only work-around I can think of is that the PDF file is renamed _afterwards_ by the macro, but as the PrintToFile dialog does not seem to have a default too, you would need to take care to input the same name everytime. Is this acceptable ?"

I don't mind to add code that renames the PDF file (the output filename would always be the same) but I don't have a clue on how to write that code (not yet anyway).
This macro would have to find the file first and should I let the PDF file be saved in the same folder as the Excel file or can I specify path I wonder?
The new name for the file is stored in a range named "FileName" on the same sheet as the printout and the value of that range changes when I bring up another customer.

"FileName" actually is a merge of two cells (Reginal bank nr and local bank nr) and will look like this:  1234_4863248

I'm not sure I understand what you mean by ", you would need to take care to input the same name everytime".  I've got a feeling it will use the same name as the workbook but with another extension (in this case PDF).

If I select "PDF-Writer" as printer before I start the macro I can run this code to get to the "Save As" dialog.

Sub PrintFile()
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub

All I wish for really is that Excel somehow could copy the value in range "FileName" before it started the printout and not lose this value before it's time to paste it into the dialog box.

If I copy "FileName" and start the a.m. code I will get a dialog box promting for a filename (with my workbookname.pdf as default) but there will be nothing left to paste. I know that this is not Excel's fault but I was hoping it could store the value somehow and not just paste it there but also hit the OK-button afterwards and continue with next step in the code.

Most important is of course to store the value.

Could I be more specific than this ?   =)

Johan
0
 
bruintjeCommented:
Hi Okki,

Think this will start it up

Sub PrintFile()

Dim test As Boolean
Dim tFileName As String
Dim rNameRange As Range

    For Each Cell In Range("A1", "A100")
       'simulate button push
       'call macro that changes sheetcontent or the next name in the NameRange
       'then fill your test bool with the failure or not of the save
       tFileName = CStr(Cell.Value)
       test = ActiveWindow.SelectedSheets(1).PrintOut(, , 1, , , 1, , "c:\PDF_Docs\" & tFileName & ".PDF")
    Next Cell
End Sub

It's crude but can be refined...
Got a range filled with filenames, here A1:A100 and steps through it calling the sheetcontents from the datasource and then saving it to PDF....

This worked for me with xls files...but that's hust an extension to change...

Got questions just ask..

HTH:O)Bruintje
0
 
criCommented:
If I interpret VBA/Excel 97 help correctly, then you can not pass the PrintFilename as argument, only via dialog box. It this is true (*), then you must decide if compatibility with Excel 97 is a "nice to have" or a "if feasible" requirement. For the first case bruintje already gave you the answer, for the second I or another one would continue.  

(*) bruintje: Do you have Excel 2000 ?
0
 
OkkiAuthor Commented:
Well Bruintje, I don't really know what questions to start with.  Maybe it's better if I explain a little better first ?

My Range("FileName") = Cell B1 of sheet1 (ALWAYS!)
This means I could use Range("B1") instead of course.

As you understand by now I probably will not need any "For Each" in my code.

When my code starts my printout it stops in the PDF Save File As Dialog with part of the name of the Excel workbook as suggested filename.  That's not the filename I want to use.  I want the value of cell B1 (Range FileName) to ALWAYS be the PDF file name.

Before I print next time I will let a macro change the contents of the sheet (including cell B1) and therefore I will not get 2 PDF files with the same name.

I have tried with selecting cell B1 in the beginning of the code and do a "Copy.Selection" but when I get to the dialog there is nothing to paste (Memory is erased). All I want is that damn cell value to stay in memory somehow but furthermore  I would like the procedure to do the paste (Ctrl+V) for me also.

When I played around with your code I almost got it to work.  The text in the "Save As Filename" dialog box was "Maj.pdf" (which is a little funny since the name of the Excel workbook is "Avi FSPA Maj.xls" but I suppose VBA does not like spaces in filenames) and when I pressed down Ctrl+V something new came into the Filename box, but unfortionally not what I wanted to be there.  It was the complete path but only the way it was written in the code (in other words it said C:/….& tFileName & ".PDF"….) instead of the value of cell B1.

Later when I played around I could paste again.  This time the result was "Sub PrintFile()" and that's not quite the filename I had in mind either.  I think we are close, but perhaps not.

Johan
0
 
bruintjeCommented:
(*)yup both in fact running in Office1K and Office2K.....:O)

saw it earlier today that you have much more control of things in Office2000, so that printout stuff is only gonna work that way in Office2K
0
 
bruintjeCommented:
ah Okki can you post some of what you used here, then we can look at what is running already
0
 
criCommented:
Okki, did you see my last post ?
0
 
OkkiAuthor Commented:
Cri: I'm doing this file for my brother and I have a strong feeling they might still use Excel 97 (but I'm  not certain).
I could of course make all the PDF's at home but I think he would like to be able to do it himself also.

I realize that a code that changes a filename will take a little longer to execute but right now I don't have any alternatives since I have no working alternative and I have about 600 PDF files to create (and perhaps more in the future).

The fact that I sometimes have been able to paste (not the right stuff though) shows that there is a solution somewhere out there.

Johan
0
 
OkkiAuthor Commented:
bruintje :
I now know why I sometimes managed to get the posibillity to paste.  It's when I copy something in VBA editor.  That will stay in memory.

Example:  
I copied this string in VBA and pasted it into the code:  "    Range("B1").Select"
Then I tried the macro from the sheet and then I could paste just that:  "    Range("B1").Select" (do you notice all the spaces in front just like in the code).

Well I removed that string again inside VBA-Editor but it was still in memory the next time I run the macro.

This explains why I got "Sub PrintFile ()" before. It took the first line of an area I had copied before inside the editor.

I don't have any "working" examples since I make changes in the same code all the time.

Johan
0
 
OkkiAuthor Commented:
Cri:  Yes, I belive I have seen all your posts.  Is there something you want to point out for me ?
0
 
bruintjeCommented:
two ways fo doing it with a lot of effort....

via an VBA object and copying the name from there to the clipboard, being able to paste from there

or getting the windowhandle of the printscreen and replacing the text with the name...

just a few hours to go and a busy day tomorrow so i'm off for now....

another way could be to reference the acrobat dll /activeX and try to convert it with an function in there....but then you have to search for info on the adobe site and look if they support a forma of scripting, which Corel does since a long time and all others doing it...

then you could save the sheets as excel files and loop through the directory from within Acrobat......far off i guess must be sleepy....
0
 
OkkiAuthor Commented:
I found this page but it did not solve my problems, yet.

http://www.cpearson.com/excel/clipboar.htm

Maybe I should study it harder ?

Well, thanks anyway and good night.

Johan
0
 
OkkiAuthor Commented:
I've cracked it !

This is the working code for the first part of my question:

Sub PrintFilePDF()
Dim MyDataObj As New DataObject
    MyDataObj.SetText Range("FilNamn").Value
    MyDataObj.PutInClipboard
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub

Maybe I should be pleased with this result.  It would be fun if someone knew how to get the text there by itself.  Now I just do "Paste" to replace the suggested filename.

I'll keep the question open 4 a while.

Thanks for all your efforts so far !

Johan
0
 
calacucciaCommented:
Hi Okki,

Did not read all of the comments and efforts made by Bruintje and Cri, and your final conclusion, but this code should put what you want into the FileName dialog box:

Sub TestPrintOut()
TestName = "MyPDFFileName.pdf"
SendKeys (TestName)
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub

You have to run it in run-time though, not via the debugger.

If it works, all you'll have to do, is to assign the name from your range into the varialbe TestName.

Calacuccia
0
 
OkkiAuthor Commented:
And we have a winner !
Why am I not surprised to see your name there Calacuccia ?

This sub does all the things I asked for:

Sub TestPrintOut2()
TestName = Range("FileName").Value
SendKeys (TestName)
SendKeys ("{ENTER}")
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub

I actually was going to ask if someone knew how to use the "SendKeys" event since I found it in my "Excel 2000 Power Programming with VBA" book written by John Walkenbach.

I found this in the online VBA-help:

SendKeys Statement Example
This example uses the Shell function to run the Calculator application included with Microsoft Windows. It uses the SendKeys statement to send keystrokes to add some numbers, and then quit the Calculator. (To see the example, paste it into a procedure, then run the procedure. Because AppActivate changes the focus to the Calculator application, you can't single step through the code.). On the Macintosh, use a Macintosh application that accepts keyboard input instead of the Windows Calculator.

Dim ReturnValue, I
ReturnValue = Shell("CALC.EXE", 1)    ' Run Calculator.
AppActivate ReturnValue     ' Activate the Calculator.
For I = 1 To 100    ' Set up counting loop.
    SendKeys I & "{+}", True    ' Send keystrokes to Calculator
Next I    ' to add each value of I.
SendKeys "=", True    ' Get grand total.
SendKeys "%{F4}", True    ' Send ALT+F4 to close Calculator.

This example didn't help me much so I decided to ask someone else.
It seems I don't need to anymore.

John W writes the following:

SendKeys can be quite tricky to use because the keystrokes are not actually sent until VBA is finished executing. For best results, your procedures should not perform any other actions after sending keystrokes to another application. A good rule of thumb is to use SendKeys only as a last resort.

I guess this means I should not add code after the printout command but I would like that.  =(

What do you think Calacuccia, is it nice of me to leave the question open for a while (so others can read this without loosing points) or is it custom to accept the answer as soon as you are pleased ?

If  I don't hear from you I will close the question tomorrow anyway.

Johan

PS.  You would not know how to select printer in the code aswell ?
0
 
calacucciaCommented:
Hi Okki,

As you place the SendKey statement BEFORE the PrintOut dialogBox appears, all should work quite well. The reason: The Printout dialog pauses execution by itself, as it is waiting for input.

That's why even when sendkeys is ran by the procedure before calling the PrintoutDialog, still the keys are only sent once execution is stopped.

In certain cases, I would imagine that it's better to leave out the 'Enter' line, and click 'OK' or hit enter for yourself, to not mix things up.

Normally it shold work as it stands though, and any code placed after the PrintOut line should run fine.

You could test it by adding a dialog box after the PrintOut command:
MsgBox("This works after all")


Calacuccia
0
 
OkkiAuthor Commented:
I already tested with this code:

Sub PrintFilePDF()
Start:
    TestName = Range("FileName").Value
    SendKeys (TestName)
    SendKeys ("{ENTER}")
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    MsgBox " File saved on disk is named " & TestName & ".pdf  "
    Call NextKTH
    Ans = MsgBox("I have changed to the next customer on the form. Just click OK if you wish to convert this to PDF aswell.    Otherwise click EXIT. ", vbYesNo + vbQuestion, "This dialog works thanks to Calacuccia !")
    Call sndPlaySound32("c:\windows\media\Tada.WAV", 0)
    If Ans = vbNo Then Exit Sub
    GoTo Start
End Sub

It works real well !

Johan
Sweden
0
 
OkkiAuthor Commented:
I forgot to mention that "Call NextKTH" runs another sub (that changes the printout area) but you could probably figure that one out by yourself.

To make this perfect I will have to figure out how I through code can select printer aswell. Now I have a sign for the user that he must select printer and destination folder before he execute the macro.

I'll hopefully figure it out somehow.  I think I've done something similar before (with Excel4 macro code) but it remains to find and convert it.

Johan
0
 
calacucciaCommented:
Okki,

To set the printer, use the ActivePrinter property in PrintOut

ActiveWindow.SelectedSheets.PrintOut Copies:=1 , ActivePrinter:="NameofYourPrinter"

To  know how the printer is called register a macro when changing it, or use this line (after you have set the printer you need manually)

MsgBox "The name of the active printer is " & Application.ActivePrinter

You could also set the active printer like this:
Application.ActivePrinter="Put your name here"

To change current directory and Drive use this

'Changes drive to C:\
ChDrive "C"
'Changes path
ChDir "C:\Temp\"

Calacuccia
0
 
criCommented:
Okki, I wanted a response to the question whether compatibility to Excel 97 was crucial or not. And, as we are persons answering here, a bit of feedback would not hurt either.

PS: Make a search of the Microsoft Knowledge Base with 'Excel Send Keys' (All Words, w/o '). You will get 52 hits... I would avoid this if possible.

0
 
OkkiAuthor Commented:
Calacuccia:  I almost have tears in my eyes

I created two more ranges on my sheet (outside of the printout area of course):
o Range "Drive" where they (or I) specify the drive I want to save the PDF's to
o Range "Path" is the complete path to the right folder

No matter what they did before (open or save files) these PDF's will always end up in the right place.  I just love it !

This is the final code (I think!):

Sub PrintFilePDF()
Start:
    ChDrive Range("Disk").Value
    ChDir Range("Mapp").Value
    Application.ActivePrinter = "Acrobat PDFWriter on LPT1:"
    TestName = Range("FileName").Value
    SendKeys (TestName)
    SendKeys ("{ENTER}")
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    MsgBox " File saved on disk is named " & TestName & ".pdf  "
    Call NextKTH
    Ans = MsgBox("I have changed to the next customer on the form. Just click OK if you wish to convert this to PDF
aswell.    Otherwise click EXIT. ", vbYesNo + vbQuestion, "This dialog works thanks to Calacuccia !")
    Call sndPlaySound32("c:\windows\media\Tada.WAV", 0)
    If Ans = vbNo Then Exit Sub
    GoTo Start
End Sub

You'll get your points tomorrow morning.

Regards,

Johan
0
 
OkkiAuthor Commented:
Cri:  I sense that you are not totally pleased with my behavior here.  I'm really sorry if that's a fact. I gave quite a lot of feedback to most of the questions (even repeated myself I noticed) and also your question about how crucial the Excel 97 compatibility was got an answer in my opinion.
I first said that I hoped it would be possible to use it in Excel 97 as well and later I wrote that I had a feeling my brother might still use Excel 97 but that I could accept to make the PDF's at home if necessary.  I might add that all Excel files created on my machine are automatically saved as a Excel 97 - 2000 & 5/95 Workbook and this has not been a problem for me so far.

At first I wrote:  "All I wish for really is that Excel somehow could copy the value in range "FileName" before it started the printout and not lose this value before it's time to paste it into the dialog box."

I came up with the solution on that problem myself but extended my demands to also paste the values by itself and click "Save".

All working solutions that I have not solved myself came from Calacuccia and I hope you don't find it unfair to you or "bruintje" that I decided to give him the points.

I would be a liar if I said that I was not interested to see a working code for renaming PDF files but at this point it's not important for my present project cause even if they can't use the file (despite it's been saved as Excel 97) I can do the job from home.

Thanks for all efforts anyway (and don't forget to save this code for the future).

Johan
Sweden


0
 
bruintjeCommented:
great to see it solved, nice solution 2....and this thing is automatically saved by the links :O)
0
 
criCommented:
Okki, w/o wanting to expand much more, the reason I got miffled was I had the impression that my post of Wednesday, May 03 - 02:09PM got unnoticed. I wanted to know whether to continue with a work-around for 97 or whether Bruintje's Excel 2000 (which I do not have) solution was good enough. Forget it, as a.m. there are humans answering here.  
0
 
OkkiAuthor Commented:
Cri, I understand and my answer to that question was perhaps not so clear.  At that point I had no alternatives since nothing worked and I was more interested than now.
What I'm interested in now is perhaps to know if you can get above code to work with your version of Excel.

I'm grading now but I belive that you can add your comment anyway.

Johan
0
 
Sar1973Commented:
Hi can you post a single line that allows me to launch a print of a selected file?
Say I have saved a document like "C:\myFile.ext" and I want to print it with a printer called "myPrinter".
The VBA command is: ...PrintOut ("C:\myFile.ext", "myPrinter") or what? Thanx.
0
 
criCommented:
If your setup still has DOS support, then you could use FileCopy to LPTx. If not you have the following alternatives:
a) Use the Shell command to invoke print.exe. Rather crude.
b) Ask a question in the Excel Area. Eventually a more elegant solution (COM ?) or similar can be found.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.