fname = Dir() returns and 'invalid procedure call or argument'

[Brenner]

I'm trying to verify filenames using the FName = Dir(nnn), but I prefer not
to open the files because it takes a long time across the network...

For a little background - the data conversions occur periodically, but they
are so 'involved' that they are not on a schedule, so the filenames have the
date included, but it could be 5 days or 20 days - and not something that I
can rely on in the code. So - I'm able to find out which "run" this is from
my workbook - if column r is blank, then I'm at run 13, etc.. - but there are
lots and lots of files in the folder, so I need to use a Dir("filename*.xls),
and then count those files - picking the 13th file (in this case).

Here is the code that I'm using. The FName = Dir() at the bottom is the one
that returns the error... There's a big section of code after "Else" (which
is irrelavent to this) that goes and gets the data, but I didn't want to
leave anything out...

Any suggestions?

Thanks!
John

cnt = 0
MyColumn1 = "D"
MyColumn2 = "H"
fName = Dir("\\mw\data\SMP-IT\Acceleration_Project\JDA_Docs\Johns Conversion
Metrics\ANA\Johns Conversion Load Metric ANA*.xls")
Do While fName <> ""
If FileExists(fName) = True Then
cnt = cnt + 1
End If
If cnt < ANA_Cnt Then
cnt = cnt + 1
Else
Set MyRange = Range("A12:A100")
For Each c In MyRange
If c.Value = "IDF" Then
MyRow = c.Row
Range(MyColumn2 & Trim(Str(MyRow))).Select
IDF_Value = Selection.Value
Range(MyColumn1 & Trim(Str(MyRow))).Select
IDF_Value = 1 - (IDF_Value / Selection.Value)
ElseIf c.Value = "IM" Then
MyRow = c.Row
Range(MyColumn2 & Trim(Str(MyRow))).Select
IM_Value = Selection.Value
Range(MyColumn1 & Trim(Str(MyRow))).Select
IM_Value = 1 - (IM_Value / Selection.Value)
ElseIf c.Value = "ORD" Then
MyRow = c.Row
Range(MyColumn2 & Trim(Str(MyRow))).Select
ORD_Value = Selection.Value
Range(MyColumn1 & Trim(Str(MyRow))).Select
ORD_Value = 1 - (ORD_Value / Selection.Value)
ElseIf c.Value = "PID" Then
MyRow = c.Row
Range(MyColumn2 & Trim(Str(MyRow))).Select
PID_Value = Selection.Value
Range(MyColumn1 & Trim(Str(MyRow))).Select
PID_Value = 1 - (PID_Value / Selection.Value)
ElseIf c.Value = "PSF" Then
MyRow = c.Row
Range(MyColumn2 & Trim(Str(MyRow))).Select
PSF_Value = Selection.Value
Range(MyColumn1 & Trim(Str(MyRow))).Select
PSF_Value = 1 - (PSF_Value / Selection.Value)
End If
Next
End If

fName = Dir()

Loop

Again - thanks!
JB

 

[Gary Keramidas]

this is untested, but you can try it:

add this to your declarations
Dim MyFiles() As String

then at the bottom:

ReDim Preserve MyFiles(1 To cnt)
MyFiles(cnt) = fName
fName = Dir()

 

[Brenner]

Thank you, Gary - but no, I still get the invalid procedure or argument...

Is there a way to do a Dir() and then just count through the results until
I've found the 'N-th' file?

John

 

[Gary Keramidas]

paste this in a new module and just see if it displays your file names. if it
does, add your code and test it. watch out for wordwrap on the filedir line, as
outlook express may break it into 2 lines instad of 1.

Sub test()
Dim FileDir As String
Dim FilesInPath As String
Dim MyFiles() As String
Dim NumberOfFiles As Long

FileDir = "\\mw\data\SMP-IT\Acceleration_Project\JDA_Docs\Johns Conversion
Metrics\ANA\"
FilesInPath = Dir(FileDir & "Johns Conversion Load Metric ANA*.xls")
NumberOfFiles = 0
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If

Do While FilesInPath <> ""
' your code would go here
MsgBox FilesInPath

NumberOfFiles = NumberOfFiles + 1
ReDim Preserve MyFiles(1 To NumberOfFiles)
MyFiles(NumberOfFiles) = FilesInPath
FilesInPath = Dir()
Loop
End Sub

 

[Brenner]

That does it!

Gary - you're a Wizard! )

John