To all the forum members and excel gurus! I hope someone can help.
I'm trying to put together something to try and convert Word to PDF.
The file path in B1 and I referenced it as the path in the code.
The files I am trying to open are rather similar, all starting with "Doc" and then the yyyymmdd (I can change this when needed), then another dot (".") and then a string of numbers (which I have listed in Column D).
I list the "new" filenames in column E. What I want to do is get Word to open those files, format it, then save as a PDF.
This is what I have so far. I can get the first document in row 2 to open and format but cannot do anything further. Where have I gone wrong?
Also, in the event I put in the wrong number (and the file does not exist), I want to put a message in the corresponding F cell so I can see it when the macro finishes running. I don't know how to error this.
Can someone help me?
I'm trying to put together something to try and convert Word to PDF.
The file path in B1 and I referenced it as the path in the code.
The files I am trying to open are rather similar, all starting with "Doc" and then the yyyymmdd (I can change this when needed), then another dot (".") and then a string of numbers (which I have listed in Column D).
I list the "new" filenames in column E. What I want to do is get Word to open those files, format it, then save as a PDF.
This is what I have so far. I can get the first document in row 2 to open and format but cannot do anything further. Where have I gone wrong?
VBA Code:
Sub WordToPDF()
Dim n As Long
Dim strPath As String
Dim TheFile As String
Dim PathFile As String
Dim NewFile As String
Dim objWord
Dim objDoc
Dim objSelection
strPath = Sheets("Sheet1").Range("B1")
For n = 2 To 10
TheFile = "Doc20211025." & Sheets("Sheet1").Cells(n, 4) & ".docx"
PathFile = strPath & TheFile
NewFile = Sheets("Sheet1").Cells(n, 5)
Set objWord = CreateObject("Word.Application")
Set objDoc = objWord.Documents.Open(PathFile)
objWord.Visible = True
Set objSelection = objWord.Selection
objSelection.WholeStory
With objSelection
.Font.Name = "Arial"
.Font.Size = 10
End With
On Error GoTo 0
ActiveDocument.ExportAsFixedFormat _
OutputFileName:=strPath & NewFile & ".pdf", _
ExportFormat:=wdExportFormatPDF
On Error GoTo 0
Next
End SubAlso, in the event I put in the wrong number (and the file does not exist), I want to put a message in the corresponding F cell so I can see it when the macro finishes running. I don't know how to error this.
Can someone help me?
