Word UserForm search in Excel

jhancock1994 · #1 05-21-2017, 06:42 PM

Essentially the macro should take the user input, search all sheets in the relevant excel file and if there's a match copy a value to a bookmark.

However the macro runs without copying over the data. If the UserForm2.Textbox1.Text line is replaced with the input, e.g. "January" - i.e. skipping the UserForm entry step - it works fine.

Code:

Sub Macro()

Dim xlApp As New Excel.Application
Dim xlWb As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim Result As String

Set xlWb = xlApp.Workbooks.Open("File Name.xlsx")

xlWb.Application.Visible = False
xlWb.Application.WindowState = xlMinimized

For Each xlSheet In xlWb.Sheets
    If xlSheet.Range("B3").Value = UserForm2.TextBox1.Text Then
        Result = xlSheet.Range("B9").Value
        FillBM "bm3", Result
    Exit For
     End If
     
Next xlSheet
   
    xlWb.Close
    xlApp.Quit
  
End Sub

Any ideas how to fix this?

Cheers

gmayor · #2 05-21-2017, 09:14 PM

Try the following instead, which does work provided you have the bookmark in the document, the FillBM function available and the value in TextBox1 does indeed match the value in Cell B3 and there is a value in B9

Code:

Option Explicit


Sub Macro()
Const strWB As String = "C:\Path\Workbookname.xlsx"
Dim xlApp As Object
Dim xlWb As Object
Dim xlSheet As Object
Dim Result As String
Dim oFrm As UserForm2

    Set oFrm = New UserForm2
    With oFrm
    .Show
    
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err Then
        Set xlApp = CreateObject("Excel.Application")
    End If
    On Error GoTo 0
    
    Set xlWb = xlApp.Workbooks.Open(strWB)

    xlWb.Application.Visible = False
    
    For Each xlSheet In xlWb.Sheets
        If xlSheet.Range("B3").Value = .TextBox1.Text Then
            Result = xlSheet.Range("B9").Value
            FillBM "bm3", Result
            Exit For
        End If
    Next xlSheet
    xlWb.Close
    xlApp.Quit
    End With
    Unload oFrm
End Sub

Guessed · #3 05-21-2017, 09:22 PM

Your code looks like it is fragments of larger code.

Where did you create the xlApp object?
Where is the path for the "File Name.xlsx" file?
When was the UserForm2 initialised and TextBox1 populated?

I see Graham has resolved these issues for you - study his code carefully to see what the missing pieces were.

jhancock1994 · #4 05-21-2017, 11:51 PM

Thanks Graham works perfectly

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Excel Userform to Word Template Bookmarks	JCrinage	Excel Programming	1	11-02-2016 07:03 PM
Populate Combobox from Excel into a Word UserForm	ferguson4848	Word VBA	3	10-28-2016 09:05 PM
UserForm Search, delete issues	johndough	Excel Programming	3	05-11-2014 11:44 AM
UserForm Search with multiple TextBoxes	johndough	Excel Programming	0	05-10-2014 12:11 PM
send a string from Excel UserForm to Word	saltlakebuffalo	Excel Programming	1	02-10-2014 11:01 PM