PDA

View Full Version : search in another excel file with VBA



joskjos
02-01-2016, 07:32 AM
I have the following code:
This code is good only if I keep an empty column in my search Excel file then must be beaten anyone have an idea how to do this?
And what is will is: there must be sought in multiple tabs


Private Sub CommandButton1_Click()
ChDrive "C:\"
FileToOpen = Application.GetOpenFilename _
(Title:="Please select Design Summary", _
FileFilter:="Excel Files *.xlsm (*.xlsm),")
If FileToOpen = False Then
MsgBox "No file specified!", vbExclamation, "LOOK OUT !"
Exit Sub
Else
Workbooks.Open Filename:=FileToOpen, UpdateLinks:=0
With ActiveWorkbook
ar = .Sheets(1).UsedRange
.Close 0
End With
End If
For j = 1 To UBound(ar, 1)
For jj = 1 To UBound(ar, 2)
If Left(ar(j, jj), 5) = "name:" Then c00 = c00 & "|" & Replace(ar(j, jj), "name: ", "")
Next jj
Next j
ComboBox1.List = Split(Mid(c00, 2), "|")
End Sub

And it is read by a Combobox :


[Private Sub ComboBox1_Change()
For j = 1 To UBound(ar)
c01 = c01 & "|" & ar(j, ComboBox1.ListIndex + 1)
Next j
Sheets(1).[A2].Resize(UBound(Split(Mid(c01, 2), "|")) + 1, 1) = Application.Transpose(Split(Mid(c01, 2), "|"))
End Sub

snb
02-03-2016, 04:50 AM
Crossposted:

http://www.helpmij.nl/forum/showthread.php/882859-zoeken-in-ander-bestand-met-VBA