We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Course Of The Month
4 days, 4 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
search for text in a file in a folder from an open file.
Posted on 2013-11-12
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
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
[X]
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
-
8
4
13 Comments
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.
I don't have access to my VB code at present to paste some example code for you but this is definitely possible.
Active today
Hi,
You could use this function as UDF in J6
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
Active today
Hi,
You could use directly as formula or in a sub
see Example
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
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
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
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
Active today
Hi
in your cell enter this type of formula
in your cell enter this type of formula
=IF(fIsReferenceThere(A1),"Yes","No")
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
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
Sorry.
Regards
Active today
Hi,
I've rearranged the code as Sub
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
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
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
Featured Post
Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Suggested Courses
Course of the Month4 days, 4 hours left to enroll
691 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.