Getting cell value while printing from Excel

[bmquiroz]

Hi all,

I am using the code below to covert several pages within a worksheet to PDF format. I've been accomplishing this by printing each page to an Adobe printer then using Distiller to convert to PDF. Everything works fine but I've run into a wall trying to add cell values to the PDF file names. I need to enumerate through a range and pull the value off of each cell and add it to corresponding PDF file. I have been trying to do this using nested loops but am running into endless looping problems.

Thanks.

Sub ExtracttoPDF()

Dim iVBreaks As Integer
Dim iTotPages As Integer
Dim pb As VPageBreak
Dim myLoop As Integer
Dim STDprinter As String
Dim PSFileName As String
Dim PDFFileName As String
Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller

PSFileName = "c:\PDF_Temp\myPostScript.ps"

STDprinter = Application.ActivePrinter
Application.ActivePrinter = "Adobe PDF on Ne05:"
    
Application.ScreenUpdating = False

iVBreaks = ActiveSheet.VPageBreaks.Count + 1

i = iVBreaks

For j = 1 To i

PDFFileName = "c:\PDF_Temp\PDF\" & j & ".pdf"
ActiveWindow.SelectedSheets.PrintOut From:=j, To:=j, Copies:=1, Collate:=True, PrintToFile:=True, PrToFileName:=PSFileName
myPDF.FileToPDF PSFileName, PDFFileName, ""
Kill Left(PDFFileName, Len(PDFFileName) - 3) & "log"
Kill (PSFileName)

Next

Application.ActivePrinter = STDprinter

End Sub

 

[CautionMP]

SIPIN,
Are you looking for a cell value that is tied to the [tt]VPageBreak[/tt]?

If so you might try [tt]ActiveSheet.VPageBreaks(j).Location.Cells(,1)[/tt] This will retrieve the value in the first column after the [tt]VPageBreak[/tt].

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[bmquiroz]

CMP thanks for the reply. Not sure if its tied to the VPageBreak. It is in the same column as the page in the worksheet. The row is 8 but the column changes with each page.

Thanks again.

 

[CautionMP]

SIPIN,
Sounds like [tt]ActiveSheet.VPageBreaks(j).Location.Cells(8,1)[/tt]

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[bmquiroz]

Caution,

I added that line to the code below to echo the cell value of each page but to no avail. Also getting "Subscript out of range" on x = ActiveSheet.VPageBreaks(j).Location.Cells(8, 1)

-B

Sub PageNameTest()
Dim iVBreaks As Integer
Dim pb As VPageBreak
    
Application.ScreenUpdating = False

iVBreaks = ActiveSheet.VPageBreaks.Count + 1

i = iVBreaks

For j = 1 To i

x = ActiveSheet.VPageBreaks(j).Location.Cells(8, 1)
MsgBox x

Next

End Sub

 

[CautionMP]

bmquiroz,
I'm not getting an error, let's check step 1. I'm using Excel 2000 SR-1.

Here is the routine I'm using to test a sheet with values in cells A1:Z100.

Sub TestPageBreaks()
Dim wksCurrent As Worksheet
Dim iVBreak As Integer
Dim strMessage As String

Set wksCurrent = ActiveSheet

For iVBreak = 1 To wksCurrent.VPageBreaks.Count
  strMessage = "Address: " & wksCurrent.VPageBreaks(iVBreak).Location.Address & _
               vbCrLf & _
               "Value: " & wksCurrent.VPageBreaks(iVBreak).Location.Cells(8, 1)
  MsgBox strMessage, vbOKOnly, "Page Break test"
Next iVBreak

Set wksCurrent = Nothing
End Sub

CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[bmquiroz]

CMP,

Tried the code and am not getting the error anymore but still unable to get the values I need. The macro is returning the value of $G$10 - $S$10 which are null, the cell range I need is $F$8 - $S$8.

Thanks.

 

[CautionMP]

bmquiroz,
Did you try [tt]Cells(6, -1)[/tt]?

CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[bmquiroz]

CMP,

Same results, the MsgBox echos value range $G$10 - $S$10. Could it be because the print area is set to F10:S24?

Thanks for your help.