File not found in FileDateTime Function

I have an error mentioned in the title. To me it looks ok especially when I debug.print it seems ok and opens that file as well.

	


















	
Sub TensionTime()
Dim sPath As String
Dim sFile As String
Dim sDir As String
Dim oLB As Workbook
Dim namee As String
Dim aveht As Range
Dim avest1 As Range
Dim avest2 As Range
Dim filedate As Date
 
sPath = "J:\WHP Facility Folder\Arch 3 a - 909 - Projects Raw Data\Winding\"
sDir = FileSystem.Dir(sPath & "*" & namee & "*" & ".csv", vbNormal)

Do Until LenB(sDir) = 0
   Debug.Print sDir
    Set oLB = Workbooks.Open(sPath & sDir)
    
    ' -- Do Stuff
    Workbooks(sDir).Worksheets(1).Activate
   
        Range("F2").Select
    Set aveht = Range(Selection, Selection.End(xlDown))
    
    
    Workbooks("Date vs Spool Tension").Worksheets(1).Activate
    
    Range("D16") = WorksheetFunction.AverageIf(aveht, ">0")
    filedate = FileDateTime(sDir)
    
     Exit Sub
    
    
    '=AVERAGEIFS(F5:AM5,F5:AM5,"> 0",F$2:AM$2,"total")
    '
    If oLB.Saved = False Then oLB.Save
    oLB.Close False
    sDir = Dir$
Loop





End Sub


 

Originally Posted by Richard Buttrey

Perhaps because you are including the '*' character (which is not permitted in a file name) in the sDir variable ?

Hi
Seems that you are right. Have any ideas how to overcome this issue? P.S. I need to use "**" for the name variation

Originally Posted by Richard Buttrey

Hi,

Since you imply the file name variable bit changes then you must use a variable and allocate the string of varying characters to the variable before you encounter the line of code. And since you seem to have both a preceding and succeeding variable string you first need to Dim two variables for the changing bit of the file name. e.g.

Dim stFileNameVar1 As String
Dim stFileNameVar2 As String

Then allocate the actual file name. e.g. supposing the characters for the file name variations are in cells A1 and A2 on Sheet1 then

stFileNameVar1 = Sheet1.Range("A1")
stFileNameVar2 = Sheet1.Range("A2")

Then your line of code will be

sDir = FileSystem.Dir(sPath & stFileNameVar1  & YourFixedFileName & stFileNameVar2 & ".csv", vbNormal)

It did not work
Still says file not found.
I have declared string variable and singed value "*" to it and then used in the filename as suggested.


[CODE][/

 Sub TensionTime()
Dim sPath As String
Dim sFile As String
Dim sDir As String
Dim oLB As Workbook
Dim namee As String
Dim aveht As Range
Dim avest1 As Range
Dim avest2 As Range
Dim filedate As Date
  Dim lastRow As Long
Dim valueht As Range
Dim star As String
 namee = Range("A1")
star = "*"

If namee = "CA" Then

sPath = "J:\WHP Facility Folder\Arch 3 a - 909 - Projects Raw Data\Winding\"
sDir = FileSystem.Dir(sPath & star & namee & star & ".csv", vbNormal)

Do Until LenB(sDir) = 0
   Debug.Print sDir
    Set oLB = Workbooks.Open(sPath & sDir)
    
    ' -- Do Stuff
    Workbooks(sDir).Worksheets(1).Activate
   
    
   Range("F2").Select
    Set aveht = Range(Selection, Selection.End(xlDown))
    
    
    Workbooks("Date vs Spool Tension").Worksheets(1).Activate
    


lastRow = Range("f14").End(xlDown).Row

Range("f" & lastRow + 1).Select


 Selection = WorksheetFunction.AverageIf(aveht, ">0")
    
 

    
    filedate = FileDateTime(sDir)
   lastRow = Range("b14").End(xlDown).Row

Range("b" & lastRow + 1).Select


Selection = FileDateTime(sDir)
    
   'spool tension 1
    Workbooks(sDir).Worksheets(1).Activate
   
    
   Range("l2").Select
    Set aveht = Range(Selection, Selection.End(xlDown))
    
    
    Workbooks("Date vs Spool Tension").Worksheets(1).Activate
    


lastRow = Range("i14").End(xlDown).Row

Range("i" & lastRow + 1).Select


 Selection = WorksheetFunction.AverageIf(aveht, ">0")
   '
      'spool tension 2
    Workbooks(sDir).Worksheets(1).Activate
   
    
   Range("m2").Select
    Set aveht = Range(Selection, Selection.End(xlDown))
    
    
    Workbooks("Date vs Spool Tension").Worksheets(1).Activate
    


lastRow = Range("l14").End(xlDown).Row

Range("l" & lastRow + 1).Select


 Selection = WorksheetFunction.AverageIf(aveht, ">0")
 '
   'speed
       Workbooks(sDir).Worksheets(1).Activate
   
    
   Range("b2").Select
    Set aveht = Range(Selection, Selection.End(xlDown))
    
    
    Workbooks("Date vs Spool Tension").Worksheets(1).Activate
    


lastRow = Range("d14").End(xlDown).Row

Range("d" & lastRow + 1).Select


 Selection = WorksheetFunction.AverageIf(aveht, ">0")
    
    
  
    '
    If oLB.Saved = False Then oLB.Save
    oLB.Close False
    sDir = Dir$
Loop

End If

CODE]

Originally Posted by Richard Buttrey

Hi,

Since you imply the file name variable bit changes then you must use a variable and allocate the string of varying characters to the variable before you encounter the line of code. And since you seem to have both a preceding and succeeding variable string you first need to Dim two variables for the changing bit of the file name. e.g.

Dim stFileNameVar1 As String
Dim stFileNameVar2 As String

Then allocate the actual file name. e.g. supposing the characters for the file name variations are in cells A1 and A2 on Sheet1 then

stFileNameVar1 = Sheet1.Range("A1")
stFileNameVar2 = Sheet1.Range("A2")

Then your line of code will be

sDir = FileSystem.Dir(sPath & stFileNameVar1  & YourFixedFileName & stFileNameVar2 & ".csv", vbNormal)

I have solved it.
Thanks for help.
The problem was that in the function FileDateTime(sDir) , the sDir was just the name of file and it should be entire path+filename

pls how u do that im so lost

HTML Code: 

pls help