Get Folder Function help - Word to excel import

[ieishab6]

Error: Object variable or With block variable not set

I am creating a macro that allows me to import a word form document into excel. I am receiving the following error message and I am unsure of how to correct the error:

Error: Object variable or With block variable not set

This is my code:

Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
oFolder = Nothing
End Function

[arangogs]

Hi,

You need to enter Set before oFolder (see below)

Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)

Set is always used when assigning to an object.

[ieishab6]

Hi,

And thank you! When I try to run it, it still doesn't work. Below is the code:

Sub GetFormData()
'Note: this code requires a reference to the Word object model
Application.ScreenUpdating = False
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim CCtrl As Word.FormField
Dim strFolder As String, strFile As String
Dim WkSht As Worksheet, i As Long, j As Long
strFolder = GetFolder
If strFolder = "" Then Exit Sub
Set WkSht = ActiveSheet
i = WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row
strFile = Dir(strFolder & "\*.docx", vbNormal)
While strFile <> ""
i = i + 1
Set wdDoc = wdApp.Documents.Open(Filename:=strFolder & "" & strFile, AddToRecentFiles:=False, Visible:=False)
With wdDoc
j = 0
For Each FmFld In .FormFields
j = j + 1
WkSht.Cells(i, j) = FmFld.Result
Next
End With
wdDoc.Close SaveChanges:=False
strFile = Dir()
Wend
wdApp.Quit
Set wdDoc = Nothing: Set wdApp = Nothing: Set WkSht = Nothing
Application.ScreenUpdating = True
End Sub

Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function

[arangogs]

Hi,
i have highlighted in bold, the changes i have made to your code.

as you will see, i have added the word 'New' to your wdApp object, creating an instance(setting in) in preparation of use.

I would also a backslash '\' to the document path, as i think the folder return, may omit the trailing backslash.


hope this helps, i cannot replicate what you have on your word document, therefore i a hoping this section works ok.




Hi,

And thank you! When I try to run it, it still doesn't work. Below is the code:

Sub GetFormData()
'Note: this code requires a reference to the Word object model
Application.ScreenUpdating = False
Dim wdApp As new Word.Application
Dim wdDoc As Word.Document
Dim CCtrl As Word.FormField
Dim strFolder As String, strFile As String
Dim WkSht As Worksheet, i As Long, j As Long
strFolder = GetFolder
If strFolder = "" Then Exit Sub
Set WkSht = ActiveSheet
i = WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row
strFile = Dir(strFolder & "\*.docx", vbNormal)
While strFile <> ""
i = i + 1
Set wdDoc = wdApp.Documents.Open(Filename:=strFolder & "" & strFile, AddToRecentFiles:=False, Visible:=False)

With wdDoc
j = 0
For Each FmFld In .FormFields
j = j + 1
WkSht.Cells(i, j) = FmFld.Result
Next
End With
wdDoc.Close SaveChanges:=False
strFile = Dir()
Wend
wdApp.Quit
Set wdDoc = Nothing: Set wdApp = Nothing: Set WkSht = Nothing
Application.ScreenUpdating = True
End Sub

Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function