Member Avatar for Maria_19

Hey guyz
I got this search macro from the web for my excel project but as Iam a beginner i really dont know how to modify it. I need to use this search code to identify and retrieve documents for that its going to scan through a especified location. I don know how to especify the location out side excel :confused: can some one plz help
Search Macro

Public Sub Test()
Dim SR As Integer
Dim ER As Integer
Dim SC As Integer
Dim EC As Integer
Dim RowVar As Integer
Dim ColVar As Integer
Dim found As Boolean
 
'Change the indexes for your own Row range from 1 to 65536
SR = 1
ER = 20
'Change the indexes for your own Column range from 1 to 256 - in English from "A" to "IV"
SC = 1
EC = 10
found = False
For RowVar = SR To ER
For ColVar = SC To EC
If Not found Then 'This ensures that you stop at the first occurrence
If Cells(RowVar, ColVar).Formula = "Angstrom" Then 'This is where your search text goes
Cells(RowVar, ColVar).Select
found = True
End If
End If
Next
Next
End Sub
Set xlApp = Nothing
End Sub
  • 3 Contributors
  • 9 Replies
  • 147 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by Comatose
Member Avatar for williamrojas78

Hi

You have to set the workbook you want to search, and then set the range. I guess the range is always going to be the same.

Somthing like this will open the workbook and set the sheet1 as the sheet you will working on :

Dim oWB As Workbook
      Dim oSheet As Worksheet
      
      Workbooks.Open Filename:="C:\test.xls"
      Set oWB = ActiveWorkbook
                 
      Set oSheet = oWB.Sheets("Sheet1")
      oSheet.Range("a1:a21").Select

After that do the search in the specified range as you did before.

Hope it helps

Member Avatar for Comatose

I should merge this and your other thread, since they are about the same topic.... but for now, I'll leave them be. The code you posted only searches through the grid of the excel document, and (as far as I can tell) never onces looks at any kind of filename. I can code a sub/function to do what you want.... the problem we are facing is that this will be done strictly in VB6 Code. Yes, there is a difference between VB and VBA, but it's not extreme... and the problem is that I don't know how much (if any) of the commands/keywords that vb6 has are not in VBA. I know that vb6 has some additional commands, and certainly added power that you simply can not harness with VBA, but I'm not sure (since I don't use office) if the code will work 100% as is.... so try this sub, and let's see where we need to modify.... baby steps.

Public Sub GetDocumentStr(PartOfFileName, FolderToSearch)
If Right(FolderToSearch, 1) <> "*" Then
    If Right(FolderToSearch, 1) <> "\" Then FolderToSearch = FolderToSearch & "\"
    FolderToSearch = FolderToSearch & "*.*"
End If

TFname = Dir(FolderToSearch)
Do While TFname <> ""
    If InStr(1, TFname, PartOfFileName) <> 0 Then
        Exit Do
    End If
    TFname = Dir
Loop

If TFname = "" Then
    MsgBox "None Found"
Else
    MsgBox TFname
End If
End Sub

Now, that Sub only does part of what you want.... it searches a folder you give it, for a part of a filename that you specify, and then pops up a box telling you the name (or none if that text is not found in a file name)..... The reason I built it this way, is because I have no idea which cell you plan to use to get the file name from. We can modify this little by little until we get it where you need it to be, but for now, let's make sure this works, and post again with any changes you need.

Member Avatar for Maria_19

Thanx william :)

Member Avatar for Maria_19

Thanx Comatoes I really appriciate the fact that you took time out to help me.
You already know about my project The idea is to have a macro do two things basically search and retrieve. what i wanned it to do was search the directory as in out side Excel. I got this code: It pretty much does what i want but i need your help in some modifications plzzz.

Sub DirLoop()
   Dim MyFile As String, Sep As String, filename1 As String
   filename1 = "H:\2006\Course Description\AIX 5L"
   ' Sets up the variable "MyFile" to be each file in the directory
   ' This example looks for all the files that have an .xls extension.
   ' This can be changed to whatever extension is needed. Also, this
   ' macro searches the current directory. This can be changed to any
   ' directory.
   ' Test for Windows or Macintosh platform. Make the directory request.
   Sep = Application.PathSeparator
   If Sep = "\" Then
      ' Windows platform search syntax.
      MyFile = Dir(filename1 & Sep & "*.doc")
   Else
      ' Macintosh platform search syntax.
      ' MyFile = Dir("", MacID("XLS5"))
   End If
   ' Starts the loop, which will continue until there are no more files
   ' found.
   Do While MyFile <> ""
      ' Displays a message box with the name of the file. This can be
      ' changed to any procedure that would be needed to run on every
      ' file in the directory such as opening each file.
      MsgBox filename1 & Sep & MyFile
      MyFile = Dir()
   Loop
End Sub

Now the problem with this code is that it goes in the specified path and starts poping up names of all the files in msg boxs. What I need is it to jus search the specific file name and retrieve the file as in "Open file.doc " not display the names only. Do you think u can help plzzzz! I believe if i merg the two codes (urs and mine) I should be able to get what m lukin for but i am a beginner so Iam not so confident about my work:$

Member Avatar for Comatose

For what you want to do, your code is way to static (doesn't change). You want to search a directory, by the info in a cell of an excel document...... you need to be able to change which directory you are searching, and what name of the document you need..... when I get home from work, I'll make it open the document in excel.

Member Avatar for Maria_19

I tried ur search macro it worked! I specified cells to pick up the search string it works fine:) Thanx alot

Member Avatar for Comatose

So, It is all working correctly?

Member Avatar for Maria_19

No Iwont say that I dont need any more help NOWAY! but for now since the search code is working m trying to work on how to make it open the .doc file and insert it at the end of a word document proposal. Do u think you can help me out with it?? Ican forward u the search code with the modifications i made if its required?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.