Hopefully a quick one.
I through together a macro for splitting a word file (merged file of letters) into individual pdfs and naming them based on a ref number included in the file.
'Start split
For Each sec In ActiveDocument.Sections
Set rng = sec.Range 'Range of section
SecText = sec.Range.Text 'All text within section
SecTextPosition = InStr(SecText, "Our ref: ") 'Position of "Out ref: " within the section
strCDRS = Mid(SecText, (SecTextPosition + 9), 16) 'Retrieved CDRS reference
If sec.Index < ActiveDocument.Sections.Count Then
rng.MoveEnd wdCharacter, -1 'drop trailing section break
End If
rng.ExportAsFixedFormat strFolder & "\" & Replace(strCDRS, "/", "-") & "-" & strLetterType & ".pdf", wdExportFormatPDF
Set rng = Nothing
Next sec
This works perfectly when embedded in the word file. However, when embedding in the excel file and referencing the document, I get a type mismatch on the:
Set rng = sec.Range 'Range of section
Look at the value of sec.Range, it looks fine, so it appears to be something to do with the rng range object. Am I missing something obvious?
Full draft code as follows:
Sub SplitExport()
Dim sec As Section
Dim rng As Range
Dim strSplitFile As String
Dim strCDRS As String
Dim strLetterType As String
Dim strFolder As String
Dim SecText As String
Dim SecTextPosition As Long
Dim strfldr As FileDialog
Dim strfile As FileDialog
Dim WordFile As Word.Document
'Set word application
Set wordapp = CreateObject("word.Application")
'Pick file to split
Set strfile = Application.FileDialog(msoFileDialogFilePicker)
With strfile
.Title = "Select a file to split"
.AllowMultiSelect = False
.Show
strSplitFile = .SelectedItems(1)
End With
'Check if a file was selected
If strSplitFile = "" Then
MsgBox "Cannot proceed without file selection", vbOKOnly + vbCritical, "Error"
Exit Sub
End If
'Set Letter Type String
strLetterType = InputBox("Please enter letter code...")
If strLetterType = "" Then
MsgBox "Cannot proceed without letter code", vbOKOnly + vbCritical, "Error"
Exit Sub
End If
'Set folder to save PDFs to
Set strfldr = Application.FileDialog(msoFileDialogFolderPicker)
With strfldr
.Title = "Select a folder to save split files"
.AllowMultiSelect = False
.Show
strFolder = .SelectedItems(1)
End With
'Check a folder was selected
If strFolder = "" Then
MsgBox "Cannot proceed without folder selection", vbOKOnly + vbCritical, "Error"
Exit Sub
End If
'Open file to split
Set WordFile = wordapp.Documents.Open(strSplitFile)
WordFile.Activate
'Start split
For Each sec In ActiveDocument.Sections
Set rng = sec.Range 'Range of section
SecText = sec.Range.Text 'All text within section
SecTextPosition = InStr(SecText, "Our ref: ") 'Position of "Out ref: " within the section
strCDRS = Mid(SecText, (SecTextPosition + 9), 16) 'Retrieved reference
If sec.Index < ActiveDocument.Sections.Count Then
rng.MoveEnd wdCharacter, -1 'drop trailing section break
End If
rng.ExportAsFixedFormat strFolder & "\" & Replace(strCDRS, "/", "-") & "-" & strLetterType & ".pdf", wdExportFormatPDF
Set rng = Nothing
Next sec
End Sub
rng
as aWord.Range
(if using early binding) or as anObject
(if using late binding, which you are) or else it will be defined as anExcel.Range
. – YowE3K