Start Free Trial

asked on

Excel sheet has data in colum A. When run macro has to open another excel that has the same data in the sheet called "Desktops"

Hi,

Excel sheet has data in colum A. When run macro has to open another excel that has the same data in the sheet called "Desktops". And get the whole rows that gets a match of the colum A data to another sheet in the same excel where i run the macro.
So i just get what i am searching for.
say in Sheet1 in colum A i have

Sharath
Ramesh
When run macro has to search the Desktops sheet whose path is mentioned already in the macro.
Get the whole rows where it gets an exact match.

Regards
Sharath

Why not do that with a vlookup function? That way you'd also get a dynamic connection to your other file.

Saurabh Singh Teotia

Can you please attach your both sample workbooks out here....

Saurabh...

ASKER

Sorry for the delay...
Attached is an sample excel file which has just 3 colums but i need to search more that 60 + colums and 2500 rows.

The txt file in c:\ will have the search data

search-Find.xls

Saurabh Singh Teotia

Can you also attach your raw data...because i want to see how does that looks like...

ASKER

The txt file is attached
Search-file.txt

Hi, you said:
>> say in Sheet1 in colum A i have
>> Sharath
>> Ramesh
>> When run macro has to search the Desktops sheet whose path is mentioned already in the macro.
>> Get the whole rows where it gets an exact match.

So your search term comes from column A in the current workbook. In the Desktop sheet of the other workbook, is there a specific column that you want to match with the search term, or do you want to search all columns on each row?

Regards,

Rob.

ASKER

Rob i want to search in the whole sheet.
Its not always that i have name.Some times machine names,location,building etc.
So should match any all data that matches the data. Has to be full search data.And no case sensivity....

ASKER

Rob i want to search in the whole sheet.
Its not always that i have name.Some times machine names,location,building etc.
So should match any all data that matches the data. Has to be full search data.And no case sensivity....

ASKER

Rob any help on this....

Try this macro in the same workbook as your search terms.

Regards,

Rob.

Sub Search_Desktops_Sheet()
    Application.ScreenUpdating = False
    Set objSearchWB = ActiveWorkbook
    strDesktopsWBPath = objSearchWB.Path & "\" & "search-find.xls"
    strResultsSheet = "Search_Results"
    Set objDesktopsWB = Workbooks.Open(strDesktopsWBPath, False, True)
    Set objDesktopsSheet = objDesktopsWB.Sheets("Desktops")
    Application.DisplayAlerts = False
    On Error Resume Next
    objSearchWB.Sheets(strResultsSheet).Delete
    Err.Clear
    On Error GoTo 0
    Application.DisplayAlerts = True
    objSearchWB.Sheets.Add After:=objSearchWB.Sheets(objSearchWB.Sheets.Count)
    objSearchWB.Sheets(objSearchWB.Sheets.Count).Name = strResultsSheet
    For intRow1 = 2 To objSearchWB.Sheets(1).Cells(65536, "A").End(xlUp).Row
        intFoundRow = 0
        strSearchTerm = objSearchWB.Sheets(1).Cells(intRow1, "A").Value
 
        objDesktopsSheet.Activate
        objDesktopsSheet.Cells(1, "A").Select
        Set objCell = ActiveCell
        Set objCell = objDesktopsSheet.Cells.Find(What:=strSearchTerm, After:=objCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
        If Not objCell Is Nothing Then
            If objCell.Row > intFoundRow Then
                'MsgBox "Found " & strSearchTerm & " at row " & objCell.Row
                intFoundRow = objCell.Row
                objDesktopsSheet.Rows(intFoundRow & ":" & intFoundRow).Copy objSearchWB.Sheets(strResultsSheet).Range("A" & objSearchWB.Sheets(strResultsSheet).Cells(65536, "A").End(xlUp).Row + 1)
            Else
                Set objCell = Nothing
            End If
        End If
        While Not objCell Is Nothing
            Set objCell = Cells.FindNext(After:=objCell)
            If Not objCell Is Nothing Then
                If objCell.Row > intFoundRow Then
                    'MsgBox "Found " & strSearchTerm & " at row " & objCell.Row
                    intFoundRow = objCell.Row
                    objDesktopsSheet.Rows(intFoundRow & ":" & intFoundRow).Copy objSearchWB.Sheets(strResultsSheet).Range("A" & objSearchWB.Sheets(strResultsSheet).Cells(65536, "A").End(xlUp).Row + 1)
                Else
                    Set objCell = Nothing
                End If
            End If
        Wend
    Next
    objDesktopsWB.Close
    Application.ScreenUpdating = True
    MsgBox "Done"
End Sub

Open in new window

ASKER

Rob should i have the search data in a xls file ot txt file?
Should i change the sheets names any where.

I tried it opens the xlsx file but does not get the data

The search data should be in column A of any workbook other than the one with Desktops in it.
The search data should start on Row 2.

The results then get placed in a new sheet called Results_Sheet, in the workbook that has the search terms (and the macro).

Regards,

Rob.

ASKER

Rob i just did as said . But no data is generated.

It opens the xlsx file but does not pull data...

ASKER

Rob i just did as said . But no data is generated.

It opens the xlsx file but does not pull data...

Here's the sample files that I ran this with...
searchTerms.xls ---> this has the macro, and the terms in column A to search
Results get placed in a Results_Sheet sheet in this workbook
search-Find.xls ---> this has the Desktops sheet that gets searched

Rob.
Q-23427641.zip

ASKER

Rob the attached files work perfect.
But when i place the searchterms.xls in the same UNC folder where my Desktops file is there and just change the file name to my file name...It does not get the data

ASKER

Rob the attached files work perfect.
But when i place the searchterms.xls in the same UNC folder where my Desktops file is there and just change the file name to my file name...It does not get the data

In the searchterms.xls file, are the terms in column A starting at row 2?

Under this line in the macro:
strSearchTerm = objSearchWB.Sheets(1).Cells(intRow1, "A").Value

put this
MsgBox "Looking for " & strSearchTerm

and see if it's picking up the search terms to look for...

Rob.

ASKER

Rob Placed the line but no message...

ASKER

Rob Placed the line but no message...

Oh, maybe your search terms aren't on the first sheet in that workbook.

What is the name of the sheet where your search terms are?

Rob.

ASKER

The sheet name is "Desktops"
Its the first sheet in the file

No, in the other workbook, where you are putting the search terms. The one where the words to seach for are located....

Rob.

ASKER

I am using the same workbook which you have attached.
Searchterm.xls
Sheet name = SearchTerms

ASKER

I am using the same workbook which you have attached.
Searchterm.xls
Sheet name = SearchTerms

Might be the same issue as before....try this...I've added the
Set objDesktopsWB = ActiveWorkbook

Rob.

Sub Search_Desktops_Sheet()
    Application.ScreenUpdating = False
    Set objSearchWB = ActiveWorkbook
    strDesktopsWBPath = objSearchWB.Path & "\" & "search-find.xls"
    strResultsSheet = "Search_Results"
    Set objDesktopsWB = Workbooks.Open(strDesktopsWBPath, False, True)
    Set objDesktopsWB = ActiveWorkbook
    Set objDesktopsSheet = objDesktopsWB.Sheets("Desktops")
    Application.DisplayAlerts = False
    On Error Resume Next
    objSearchWB.Sheets(strResultsSheet).Delete
    Err.Clear
    On Error GoTo 0
    Application.DisplayAlerts = True
    objSearchWB.Sheets.Add After:=objSearchWB.Sheets(objSearchWB.Sheets.Count)
    objSearchWB.Sheets(objSearchWB.Sheets.Count).Name = strResultsSheet
    For intRow1 = 2 To objSearchWB.Sheets("SearchTerms").Cells(65536, "A").End(xlUp).Row
        intFoundRow = 0
        strSearchTerm = objSearchWB.Sheets("SearchTerms").Cells(intRow1, "A").Value
 
        objDesktopsSheet.Activate
        objDesktopsSheet.Cells(1, "A").Select
        Set objCell = ActiveCell
        Set objCell = objDesktopsSheet.Cells.Find(What:=strSearchTerm, After:=objCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
        If Not objCell Is Nothing Then
            If objCell.Row > intFoundRow Then
                'MsgBox "Found " & strSearchTerm & " at row " & objCell.Row
                intFoundRow = objCell.Row
                objDesktopsSheet.Rows(intFoundRow & ":" & intFoundRow).Copy objSearchWB.Sheets(strResultsSheet).Range("A" & objSearchWB.Sheets(strResultsSheet).Cells(65536, "A").End(xlUp).Row + 1)
            Else
                Set objCell = Nothing
            End If
        End If
        While Not objCell Is Nothing
            Set objCell = Cells.FindNext(After:=objCell)
            If Not objCell Is Nothing Then
                If objCell.Row > intFoundRow Then
                    'MsgBox "Found " & strSearchTerm & " at row " & objCell.Row
                    intFoundRow = objCell.Row
                    objDesktopsSheet.Rows(intFoundRow & ":" & intFoundRow).Copy objSearchWB.Sheets(strResultsSheet).Range("A" & objSearchWB.Sheets(strResultsSheet).Cells(65536, "A").End(xlUp).Row + 1)
                Else
                    Set objCell = Nothing
                End If
            End If
        Wend
    Next
    objDesktopsWB.Close
    Application.ScreenUpdating = True
    MsgBox "Done"
End Sub

Open in new window

ASKER

Rob still not retrieving the results

I am using the same file you uploaded just changing the xlsm file name.
Desktops sheet has all the data and i have these data in the "Serchterm.xls

Sharath
Ramesh

These 2 names are there in the desktops sheet in many places like colum
C,H,BI

But does not get the rows

ASKER

Rob still not retrieving the results

I am using the same file you uploaded just changing the xlsm file name.
Desktops sheet has all the data and i have these data in the "Serchterm.xls

Sharath
Ramesh

These 2 names are there in the desktops sheet in many places like colum
C,H,BI

But does not get the rows

Hmmm, odd....if the two workbooks that I posted worked for you, then what happens if you manually save your workbook with the Desktops sheet as a normal XLS file, just for testing, then change the path to suit, and see if it can find the results from the normal XLS version?

Rob.

ASKER

Still does not get the data....

ASKER

Still does not get the data....

ASKER

Rob even when changed the file as xls it does not work

ASKER

Rob i just checked putting in all my data into the sheet you attached here and it worked fine...
So that's what i wanted... Instead of opening my file i can copy the data to this file and search to retrieve data...
So need these changes

1. Every time the query is run it needs to clear the results sheet and place newly queried data
2. Need to get the headers from the sheet by default thats in Row 2

ASKER

Rob i just checked putting in all my data into the sheet you attached here and it worked fine...
So that's what i wanted... Instead of opening my file i can copy the data to this file and search to retrieve data...
So need these changes

1. Every time the query is run it needs to clear the results sheet and place newly queried data
2. Need to get the headers from the sheet by default thats in Row 2

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Thanks Rob worked perfect any help with the other posts?