Vlookup open file on background

[remco77a]

I have one last question I can't get out of, maybe someone is kind enough to help me one more time.
With the help of VLOOKUP I search for my data in another workbook, when I start searching, the "database" file is opened for a few secconds and closed immediately.


Is there a possibility in VBA that the file database can be opened in the background or can be read out unopened and the data is written to my textboxes?

Private Sub CmdFind_Click()




Dim Search As Variant


Dim book1 As Workbook
Dim extwbk As Workbook
Dim x As Range




Set book1 = ThisWorkbook
Set extwbk = Workbooks.Open("C:\Users\Desktop\excel\database.xlsm")
Set x = extwbk.Worksheets("data").Range("A7:x10000")


Search = txtfind.Value
If IsNumeric(Search) Then Search = Val(Search)
If IsError(Application.Match(Search, x.Columns(1), 0)) Then
    MsgBox "wrong number", vbCritical, Search
    Exit Sub
End If
txtname.Text = Application.WorksheetFunction.VLookup(Search, x, 18, False)
Txtstreet.Text = Application.WorksheetFunction.VLookup(Search, x, 2, False)
txtnumber.Text = Application.WorksheetFunction.VLookup(Search, x, 3, False)
txtpostcode.Text = Application.WorksheetFunction.VLookup(Search, x, 4, False)
Txtprovince.Text = Application.WorksheetFunction.VLookup(Search, x, 5, False)
txtcity.Text = Application.WorksheetFunction.VLookup(Search, x, 6, False)
txtarea.Text = Application.WorksheetFunction.VLookup(Search, x, 7, False)
Txtlastname.Text = Application.WorksheetFunction.VLookup(Search, x, 8, False)
Txtremark.Text = Application.WorksheetFunction.VLookup(Search, x, 9, False)
txtopmerking.Text = Application.WorksheetFunction.VLookup(Search, x, 10, False)
txtFoto.Text = Application.WorksheetFunction.VLookup(Search, x, 11, False)
txtcount.Text = Application.WorksheetFunction.VLookup(Search, x, 12, False)




extwbk.Close savechanges:=False


End Sub

[p45cal]

If you're going to be opening and closing an external file on every click of the search button it's a bit resource-hungry.
I suggest instead to open it once, grab the data, then close it. If you only need to use the data when the userform is active, you could do this as part of the userform's initialise event, store the data in an array (I've called it y) which you've declared as a global variable, at the top of the userform's code-module just below any Option Explicit statement with Dim y or (Public y if you might use it elsewhere).
Then the initialise event code can contain:

Set xlApp = New Excel.Application 'it remains invisible.
With xlApp
  Set extwbk = .Workbooks.Open("C:\Users\Desktop\excel\database.xlsm")
  y = extwbk.Worksheets("data").Range("A7:x10000").Value    'if you do this sort of thing you can do all your searches in-memory on the y array - it's a lot faster and the external workbook can be closed.See below
  'do anything else here while the workbook is open before closing it and quitting the Excel instance holding it:
  .DisplayAlerts = False
  .Quit
End With
Set xlApp = Nothing

The your Click event might be:

Private Sub CmdFind_Click()
Dim Search As Variant

Search = txtfind.Value
If IsNumeric(Search) Then Search = Val(Search)
myRow = Application.Match(Search, Application.Index(y, 0, 1), 0)
If IsError(myRow) Then
  MsgBox "wrong number", vbCritical, Search
  Exit Sub
End If
txtname.Text = y(myRow, 18)
Txtstreet.Text = y(myRow, 2)
txtnumber.Text = y(myRow, 3)
txtpostcode.Text = y(myRow, 4)
Txtprovince.Text = y(myRow, 5)
txtcity.Text = y(myRow, 6)
txtarea.Text = y(myRow, 7)
Txtlastname.Text = y(myRow, 8)
Txtremark.Text = y(myRow, 9)
txtopmerking.Text = y(myRow, 10)
txtFoto.Text = y(myRow, 11)
txtcount.Text = y(myRow, 12)
End Sub

I can't test this easily so you've still some work to do.

ps. You might not need to open the external workbook in a different instance of Excel, you might get away with opening it in the same instance but change its Window's .Visible property to FALSE.

[p45cal]

or can be read out unopened

VBA can't easily get data from a closed workbook (but check out the new Power queries in the Data ribbon, you might be able to set up a refreshable query to get the necessary data into a table in your active workbook), but a formula in a cell can, so you could do something like:

Sub NotRecommended()
shtAddress = "A7:x10000"
a = "'C:\Users\Desktop\excel\[database.xlsm]data'!" & shtAddress
With Range(shtAddress)
  r = .Rows.Count
  c = .Columns.Count
End With
myFormula = Application.ConvertFormula(a, xlA1, xlR1C1, True)
Set NewSht = ThisWorkbook.Sheets.Add(after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
With NewSht.Cells(1).Resize(r, c)
  .FormulaArray = "=" & myFormula
  .value = .value 'turns them from formulae into plain values.
  'y = .Value 'puts the values into an array.
End With
'NewSht.Delete
End Sub

but be aware that blank cells in the external workbook will be represented by zeroes.

[remco77a]

If you're going to be opening and closing an external file on every click of the search button it's a bit resource-hungry.
I suggest instead to open it once, grab the data, then close it. If you only need to use the data when the userform is active, you could do this as part of the userform's initialise event, store the data in an array (I've called it y) which you've declared as a global variable, at the top of the userform's code-module just below any Option Explicit statement with Dim y or (Public y if you might use it elsewhere).
Then the initialise event code can contain:

Set xlApp = New Excel.Application 'it remains invisible.
With xlApp
  Set extwbk = .Workbooks.Open("C:\Users\Desktop\excel\database.xlsm")
  y = extwbk.Worksheets("data").Range("A7:x10000").Value    'if you do this sort of thing you can do all your searches in-memory on the y array - it's a lot faster and the external workbook can be closed.See below
  'do anything else here while the workbook is open before closing it and quitting the Excel instance holding it:
  .DisplayAlerts = False
  .Quit
End With
Set xlApp = Nothing

The your Click event might be:

Private Sub CmdFind_Click()
Dim Search As Variant

Search = txtfind.Value
If IsNumeric(Search) Then Search = Val(Search)
myRow = Application.Match(Search, Application.Index(y, 0, 1), 0)
If IsError(myRow) Then
  MsgBox "wrong number", vbCritical, Search
  Exit Sub
End If
txtname.Text = y(myRow, 18)
Txtstreet.Text = y(myRow, 2)
txtnumber.Text = y(myRow, 3)
txtpostcode.Text = y(myRow, 4)
Txtprovince.Text = y(myRow, 5)
txtcity.Text = y(myRow, 6)
txtarea.Text = y(myRow, 7)
Txtlastname.Text = y(myRow, 8)
Txtremark.Text = y(myRow, 9)
txtopmerking.Text = y(myRow, 10)
txtFoto.Text = y(myRow, 11)
txtcount.Text = y(myRow, 12)
End Sub

I can't test this easily so you've still some work to do.

ps. You might not need to open the external workbook in a different instance of Excel, you might get away with opening it in the same instance but change its Window's .Visible property to FALSE.

Thank you for your help, I am very happy with that. Now I have recently become familiar with VBA and am trying to understand what I am making and what you are telling. I still cannot manage it completely independently. would you like to help me again?


As you indicated, I modified userform's initialization event with the code below.
I have adjusted my Vlookup as you indicated, but the search value is not found. It is quite possible that it is up to me, but would like to learn from it.

Private Sub UserForm_Initialize()
Dim Y As Variant


Set xlApp = New Excel.Application 'it remains invisible.
With xlApp
  Set extwbk = .Workbooks.Open("C:\Users\Desktop\excel\database.xlsm")
  Y = extwbk.Worksheets("data").Range("A7:x10000").Value    'if you do this sort of thing you can do all your searches in-memory on the y array - it's a lot faster and the external workbook can be closed.See below
  'do anything else here while the workbook is open before closing it and quitting the Excel instance holding it:
  .DisplayAlerts = False
  .Quit
  
End With
 
 Call ResetSame_Form


End Sub

[p45cal]

You've Dimmed y at the top of the initialise event code rather than at the top of the code-module.

[remco77a]

Thanks a lot it's works!