Search For Workbook In Specific Directory, Based On Keyword

Nov 10, 2009

I am trying to find code that locates a workbook (file) in a specific directory, based on a keyword and stores the workbook location in a string to be opened later.

For example, find a workbook containing the word "ancaster" in it's file name (actual file name is "ancaster_summary_2009") in file path:

"C:My Documents" and store the filename and path in a string called "ancasterBook"

View 4 Replies


Search For Keyword In Different Workbook

Jun 21, 2008

I am looking to write a macro that will search for keywords on a sheet, on a workbook at a specific path, and would like some help getting this macro started...

It would get run from "WorkbookA", and would look at "WorkbookB" (wherever it is) and find the cell containing the keyword, where the cell value would be the output. I would really prefer that it did not open Workbook B while this macro is run, if thats possible.

View 5 Replies View Related

Keyword Search - Macros To Change Font Color For Specific Keywords (not Entire Cell)

May 5, 2014

I need to conduct keyword searches in excel and color the keywords a different color without changing the font color of the entire cell. now, i found some code from another thread that does this, but i cant seem to get it to work with more than 2 keywords. i need fine-tuning the provided code to do what i need it to do.


the VBA code i used was this:


I was playing around with the example, the Cat_Mouse.xlsm, and when i modified the 'myList' and 'myColor' arrays, it does not work.


For example, I modified the code to add the word bat:

[Code] .....

When i run the macros, the word "Bat" does not become colored red. interestingly enough, when i substitute the word "bat" for the word "hat" in the "myLIST array, the word "hat" does change to the red font.

I am looking to use this code to address keywords in my excel file by coloring all key words red and i have more than 10 key words.

View 9 Replies View Related

Search In Directory For Specific Cells

Feb 19, 2008

I got a directory with several xls-files.

I want in an new excel file to search automatically for all strings (11 till 48) in all files this particular directory.

All the strings (11-48) are being placed on this new file in worksheet 1.

View 9 Replies View Related

Macro To Search For Specific TXT In Directory Of Approx 1000 XLS Files

Oct 3, 2012

Need macro to search approx 1000+ xls files in folder/directory for common text string "see reference" and then output the file number which is located in cell A1 to new spreadsheet for each file the text "see reference" is found.

View 2 Replies View Related

Macro: Create Directory Based On Active Workbook And Save To It

Sep 8, 2006

Read “My Documents” Path And Use Result



Typical user OS will be Windows XP Pro / Win 2K
Excel version : 97 / 2002 / 2003

1. Corporate network security settings will only allow directory/subdirectory creation in the “My Documents” section of customers individual computers.

2. Per customer request, VBA application needs to save extracted files for future use.

3. I can specify an initial “My Documents” subdirectory be made and the VBA application file be loaded/copied into that location – i.e. – “My DocumentsCat”.

4. When VBA application is opened from that specified directory, (first time), the application needs to make an additional subdirectory tree to save future files. I can read the opened from location via VBA with the following:

Dim filepath As String
filepath = ThisWorkbook.Path
As an example – this code would produce a string definition of “filepath” – such as the following:............................

View 5 Replies View Related

Keyword Search And Export

Feb 8, 2014

Code / macro that would automatically search a word doc for keywords and export every instance of that keyword into column A of a spreadsheet, the first three or four words prior to that keywords into column B, and the entire sentence where the keywords is found into column C?

I often pull hundreds of pages of text from a program that exports into Word and have to look through the pages to find every instance where a point is referenced by geolocation. Then I have to filter through that data to determine which of the points I found are truly important before I save it all to a csv or xls and feed it into mapping software.

If I had a code / macro that did the search and export automatically, it would save hours of work.

View 5 Replies View Related

Excel 2007 :: Search As Per Keyword

Apr 18, 2013

Currently I am using excel 2007. In that I have two sheets (sheet1 as Dashboard & Sheet2 as Database). On sheet1 I need to develop a functionality of searching as per key word (Keyword will be typed on cell B2) and i need to display search data below cell B2 till whatever cells depending as per database.

View 3 Replies View Related

VBA Keyword Search To Return Row Numbers

Mar 3, 2008

i'm writing a few VB subs to help manage a database of mishaps, solutions and lessons learned, there are 11 columns and each databade entry has a separate row, there are approximately 1,000 rows.

Columns are:
ID- a unique iderntifier for each entry- integer
Date- date the entry was made- date
Project Number- a reference to an internal project file- string
Operator- company we were working for- string
Installation- where the work took place- string
Category- type of problem encountered- string
Application- field of problem encountered- string
Issue- string
Background- string
Problems- string
Lessons Learned- string...........

View 9 Replies View Related

Keyword Search Function Using SUMPRODUCT

Oct 30, 2009

I am trying to set up a keyword search where someone can type a word in D8 and the number of books with that word in its title will be returned.

I have managed to get the following sum to work if I put the exact title in the search however I would like it to be possible for a partial search.

=SUMPRODUCT(--('Books List'!G2:G1166=D8))

View 9 Replies View Related

Search For Keyword Or Phrase In Website Using Excel?

Apr 11, 2012

I would like to search for a keyword or a phrase in a website using excel.

Generally we copy the keyword or a phrase and paste it in google and go for search. but i wanted to do this using excel as i have to search same pharse in multiple websites.

View 1 Replies View Related

Search Or Find A Specific Worksheet Tab With Workbook

Apr 28, 2009

I have over 200 worksheets within 1 workbook, is there a way to search or find a specific worksheet by its name? I've tried the find option, clicked search in workbook but it only searched cells within the different worksheets of the workbook and not the title (tabs) of the worksheets.

View 4 Replies View Related

Search For Keyword With And Condition And Return To Different Sheet Within Excel

Jul 22, 2014

I've got an Excel document with over 323 entries and I need to search the data to find where ever a certain keyword is mentioned and copy the entire row into a separate sheet within the same workbook. However, I have a list of around 323 keywords and there is AND condition as doing this using Ctrl+F would take some time.

The data is in Sheet "Training Dataset" and the particular column that needs to be searched is Column "A" . "Training Dataset" is the sheet where I would like the found results to be copied to in the column B and C. The list of Keywords that need to be searched for are located in Sheet "Keywords" , starting from Cell C and D.

View 12 Replies View Related

Extract Text After Specific Keyword

Mar 22, 2008

I have a sheet with only one colomn, and about 200 rows, containing alot of 'garbage'.

Each few rows, I have a keyword ("REFDES=") and after this keyword I have a value ("R3254"). I need to extract only these values and to put them in one row, few colomns.
For example, if the sheet looks like this: ......

View 9 Replies View Related

VBA To Color Cells - Search Workbook For Specific Values?

Mar 22, 2013

I am trying to create a VBA to:

1. Search workbook for a specific values and then to color that cell with a corresponding color.

2. Search workbook for a specific values and then color other cells underneath (the next 3 merged rows after the cell containing the value) with a corresponding color.

View 3 Replies View Related

Macro For Returning URL Of First Search Result On Google From Keyword List In Excel

Apr 3, 2013

So basically I have an Excel sheet which has keywords that need to be entered in Google search. I need the URL of the first page of the search result that appears after that keyword is entered. IS there a macro for the same?

View 3 Replies View Related

Find Keyword In String And Replace That Cell With Keyword Found

Mar 9, 2014

I'm looking to identify a keyword in a string and then replace that string with just the keyword.

The string is a product description. The keyword is a product group. The keyword can showup in any position in the string.

I can't post the actual data do to confidentiality requirements. but here's an example.



green grapes bunch

[Code] .......

View 2 Replies View Related

Search For A Directory Using VBA

Dec 20, 2011

For a macro i'm writing I need to search for a directory.

Users enter a projectnumber, this is being used for a search string.

But how can I search for folders/directories in Windows?

I've got the startfolder: 'C:Projects'

Then the project which the user is searching for can be in several subdirectories.

View 4 Replies View Related

Format Row Based On Keyword

Aug 31, 2007

I have 4 macros which inserts a row in workbook (at specific interval of time). These macros run in a endless loop. The problem arises if any other workbook is open and active at the same time. The macro tries to update this active workbook.
Can I ensure that macro runs for only specific workbook (whether the workbook is active or not)

Option Explicit
Public RunWhen As Double
Public RunWhat As String
Dim wCount
Sub Macro3()
'Initialize counter
wCount = 100
Call Macro4
End Sub
Sub Macro4()
If wCount > 0 Then
' If counter greater than zero then update excel with counter and
' call macro to decrease the counter
Cells(1, 2).Value = wCount
RunWhat = "Macro5"...................

View 2 Replies View Related

Search Keyword In Worksheet / Copy And Paste Adjacent Cells To New Worksheet

Sep 24, 2012

I've been tackling this data capture/paste issue for a week or so. I found the string below which does provide a good foundation for my challenge. But, my basic level of understanding macros limits my modifications to meet my needs.

[URL] ......

I have 20 worksheets in my master file corresponding to Excel files individual associates will update weekly. After the associates have updated their individual files for the week, I want to capture the data entered and paste values into a master file containing a worksheet for each associate (sharing the same name as the individual associate file). All of these files are housed on team SharePoint sites.

I need a macro to perform several steps after clicking a "Run Update" macro button in the master file:
Open individual associate fileIn master file, search for each Initiative listed in column B (starting cell B3) in the individual associate file (in column B starting at cell B11)If Initiative is found in individual associate file, copy adjacent data in columns D:J for the respective rowIn master file, paste values to the corresponding Initiative row for the corresponding week's worth of dataIf Initiative is not found in the individual associate file, move to the next Initiative listed in the master fileRepeat these steps for each individual associate file

Linking would be the easiest way to accomplish this if I wanted to have a multitude of weekly individual files for the associates. However, I'd rather each associate have one file for them to update (basically overwriting their previous week's entries).

I need to ensure the paste values corresponds to the appropriate day of the week. In simpler terms, if the date in the individual associate file in cell D9 reads Oct 1, 2012, the data captured from that row needs to be pasted to the corresponding row/column in the master file that reads the same date.

View 2 Replies View Related

Rows Of Data Based On Keyword

Sep 30, 2008

Attached is a spreadsheet describing my problem in greater detail, but essentially i want to extract rows of data depending on quoter name. I'm fairly competent with excel formulae, but cannot get the desired results. I know nothing of VBA other than copying and pasting other's VBA projects in and fiddling with them until something good happens!!
I wish to extract the data and deposit into another worksheet or better still another workbook entirely but without any breaks in the rows of data.

Havent had much luck with advanced filter either.

View 14 Replies View Related

Add Page Break Based On Keyword

Dec 16, 2007

I am using the following code to insert pagebreaks based on changes in column D. I would like to change it so that page breaks are inserted after the appearance of the word " Total" in column D. Note that the word total will typically be preceeded by other words. Example "Sales Total" or "G. Smith Total". If possible, I would like to suppress this logic when the words "Grand Total" appear as I would end up with a grand total page with nothing else on it.

Sub set_page_breaks()
Dim NumRows As Long
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
ActiveWindow.View = xlPageBreakPreview
ActiveWindow.Zoom = 85
NumRows = Range("A65536").End(xlUp).Row 'get the row count
With ActiveSheet
FirstRow = 2
LastRow = NumRows
For iRow = FirstRow To LastRow
If ActiveSheet.Cells(iRow, "D").Value _.........................

View 2 Replies View Related

VB- Search Directory, Update Master File From Multiple Files

May 16, 2007

Here is a project I can’t seem to do on my own if you can point me in the right direction I would be grateful! here is the best suedo code to describe the needs of the script:

In the open workbook named MASTER, on Worksheet named -MASTER- ( Let’s refer to this all as just MASTER)

(In production, the name of this workbook will be an account #_ date, and the worksheet will be a date)

For each numeric value in row 6 (we’ll call these values AD###) of MASTER

'*****Part one of routine*****

Search directory "H:AccountingAdvertising Accounts" for workbook named AD### AX.xls

(Note the AX suffix of the file name)
If match is found open workbook and proceed to Part two

If match is not found, goto Private Sub AD_MISSING

'*****Part two of routine*****

View 10 Replies View Related

Pop Up A Userform To Ask User To Key In Data Based On The Keyword

Nov 21, 2008

i am attach an sample excel file here.

1st sheet "sample",
first, the marco look for column c "F No."
if it search the content start with "F" and follow by 8 digit numberic,

2nd sheet "pop up",
then pop up userform to ask user to key in the textbox there. if it search for as many as "F" with 8 digit numberic, it will show at the userform and ask user to key in the data in the textbox there..

3rd sheet "key in",
user can key in any data they want

4th sheet "output",
after the user click the "OK" button
all the data put in the textbox there can automatic go to the column "a"
can refer by the sheet output..

View 2 Replies View Related

Copy Selected Files Into A Specific Directory

Jan 29, 2009

I need a procedure to:
a) let the user select any file (name or type)
b) Paste the selected file in a Specific folder.

View 4 Replies View Related

Open All Excel Files In Directory - Code Doesn't Work For One Specific Path

Feb 8, 2014

I have my code here:

Sub openfiles()Dim Path As String
Dim ExcelFile As String
' Path = GetFolder("C:UsersKinteshDesktop")
Path = "C:UsersKinteshDesktopVBA programmingMaps"
ExcelFile = Dir(Path & "*.xls")

[Code] ....

GetFolder = sitem
Set fldr = Nothing
End Function

My problem is that the code all actually works (including the function and when I use the commented part), but pointing to this one specific directory (the one I'm using right now), literally nothing happens.

View 6 Replies View Related

Defining Workbook Save Directory

Nov 5, 2008

I have managed to get my workbook to save with a filename based on two textboxes. I am not sure, however, how to define the directory. I have tried adding a ("mydirectory") on the saveas line but it does not work.

This is my
Private Sub CommandButton2_Click()
Dim wb As Workbook
Set wb = ActiveWorkbook
Dim strText As String
strText = txtLMSRef.Text & "," & txtPartial.Text
wb.SaveAs Filename:=strText
Set wb = Nothing

End Sub

View 9 Replies View Related

Save Workbook As Copy To Another Directory

May 20, 2009

I need save my workbook as copy to another directory, ( C: ) with VBA code

View 9 Replies View Related

How To Search Multiple Workbooks For A Specific Value In A Specific Cell

Sep 14, 2008

I'm trying to search through multiple worksheets (that are closed) to see if a value in cell B12 (of every worksheet) matches a value in a seperate worksheet (which is in a seperate workbook)

View 14 Replies View Related

Linking Dynamically To Other Workbook In Directory Specified In Cell?

Mar 6, 2014

Using Dropbox, a lot of people will fill in formulas in personal workbooks from using data from another, central workbook. Since I cannot control where they install Dropbox or where they keep their personal workbooks, I must account for that in functions.

Now, my idea is to make them define the directory of the source in their personal workbook in a specific cell and then using that as a basis for the rest of the functions.


Cell A1: C:Usersuser1Dropboxfolder (copy paste from path in Windows Explorer)

Cell B1: A1&[CentralWorkbook.xlsx]SheetName'!$H$1

Basically I want the unknowing user to copy-paste the path of the central workbook into a specific cell in their own workbook and then build my functions bsed on that. How can this be done?

View 2 Replies View Related

Copyrights 2005-15, All rights reserved