|
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 09:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com