gravanoc
Active Member
- Joined
- Oct 20, 2015
- Messages
- 348
- Office Version
- 365
- Platform
- Windows
- Mobile
I'm experiencing an issue with exporting the contents of a Word document to PDF. After pasting a table from Outlook into Excel, certain parts of the table are retained, then the new table is pasted into Word. The Word document is saved using a specific filename, the date, and a number to preserve uniqueness. Depending on a True/False condition set by a checkbox, the program will either create a PDF from the Word document or it will stop so the user can edit the table further. In the latter case, a button appears that will finish the remaining steps of exporting the PDF via Word to a specified folder.
If there is no further editing to be done to the Word document, the program runs without incident by creating both the Word doc & PDF, but if the PDF export is done separately, then Excel will appear to lock up. In reality a message appears stating that "XXX.docx is locked for editing", and gives a list of options. I would like to avoid this.
I noticed that the part of my script checking for an existing instance of Word doesn't find it if it is open, so it always defaults to using CreateObject. This seems to be the problem, but it could be something else. I've included both subroutines here, but it's the second one causing the issue.
If there is no further editing to be done to the Word document, the program runs without incident by creating both the Word doc & PDF, but if the PDF export is done separately, then Excel will appear to lock up. In reality a message appears stating that "XXX.docx is locked for editing", and gives a list of options. I would like to avoid this.
I noticed that the part of my script checking for an existing instance of Word doesn't find it if it is open, so it always defaults to using CreateObject. This seems to be the problem, but it could be something else. I've included both subroutines here, but it's the second one causing the issue.
VBA Code:
Sub CopyToWord(ByVal rngTable As Range)
Dim wordApp As Object, wordDoc As Object
Dim path As String, fileName As String
Dim iteration As Long
Dim currDate As Date
pathWord = wksProcess.Range("H11").Value
pathPDF = wksProcess.Range("H12").Value
currDate = wksProcess.Range("CurrDate").Value
If Day(currDate) < Day(Now) Then
wksProcess.Range("Iteration") = 1
wksProcess.Range("CurrDate") = Format(Now, "mm/dd/yyyy")
End If
currDate = wksProcess.Range("CurrDate").Value
iteration = wksProcess.Range("Iteration").Value
fileName = "Export " & Format(currDate, "mm-dd-yyyy") & " - " & iteration
wksProcess.Range("Iteration").Value = iteration + 1
On Error Resume Next
Set wordApp = GetObject("Word.Application")
If Err.Number <> 0 Then
Set wordApp = CreateObject("Word.Application")
End If
On Error GoTo 0
wordApp.Visible = True
Set wordDoc = wordApp.Documents.Add
rngTable.Copy
wordApp.Activate
wordDoc.Activate
wordApp.Selection.PasteExcelTable True, False, True
wordDoc.SaveAs2 fileName:=pathWord & Application.PathSeparator & fileName, FileFormat:=wdFormatDocumentDefault
If wksProcess.Range("Conversion") = True Then
wordDoc.SaveAs2 fileName:=pathPDF & Application.PathSeparator & fileName, FileFormat:=wdFormatPDF
wordApp.Quit
If wksProcess.Range("Messages") = False Then MsgBox "Word & PDF files successfully created in the specified folders.", vbOKOnly
Else
wksProcess.cmdPDF.Enabled = True
If wksProcess.Range("Messages") = False Then MsgBox "Word file successfully created in the specified folder. " _
& "To export the Word table to PDF please press the Export to PDF button when ready.", vbOKOnly
End If
Set wordApp = Nothing
End Sub
Sub ExportFileToPDF()
Dim wordApp As Object, wordDoc As Object
Dim iteration As Long
Dim pathWord As String, pathPDF As String
Dim fileName As String
pathWord = wksProcess.Range("H11").Value
pathPDF = wksProcess.Range("H12").Value
currDate = wksProcess.Range("CurrDate").Value
iteration = wksProcess.Range("Iteration") - 1
fileName = "Export " & Format(currDate, "mm-dd-yyyy") & " - " & iteration
On Error Resume Next
Set wordApp = GetObject("Word.Application")
If Err.Number <> 0 Then
Set wordApp = CreateObject("Word.Application")
End If
On Error GoTo 0
wordApp.Activate
' The next line is where it locks up and that dialog with options appears.
Set wordDoc = wordApp.Documents.Open(pathWord & Application.PathSeparator & fileName & ".docx")
wordDoc.Activate
wordDoc.SaveAs2 fileName:=pathPDF & Application.PathSeparator & fileName, FileFormat:=wdFormatPDF
If wksProcess.Range("Messages") = False Then MsgBox "PDF exported to specified folder.", vbOKOnly
Set wordApp = Nothing
End Sub
