Solved: Search Excel for data with leading zero

[mdmackillop]

I'm wanting to search a number formatted column in Excel ("0000") format for values obtained using FileSearch (simulated by the array), but I'm not getting a result from the numbers 0105 and 0205. A simple Excel data file is attached.


Option Explicit
Sub GetData()
Dim Order
Dim i As Long
Dim Nm As String, Add1 As String, Add2 As String, Details As String
Dim MyXL As Object ' Variable to hold reference
Dim Arr, a
Arr = Array("0105.xls", "0205.xls", "1105.xls", "1205.xls")
On Error Resume Next ' Defer error trapping.
'Open Audit workbook
Set MyXL = GetObject(, "Excel.Application")
Set MyXL = GetObject("C:\AAA\Test.XLS")
Selection.WholeStory
Selection.Delete Unit:=wdCharacter, Count:=1
For Each a In Arr
Order = Format(Split(a, ".")(0), "0000")
With MyXL.Sheets("List").Columns(1)
Nm = Trim(.Find(Order).Offset(, 1))
Add1 = Trim(.Find(Order).Offset(, 2))
End With
Details = "Ref. " & Order & vbCr & Nm & " - " & Add1
Selection.TypeText Details
Selection.TypeParagraph
Next
Selection.HomeKey Unit:=wdStory
Set MyXL = Nothing ' Release reference to the
End Sub

[Bob Phillips]

Malcolm, How about

Order = Format(Split(a, ".")(0), "0")

[mdmackillop]

Hi Bob,
That is finding the items, but I'm needing the equivalent of xlWhole otherwise it is finding 105 in 2105. Also, the data won't be in order.
As an aside, it would be useful to return the Excel range, as in
Set c = .Find(...), but I've not found the correct coding for that either.

[Bob Phillips]

Malcolm,

If I understand the logic, I think this should do it



Const xlWhole As Long = 1

Sub GetData()
Dim Order As String
Dim i As Long
Dim Nm As String, Add1 As String, Add2 As String, Details As String
Dim MyXL As Object ' Variable to hold reference
Dim Arr, a
Dim oCell As Object
Arr = Array("0105.xls", "0205.xls", "1105.xls", "1205.xls")
On Error Resume Next ' Defer error trapping.
'Open Audit workbook
Set MyXL = GetObject(, "Excel.Application")
Set MyXL = GetObject("C:\Test.XLS")
Selection.WholeStory
Selection.Delete Unit:=wdCharacter, Count:=1
For Each a In Arr
Order = Format(Split(a, ".")(0), "0")
With MyXL.Sheets("List").Columns(1)
Set oCell = Nothing
Set oCell = .Find(Order, , , xlWhole)
If Not oCell Is Nothing Then
Nm = Trim(oCell.Offset(, 1).Value)
Add1 = Trim(oCell.Offset(, 2).Value)
End If
End With
Details = "Ref. " & Order & vbCr & Nm & " - " & Add1
Selection.TypeText Details
Selection.TypeParagraph
Next
Selection.HomeKey Unit:=wdStory
MyXL.Quit
Set MyXL = Nothing ' Release reference to the
End Sub

[mdmackillop]

Thanks Bob,
That's perfect, and a good help in my understanding of working across applications. I'll tidy it up in due course and submit it as a KB item.
Regards
Malcolm

[MrHanky]

Just found this and it just about fits the bill for something that I am doing right now. Just one thing is needed to be perfect for my needs. That is for it to retrieve multiple results from the same search. So for instance if it is searchng for "0105" and that result exists 3 tmes in the Excel document that it is referencing, for it to retrieve all 3 sets of results. Does anyone know how to modfy the code from Malcom and Bob to do this?

Thanks

Mark