Can some one help me with this code plz
 

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

Re: Can some one help me with this code plz
 

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

Re: Can some one help me with this code plz
 

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.

Re: Can some one help me with this code plz
 

Thanx william :)

Re: Can some one help me with this code plz
 

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:$

Re: Can some one help me with this code plz
 

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.

Re: Can some one help me with this code plz
 

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

Re: Can some one help me with this code plz
 

So, It is all working correctly?

Re: Can some one help me with this code plz
 

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?

Re: Can some one help me with this code plz
 

Hmn.... I just wrote these, so let me know what you think:
http://www.daniweb.com/techtalkforums/thread51307.html
and then
http://www.daniweb.com/techtalkforums/thread51315.html