Excel VBA to convert all Word files in a specific folder to PDF [closed]
up vote
-8
down vote
favorite
I've found in below link an Excel vba that converts excel files in a specific directory to pdfs.
I want your help to do the needful changes on this code to make it converts Word documents in a specific directory to pdfs.
credits to:
https://www.listendata.com/2013/02/excel-macro-convert-multiple-excel.html
Code is shown below:
Sub ExcelToPDF2()
Dim Path As String, FilesInPath As String _
, OutputPath As String, OutputPath2 As String
Dim MyFiles() As String, Fnum As Long
Dim Buk As Workbook, BukName As String
Dim CalcMode As Long
Dim sh As Worksheet
Dim StartTime As Date, EndTime As Date
Dim LPosition As Integer
'Specify the path of a folder where all the excel files are stored
StartTime = Timer
Path = Range("G6").Text & ""
OutputPath = Range("G8").Text & ""
FilesInPath = Dir(Path & "*.xl*")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If
Fnum = 0
Do While FilesInPath <> ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set Buk = Nothing
On Error Resume Next
Set Buk = Workbooks.Open(Path & MyFiles(Fnum))
On Error GoTo 0
If Not Buk Is Nothing Then
LPosition = InStr(1, Buk.Name, ".") - 1
BukName = Left(Buk.Name, LPosition)
Buk.Activate
OutputPath2 = OutputPath & BukName & ".pdf"
On Error Resume Next
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=OutputPath2,
_
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
On Error GoTo 0
End If
Buk.Close SaveChanges:=False
Next Fnum
End If
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With
EndTime = Timer
MsgBox "Task succesfully completed in " & Format(EndTime - StartTime,
"0.00") & " seconds"
End Sub
excel vba pdf ms-word type-conversion
closed as too broad by Tim Williams, Cindy Meister, James Z, gnat, K.Dᴀᴠɪs Nov 13 at 0:49
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
add a comment |
up vote
-8
down vote
favorite
I've found in below link an Excel vba that converts excel files in a specific directory to pdfs.
I want your help to do the needful changes on this code to make it converts Word documents in a specific directory to pdfs.
credits to:
https://www.listendata.com/2013/02/excel-macro-convert-multiple-excel.html
Code is shown below:
Sub ExcelToPDF2()
Dim Path As String, FilesInPath As String _
, OutputPath As String, OutputPath2 As String
Dim MyFiles() As String, Fnum As Long
Dim Buk As Workbook, BukName As String
Dim CalcMode As Long
Dim sh As Worksheet
Dim StartTime As Date, EndTime As Date
Dim LPosition As Integer
'Specify the path of a folder where all the excel files are stored
StartTime = Timer
Path = Range("G6").Text & ""
OutputPath = Range("G8").Text & ""
FilesInPath = Dir(Path & "*.xl*")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If
Fnum = 0
Do While FilesInPath <> ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set Buk = Nothing
On Error Resume Next
Set Buk = Workbooks.Open(Path & MyFiles(Fnum))
On Error GoTo 0
If Not Buk Is Nothing Then
LPosition = InStr(1, Buk.Name, ".") - 1
BukName = Left(Buk.Name, LPosition)
Buk.Activate
OutputPath2 = OutputPath & BukName & ".pdf"
On Error Resume Next
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=OutputPath2,
_
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
On Error GoTo 0
End If
Buk.Close SaveChanges:=False
Next Fnum
End If
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With
EndTime = Timer
MsgBox "Task succesfully completed in " & Format(EndTime - StartTime,
"0.00") & " seconds"
End Sub
excel vba pdf ms-word type-conversion
closed as too broad by Tim Williams, Cindy Meister, James Z, gnat, K.Dᴀᴠɪs Nov 13 at 0:49
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
Welcome to Stackoverflow. I'd recommend having a read of the How to ask page. More specifically, you're not likely to get much help if you just ask for code without showing what you've tried, and what specific problems you've encountered.
– Matt
Nov 11 at 0:46
Hi @Matt, appreciate your notice, but I think the question is fair enough specific. you're kindly requested to re-evaluate the question and provide support if any.
– Ready2go
Nov 11 at 0:59
add a comment |
up vote
-8
down vote
favorite
up vote
-8
down vote
favorite
I've found in below link an Excel vba that converts excel files in a specific directory to pdfs.
I want your help to do the needful changes on this code to make it converts Word documents in a specific directory to pdfs.
credits to:
https://www.listendata.com/2013/02/excel-macro-convert-multiple-excel.html
Code is shown below:
Sub ExcelToPDF2()
Dim Path As String, FilesInPath As String _
, OutputPath As String, OutputPath2 As String
Dim MyFiles() As String, Fnum As Long
Dim Buk As Workbook, BukName As String
Dim CalcMode As Long
Dim sh As Worksheet
Dim StartTime As Date, EndTime As Date
Dim LPosition As Integer
'Specify the path of a folder where all the excel files are stored
StartTime = Timer
Path = Range("G6").Text & ""
OutputPath = Range("G8").Text & ""
FilesInPath = Dir(Path & "*.xl*")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If
Fnum = 0
Do While FilesInPath <> ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set Buk = Nothing
On Error Resume Next
Set Buk = Workbooks.Open(Path & MyFiles(Fnum))
On Error GoTo 0
If Not Buk Is Nothing Then
LPosition = InStr(1, Buk.Name, ".") - 1
BukName = Left(Buk.Name, LPosition)
Buk.Activate
OutputPath2 = OutputPath & BukName & ".pdf"
On Error Resume Next
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=OutputPath2,
_
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
On Error GoTo 0
End If
Buk.Close SaveChanges:=False
Next Fnum
End If
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With
EndTime = Timer
MsgBox "Task succesfully completed in " & Format(EndTime - StartTime,
"0.00") & " seconds"
End Sub
excel vba pdf ms-word type-conversion
I've found in below link an Excel vba that converts excel files in a specific directory to pdfs.
I want your help to do the needful changes on this code to make it converts Word documents in a specific directory to pdfs.
credits to:
https://www.listendata.com/2013/02/excel-macro-convert-multiple-excel.html
Code is shown below:
Sub ExcelToPDF2()
Dim Path As String, FilesInPath As String _
, OutputPath As String, OutputPath2 As String
Dim MyFiles() As String, Fnum As Long
Dim Buk As Workbook, BukName As String
Dim CalcMode As Long
Dim sh As Worksheet
Dim StartTime As Date, EndTime As Date
Dim LPosition As Integer
'Specify the path of a folder where all the excel files are stored
StartTime = Timer
Path = Range("G6").Text & ""
OutputPath = Range("G8").Text & ""
FilesInPath = Dir(Path & "*.xl*")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If
Fnum = 0
Do While FilesInPath <> ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set Buk = Nothing
On Error Resume Next
Set Buk = Workbooks.Open(Path & MyFiles(Fnum))
On Error GoTo 0
If Not Buk Is Nothing Then
LPosition = InStr(1, Buk.Name, ".") - 1
BukName = Left(Buk.Name, LPosition)
Buk.Activate
OutputPath2 = OutputPath & BukName & ".pdf"
On Error Resume Next
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=OutputPath2,
_
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
On Error GoTo 0
End If
Buk.Close SaveChanges:=False
Next Fnum
End If
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With
EndTime = Timer
MsgBox "Task succesfully completed in " & Format(EndTime - StartTime,
"0.00") & " seconds"
End Sub
excel vba pdf ms-word type-conversion
excel vba pdf ms-word type-conversion
edited Nov 11 at 0:50
asked Nov 11 at 0:23
Ready2go
73
73
closed as too broad by Tim Williams, Cindy Meister, James Z, gnat, K.Dᴀᴠɪs Nov 13 at 0:49
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
closed as too broad by Tim Williams, Cindy Meister, James Z, gnat, K.Dᴀᴠɪs Nov 13 at 0:49
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
Welcome to Stackoverflow. I'd recommend having a read of the How to ask page. More specifically, you're not likely to get much help if you just ask for code without showing what you've tried, and what specific problems you've encountered.
– Matt
Nov 11 at 0:46
Hi @Matt, appreciate your notice, but I think the question is fair enough specific. you're kindly requested to re-evaluate the question and provide support if any.
– Ready2go
Nov 11 at 0:59
add a comment |
Welcome to Stackoverflow. I'd recommend having a read of the How to ask page. More specifically, you're not likely to get much help if you just ask for code without showing what you've tried, and what specific problems you've encountered.
– Matt
Nov 11 at 0:46
Hi @Matt, appreciate your notice, but I think the question is fair enough specific. you're kindly requested to re-evaluate the question and provide support if any.
– Ready2go
Nov 11 at 0:59
Welcome to Stackoverflow. I'd recommend having a read of the How to ask page. More specifically, you're not likely to get much help if you just ask for code without showing what you've tried, and what specific problems you've encountered.
– Matt
Nov 11 at 0:46
Welcome to Stackoverflow. I'd recommend having a read of the How to ask page. More specifically, you're not likely to get much help if you just ask for code without showing what you've tried, and what specific problems you've encountered.
– Matt
Nov 11 at 0:46
Hi @Matt, appreciate your notice, but I think the question is fair enough specific. you're kindly requested to re-evaluate the question and provide support if any.
– Ready2go
Nov 11 at 0:59
Hi @Matt, appreciate your notice, but I think the question is fair enough specific. you're kindly requested to re-evaluate the question and provide support if any.
– Ready2go
Nov 11 at 0:59
add a comment |
2 Answers
2
active
oldest
votes
up vote
1
down vote
accepted
I've finally found the correct VBA I was looking for:
'In your VBA window go to tools then references and add a reference to
'Microsoft Word
Sub Converter()
Dim cnt As Integer, currfile As String
Dim TrimFile As String, Path As String, FilesInPath As String _
, MyFiles() As String, Fnum As Long
Dim CalcMode As Long, LPosition As Long
Dim StartTime As Date, EndTime As Date
Dim objWord As Word.Application
Dim objDoc As Word.Document
ThisWorkbook.Activate
currfile = ActiveWorkbook.Name
Windows(currfile).Activate
Sheets("Sheet1").Activate
StartTime = Timer
Path = Range("C3").Text & ""
FilesInPath = Dir(Path & "*.doc*")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If
Fnum = 0
Do While FilesInPath <> ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set objWord = CreateObject("Word.Application")
'objWord.Visible = True
On Error Resume Next
Set objDoc = Word.Documents.Open(Path & MyFiles(Fnum))
On Error GoTo 0
If Not objDoc Is Nothing Then
LPosition = InStr(1, objDoc.Name, ".") - 1
TrimFile = Left(objDoc.Name, LPosition)
On Error Resume Next
objDoc.ExportAsFixedFormat OutputFileName:=objDoc.Path & "" & TrimFile & ".pdf",
ExportFormat:=wdExportFormatPDF, _
OpenAfterExport:=False, OptimizeFor:=wdExportOptimizeForPrint, Range:= _
wdExportAllDocument, From:=1, To:=1, Item:=wdExportDocumentContent, _
IncludeDocProps:=True, KeepIRM:=True, CreateBookmarks:= _
wdExportCreateNoBookmarks, DocStructureTags:=True, BitmapMissingFonts:= _
True, UseISO19005_1:=False
End If
objDoc.Close
Next Fnum
End If
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With
objWord.Quit
Set objDoc = Nothing
Set objWord = Nothing
EndTime = Timer
MsgBox " Task succesfully completed in " & Format(EndTime - StartTime, "0.00") & "
seconds"
End Sub
Great that you were able to work it out yourself! I've taken the liberty to remove off-topic commentary from your answer and indented the code for better readability.
– usr2564301
Nov 11 at 12:15
add a comment |
up vote
0
down vote
Honestly, the easiest way I can think of to do this is to just record a macro. If you go to Word->Developer->Record a Macro, you can record the function that you want to do. From there, you would have the code, and you can change certain areas from there. Here is the code I got with a few tweaks to do what I think you are looking for:
Sub Macro1()
'
' Macro1 Macro
'
'
Dim i As Integer, FileLocation As String, WDoc() As Word.Document
Dim FilesInPath As String, Path As String, MyFiles() As String, iend As Integer
Path = "C:..." ' This is where you would like to get the files that need to be exported to .pdfs
NewPath = "C:..." ' This is where you would like to send the exported files
FilesInPath = Dir(Path & "*.doc*")
iend = 0
Do While FilesInPath <> ""
iend = iend + 1
ReDim Preserve MyFiles(1 To iend)
MyFiles(iend) = FilesInPath
FilesInPath = Dir()
Loop
For i = 1 To iend
ReDim Preserve WDoc(i)
Set WDoc(i) = Word.Documents.Open(Path & MyFiles(i))
FileLocation = NewPath & WDoc(i).Name & ".pdf" ' Location and name of new file
WDoc(i).ExportAsFixedFormat OutputFileName:=FileLocation, ExportFormat:=wdExportFormatPDF, _
OpenAfterExport:=False, OptimizeFor:=wdExportOptimizeForPrint, Range:= _
wdExportAllDocument, From:=1, To:=1, Item:=wdExportDocumentContent, _
IncludeDocProps:=True, KeepIRM:=True, CreateBookmarks:= _
wdExportCreateNoBookmarks, DocStructureTags:=True, BitmapMissingFonts:= _
True, UseISO19005_1:=False
WDoc(i).Close
Next i
End Sub
Hi @Parker.R, Thanks for your help. I've tried my best but I could't suit above code to my required output since I have a little experience in coding. would you please edit it in away that it can convert all word files to pdf?, I would highly appreciate your effort.
– Ready2go
Nov 11 at 2:53
Hey @Ready2go, I edited the code above. You will need to change where you would like to import the files and where you would like to export it. If it works, click the check mark on the side for me. It worked for me when I did it from Microsoft Word VBA.
– Parker.R
Nov 11 at 3:27
Hey @Parker.R, It works fine with me on Word, is there any way to make it works on Excel. Actually, I have a project on Excel and it is a part of that project.
– Ready2go
Nov 11 at 7:27
add a comment |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
I've finally found the correct VBA I was looking for:
'In your VBA window go to tools then references and add a reference to
'Microsoft Word
Sub Converter()
Dim cnt As Integer, currfile As String
Dim TrimFile As String, Path As String, FilesInPath As String _
, MyFiles() As String, Fnum As Long
Dim CalcMode As Long, LPosition As Long
Dim StartTime As Date, EndTime As Date
Dim objWord As Word.Application
Dim objDoc As Word.Document
ThisWorkbook.Activate
currfile = ActiveWorkbook.Name
Windows(currfile).Activate
Sheets("Sheet1").Activate
StartTime = Timer
Path = Range("C3").Text & ""
FilesInPath = Dir(Path & "*.doc*")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If
Fnum = 0
Do While FilesInPath <> ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set objWord = CreateObject("Word.Application")
'objWord.Visible = True
On Error Resume Next
Set objDoc = Word.Documents.Open(Path & MyFiles(Fnum))
On Error GoTo 0
If Not objDoc Is Nothing Then
LPosition = InStr(1, objDoc.Name, ".") - 1
TrimFile = Left(objDoc.Name, LPosition)
On Error Resume Next
objDoc.ExportAsFixedFormat OutputFileName:=objDoc.Path & "" & TrimFile & ".pdf",
ExportFormat:=wdExportFormatPDF, _
OpenAfterExport:=False, OptimizeFor:=wdExportOptimizeForPrint, Range:= _
wdExportAllDocument, From:=1, To:=1, Item:=wdExportDocumentContent, _
IncludeDocProps:=True, KeepIRM:=True, CreateBookmarks:= _
wdExportCreateNoBookmarks, DocStructureTags:=True, BitmapMissingFonts:= _
True, UseISO19005_1:=False
End If
objDoc.Close
Next Fnum
End If
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With
objWord.Quit
Set objDoc = Nothing
Set objWord = Nothing
EndTime = Timer
MsgBox " Task succesfully completed in " & Format(EndTime - StartTime, "0.00") & "
seconds"
End Sub
Great that you were able to work it out yourself! I've taken the liberty to remove off-topic commentary from your answer and indented the code for better readability.
– usr2564301
Nov 11 at 12:15
add a comment |
up vote
1
down vote
accepted
I've finally found the correct VBA I was looking for:
'In your VBA window go to tools then references and add a reference to
'Microsoft Word
Sub Converter()
Dim cnt As Integer, currfile As String
Dim TrimFile As String, Path As String, FilesInPath As String _
, MyFiles() As String, Fnum As Long
Dim CalcMode As Long, LPosition As Long
Dim StartTime As Date, EndTime As Date
Dim objWord As Word.Application
Dim objDoc As Word.Document
ThisWorkbook.Activate
currfile = ActiveWorkbook.Name
Windows(currfile).Activate
Sheets("Sheet1").Activate
StartTime = Timer
Path = Range("C3").Text & ""
FilesInPath = Dir(Path & "*.doc*")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If
Fnum = 0
Do While FilesInPath <> ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set objWord = CreateObject("Word.Application")
'objWord.Visible = True
On Error Resume Next
Set objDoc = Word.Documents.Open(Path & MyFiles(Fnum))
On Error GoTo 0
If Not objDoc Is Nothing Then
LPosition = InStr(1, objDoc.Name, ".") - 1
TrimFile = Left(objDoc.Name, LPosition)
On Error Resume Next
objDoc.ExportAsFixedFormat OutputFileName:=objDoc.Path & "" & TrimFile & ".pdf",
ExportFormat:=wdExportFormatPDF, _
OpenAfterExport:=False, OptimizeFor:=wdExportOptimizeForPrint, Range:= _
wdExportAllDocument, From:=1, To:=1, Item:=wdExportDocumentContent, _
IncludeDocProps:=True, KeepIRM:=True, CreateBookmarks:= _
wdExportCreateNoBookmarks, DocStructureTags:=True, BitmapMissingFonts:= _
True, UseISO19005_1:=False
End If
objDoc.Close
Next Fnum
End If
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With
objWord.Quit
Set objDoc = Nothing
Set objWord = Nothing
EndTime = Timer
MsgBox " Task succesfully completed in " & Format(EndTime - StartTime, "0.00") & "
seconds"
End Sub
Great that you were able to work it out yourself! I've taken the liberty to remove off-topic commentary from your answer and indented the code for better readability.
– usr2564301
Nov 11 at 12:15
add a comment |
up vote
1
down vote
accepted
up vote
1
down vote
accepted
I've finally found the correct VBA I was looking for:
'In your VBA window go to tools then references and add a reference to
'Microsoft Word
Sub Converter()
Dim cnt As Integer, currfile As String
Dim TrimFile As String, Path As String, FilesInPath As String _
, MyFiles() As String, Fnum As Long
Dim CalcMode As Long, LPosition As Long
Dim StartTime As Date, EndTime As Date
Dim objWord As Word.Application
Dim objDoc As Word.Document
ThisWorkbook.Activate
currfile = ActiveWorkbook.Name
Windows(currfile).Activate
Sheets("Sheet1").Activate
StartTime = Timer
Path = Range("C3").Text & ""
FilesInPath = Dir(Path & "*.doc*")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If
Fnum = 0
Do While FilesInPath <> ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set objWord = CreateObject("Word.Application")
'objWord.Visible = True
On Error Resume Next
Set objDoc = Word.Documents.Open(Path & MyFiles(Fnum))
On Error GoTo 0
If Not objDoc Is Nothing Then
LPosition = InStr(1, objDoc.Name, ".") - 1
TrimFile = Left(objDoc.Name, LPosition)
On Error Resume Next
objDoc.ExportAsFixedFormat OutputFileName:=objDoc.Path & "" & TrimFile & ".pdf",
ExportFormat:=wdExportFormatPDF, _
OpenAfterExport:=False, OptimizeFor:=wdExportOptimizeForPrint, Range:= _
wdExportAllDocument, From:=1, To:=1, Item:=wdExportDocumentContent, _
IncludeDocProps:=True, KeepIRM:=True, CreateBookmarks:= _
wdExportCreateNoBookmarks, DocStructureTags:=True, BitmapMissingFonts:= _
True, UseISO19005_1:=False
End If
objDoc.Close
Next Fnum
End If
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With
objWord.Quit
Set objDoc = Nothing
Set objWord = Nothing
EndTime = Timer
MsgBox " Task succesfully completed in " & Format(EndTime - StartTime, "0.00") & "
seconds"
End Sub
I've finally found the correct VBA I was looking for:
'In your VBA window go to tools then references and add a reference to
'Microsoft Word
Sub Converter()
Dim cnt As Integer, currfile As String
Dim TrimFile As String, Path As String, FilesInPath As String _
, MyFiles() As String, Fnum As Long
Dim CalcMode As Long, LPosition As Long
Dim StartTime As Date, EndTime As Date
Dim objWord As Word.Application
Dim objDoc As Word.Document
ThisWorkbook.Activate
currfile = ActiveWorkbook.Name
Windows(currfile).Activate
Sheets("Sheet1").Activate
StartTime = Timer
Path = Range("C3").Text & ""
FilesInPath = Dir(Path & "*.doc*")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If
Fnum = 0
Do While FilesInPath <> ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set objWord = CreateObject("Word.Application")
'objWord.Visible = True
On Error Resume Next
Set objDoc = Word.Documents.Open(Path & MyFiles(Fnum))
On Error GoTo 0
If Not objDoc Is Nothing Then
LPosition = InStr(1, objDoc.Name, ".") - 1
TrimFile = Left(objDoc.Name, LPosition)
On Error Resume Next
objDoc.ExportAsFixedFormat OutputFileName:=objDoc.Path & "" & TrimFile & ".pdf",
ExportFormat:=wdExportFormatPDF, _
OpenAfterExport:=False, OptimizeFor:=wdExportOptimizeForPrint, Range:= _
wdExportAllDocument, From:=1, To:=1, Item:=wdExportDocumentContent, _
IncludeDocProps:=True, KeepIRM:=True, CreateBookmarks:= _
wdExportCreateNoBookmarks, DocStructureTags:=True, BitmapMissingFonts:= _
True, UseISO19005_1:=False
End If
objDoc.Close
Next Fnum
End If
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With
objWord.Quit
Set objDoc = Nothing
Set objWord = Nothing
EndTime = Timer
MsgBox " Task succesfully completed in " & Format(EndTime - StartTime, "0.00") & "
seconds"
End Sub
edited Nov 11 at 12:14
usr2564301
17.5k73270
17.5k73270
answered Nov 11 at 11:05
Ready2go
73
73
Great that you were able to work it out yourself! I've taken the liberty to remove off-topic commentary from your answer and indented the code for better readability.
– usr2564301
Nov 11 at 12:15
add a comment |
Great that you were able to work it out yourself! I've taken the liberty to remove off-topic commentary from your answer and indented the code for better readability.
– usr2564301
Nov 11 at 12:15
Great that you were able to work it out yourself! I've taken the liberty to remove off-topic commentary from your answer and indented the code for better readability.
– usr2564301
Nov 11 at 12:15
Great that you were able to work it out yourself! I've taken the liberty to remove off-topic commentary from your answer and indented the code for better readability.
– usr2564301
Nov 11 at 12:15
add a comment |
up vote
0
down vote
Honestly, the easiest way I can think of to do this is to just record a macro. If you go to Word->Developer->Record a Macro, you can record the function that you want to do. From there, you would have the code, and you can change certain areas from there. Here is the code I got with a few tweaks to do what I think you are looking for:
Sub Macro1()
'
' Macro1 Macro
'
'
Dim i As Integer, FileLocation As String, WDoc() As Word.Document
Dim FilesInPath As String, Path As String, MyFiles() As String, iend As Integer
Path = "C:..." ' This is where you would like to get the files that need to be exported to .pdfs
NewPath = "C:..." ' This is where you would like to send the exported files
FilesInPath = Dir(Path & "*.doc*")
iend = 0
Do While FilesInPath <> ""
iend = iend + 1
ReDim Preserve MyFiles(1 To iend)
MyFiles(iend) = FilesInPath
FilesInPath = Dir()
Loop
For i = 1 To iend
ReDim Preserve WDoc(i)
Set WDoc(i) = Word.Documents.Open(Path & MyFiles(i))
FileLocation = NewPath & WDoc(i).Name & ".pdf" ' Location and name of new file
WDoc(i).ExportAsFixedFormat OutputFileName:=FileLocation, ExportFormat:=wdExportFormatPDF, _
OpenAfterExport:=False, OptimizeFor:=wdExportOptimizeForPrint, Range:= _
wdExportAllDocument, From:=1, To:=1, Item:=wdExportDocumentContent, _
IncludeDocProps:=True, KeepIRM:=True, CreateBookmarks:= _
wdExportCreateNoBookmarks, DocStructureTags:=True, BitmapMissingFonts:= _
True, UseISO19005_1:=False
WDoc(i).Close
Next i
End Sub
Hi @Parker.R, Thanks for your help. I've tried my best but I could't suit above code to my required output since I have a little experience in coding. would you please edit it in away that it can convert all word files to pdf?, I would highly appreciate your effort.
– Ready2go
Nov 11 at 2:53
Hey @Ready2go, I edited the code above. You will need to change where you would like to import the files and where you would like to export it. If it works, click the check mark on the side for me. It worked for me when I did it from Microsoft Word VBA.
– Parker.R
Nov 11 at 3:27
Hey @Parker.R, It works fine with me on Word, is there any way to make it works on Excel. Actually, I have a project on Excel and it is a part of that project.
– Ready2go
Nov 11 at 7:27
add a comment |
up vote
0
down vote
Honestly, the easiest way I can think of to do this is to just record a macro. If you go to Word->Developer->Record a Macro, you can record the function that you want to do. From there, you would have the code, and you can change certain areas from there. Here is the code I got with a few tweaks to do what I think you are looking for:
Sub Macro1()
'
' Macro1 Macro
'
'
Dim i As Integer, FileLocation As String, WDoc() As Word.Document
Dim FilesInPath As String, Path As String, MyFiles() As String, iend As Integer
Path = "C:..." ' This is where you would like to get the files that need to be exported to .pdfs
NewPath = "C:..." ' This is where you would like to send the exported files
FilesInPath = Dir(Path & "*.doc*")
iend = 0
Do While FilesInPath <> ""
iend = iend + 1
ReDim Preserve MyFiles(1 To iend)
MyFiles(iend) = FilesInPath
FilesInPath = Dir()
Loop
For i = 1 To iend
ReDim Preserve WDoc(i)
Set WDoc(i) = Word.Documents.Open(Path & MyFiles(i))
FileLocation = NewPath & WDoc(i).Name & ".pdf" ' Location and name of new file
WDoc(i).ExportAsFixedFormat OutputFileName:=FileLocation, ExportFormat:=wdExportFormatPDF, _
OpenAfterExport:=False, OptimizeFor:=wdExportOptimizeForPrint, Range:= _
wdExportAllDocument, From:=1, To:=1, Item:=wdExportDocumentContent, _
IncludeDocProps:=True, KeepIRM:=True, CreateBookmarks:= _
wdExportCreateNoBookmarks, DocStructureTags:=True, BitmapMissingFonts:= _
True, UseISO19005_1:=False
WDoc(i).Close
Next i
End Sub
Hi @Parker.R, Thanks for your help. I've tried my best but I could't suit above code to my required output since I have a little experience in coding. would you please edit it in away that it can convert all word files to pdf?, I would highly appreciate your effort.
– Ready2go
Nov 11 at 2:53
Hey @Ready2go, I edited the code above. You will need to change where you would like to import the files and where you would like to export it. If it works, click the check mark on the side for me. It worked for me when I did it from Microsoft Word VBA.
– Parker.R
Nov 11 at 3:27
Hey @Parker.R, It works fine with me on Word, is there any way to make it works on Excel. Actually, I have a project on Excel and it is a part of that project.
– Ready2go
Nov 11 at 7:27
add a comment |
up vote
0
down vote
up vote
0
down vote
Honestly, the easiest way I can think of to do this is to just record a macro. If you go to Word->Developer->Record a Macro, you can record the function that you want to do. From there, you would have the code, and you can change certain areas from there. Here is the code I got with a few tweaks to do what I think you are looking for:
Sub Macro1()
'
' Macro1 Macro
'
'
Dim i As Integer, FileLocation As String, WDoc() As Word.Document
Dim FilesInPath As String, Path As String, MyFiles() As String, iend As Integer
Path = "C:..." ' This is where you would like to get the files that need to be exported to .pdfs
NewPath = "C:..." ' This is where you would like to send the exported files
FilesInPath = Dir(Path & "*.doc*")
iend = 0
Do While FilesInPath <> ""
iend = iend + 1
ReDim Preserve MyFiles(1 To iend)
MyFiles(iend) = FilesInPath
FilesInPath = Dir()
Loop
For i = 1 To iend
ReDim Preserve WDoc(i)
Set WDoc(i) = Word.Documents.Open(Path & MyFiles(i))
FileLocation = NewPath & WDoc(i).Name & ".pdf" ' Location and name of new file
WDoc(i).ExportAsFixedFormat OutputFileName:=FileLocation, ExportFormat:=wdExportFormatPDF, _
OpenAfterExport:=False, OptimizeFor:=wdExportOptimizeForPrint, Range:= _
wdExportAllDocument, From:=1, To:=1, Item:=wdExportDocumentContent, _
IncludeDocProps:=True, KeepIRM:=True, CreateBookmarks:= _
wdExportCreateNoBookmarks, DocStructureTags:=True, BitmapMissingFonts:= _
True, UseISO19005_1:=False
WDoc(i).Close
Next i
End Sub
Honestly, the easiest way I can think of to do this is to just record a macro. If you go to Word->Developer->Record a Macro, you can record the function that you want to do. From there, you would have the code, and you can change certain areas from there. Here is the code I got with a few tweaks to do what I think you are looking for:
Sub Macro1()
'
' Macro1 Macro
'
'
Dim i As Integer, FileLocation As String, WDoc() As Word.Document
Dim FilesInPath As String, Path As String, MyFiles() As String, iend As Integer
Path = "C:..." ' This is where you would like to get the files that need to be exported to .pdfs
NewPath = "C:..." ' This is where you would like to send the exported files
FilesInPath = Dir(Path & "*.doc*")
iend = 0
Do While FilesInPath <> ""
iend = iend + 1
ReDim Preserve MyFiles(1 To iend)
MyFiles(iend) = FilesInPath
FilesInPath = Dir()
Loop
For i = 1 To iend
ReDim Preserve WDoc(i)
Set WDoc(i) = Word.Documents.Open(Path & MyFiles(i))
FileLocation = NewPath & WDoc(i).Name & ".pdf" ' Location and name of new file
WDoc(i).ExportAsFixedFormat OutputFileName:=FileLocation, ExportFormat:=wdExportFormatPDF, _
OpenAfterExport:=False, OptimizeFor:=wdExportOptimizeForPrint, Range:= _
wdExportAllDocument, From:=1, To:=1, Item:=wdExportDocumentContent, _
IncludeDocProps:=True, KeepIRM:=True, CreateBookmarks:= _
wdExportCreateNoBookmarks, DocStructureTags:=True, BitmapMissingFonts:= _
True, UseISO19005_1:=False
WDoc(i).Close
Next i
End Sub
edited Nov 11 at 3:25
answered Nov 11 at 2:31
Parker.R
64
64
Hi @Parker.R, Thanks for your help. I've tried my best but I could't suit above code to my required output since I have a little experience in coding. would you please edit it in away that it can convert all word files to pdf?, I would highly appreciate your effort.
– Ready2go
Nov 11 at 2:53
Hey @Ready2go, I edited the code above. You will need to change where you would like to import the files and where you would like to export it. If it works, click the check mark on the side for me. It worked for me when I did it from Microsoft Word VBA.
– Parker.R
Nov 11 at 3:27
Hey @Parker.R, It works fine with me on Word, is there any way to make it works on Excel. Actually, I have a project on Excel and it is a part of that project.
– Ready2go
Nov 11 at 7:27
add a comment |
Hi @Parker.R, Thanks for your help. I've tried my best but I could't suit above code to my required output since I have a little experience in coding. would you please edit it in away that it can convert all word files to pdf?, I would highly appreciate your effort.
– Ready2go
Nov 11 at 2:53
Hey @Ready2go, I edited the code above. You will need to change where you would like to import the files and where you would like to export it. If it works, click the check mark on the side for me. It worked for me when I did it from Microsoft Word VBA.
– Parker.R
Nov 11 at 3:27
Hey @Parker.R, It works fine with me on Word, is there any way to make it works on Excel. Actually, I have a project on Excel and it is a part of that project.
– Ready2go
Nov 11 at 7:27
Hi @Parker.R, Thanks for your help. I've tried my best but I could't suit above code to my required output since I have a little experience in coding. would you please edit it in away that it can convert all word files to pdf?, I would highly appreciate your effort.
– Ready2go
Nov 11 at 2:53
Hi @Parker.R, Thanks for your help. I've tried my best but I could't suit above code to my required output since I have a little experience in coding. would you please edit it in away that it can convert all word files to pdf?, I would highly appreciate your effort.
– Ready2go
Nov 11 at 2:53
Hey @Ready2go, I edited the code above. You will need to change where you would like to import the files and where you would like to export it. If it works, click the check mark on the side for me. It worked for me when I did it from Microsoft Word VBA.
– Parker.R
Nov 11 at 3:27
Hey @Ready2go, I edited the code above. You will need to change where you would like to import the files and where you would like to export it. If it works, click the check mark on the side for me. It worked for me when I did it from Microsoft Word VBA.
– Parker.R
Nov 11 at 3:27
Hey @Parker.R, It works fine with me on Word, is there any way to make it works on Excel. Actually, I have a project on Excel and it is a part of that project.
– Ready2go
Nov 11 at 7:27
Hey @Parker.R, It works fine with me on Word, is there any way to make it works on Excel. Actually, I have a project on Excel and it is a part of that project.
– Ready2go
Nov 11 at 7:27
add a comment |
Welcome to Stackoverflow. I'd recommend having a read of the How to ask page. More specifically, you're not likely to get much help if you just ask for code without showing what you've tried, and what specific problems you've encountered.
– Matt
Nov 11 at 0:46
Hi @Matt, appreciate your notice, but I think the question is fair enough specific. you're kindly requested to re-evaluate the question and provide support if any.
– Ready2go
Nov 11 at 0:59