Save WordDocument as PDF using Excel 2010

[heysus jamal]

Hi all,

My macro takes values from an excel spreadsheet and pastes them into bookmarks on a word document template. It then saves it as a word document - however my clients have requested to start sending the statements in pdf. I have the Acrobat add-in installed but cannot figure out the code to SaveAs a pdf. I have a feeling this is a very simple fix but I can't figure it out

This is all being done in Office 2010 by the way!

[vba]
'CREATES MARGIN LETTERS
'************************************************************************** ************
If CallRequirement > 0 Or IntMarginCallRec > 0 Then

If LegalEntity = "GroupInc" Then FilePath = "\\01\Treasury Shared\ALM\Derivative Collateral\Templates\Inc\"
If LegalEntity = "Bank" Then FilePath = "\\01\Treasury Shared\ALM\Derivative Collateral\Templates\Bank\"
FileName = BankName & " Margin Call.dotx"
Set oWA = New Word.Application
Set oWD = oWA.Documents.Add(FilePath & FileName)
'oWD.Application.Visible = True
' ^TOGGLE WORD APP VISIBILITY^
ActiveDocName = oWD.Name

'PASTES VALUES INTO BOOKMARKS IN THE MARGIN LETTER TEMPLATES
'************************************************************************** ************
'FOR BANK TEMPLATES
If LegalEntity = "Bank" Then
oWD.Bookmarks("StatementDate").Range.Text = WorksheetFunction.Text(Cells(RowNum - MondayAdjustment, 1), "dddd, MMMM dd, yyyy")
oWD.Bookmarks("ValueDate").Range.Text = WorksheetFunction.Text(Cells(RowNum, 1), "dddd, MMMM dd, yyyy")
oWD.Bookmarks("MTMValue").Range.Text = WorksheetFunction.Text(Cells(RowNum, 2), "$#,##0.00_);($#,##0.00)")
oWD.Bookmarks("TotalRequirement").Range.Text = WorksheetFunction.Text(Cells(RowNum, 2), "$#,##0.00_);($#,##0.00)")
oWD.Bookmarks("ValueofHeldCollateral").Range.Text = WorksheetFunction.Text(Cells(RowNum, 3), "$#,##0.00_);($#,##0.00)")
oWD.Bookmarks("NetExcessDeficit").Range.Text = WorksheetFunction.Text(Cells(RowNum, 4), "$#,##0.00_);($#,##0.00)")
oWD.Bookmarks("CallRequirement").Range.Text = WorksheetFunction.Text(Cells(RowNum, 5), "#,##0.00_);(#,##0.00)")
'FOR GROUP INC TEMPLATES
ElseIf LegalEntity = "GroupInc" Then
'ALL BANKS ASIDE FROM CITIBANK
oWD.Bookmarks("StatementDate").Range.Text = WorksheetFunction.Text(Cells(RowNum - MondayAdjustment, 1), "dddd, MMMM dd, yyyy")
oWD.Bookmarks("ValueDate").Range.Text = WorksheetFunction.Text(Cells(RowNum, 1), "dddd, MMMM dd, yyyy")
oWD.Bookmarks("MTMValue").Range.Text = WorksheetFunction.Text(Cells(RowNum, 2), "$#,##0.00_);($#,##0.00)")
oWD.Bookmarks("CallRequirement").Range.Text = WorksheetFunction.Text(Cells(RowNum, 7), "#,##0.00_);(#,##0.00)")
oWD.Bookmarks("TotalRequirement").Range.Text = WorksheetFunction.Text(Cells(RowNum, 4), "$#,##0.00_);($#,##0.00)")
If BankName <> "Morgan Stanley" Then oWD.Bookmarks("InitialMargin").Range.Text = WorksheetFunction.Text(Cells(RowNum, 3), "$#,##0.00_);($#,##0.00)")
If BankName <> "Citibank" Then oWD.Bookmarks("NetExcessDeficit").Range.Text = WorksheetFunction.Text(Cells(RowNum, 6), "$#,##0.00_);($#,##0.00)")
If BankName <> "Citibank" Then oWD.Bookmarks("ValueofHeldCollateral").Range.Text = WorksheetFunction.Text(Cells(RowNum, 5), "$#,##0.00_);($#,##0.00)")
'CITIBANK ONLY
If BankName = "Citibank" And IntMarginCallRec > 0 Then oWD.Bookmarks("IACallRequirement").Range.Text = WorksheetFunction.Text(IntMarginCallRec, "#,##0.00_);(#,##0.00)")
If BankName = "Citibank" And Cells(RowNum, 7) > 0 Then oWD.Bookmarks("MTMCallRequirement").Range.Text = WorksheetFunction.Text(Cells(RowNum, 7), "#,##0.00_);(#,##0.00)")
If BankName = "Citibank" Then oWD.Bookmarks("InitialMarginHeld").Range.Text = WorksheetFunction.Text(Cells(RowNum - 1, 3), "#,##0.00_);($#,##0.00)")
If BankName = "Citibank" Then oWD.Bookmarks("InitialMarginRequired").Range.Text = WorksheetFunction.Text(Cells(RowNum, 3), "#,##0.00_);($#,##0.00)")
If BankName = "Citibank" Then oWD.Bookmarks("NetExcessDeficit").Range.Text = WorksheetFunction.Text(Cells(RowNum, 5), "$#,##0.00_);$#,##0.00")
If BankName = "Citibank" Then oWD.Bookmarks("ValueofHeldCollateral").Range.Text = WorksheetFunction.Text(Cells(RowNum, 4), "$#,##0.00_);($#,##0.00)")

'*******************************************************************
'*******************************************************************
'*******************************************************************
'*******************************************************************
'**********THIS IS WHERE I NEED TO SAVE AS A .PDF - I CAN ONLY FIGURE OUT HOW TO SAVE AS A WORD DOC**************
'SAVES THE MARGIN LETTERS IN APPROPRIATE LOCATION
If LegalEntity = "GroupInc" Then oWD.SaveAs FileName:="\\01\Treasury Shared\ALM\Derivative Collateral\Counterparty Folders\Inc\" & BankName & "\Margin Calls\" & BankName & " Margin Call " & WorksheetFunction.Text(CallDate, "[$-409]yyyy-mm-dd") & ".docx"
If LegalEntity = "Bank" Then oWD.SaveAs FileName:="\\01\Treasury Shared\ALM\Derivative Collateral\Counterparty Folders\Bank\" & BankName & "\Margin Calls\" & BankName & " Margin Call " & WorksheetFunction.Text(CallDate, "[$-409]yyyy-mm-dd") & ".docx"

End If

'CLOSES MICROSOFT WORD
oWD.Close (0)
oWA.Quit
Set oWD = Nothing
Set oWA = Nothing

End If
[/vba]

[Kenneth Hobs]

In MSWord, syntax might go something like:
[VBA]Sub Macro1()
ActiveDocument.ExportAsFixedFormat OutputFileName:= _
"c:\1.pdf", ExportFormat:= _
wdExportFormatPDF, OpenAfterExport:=True, OptimizeFor:= _
wdExportOptimizeForPrint, Range:=wdExportFromTo, From:=3, To:=5, Item:= _
wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _
CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _
BitmapMissingFonts:=True, UseISO19005_1:=True
End Sub
[/VBA]

[heysus jamal]

I'm using Automation to control Word from Excel - so the word document is not my active document. I found this elsewhere on the web, someone else had a similar problem to mine and solved it following the gentleman's code below

Are you using Automation to control Word from Excel? If so, I assume that you have a variable of type Word.Document (or Object if you use late binding) that refers to the document that you create. Let's say you have named this variable objDoc.
You can save the document as PDF as follows:
[vba]objDoc.SaveAs2 "C:\Docs\MyDoc.pdf", 17[/vba] 17 is the value of the symbolic constant wdFormatPDF.
If you haven't created a variable for the document, but refer to the active document in the Word application object, say objWordApp:
[vba]objWordApp.ActiveDocument.SaveAs2 "C:\Docs\MyDoc.pdf", 17[/vba]

I tried doing the ", 17" like the user suggested above to no avail. This is driving me insane! It should not be this hard to do this!

[Kenneth Hobs]

It may be a network UNC path problem. Try using SaveAs2 or my method to your local c:\ folder.

[heysus jamal]

It may be a network UNC path problem. Try using SaveAs2 or my method to your local c:\ folder.

[vba]
If LegalEntity = "GroupInc" Then oWD.SaveAs2 (FileName:="\\crplivfp01\Treasury Shared\ALM\Derivative Collateral\Counterparty Folders\CIT Group Inc\" & BankName & "\Margin Calls\" & BankName & " Margin Call " & WorksheetFunction.Text(CallDate, "[$-409]yyyy-mm-dd"),17)[/vba]

When I tried using the 'SaveAs2' method above I get the following error message.

Compile error:

Expected: named parameter

Any idea what that means?

[Kenneth Hobs]

Resolve the filename string first. I set it to s.
[VBA]Dim s as String
s ="\\crplivfp01\Treasury Shared\ALM\Derivative Collateral\Counterparty Folders\CIT Group Inc\" & BankName & "\Margin Calls\" & BankName & " Margin Call " & WorksheetFunction.Text(CallDate, "[$-409]yyyy-mm-dd")
debug.print s[/VBA]
Check Immediate window for result after playing or use MsgBox to check s.

You don't really need to use a parameter name in your SaveAs method since you are just using the first two in order.

Try using Format() to format your date.

[heysus jamal]

After more research it turns out I shouldn't be using 'SaveAs.' Instead I should be using 'ExportAsFixedFormat'....

I now have the below code to export as pdf, however I am now getting this error message...

[vba] If LegalEntity = "GroupInc" Then oWD.ExportAsFixedFormat OutputFileName:= _
"\\crplivfp01\Treasury Shared\ALM\Derivative Collateral\Counterparty Folders\Inc\" & BankName & "\Margin Calls\" & BankName & " Margin Call " & WorksheetFunction.Text(CallDate, "[$-409]yyyy-mm-dd").pdf, ExportFormat:= _
wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _
wdExportOptimizeForPrint, Range:=wdExportAllDocument, From:=1, To:=1, _
Item:=wdExportDocumentContent, IncludeDocProps:=False, KeepIRM:=True, _
CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _
BitmapMissingFonts:=True, UseISO19005_1:=False[/vba]

I quoted the error message I now receive below... I feel like I am one step closer!!!

Run-time error '424':

Object required

[Kenneth Hobs]

[VBA]If oWD is Nothing then Msgbox "oWD is Nothing."[/VBA]

[heysus jamal]

[vba]If oWD is Nothing then Msgbox "oWD is Nothing."[/vba]

Sorry Kenneth, I do not understand what you mean by this?

[Kenneth Hobs]

Before using that object, insert that code to check if it was created or not. Otherwise, use F8 and step through your code to see which line the problem occurs.