How do you designate file name as a cell value when printing using VB?

[mbreston]

If someone can provide example code that would be great. I am printing from excel to adobe print driver and would like the adobe file to be saved as a name defined as a cell in the spreadsheet.

 

[Gavona]

Record yourself doing the save manually and then inspect the code. Modify it as necessary. The following might help.

MyName =Range("a1").value

Post back with what you have tried if still having problems.



Gavin

 

[mbreston]

Gavin,

Thanks so much for your feedback. I am still not having success though. Below is the code I recorded. The file name I want to use is in cell k6 of the leads sheet. I am printing a range named auto30 using adobe printdriver. I want to save the adobe file as the name in k6. When I record printing and the file naming msg box comes up, i am not able to select a value from a cell. I am also not able to paste using ctrl v in the file name input box. None of my efforts with the file naming input box are recorded by the macro recorder. Please note that I am not trying to name the excel file. I am trying to name the pdf file that is created in adobe from printing the range called "auto30".

Sub testnamefile()
'
Sheets("Leads").Select
Range("K6").Select
Selection.Copy
Application.Goto Reference:="auto30"
Application.CutCopyMode = False
Selection.PrintOut Copies:=1, Collate:=True
Sheets("Leads").Select
Selection.Copy
End Sub

 

[Gavona]

Create a variable called MyName in your code and store the name there rather than in the clipboard.

I don't have the adobe printdriver but the help for the printout method shows the format to be: expression.PrintOut(From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFileName)

So I would try (including simplification of your code)

Sub testnamefile()

Dim MyName
MyName = Sheets("Leads").Range("K6").value

'or if you do want the name in the clipboard for some reason:
'Sheets("Leads").Range("K6").copy

Range("auto30").PrintOut PrintToFile:=True, PrToFileName:=MyName

End Sub

It might also be worth using the ActivePrinter parameter to explicitly refer to the Adobeprintdriver.

I hope that the above helps but if not then I am sure someone else will help.


Gavin