Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

search for text in a file in a folder from an open file.

Posted on 2013-11-12
13
175 Views
Last Modified: 2014-01-20
I need to search for a reference in a folder that has several tabs containing over 200,000 rows.

Is it possible with VBA code to look for the reference and if found enter Yes a cell on my open file.

So..... the file is in the path G:\Clients\Open Invoice\Open Invoice Documentation Database - UPDATED.xlsx

the file has 3 tabs: List1, List2, List3

My file [the one with the Macro] needs to look up the Reference that is in C5 and if it finds it in the above file in any of the Tabs return 'Yes' in my file cell J6 and if not found put 'No'

Previously I have been opening the file Open Invoice Documentation Database - UPDATED.xlsx and copying all 3 tabs onto a new sheet in my file and then doing a VLookup, but this seems to take forever.

Any help much appreciated.

I realise this could be a tricky one or even impossible.

Thanks
0
Comment
Question by:Jagwarman
  • 8
  • 4
13 Comments
 
LVL 9

Expert Comment

by:guswebb
ID: 39641412
This is possible. I did something similar to open timesheets which were individual Excel files, and extract the contents of a particular cell pertaining to mileage. A VB script can be created to open all files with *.xls* extensions within a nominated directory (including sub-folders), inspect the contents of a nominated cell or cells (or search for content), and then write data in to a log file Excel file. In my case, I needed to open up over 2000 Excel files and extract the data, whilst also logging the process in a .txt log file.

I don't have access to my VB code at present to paste some example code for you but this is definitely possible.
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39641433
Hi,

You could use this function as UDF in J6
Function fIsReferenceThere(Filename As String) As Boolean

'
Set SrcWbk = Workbooks.Open(Filename:="G:\Clients\Open Invoice\Open Invoice Documentation Database - UPDATED.xlsx")
For Each sh In SrcWbk.Worksheets
    With sh.Cells
        Set FoundRef = Cells.Find(What:=Filename, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=False)
    End With
Next
If FoundRef Is Nothing Then
    fIsReferenceThere = False
Else
    fIsReferenceThere = True
End If

Application.DisplayAlerts = False
SrcWbk.Close
Application.DisplayAlerts = True

End Function

Open in new window

Regards
0
 

Author Comment

by:Jagwarman
ID: 39647273
Hi Rgonzo1971

I'm not familiar with 'Function' how would I call this from my Sub

Thanks
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39647333
Hi,

You could use directly as formula or in a sub

see Example

Sub Macro()
if fIsReference("myReference") = True Then
 Msgbox "Yes", vbOkOnly
End If

End Sub

Open in new window

Modified Code
Function fIsReferenceThere(strReference As String) As Boolean

'
Set SrcWbk = Workbooks.Open(FileName :="G:\Clients\Open Invoice\Open Invoice Documentation Database - UPDATED.xlsx")
For Each sh In SrcWbk.Worksheets
    With sh.Cells
        Set FoundRef = .Find(What:=strReference , LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=False)
    End With
Next
If FoundRef Is Nothing Then
    fIsReferenceThere = False
Else
    fIsReferenceThere = True
End If

Application.DisplayAlerts = False
SrcWbk.Close
Application.DisplayAlerts = True

End Function

Open in new window

Regards
0
 

Author Comment

by:Jagwarman
ID: 39647443
Sorry for being an idot Rgonzo1971

I am getting Sub or Function not defined ????

Regards
0
 

Author Comment

by:Jagwarman
ID: 39647466
Sussed it. Will test it out now
0
 

Author Comment

by:Jagwarman
ID: 39647502
Ok it's probably me but when I run it, it only returns the message box with Yes every time even if I put in a reference that I know is not in the file.

Also, as I mentioned in my original spec, I really need it to put the word Yes in cell J6 if it finds the reference and No in J6 if it does not find it,

Can you assist Rgonzo1971

Thanks
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39647619
Hi
in your cell enter this type of formula

=IF(fIsReferenceThere(A1),"Yes","No")

Open in new window

Code corrected
Function fIsReferenceThere(strReference As String) As Boolean

'
Set SrcWbk = Workbooks.Open(Filename:="G:\Clients\Open Invoice\Open Invoice Documentation Database - UPDATED.xlsx")
For Each sh In SrcWbk.Worksheets
    With sh
        Set FoundRef = .Cells.Find(What:=strReference, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=False)
    End With
Next
If FoundRef Is Nothing Then
    fIsReferenceThere = False
Else
    fIsReferenceThere = True
End If

Application.DisplayAlerts = False
SrcWbk.Close
Application.DisplayAlerts = True

End Function

Open in new window

Regards
0
 

Author Comment

by:Jagwarman
ID: 39647772
I did that  Rgonzo1971 but it made no difference it still returns 'Yes' in the Message Box despite me putting in a spurious ref and it still does not populate J6.

Sorry.

Regards
0
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39647891
Hi,

I've rearranged the code as Sub

Sub IsReferenceThere()
Application.ScreenUpdating = False
strReference = "aaaaaaaaa"     ' Change here your reference

Set SrcWbk = Workbooks.Open(Filename:="G:\Clients\Open Invoice\Open Invoice Documentation Database - UPDATED.xlsx")
For Each sh In SrcWbk.Sheets
    With sh
        Set FoundRef = .Cells.Find(What:=strReference, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=False)
    End With
Next
If FoundRef Is Nothing Then
   ThisWorkbook.Sheets("Sheet1").Range("J6") = "No"
Else
   ThisWorkbook.Sheets("Sheet1").Range("J6") = "Yes"
End If

Application.DisplayAlerts = False
SrcWbk.Close
Application.DisplayAlerts = True

End Sub

Open in new window

IsRef.xlsm
0
 

Author Comment

by:Jagwarman
ID: 39648006
That is perfect Rgonzo1971 Thank you
0
 

Author Comment

by:Jagwarman
ID: 39788770
Rgonzo1971

can you assist with a problem I have with the macro you provided to me.

It's quite bizzare. It has been working fine but now it has given up the ghost.

when I check the file manually and do a 'Find' I can see the item I am looking for. When I run the macro it puts "No" in J6

Would you have any idea what might be causing this

I have done all the usual checks, looking for spaces, checking the format etc.

Thanks
Regards
Jagwarman
0
 

Author Comment

by:Jagwarman
ID: 39794258
Hi Rgonzo1971 really need your help with this one.

Hope you are not on holiday
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question