Solved

Get PDF filename from Excel sheet on printout

Posted on 2000-05-03
30
2,027 Views
Last Modified: 2008-06-29
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
0
Comment
Question by:Okki
  • 13
  • 7
  • 5
  • +2
30 Comments
 
LVL 13

Expert Comment

by:cri
ID: 2773488
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
 
LVL 13

Expert Comment

by:cri
ID: 2773499
Ooops, do you happen to have Excel 2000 ? In this case wait for an expert who does too.
0
 
LVL 4

Author Comment

by:Okki
ID: 2774258
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
 
LVL 44

Expert Comment

by:bruintje
ID: 2774541
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
 
LVL 13

Expert Comment

by:cri
ID: 2775245
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
 
LVL 4

Author Comment

by:Okki
ID: 2775307
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
 
LVL 44

Expert Comment

by:bruintje
ID: 2775315
(*)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
 
LVL 44

Expert Comment

by:bruintje
ID: 2775331
ah Okki can you post some of what you used here, then we can look at what is running already
0
 
LVL 13

Expert Comment

by:cri
ID: 2775347
Okki, did you see my last post ?
0
 
LVL 4

Author Comment

by:Okki
ID: 2775363
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
 
LVL 4

Author Comment

by:Okki
ID: 2775459
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
 
LVL 4

Author Comment

by:Okki
ID: 2775475
Cri:  Yes, I belive I have seen all your posts.  Is there something you want to point out for me ?
0
 
LVL 44

Expert Comment

by:bruintje
ID: 2775641
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
 
LVL 4

Author Comment

by:Okki
ID: 2775669
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
 
LVL 4

Author Comment

by:Okki
ID: 2775727
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 17

Expert Comment

by:calacuccia
ID: 2775806
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
 
LVL 17

Accepted Solution

by:
calacuccia earned 100 total points
ID: 2775810
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
 
LVL 4

Author Comment

by:Okki
ID: 2776582
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
 
LVL 17

Expert Comment

by:calacuccia
ID: 2777177
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
 
LVL 4

Author Comment

by:Okki
ID: 2777353
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
 
LVL 4

Author Comment

by:Okki
ID: 2777435
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
 
LVL 17

Expert Comment

by:calacuccia
ID: 2777576
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
 
LVL 13

Expert Comment

by:cri
ID: 2778119
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
 
LVL 4

Author Comment

by:Okki
ID: 2778803
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
 
LVL 4

Author Comment

by:Okki
ID: 2778943
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
 
LVL 44

Expert Comment

by:bruintje
ID: 2779479
great to see it solved, nice solution 2....and this thing is automatically saved by the links :O)
0
 
LVL 13

Expert Comment

by:cri
ID: 2779612
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
 
LVL 4

Author Comment

by:Okki
ID: 2779862
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
 
LVL 9

Expert Comment

by:Sar1973
ID: 21893630
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
 
LVL 13

Expert Comment

by:cri
ID: 21894716
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now