Find CSV File Question

[robcarr]

Dear All,

I am using the coding below to find a file in dfile and save it in pathname. is there a way to find the file in dfile if dfile has 2 numbers in front of it.

Sheets("Personal&quot.Select
dfile = "M:\Templates\Saved Files\"
efile = "*all_work_summary.csv"
'the same line split down
pathname = "C:\Rob\Test\" & _
Format(Range("c2&quot.value, "mmmm&quot & "\" & _
Format(Range("c2&quot.value, "dd.mm&quot & ".xls"
'to here
pathname1 = Format(Range("c2&quot.value, "dd.mm&quot & ".xls"
ChDir dfile
'checks the location to see if file exists
sfile = dfile & efile
sfile1 = Dir(sfile)
sfile2 = dfile & sfile1
If sfile1 <> &quot;&quot; Then ' if the file exists it opens and saves as .xls
Workbooks.Open Filename:=sfile2
ActiveWorkbook.SaveAs Filename:=pathname, _
FileFormat:=xlNormal, Password:=&quot;&quot;, WriteResPassword:=&quot;&quot;, _
ReadOnlyRecommended:=False, CreateBackup:=False
' then closes the file
Application.CutCopyMode = False
ActiveWindow.Close
' displays a message advising file opened and saved to disk
MsgBox &quot;File &quot; & sfile1 & &quot; was found and saved to disk&quot; _
& &quot; as &quot; & pathname1, vbInformation
Kill sfile2
Mainform.Show
Else
' displays a message saying file does not exist in location.
MsgBox &quot;File &quot; & sfile & &quot; is not in location&quot;, vbExclamation
Mainform.Show

End If

Thanks Rob.

 

[Nelviticus]

Not sure that I undertsand your question properly, but I did spot one potential problem in your code: you need a 'ChDrive dfile' before your 'ChDir dfile'.

This is because if your current default path is on a different drive to the one in dfile, ChDir won't work, even though the drive letter is specified in dfile.

N.

 

[robcarr]

The coding works fine, I havent had a problem in 6 months with the coding as it is to remove 1 number from the front of the filename, what I want to do is if I get in the location a filename like 66all_work_summary.csv , i want it to remove the 66 from the filename.

Thanks

Thanks Rob.

 

[Nelviticus]

OK, well here's a little function that finds out whether the first character in a string is numeric and if so removes it, then does the same for the second character:

Function StripNum(sString As String) As String

' Strips the first two characters from sString if they are numeric

If Len(sString) > 1 And IsNumeric(Left(sString, 1)) Then

sString = Right(sString, Len(sString) - 1)

If Len(sString) > 1 And IsNumeric(Left(sString, 1)) Then

sString = Right(sString, Len(sString) - 1)

End If

End If

StripNum = sString

End Function

Just pass your file name to it like this:

dfile = StripNum(dfile)

Hope it solves your problem.

N.