file transfer from one folder to another.

[venkitaraman]

hello everyone,

i want to know if i will be able to transfer a file from one folder to another folder in the same drive.

right now iam able to search for a file and able to generate a succeess message when the file is found by the following code :-

With fs
With .PropertyTests
.Add _
Name:="Text or Property", _
Condition:=msoConditionIncludesPhrase, _
Value:="6601"
End With
.LookIn = "B:\Navaz\consolidated_report_for_30_nov_2007\client_report"
.SearchSubFolders = False
.Filename = "*"
.MatchTextExactly = False
.FileType = msoFileTypeAllFiles
End With
If fs.Execute() > 0 Then
MsgBox "There were " & _
fs.FoundFiles.Count & _
" file(s) found."

For i = 1 To fs.FoundFiles.Count
MsgBox fs.FoundFiles(i)
Next i
Else
MsgBox "There were no files found."

End If

but i want to know if i can transfer the searched file to another folder say "B:\Venki\client_report".


regards

Venkitaraman

[Bob Phillips]

With fs
With .PropertyTests
.Add _
Name:="Text or Property", _
Condition:=msoConditionIncludesPhrase, _
Value:="6601"
End With
.LookIn = "B:\Navaz\consolidated_report_for_30_nov_2007\client_report"
.SearchSubFolders = False
.Filename = "*"
.MatchTextExactly = False
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
MsgBox "There were " & _
.FoundFiles.Count & _
" file(s) found."

For i = 1 To .FoundFiles.Count
Name .FoundFiles(i), "B:\Venki\client_report\" & _
Right$(.FoundFiles(i), Len(.FoundFiles(i)) - InStrRev(.FoundFiles(i), "\"))
Next i
Else
MsgBox "There were no files found."
End If
End With

[venkitaraman]

hi,
iam getting runtime errror '91'. object variable or with block variable not set.

please help

venkitaraman

[Bob Phillips]

Where?

[venkitaraman]

hi,
i have included 'Dim name As FileTypes' ...what should i set name to

[Bob Phillips]

I have no idea, what is names?

[venkitaraman]

hi,
the entire code now will look like this.....

Sub test()
Dim fs As FileSearch
Dim name As FileTypes

Set fs = Application.FileSearch
fs.NewSearch
With fs
With .PropertyTests
.Add _
name:="Text or Property", _
Condition:=msoConditionIncludesPhrase, _
Value:="6601"
End With
.LookIn = "B:\Navaz\consolidated_report_for_30_nov_2007\client_report"
.SearchSubFolders = False
.Filename = "*"
.MatchTextExactly = False
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
MsgBox "There were " & _
.FoundFiles.Count & _
" file(s) found."

For i = 1 To .FoundFiles.Count

name.FoundFiles (i), "B:\Venki\client_report\" & _
Right$(.FoundFiles(i), Len(.FoundFiles(i)) - InStrRev(.FoundFiles(i), "\"))

MsgBox "ok"
Next i
Else
MsgBox "There were no files found."
End If
End With

End Sub


i hope it has to do something with the name object which i have to set some reference. but i dont know what the reference type i have to set .

please help


regards
venki

[Bob Phillips]

I think I see what you are doing, you don't define it all, it is a VBA statement



Sub test()
Dim fs As FileSearch
Dim i As Long

Set fs = Application.FileSearch
With fs
.NewSearch
.PropertyTests.Add name:="Text or Property", _
Condition:=msoConditionIncludesPhrase, _
Value:="6601"

.LookIn = "B:\Navaz\consolidated_report_for_30_nov_2007\client_report"
.SearchSubFolders = False
.Filename = "*"
.MatchTextExactly = False
.FileType = msoFileTypeAllFiles

If .Execute() > 0 Then

MsgBox "There were " & _
.FoundFiles.Count & _
" file(s) found."

ReDim namesfound(1 To .FoundFiles.Count)
For i = 1 To .FoundFiles.Count

name .FoundFiles(i), "B:\Venki\client_report\" & _
Right$(.FoundFiles(i), Len(.FoundFiles(i)) - InStrRev(.FoundFiles(i), "\"))

MsgBox "ok"
Next i
Else

MsgBox "There were no files found."
End If
End With

End Sub

[venkitaraman]

i get a invalid qualifier error while compling

name .FoundFiles(i), "B:\Venki\client_report\" & _
Right$(.FoundFiles(i), Len(.FoundFiles(i)) - InStrRev(.FoundFiles(i), "\"))

[Bob Phillips]

Don't know why but this is failing on my box, maybe it will be okay for you



Option Explicit

Sub test()
Dim fs As FileSearch
Dim i As Long

Set fs = Application.FileSearch
With fs
.NewSearch
'.PropertyTests.Add Name:="Text or Property", _
Condition:=msoConditionIncludesPhrase, _
Value:="6601"

.LookIn = "C:\test" '"B:\Navaz\consolidated_report_for_30_nov_2007\client_report"
.SearchSubFolders = False
.Filename = "*"
.MatchTextExactly = False
.FileType = msoFileTypeAllFiles

If .Execute() > 0 Then

MsgBox "There were " & .FoundFiles.Count & " file(s) found."
For i = 1 To .FoundFiles.Count

Name .FoundFiles(i) as "B:\Venki\client_report\" & _
Right$(.FoundFiles(i), Len(.FoundFiles(i)) - InStrRev(.FoundFiles(i), "\"))
Next i
MsgBox "OK"
Else

MsgBox "There were no files found."
End If
End With

End Sub

[venkitaraman]

getting syntax error on

Name .FoundFiles(i) As "B:\Venki\client_report\" & _
Right$(.FoundFiles(i), Len(.FoundFiles(i)) - InStrRev(.FoundFiles(i), "\"))

[Bob Phillips]

Yeah me too, and i can't see why at the moment.

[Ivan F Moala]

Appears to compile name as non command

but this works.... coercing it via brackets


Name (.FoundFiles(i)) As ("B:\Venki\client_report\" & Right$(.FoundFiles(i), Len(.FoundFiles(i)) - _
InStrRev(.FoundFiles(i), "\")))

[venkitaraman]

hi,

i used the following code provided by EVAN
" Name (.FoundFiles(i)) As ("B:\Venki\client_report\" & Right$(.FoundFiles(i), Len(.FoundFiles(i)) - _
InStrRev(.FoundFiles(i), "\"))) "

now iam not getting any error but..first i get a message that " 1 file found" and when i click 'ok' i get a message saying " run time error : file not found"

please help



regards
venkitaraman

[venkitaraman]

hi,

i have refined the code and iam getting some good results now..thanks allot ... i dont get any error message
but its not the end...i may need some more help to complete the project
the code now looks like this :

Sub test()
Dim fs As FileSearch
Dim i As Long

Set fs = Application.FileSearch
With fs
.NewSearch
.PropertyTests.Add Name:="Text or Property", _
Condition:=msoConditionIncludesPhrase, _
Value:="6601"

.LookIn = "C:\navaz"
.SearchSubFolders = False
.Filename = "*"
.MatchTextExactly = False
.FileType = msoFileTypeAllFiles

If .Execute() > 0 Then

MsgBox "There were " & .FoundFiles.Count & " file(s) found."
For i = 1 To .FoundFiles.Count

Name (.FoundFiles(i)) As ("C:\venki1\client_report" & Right$(.FoundFiles(i), Len(.FoundFiles(i)) - _
InStrRev(.FoundFiles(i), "\")))
Next i
MsgBox "OK"
Else

MsgBox "There were no files found."
End If
End With

End Sub


now...
Condition:=msoConditionIncludesPhrase, _
Value:="6601"
'6601' is just one value in the column. say '6601' is value of cell number 'A5'. i have a column of value that starts from 'A5' and ends at 'A200' which will vary every day.

So what iam trying to do is ... put the following code in a loop which may look similar to the following

for i= 'range.start value' to 'range.end value'
With fs
.NewSearch
.PropertyTests.Add Name:="Text or Property", _
Condition:=msoConditionIncludesPhrase, _
Value:="i"

"rest of the code"
next i

.........................................................................

i want to know what will be "range.startvalue" and range.end value".. i.e how will i get the start and end value of the renage for eg) if the column starts with value in A5 say '6601' and end in column A200 say '126545'. how am i going to loop this.

regards

Venkitaraman

[Bob Phillips]

For Each cell In Range("A5:A200")

...

Next cell

[venkitaraman]

hi,

i changed the code to

Sub test()
Dim fs As FileSearch
Dim i As Long
Dim cell As Range


For Each cell In Range("B5:B10")

Set fs = Application.FileSearch
With fs
.NewSearch
.PropertyTests.Add Name:="Text or Property", _
Condition:=msoConditionIncludesPhrase, _
Value:=Application.Cells.Value

.LookIn = "C:\navaz"
.SearchSubFolders = False
.Filename = "*"
.MatchTextExactly = False
.FileType = msoFileTypeAllFiles

If .Execute() > 0 Then

MsgBox "There were " & .FoundFiles.Count & " file(s) found."
For i = 1 To .FoundFiles.Count

Name (.FoundFiles(i)) As ("C:\venki1\client_report" & Right$(.FoundFiles(i), Len(.FoundFiles(i)) - _
InStrRev(.FoundFiles(i), "\")))
Next i
MsgBox "OK"
Else

MsgBox "There were no files found."
End If
End With
Next cell
End Sub

***********************************


now iam getting out of memory error at " .PropertyTests.Add Name:="Text or Property", _
Condition:=msoConditionIncludesPhrase, _
Value:=Application.Cells.Value"


let me make myself clear one more time .....

i was able to search and transfer a file from one folder to another folder.
what i want to is search using the entire column values as search string.
previously we were hardcording each value like " .PropertyTests.Add Name:="Text or Property", _
Condition:=msoConditionIncludesPhrase, _
Value:="6601".

attached is a sample excel file where the start value is '6601' and i have to first start the serach using that value and continue till i reach the end value i.e '1688476'. so the searching process shoild be in a loop, where the loop starts at 6601 and end at 1688476'. ALSO THE COLUMN VALUE IS DYNAMIC AND WILL CHANGE EVERY DAY. SO CANNOT BE HARDCODED

[Bob Phillips]

Nothing attached

[venkitaraman]

please find the attached file

[venkitaraman]

Any Updates