Save a Selection as a PDF [SOLVED]

[Sean Winder]

Hi guys,

So for my A2 coursework I have to create a spreadsheet for a business to use to keep track of orders, and I have made one that I quite like. On one of the sheets, I have an auto-generating invoice, that pulls data from the order form and concatenates it. To the right of the invoice, I have two macro buttons, one to print the invoice, and one to save & open it in PDF format, so I can email it. However, due to the way that this VBA code currently works, I have to change the macro every time I transfer the file to a new PC, since I need to make it work on multiple machines.

Here's the code:

Sub SaveAsPDF()
'
' SaveAsPDF Macro
'

'
    Range("B3:I38").Select
    ActiveSheet.Unprotect
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "H:\School\Year 13\ICT\Mr Mordue\Contrast Photography Invoice.pdf",     Quality:= _ xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Essentially, this is unprotecting the worksheet, selecting the cells, removing their aesthetic border, exporting them, and then re-doing everything that was undone.

This works fine, however the bit shown in bold is what's causing me trouble, the fact that it will only save in this location.

What I really want it to do is detect where the spreadsheet has been loaded from, and save in the same directory as that.

Any help will be greatly appreciated.

Sean

 

[Hui]

In VBA you can use

Activeworkbook.Path

to show where the file was opened from

 

[Sean Winder]

Cheers, I'll give it a go :D

 

[Sean Winder]

So whereabouts would i put that code to make it work? Would I put it before the filename, and then just tell it to save as Invoice.pdf?

 

[Hui]

That code only returns the current directory

You need to set the directory which is a text string in the line

Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
  "H:\School\Year 13\ICT\Mr Mordue\Contrast Photography Invoice.pdf", Quality:= _
  xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
  OpenAfterPublish:=True

It is more normal to define the Directory and Filename separately and add them together in the code

myDir = ActiveWorkbook.Path + "\"
myFile = "Contrast Photography Invoice.pdf"

Selection.ExportAsFixedFormat _
  Type:=xlTypePDF, _
  Filename:=myDir + myFile, _
  Quality:=xlQualityStandard, _
  IncludeDocProperties:=True, _
  IgnorePrintAreas:=False, _
  OpenAfterPublish:=True

 

[Sean Winder]

Thanks for the quick response, I'm now wondering whether to have it save automatically using the code that you have kindly provided, or whether to just have it open, and then let the used do as they like with it?

Do you reckon that using the VBA code would get me a higher grade due to it being more advanced?

 

[Sean Winder]

Having added your code and seeing how well it works, I shall keep it.

Thanks a lot for your help :D

Sean