I am usually the following code to move files into proper directories
based on what they have been named at the scanner.

The problem is: I cannot get the if statement with the FileExists
method to recognize that there are files in that directory that
contain the text "HMB4129". Why is this?

Sub FileScanned()
'
' FileScanned Macro
' Macro recorded 6/23/2004 by Michael McClellan, Jr.
'

'
Dim DirVar1 As String
Dim DirVar2 As String
Dim DirVar3 As String
Dim DirVar4 As String

Dim CurrDir As String
Dim CurrFile As String

Dim rownum As Integer
Dim FilesFiled As Integer

Sheets("Orders").Select
FilesFiled = 0
rownum = 47
'Do

DirVar1 = Range("A" & rownum).Value 'office code
DirVar2 = Range("B" & rownum).Value 'HM PO#
DirVar3 = Range("F" & rownum).Value 'vendor code
DirVar4 = Range("L" & rownum).Value 'customer po

CurrDir = "HM" & DirVar1 & DirVar2 & " - " & DirVar3 & " - " &
DirVar4
CurrFile = "HM" & DirVar1 & DirVar2

Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists("C:\Ricoh Scan Files\*HMB4129.pdf") = True Then
fs.MoveFile "C:\Ricoh Scan Files\*" & CurrFile & ".pdf",
"R:\Orders\" & CurrDir & "\"
FilesFiled = FilesFiled + 1
End If

rownum = rownum + 1
'Loop Until DirVar2 = ""
MsgBox FilesFiled & " files were filed."
End Sub

FileExists doesn't do wildcards. If you want to do that, use FileSearch.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Michael McClellan" wrote in message
m...
I am usually the following code to move files into proper directories
based on what they have been named at the scanner.

The problem is: I cannot get the if statement with the FileExists
method to recognize that there are files in that directory that
contain the text "HMB4129". Why is this?

Sub FileScanned()
'
' FileScanned Macro
' Macro recorded 6/23/2004 by Michael McClellan, Jr.
'

'
Dim DirVar1 As String
Dim DirVar2 As String
Dim DirVar3 As String
Dim DirVar4 As String

Dim CurrDir As String
Dim CurrFile As String

Dim rownum As Integer
Dim FilesFiled As Integer

Sheets("Orders").Select
FilesFiled = 0
rownum = 47
'Do

DirVar1 = Range("A" & rownum).Value 'office code
DirVar2 = Range("B" & rownum).Value 'HM PO#
DirVar3 = Range("F" & rownum).Value 'vendor code
DirVar4 = Range("L" & rownum).Value 'customer po

CurrDir = "HM" & DirVar1 & DirVar2 & " - " & DirVar3 & " - " &
DirVar4
CurrFile = "HM" & DirVar1 & DirVar2

Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists("C:\Ricoh Scan Files\*HMB4129.pdf") = True Then
fs.MoveFile "C:\Ricoh Scan Files\*" & CurrFile & ".pdf",
"R:\Orders\" & CurrDir & "\"
FilesFiled = FilesFiled + 1
End If

rownum = rownum + 1
'Loop Until DirVar2 = ""
MsgBox FilesFiled & " files were filed."
End Sub

? dir("C:\data\*test.xls")
DAOTest.xls

so

If dir("C:\Ricoh Scan Files\*HMB4129.pdf") < "" then

--
Regards,
Tom Ogilvy


"Michael McClellan" wrote in message
m...
I am usually the following code to move files into proper directories
based on what they have been named at the scanner.

The problem is: I cannot get the if statement with the FileExists
method to recognize that there are files in that directory that
contain the text "HMB4129". Why is this?

Sub FileScanned()
'
' FileScanned Macro
' Macro recorded 6/23/2004 by Michael McClellan, Jr.
'

'
Dim DirVar1 As String
Dim DirVar2 As String
Dim DirVar3 As String
Dim DirVar4 As String

Dim CurrDir As String
Dim CurrFile As String

Dim rownum As Integer
Dim FilesFiled As Integer

Sheets("Orders").Select
FilesFiled = 0
rownum = 47
'Do

DirVar1 = Range("A" & rownum).Value 'office code
DirVar2 = Range("B" & rownum).Value 'HM PO#
DirVar3 = Range("F" & rownum).Value 'vendor code
DirVar4 = Range("L" & rownum).Value 'customer po

CurrDir = "HM" & DirVar1 & DirVar2 & " - " & DirVar3 & " - " &
DirVar4
CurrFile = "HM" & DirVar1 & DirVar2

Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists("C:\Ricoh Scan Files\*HMB4129.pdf") = True Then
fs.MoveFile "C:\Ricoh Scan Files\*" & CurrFile & ".pdf",
"R:\Orders\" & CurrDir & "\"
FilesFiled = FilesFiled + 1
End If

rownum = rownum + 1
'Loop Until DirVar2 = ""
MsgBox FilesFiled & " files were filed."
End Sub

As Bob mentioned, FileExists does not allow wildcards. However, you should
be able to just drop that part and just do the Move part with Wildcards.
However, If destination is an existing file, an error occurs. Therefore,
consider using "CopyFile" with the overwrite option, and then "DeleteFile"
(on Source). Both of these allow Wildcards.

HTH
Dana DeLouis


"Michael McClellan" wrote in message
m...
I am usually the following code to move files into proper directories
based on what they have been named at the scanner.

The problem is: I cannot get the if statement with the FileExists
method to recognize that there are files in that directory that
contain the text "HMB4129". Why is this?

Sub FileScanned()
'
' FileScanned Macro
' Macro recorded 6/23/2004 by Michael McClellan, Jr.
'

'
Dim DirVar1 As String
Dim DirVar2 As String
Dim DirVar3 As String
Dim DirVar4 As String

Dim CurrDir As String
Dim CurrFile As String

Dim rownum As Integer
Dim FilesFiled As Integer

Sheets("Orders").Select
FilesFiled = 0
rownum = 47
'Do

DirVar1 = Range("A" & rownum).Value 'office code
DirVar2 = Range("B" & rownum).Value 'HM PO#
DirVar3 = Range("F" & rownum).Value 'vendor code
DirVar4 = Range("L" & rownum).Value 'customer po

CurrDir = "HM" & DirVar1 & DirVar2 & " - " & DirVar3 & " - " &
DirVar4
CurrFile = "HM" & DirVar1 & DirVar2

Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists("C:\Ricoh Scan Files\*HMB4129.pdf") = True Then
fs.MoveFile "C:\Ricoh Scan Files\*" & CurrFile & ".pdf",
"R:\Orders\" & CurrDir & "\"
FilesFiled = FilesFiled + 1
End If

rownum = rownum + 1
'Loop Until DirVar2 = ""
MsgBox FilesFiled & " files were filed."
End Sub

OK, so why doesn't this work?


If Dir("C:\Ricoh Scan Files\*" & CurrFile & ".pdf") < "" Then


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Great comment. However, there is almost no possibility that there will
be an existing file in the destination directory.

There is a great probability however that the file to be moved will not
exist in the "scanned files" directory.



*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

I get an error that says "Bad file name or number."


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Would the option explicit have anything to do with this?


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Hmm. When I change the filename manually to "HMA4114" this whole thing
works. LIKE A CHARM. But I need the directory to be in the form of a
variable so it can sift through all the files. Here is what I did.

Could it have something to do with the variable types of CurrFile or
DimVar2?

Sub FileScanned()
'
' FileScanned Macro
' Macro recorded 6/23/2004 by Michael McClellan, Jr.
'

'
Dim DirVar1 As String
Dim DirVar2 As String
Dim DirVar3 As String
Dim DirVar4 As String

Dim CurrDir As String
Dim CurrFile As String

Dim rownum As Integer
Dim FilesFiled As Integer

Sheets("Orders").Select
FilesFiled = 0
rownum = 7
Do

DirVar1 = Range("A" & rownum).Value 'office code
DirVar2 = Range("B" & rownum).Value 'HM PO#
DirVar3 = Range("F" & rownum).Value 'vendor code
DirVar4 = Range("L" & rownum).Value 'customer po

CurrDir = "HM" & DirVar1 & DirVar2 & " - " & DirVar3 & " - " &
DirVar4
CurrFile = "HMA4114" '"HM" & DirVar1 & DirVar2

Set fs = CreateObject("Scripting.FileSystemObject")
If Dir("C:\Ricoh Scan Files\*" & CurrFile & ".pdf") < "" Then
'If fs.FileExists("C:\Ricoh Scan Files\*HMB4129.pdf") = True Then
'On Error GoTo ErrHandler:
fs.MoveFile "C:\Ricoh Scan Files\*" & CurrFile & ".pdf",
"R:\Orders\" & CurrDir & "\"
FilesFiled = FilesFiled + 1
End If

'ReturnToMacro:
rownum = rownum + 1
'Exit Sub

'error handling routine
'ErrHandler:
'Resume ReturnToMacro

Loop Until DirVar2 = ""
MsgBox FilesFiled & " files were filed."

End Sub


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

As an Example
CurrFile = "ard"
? Dir("C:\Program Files\Adobe\Acrobat
5.0\Reader\Plug_ins\Annotations\Stamps\ENU\*" & CurrFile & ".pdf")
Standard.pdf

worked fine for me.

dir just produces the file name, so if you want to do something to the file,
you need to use the path with it. Also, each time call dir it gets a new
filename, so perhaps

sPath = "C:\Program Files\Adobe\Acrobat
5.0\Reader\Plug_ins\Annotations\Stamps\ENU\*"
CurrFile = "ard"
sName = Dir(sPath & CurrFile
if sName < "" then
filecopy sPath & sName, "C:\NewDir\" & sName

End if


--
Regards,
Tom Ogilvy

"Michael McClellan" wrote in message
...

I get an error that says "Bad file name or number."


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi. I don't have an answer, but a possible technique. To me, all the
variables do not make sense. See if this idea will help.
Have two string variables, and assign them the string from your code.

sFrom = "C:\Ricoh Scan Files....etc"
sTo = "R:\Orders\...etc"

In your code, include the following...

Debug.Print sTo
Debug.Print sFrom

fs.MoveFile sFrom, sTo

If it doesn't work, then you can just post the two lines from the Debug
statement. This "might" make it easier to spot the problem.

Again, just an idea. :)
HTH
Dana DeLouis


"Michael McClellan" wrote in message
...


Hmm. When I change the filename manually to "HMA4114" this whole thing
works. LIKE A CHARM. But I need the directory to be in the form of a
variable so it can sift through all the files. Here is what I did.

Could it have something to do with the variable types of CurrFile or
DimVar2?

Sub FileScanned()
'
' FileScanned Macro
' Macro recorded 6/23/2004 by Michael McClellan, Jr.
'

'
Dim DirVar1 As String
Dim DirVar2 As String
Dim DirVar3 As String
Dim DirVar4 As String

Dim CurrDir As String
Dim CurrFile As String

Dim rownum As Integer
Dim FilesFiled As Integer

Sheets("Orders").Select
FilesFiled = 0
rownum = 7
Do

DirVar1 = Range("A" & rownum).Value 'office code
DirVar2 = Range("B" & rownum).Value 'HM PO#
DirVar3 = Range("F" & rownum).Value 'vendor code
DirVar4 = Range("L" & rownum).Value 'customer po

CurrDir = "HM" & DirVar1 & DirVar2 & " - " & DirVar3 & " - " &
DirVar4
CurrFile = "HMA4114" '"HM" & DirVar1 & DirVar2

Set fs = CreateObject("Scripting.FileSystemObject")
If Dir("C:\Ricoh Scan Files\*" & CurrFile & ".pdf") < "" Then
'If fs.FileExists("C:\Ricoh Scan Files\*HMB4129.pdf") = True Then
'On Error GoTo ErrHandler:
fs.MoveFile "C:\Ricoh Scan Files\*" & CurrFile & ".pdf",
"R:\Orders\" & CurrDir & "\"
FilesFiled = FilesFiled + 1
End If

'ReturnToMacro:
rownum = rownum + 1
'Exit Sub

'error handling routine
'ErrHandler:
'Resume ReturnToMacro

Loop Until DirVar2 = ""
MsgBox FilesFiled & " files were filed."

End Sub


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

No.


Regards,
Tom Ogilvy


"Michael McClellan" wrote in message
...

Would the option explicit have anything to do with this?


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

If that is true, then the problem would appear to be in the construct of the
string with DirVar1 and DirVar2.

You need to look at it and see if it is well formed:

CurrFile ="HM" & DirVar1 & DirVar2 '"HMA4114"
msgbox "==" & CurrFile & "<=="

Could it have something to do with the variable types of CurrFile or
DimVar2?

I wouldn't think so - string should be appropriate

Perhaps try

DirVar1 = Range("A" & rownum).Text 'office code
DirVar2 = Range("B" & rownum).Text 'HM PO#
DirVar3 = Range("F" & rownum).Text 'vendor code
DirVar4 = Range("L" & rownum).Text 'customer po

--
Regards,
Tom Ogilvy



"Michael McClellan" wrote in message
...


Hmm. When I change the filename manually to "HMA4114" this whole thing
works. LIKE A CHARM. But I need the directory to be in the form of a
variable so it can sift through all the files. Here is what I did.

Could it have something to do with the variable types of CurrFile or
DimVar2?

Sub FileScanned()
'
' FileScanned Macro
' Macro recorded 6/23/2004 by Michael McClellan, Jr.
'

'
Dim DirVar1 As String
Dim DirVar2 As String
Dim DirVar3 As String
Dim DirVar4 As String

Dim CurrDir As String
Dim CurrFile As String

Dim rownum As Integer
Dim FilesFiled As Integer

Sheets("Orders").Select
FilesFiled = 0
rownum = 7
Do

DirVar1 = Range("A" & rownum).Value 'office code
DirVar2 = Range("B" & rownum).Value 'HM PO#
DirVar3 = Range("F" & rownum).Value 'vendor code
DirVar4 = Range("L" & rownum).Value 'customer po

CurrDir = "HM" & DirVar1 & DirVar2 & " - " & DirVar3 & " - " &
DirVar4
CurrFile = "HMA4114" '"HM" & DirVar1 & DirVar2

Set fs = CreateObject("Scripting.FileSystemObject")
If Dir("C:\Ricoh Scan Files\*" & CurrFile & ".pdf") < "" Then
'If fs.FileExists("C:\Ricoh Scan Files\*HMB4129.pdf") = True Then
'On Error GoTo ErrHandler:
fs.MoveFile "C:\Ricoh Scan Files\*" & CurrFile & ".pdf",
"R:\Orders\" & CurrDir & "\"
FilesFiled = FilesFiled + 1
End If

'ReturnToMacro:
rownum = rownum + 1
'Exit Sub

'error handling routine
'ErrHandler:
'Resume ReturnToMacro

Loop Until DirVar2 = ""
MsgBox FilesFiled & " files were filed."

End Sub


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!