VBA Code - unable to open PDF after saving document as .pdf

mageeg

Board Regular
Joined
Jun 6, 2021
Messages
81
Hi, I have a database in excel which through the use of bookmarks, transfers data to a Word template.

However, after transferring the data to Word, i would like to save this document as a PDF and store it in a set location.

The following line of code does this:

VBA Code:
wdDOC.SaveAs2 (ThisWorkbook.Path & "\Inspection Test Forms\" & sh.Range("D" & iRow).Value & ".pdf")

This saves the document as a PDF and in the correct location, however upon opening it says "An error occured: Failed to open this document"

However if i change the code to .docx instead of .pdf it works fine and i can open it. However i would like this to be a PDF.

Any ideas?
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,633
Office Version
  1. 365
Platform
  1. Windows
Hi, you could try like this.

wdDOC.SaveAs2 ThisWorkbook.Path & "\Inspection Test Forms\" & sh.Range("D" & iRow).Value, 17
 
Solution

mageeg

Board Regular
Joined
Jun 6, 2021
Messages
81
Hi, you could try like this.

wdDOC.SaveAs2 ThisWorkbook.Path & "\Inspection Test Forms\" & sh.Range("D" & iRow).Value, 17

Hmm, thanks! that does work - sort of!

Well it saves as PDF and i can open it. However it isn't saving in the background like it was before. So when i click my print button it creates, saves and prints it in the background.

Now when i press it, the save as box comes up and i got to confirm it.

Here's my code if it helps

VBA Code:
Sub SendToWord()

'Declare variables

Dim wd As Object 'Word Application
Dim wdDOC As Object 'word document
Dim iRow As Long 'Variable to hold the starting row and loop through all records in database
Dim sh As Worksheet 'worksheet variable to refer  to where database is stored
Dim myValue As Variant
Dim WorksOrder As String
Dim Found As Boolean


'Start word as new document

Set wd = CreateObject("Word.Application")


'Get user entered WorksOrder Number

WorksOrder = ThisWorkbook.Sheets("PrintDocuments").Range("C3").Value


'Set worksheet

Set sh = ThisWorkbook.Sheets("Database")

iRow = 2 'row in which data starts from in database

Found = False

Do While sh.Range("A" & iRow).Value <> ""  'loop through until no data is found (last row of database)
 
If WorksOrder = sh.Range("D" & iRow).Value Then

Found = True

'opening word template

Set wdDOC = wd.Documents.Add("T:\mageeg\TEST DATA  INSPECTION SCHEDULE Issue 3.docx")

wd.Visible = False

'code to insert values from database to bookmarks in word

wd.Selection.GoTo what:=wdGoToBookmark, Name:="PartNo"
wd.Selection.TypeText Text:=sh.Range("C" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="Serial"
wd.Selection.TypeText Text:=sh.Range("E" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="ModelNo"
wd.Selection.TypeText Text:=sh.Range("O" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="WorksOrderNo"
wd.Selection.TypeText Text:=sh.Range("D" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="MaterialNo"
wd.Selection.TypeText Text:=sh.Range("F" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="MaterialNumber"
wd.Selection.TypeText Text:=sh.Range("F" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="SerialNo"
wd.Selection.TypeText Text:=sh.Range("E" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="ModelNo2"
wd.Selection.TypeText Text:=sh.Range("B" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="Type"
wd.Selection.TypeText Text:=sh.Range("H" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="Size"
wd.Selection.TypeText Text:=sh.Range("I" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="WKPRESS"
wd.Selection.TypeText Text:=sh.Range("J" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="SerialNumber"
wd.Selection.TypeText Text:=sh.Range("G" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="CertDate"
wd.Selection.TypeText Text:=sh.Range("K" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="BatchNo"
wd.Selection.TypeText Text:=sh.Range("L" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="JobNo"
wd.Selection.TypeText Text:=sh.Range("M" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="DateOfManufacture"
wd.Selection.TypeText Text:=Format(sh.Range("N" & iRow).Value, "mm/yyyy")


'code to delete the existing bookmarks from wordfile

On Error Resume Next

wdDOC.Bookmarks("PartNo").Delete
wdDOC.Bookmarks("SerialNo").Delete
wdDOC.Bookmarks("ModelNo").Delete
wdDOC.Bookmarks("WorksOrderNo").Delete
wdDOC.Bookmarks("MaterialNo").Delete
wdDOC.Bookmarks("Serial").Delete
wdDOC.Bookmarks("ModelNo2").Delete
wdDOC.Bookmarks("Type").Delete
wdDOC.Bookmarks("Size").Delete
wdDOC.Bookmarks("WKPRESS").Delete
wdDOC.Bookmarks("SerialNumber").Delete
wdDOC.Bookmarks("CertDate").Delete
wdDOC.Bookmarks("BatchNo").Delete
wdDOC.Bookmarks("JobNo").Delete
wdDOC.Bookmarks("DateOfManufacture").Delete

'save file with new name

wdDOC.SaveAs2 ThisWorkbook.Path & "\Inspection Test Forms\" & sh.Range("D" & iRow).Value, 17

'Print document

wdDOC.PrintOut

'close the word file

wdDOC.Close

'release memory of word doc

Set wdDOC = Nothing


Exit Do

End If

iRow = iRow + 1

Loop

wd.Quit 'close MS Word

Set wd = Nothing 'Release memory allocated to WD

If Found = True Then
MsgBox ("Inspection Test Sheet Created")
Else
MsgBox ("Works Order Number Not Found")
End If

End Sub
 

mageeg

Board Regular
Joined
Jun 6, 2021
Messages
81
Hi, you could try like this.

wdDOC.SaveAs2 ThisWorkbook.Path & "\Inspection Test Forms\" & sh.Range("D" & iRow).Value, 17

Correction,

Running this line now works correctly, and i can open PDF, however when i run this code Excel goes not responding and stays that way till i close it in task manager?
 

mageeg

Board Regular
Joined
Jun 6, 2021
Messages
81

ADVERTISEMENT

Correction,

Running this line now works correctly, and i can open PDF, however when i run this code Excel goes not responding and stays that way till i close it in task manager?
Oh nevermind, it was crashing because the save as box was hiding and it hadn't been clicked!

How do i stop the saveAs box popping up?
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,633
Office Version
  1. 365
Platform
  1. Windows
Are you sure it's that line of code that is causing the SaveAs dialogue and not this line wdDOC.Close?

If it is the one I suspect, try changing it to wdDOC.Close False
 

mageeg

Board Regular
Joined
Jun 6, 2021
Messages
81
Are you sure it's that line of code that is causing the SaveAs dialogue and not this line wdDOC.Close?

If it is the one I suspect, try changing it to wdDOC.Close False
Whoops! my mistake. still learning!

That change fixed it.

thanks for your help! :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,240
Messages
5,768,990
Members
425,508
Latest member
jeremyb1996

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top