Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > fname = Dir() returns and 'invalid procedure call or argument'
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 17
Default fname = Dir() returns and 'invalid procedure call or argument'
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,494
Default fname = Dir() returns and 'invalid procedure call or argument'
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()

--


Gary

"Brenner" wrote in message
...
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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 17
Default fname = Dir() returns and 'invalid procedure call or argument'
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

--
Cool


"Gary Keramidas" wrote:

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()

--


Gary

"Brenner" wrote in message
...
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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,494
Default fname = Dir() returns and 'invalid procedure call or argument'
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

--


Gary

"Brenner" wrote in message
...
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

--
Cool


"Gary Keramidas" wrote:

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()

--


Gary

"Brenner" wrote in message
...
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





Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 17
Default fname = Dir() returns and 'invalid procedure call or argument'
That does it!

Gary - you're a Wizard! :o)

John


"Gary Keramidas" wrote:

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

--


Gary

"Brenner" wrote in message
...
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

--
Cool


"Gary Keramidas" wrote:

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()

--


Gary

"Brenner" wrote in message
...
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






Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
CreatePivotTable: Invalid procedure call or argument DesertCyclist Excel Programming 4 October 15th 10 03:53 PM
Invalid procedure call or argument for Dir$ (Run-time error '5') ? Alan[_8_] Excel Programming 0 July 21st 08 03:14 PM
Invalid Procedure Call Or Argument with VBScript [email protected] Excel Programming 0 November 28th 07 01:50 PM
Invalid procedure call or argument error Patrick Simonds Excel Programming 1 August 12th 06 11:40 PM
Invalid Procedure call or argument T De Villiers[_58_] Excel Programming 1 July 25th 06 03:01 PM


All times are GMT +1. The time now is 04:51 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement