brent.fraser
08-12-2013, 09:21 AM
Hi all,I have an Excel Speadsheet that gathers content control information and places the information into a line in the Excel file.Things are working but I get the "Run-time Error 5 "Invalid Procedure Call or Argument"" error when I run it and I am not sure why. I tried to debug the code and I can run it to a point without the error but I can't pin-point the culprit.The code is below:
Option Explicit
Public strPath As String, strFile As String, strWordDocument As String
Public Sub ReplaceYes()
Cells.Replace What:=ChrW(9746), Replacement:="Yes", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
Public Sub ReplaceNo()
Cells.Replace What:=ChrW(9744), Replacement:="No", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
Public Sub GetFilePath()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.AllowMultiSelect = False
fd.Title = "Select the Vendor Questionnaire to use"
fd.Filters.Add "Documents", "*.doc; *.docm; *.docx", 1
If fd.Show Then
strWordDocument = fd.SelectedItems(1)
strPath = strWordDocument
strFile = Right(strPath, Len(strPath) - InStrRev(strPath, "\"))
MsgBox strWordDocument
End If
End Sub
Sub GetFormData()
'Note: this code requires a reference to the Word object model
GetFilePath
Application.ScreenUpdating = False
Dim wdApp As New Word.Application
Dim wdDoc As Word.Document
Dim CCtrl As Word.ContentControl
Dim WkSht As Worksheet, i As Long, j As Long
Set WkSht = ActiveSheet
i = WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row
MsgBox strFile
While strFile ""
i = i + 1
Set wdDoc = wdApp.Documents.Open(Filename:=strWordDocument, AddToRecentFiles:=False, Visible:=False)
With wdDoc
j = 0
For Each CCtrl In .ContentControls
j = j + 1
WkSht.Cells(i, j) = CCtrl.Range.Text
Next
End With
Call ReplaceYes
Call ReplaceNo
wdDoc.Close SaveChanges:=False
strWordDocument = Dir()
Wend
wdApp.Quit Set wdDoc = Nothing: Set wdApp = Nothing: Set WkSht = Nothing
Application.ScreenUpdating = True
End Sub
The sub-functions work (ReplaceYes, ReplaceNo, and GetFilePath) so I know the issue is somewhere in "GetFormData."Anyway, I know I am missing something but I am not sure yet. I am still trying to solve it.thanks everyone.
Option Explicit
Public strPath As String, strFile As String, strWordDocument As String
Public Sub ReplaceYes()
Cells.Replace What:=ChrW(9746), Replacement:="Yes", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
Public Sub ReplaceNo()
Cells.Replace What:=ChrW(9744), Replacement:="No", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
Public Sub GetFilePath()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.AllowMultiSelect = False
fd.Title = "Select the Vendor Questionnaire to use"
fd.Filters.Add "Documents", "*.doc; *.docm; *.docx", 1
If fd.Show Then
strWordDocument = fd.SelectedItems(1)
strPath = strWordDocument
strFile = Right(strPath, Len(strPath) - InStrRev(strPath, "\"))
MsgBox strWordDocument
End If
End Sub
Sub GetFormData()
'Note: this code requires a reference to the Word object model
GetFilePath
Application.ScreenUpdating = False
Dim wdApp As New Word.Application
Dim wdDoc As Word.Document
Dim CCtrl As Word.ContentControl
Dim WkSht As Worksheet, i As Long, j As Long
Set WkSht = ActiveSheet
i = WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row
MsgBox strFile
While strFile ""
i = i + 1
Set wdDoc = wdApp.Documents.Open(Filename:=strWordDocument, AddToRecentFiles:=False, Visible:=False)
With wdDoc
j = 0
For Each CCtrl In .ContentControls
j = j + 1
WkSht.Cells(i, j) = CCtrl.Range.Text
Next
End With
Call ReplaceYes
Call ReplaceNo
wdDoc.Close SaveChanges:=False
strWordDocument = Dir()
Wend
wdApp.Quit Set wdDoc = Nothing: Set wdApp = Nothing: Set WkSht = Nothing
Application.ScreenUpdating = True
End Sub
The sub-functions work (ReplaceYes, ReplaceNo, and GetFilePath) so I know the issue is somewhere in "GetFormData."Anyway, I know I am missing something but I am not sure yet. I am still trying to solve it.thanks everyone.