Consulting

Results 1 to 4 of 4

Thread: Get Folder Function help - Word to excel import

  1. #1

    Question Get Folder Function help - Word to excel import

    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

  2. #2
    VBAX Regular arangogs's Avatar
    Joined
    Jun 2009
    Location
    Ayrshire, Scotland
    Posts
    18
    Location
    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.

  3. #3
    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

  4. #4
    VBAX Regular arangogs's Avatar
    Joined
    Jun 2009
    Location
    Ayrshire, Scotland
    Posts
    18
    Location
    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.



    Quote Originally Posted by ieishab6 View Post
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •