#1
|
|||
|
|||
Word UserForm search in Excel
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 Cheers |
#2
|
||||
|
||||
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
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#3
|
||||
|
||||
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.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia Last edited by Guessed; 05-21-2017 at 09:25 PM. Reason: Seen graham's code |
#4
|
|||
|
|||
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 |