automate save as of excel-report to pdf-document.

[sverre]

Hi,

I have inherited an excel-report which I would like to automate. Every day I
need to
save in pdf-format around 12 reports from 12 different selections in a
drop-down list (I am not sure it is a drop-down list but it looks like that).

Is it possible to automatically do the following: 1) Da a selection for each
possible selection in the drop-down list 2) Save it as a pdf-report and give
it the same name as the selection in the drop-down list. Save each report on
the desktop.

Automatic calculation is shut off in the workbook. I assume I need to tell
excel to turn it on during this procedure.


Many thanks in advance!

Sverre

 

[marcus]

Hi Sverre

I have done something similar on PDF version 5 and it works well. It
assumes you are using a data Validation list for your drop down. It
Loops through the list in cell B5 and prints to pdf. You will need to
adapt this to suit your problem.

Take care

Marcus

Sub ValListLoop()

Dim rng As Range, cell As Range
Dim mycells As String
Dim PDFFileName As String

Set rng = Evaluate(ActiveSheet.Range("B5").Validation.Formula1)
For Each cell In rng
Application.Calculation = xlAutomatic 'turn calcs on
ActiveSheet.Range("B5").Value = cell.Value 'Change to suit
mycells = Range("B5").Value 'Change to suit
Sheets("Sheet1").Select 'Change to suit
Application.ActivePrinter = "Adobe PDF on Ne01:" 'Change path
Let PDFFileName = "C:\" & mycells & ".pdf" ' change to desktop
path
SendKeys PDFFileName & "{ENTER}", False
ActiveWindow.SelectedSheets.PrintOut copies:=1
SFileName = Chr(34) & PDFFileName & Chr(34)
Next
Application.Calculation = xlManual 'turn calcs off
End Sub