Vba Excel 2007 to PDF

  Bat_Breezy 16:50 27 Aug 13
Locked

I have managed to create an Excel spread sheet but would like to add some more vb script to publish a pdf file. I made a macro in Excel to publish a pdf and copied part of the script in to my main programme, however most newly added lines produce an error. Can anyone help me to correct my mistakes?

Set objExcel = CreateObject("Excel.Application") objExcel.Visible = False 'Hide the excel window. Set objWorkbook = objExcel.Workbooks.Add() Set objWorksheet = objWorkbook.Worksheets(1) For C = 1 to 3 For R = 1 to 25 `56 objExcel.Cells(R,C).Value = R objExcel.Cells(R,C).Interior.ColorIndex = R Next R Next C objExcel.Cells.EntireColumn.AutoFit objExcel.ActiveWorkbook.SaveAs "C:\users\documents\test.xlsx"

` I ran a macro in Excel and copied the text here. With ActiveSheet.PageSetup ` changinging this line to "With objExcel.ActiveSheet.PageSetup" - helps but not much. .PrintQuality = 300 .Orientation = xlLandscape .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .FitToPagesWide = 1 .FitToPagesTall = 1 .ScaleWithDocHeaderFooter = True .AlignMarginsHeaderFooter = True End With ActiveWindow.SelectedSheets.PrintPreview ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ "C:\users\documents\test.pdf", Quality:=xlQualityStandard, _ IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _ True ` Macro end

objExcel.Workbooks.close objExcel.Application.Quit

  Bat_Breezy 17:53 27 Aug 13

Hi, sorry that the script was difficult to read. I have entered it again but using carriage returns to make it more legible. Hope that helps.

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = False 'Hide the excel window.

Set objWorkbook = objExcel.Workbooks.Add()

Set objWorksheet = objWorkbook.Worksheets(1)

For C = 1 to 3

For R = 1 to 25 `56

objExcel.Cells(R,C).Value = R

objExcel.Cells(R,C).Interior.ColorIndex = R

Next R

Next C

objExcel.Cells.EntireColumn.AutoFit

objExcel.ActiveWorkbook.SaveAs "C:\users\documents\test.xlsx"

` I ran a macro in Excel and copied the script here.

With ActiveSheet.PageSetup ` changinging this line to "With objExcel.ActiveSheet.PageSetup" - helps but not much.

.PrintQuality = 300

.Orientation = xlLandscape

.PaperSize = xlPaperA4

.FirstPageNumber = xlAutomatic

.Order = xlDownThenOver

.FitToPagesWide = 1

.FitToPagesTall = 1

.ScaleWithDocHeaderFooter = True

.AlignMarginsHeaderFooter = True

End With

ActiveWindow.SelectedSheets.PrintPreview

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ "C:\users\documents\test.pdf", Quality:=xlQualityStandard, _ IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _ True

` Macro end

objExcel.Workbooks.close

objExcel.Application.Quit

This thread is now locked and can not be replied to.

Huawei P10 review

1995-2015: How technology has changed the world in 20 years

An overview: What leading creative agencies are doing to improve diversity

New iPad, iPhone SE & Red iPhone 7 on sale now