xls to PDF vba code right margin not working

[dcrighton]

Bellow is a vba code I have used to print sheets from multiple excel files. It works great except the right margin doesn't go to 0.5. I would like the top, Left, Right margins all to be 0.5 and if needed the excel sheet data can be scaled to make the margins in the PDF. I do not want the bottom margin to be set. Any help is much appreciated.

Thank you,
Devin



Sub PrintSheetToPDF()

shName = "Weld Map "

With Application.FileDialog(3)

.AllowMultiSelect = True
.Show

If .SelectedItems.Count = 0 Then
MsgBox "No file selected!"
Else

For i = 1 To .SelectedItems.Count

Set wb = Workbooks.Open(.SelectedItems(i))
Set ws = wb.Sheets(shName)

pdfName = Left(wb.FullName, InStrRev(wb.FullName, Chr(46))) & "pdf"

With ws.PageSetup
.PrintArea = "$A$1:$W$20"
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
'.BottomMargin = Application.InchesToPoints(0.5)'
End With

ws.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=pdfName, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True

wb.Close False

Next i

End If

End With

 

[shg]

If the sheet is compressed vertically to fit on a single page, the width may not extend to the right margin.

 

[dcrighton]

all the sheets pdf'd are significantly shorter in length than a full page so they are not compressed vertically, if anything I would like to change the code so that the left and right margins are the driving factors and any vertical spill over will be pdf'd on consecutive pages.

 

[shg]

.FitToPagesWide = 1
.FitToPagesTall = False

That will not magnify a sheet to extend to the right margin, though.

 

[dcrighton]

yeah I can't seem to find a good way to make the left and right margins equal when pdfing

Devin

 

[shg]

Select Center Horizontally. Or increase the font size of style Normal to make everything wider.

 

[dcrighton]

I tried using .HorizontalAlignment = x1Center within the PageSetup with, but it doesn't apply there if I could have it center within the page setup I think the code would do what I want. I don't know if it is possible though.

Devin

 

[shg]

Sorry, I can't parse that.

The constant is xlCenter (or xlHAlignCenter), not x1Center.

 

[dcrighton]

I got it working I commented out the left and right margins and did what you said, .FitToPagesWide = 1 and .TopMargin = Application.InchesToPoints (0.5) and it centers with a .5 top margin. :D

Thank you for your help
Devin Crighton

 

[shg]

You're welcome.