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









share|improve this 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















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









share|improve this 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













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









share|improve this question















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








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


















  • 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












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





share|improve this answer























  • 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


















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





share|improve this answer























  • 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


















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





share|improve this answer























  • 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















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





share|improve this answer























  • 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













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





share|improve this answer














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






share|improve this answer














share|improve this answer



share|improve this answer








edited Nov 11 at 12:14









usr2564301

17.1k73269




17.1k73269










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


















  • 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












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





share|improve this answer























  • 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















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





share|improve this answer























  • 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













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





share|improve this answer














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






share|improve this answer














share|improve this answer



share|improve this answer








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


















  • 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



Popular posts from this blog

Full-time equivalent

さくらももこ

13 indicted, 8 arrested in Calif. drug cartel investigation