Referencing Cells For PrToFileName (.PrintOut)

[boyko]

Hello,

I am trying to reference a cell containing text information in cell "D5" on sheet "Company Lists" as apart of the PrToFileName parameter fir .PrintOut. The code works when manually replacing the "name" variable with the text inside quotation marks (ex. "AAPL").

Please see my code below:

Sub Macro2()
'
' Macro2 Macro
'


'

Dim filelocation As String
Dim name As String
Dim filename As String
Dim filetype As String
Dim file As String

Sheets("Company List").Range("D3") = 1
While Sheets("Company List").Range("D3") < 41299
    
    filelocation = "G:\My Drive\BOYKO WEALTH\Boyko Data\"
    name = Sheets("Company List").Range("D5").Value
    filename = CStr(name)
    filetype = "-Boyko-Data.pdf"
    file = filelocation & filename & filetype
    
    Sheets("Company List").Select
    ActiveSheet.Range("D5").Copy
    Sheets("ENTER").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("C2").Select
    Sheets("Data Reports").Select
    Application.Wait (Now + TimeValue("0:00:05"))
    ActiveSheet.PrintOut Copies:=1, collate:=True, Preview:=False, PrintToFile:=True, PrToFileName:=file
    Sheets("Company List").Range("D3") = Sheets("Company List").Range("D3") + 1
Wend


    

    
End Sub

Thank you in advance for any help.

 

[jolivanes]

So what do you want to achieve? Explain in a concise manner please.

 

[boyko]

So what do you want to achieve? Explain in a concise manner please.

Hi,

Basically the "D5" cell updates with the current company name each time the loop runs. I would like for this name in "D5" to appear as the .pdf file name as apart of the PrToFileName. So for example; with the company "ABC", When the code loops and "ABC" is seen in "D5", I would like for the file to print as: "ABC-Boyko-Data.pdf".

Currently the code works when I replace:

name = Sheets("Company List").Range("D5").Value

With:

name="ABC"

However, I cannot manually enter the name each time as there are 41,000 different companies. That is why I created the system where "name" references: "Sheets("Company List").Range("D5").Value"

Thank you again for your help.

 

[RoryA]

That is what that code should do (assuming D5 is a formula that updates in response to the change in D3?). What exactly is happening now?

 

[boyko]

That is what that code should do (assuming D5 is a formula that updates in response to the change in D3?). What exactly is happening now?

Hi yes the code works as you suggest, but instead of just naming the files it prompts me to name them with the dialog box.

 

[jolivanes]

Read Post #2 again.
Your code in Post #1 could or should be massaged.

Change as required and see how far this gets you.

Dim PDF As String
Sheets("Sheet1").PageSetup.PrintArea = "A10:I35"    '<---- Delete or change as required
PDF = "C:\Boyko\Boyko " & Sheets("Sheet1").Range("D5") .Value & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDF

 

[jolivanes]

BTW, "While Sheets("Company List").Range("D3") < 41299"
Does this mean that there could be as many as 41300 PDF files saved?

 

[boyko]

Read Post #2 again.
Your code in Post #1 could or should be massaged.

Change as required and see how far this gets you.

Dim PDF As String
Sheets("Sheet1").PageSetup.PrintArea = "A10:I35"    '<---- Delete or change as required
PDF = "C:\Boyko\Boyko " & Sheets("Sheet1").Range("D5") .Value & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDF

Unfortunately when I export as a pdf, the formatting of the report changes (Is there a way to fix this?); font size, type, and colors do not remain the same. However, when I print as PDF, the formatting remains the same.

 

[boyko]

BTW, "While Sheets("Company List").Range("D3") < 41299"
Does this mean that there could be as many as 41300 PDF files saved?

Yes, it prints 41298 reports each time it runs.

 

[jolivanes]

Re: filetype = "-Boyko-Data.pdf"
Never heard of that file type. Can you enlighten me.