File exists, but code doesn't find it?

[ker_01]

I have some code that looks for a file; if it doesn't find it I need to exit
my sub because the rest of the sub clears my worksheet and imports new data
from the file. If the code runs without the file present, it clears my sheet
but doesn't have anything to import- which messes up a lot of subsequent
stuff.

Here is the code snippet; the problem is that I've manually placed the file
in the correct directory, but this code still doesn't find it, so it hits
the exit sub.
Any ideas what I'm doing wrong?
Thanks!!
Keith

Dim oFS As Object
Dim oFile As Object

Set oFS = CreateObject("Scripting.FilesystemObject")
On Error Resume Next
Set oFile = oFS.getfile(DataFileLocation)
On Error GoTo TTINT1NoFile:
If Not oFile Is Nothing Then 'e.g. if file is something- so this part
should execute but doesn't
'get date of file
DateTTINT1Modified = CDate(oFile.DateLastModified)
TTINT1OldDate = GetProperty("TTINT1Date", PropertyLocationCustom)
'if file is not new then alert user and don't bother to re-load file
If Not (DateTTINT1Modified > TTINT1OldDate) Then
MsgBox TTINT1 & " has not been modified since last data load;
file will not be reloaded until next updated file is available", , "Data
already loaded"
Exit Sub
End If
Else 'instead, it goes here and exits the sub
Exit Sub
End If
On Error GoTo 0

 

[ct60]

Hello

a quick look at this makes me wonder about the line

On Error GoTo TTINT1NoFile:

Is that a label in your sub? I think better might be to try

On error goto 0 (ie shut off error handling) and see what happens.

I hope that helps.

Chris

 

[ker_01]

My apologies- I didn't include the error loop. It is fairly simple (see
below). It isn't throwing that messagebox either, so I'm assuming that no
errors are occuring in the part of the code that would throw it to this
errorhandler. I've also walked through the code with F8 to confirm that it
hits the loop, but doesn't seem to think the file is there (drops to the
original post exit sub statement)
Thanks,
Keith

'Code from original post was at the top of the sub

'misc code to crunch the file, removed here for space

Exit Sub

TTINT1NoFile:
'error handling code
MsgBox "TTINT1.txt was not found at the expected location. Unable to
load updated TTINT1 data." & vbCrLf & vbCrLf & _
"TTINT files are removed once they have been processed, so if the TTINT
'upload' has already occured today you may ignore this error.", , "File Not
Found"
Resume Next

End Sub

 

[Jim Cone]

Looks like you need to assign a file path to the DataFileLocation variable.
Something like...
Dim DataFileLocation as String
DataFileLocation = "C:\Documents and Settings\Name\My Documents\FileName"
--
Jim Cone
Portland, Oregon USA




"ker_01"
wrote in message
I have some code that looks for a file; if it doesn't find it I need to exit
my sub because the rest of the sub clears my worksheet and imports new data
from the file. If the code runs without the file present, it clears my sheet
but doesn't have anything to import- which messes up a lot of subsequent
stuff.

Here is the code snippet; the problem is that I've manually placed the file
in the correct directory, but this code still doesn't find it, so it hits
the exit sub.
Any ideas what I'm doing wrong?
Thanks!!
Keith

Dim oFS As Object
Dim oFile As Object

Set oFS = CreateObject("Scripting.FilesystemObject")
On Error Resume Next
Set oFile = oFS.getfile(DataFileLocation)
On Error GoTo TTINT1NoFile:
If Not oFile Is Nothing Then 'e.g. if file is something- so this part
should execute but doesn't
'get date of file
DateTTINT1Modified = CDate(oFile.DateLastModified)
TTINT1OldDate = GetProperty("TTINT1Date", PropertyLocationCustom)
'if file is not new then alert user and don't bother to re-load file
If Not (DateTTINT1Modified > TTINT1OldDate) Then
MsgBox TTINT1 & " has not been modified since last data load;
file will not be reloaded until next updated file is available", , "Data
already loaded"
Exit Sub
End If
Else 'instead, it goes here and exits the sub
Exit Sub
End If
On Error GoTo 0

 

[ker_01]

Jim- My apologies, I was trying to cut down on the necessary code posted,
but that isn't a good strategy when others are helping troubleshoot. I have
the path and file set as global variables (I use them in other modules as
well). Here is the whole sub. The source file(s) are produced automatically
and the name/location doesn't change, and I've confirmed that it hasn't
changed in my code. Is there any other reason (recent windows updates,
anything at all?) that might affect whether the VBA can locate the file?
Maybe a lag in server response?

I tried the code at http://www.vbaexpress.com/kb/getarticle.php?kb_id=247 to
see if I could at least confirm that the directory is accessible to VBA;
I've confirmed that I can get a correct count of both the files in that
directory, and specifically the .txt files in that directory. For some
reason, the code below just quit working, and I can't figure out why

The good news is that the error occurs only about 20 lines in (marked
in-line), so you don't have to read the whole sub to see what's going on.
I appreciate any suggestions - fixes or additional ideas on what to
troubleshoot.
Best,
Keith


Global Const DataFileLocation = "\\Server\path\subpath\" 'real path replaced
with fake path in this post for IT security reasons
Global Const TTINT1 = "TTINT1.txt"
Global Const TTINT2 = "TTINT2.txt"

Sub ReadTTINT1()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.StatusBar = "Loading Raw Data Files: " & TTINT1
Dim LineofText As String
Dim Paragraphs() As String
Dim rw As Long
Dim lIndex As Long
Dim mywrksht As Worksheet

TTINT1_Parse = Array(0, 1, 8, 23, 36, 44, 71, 79, 86, 93, 102, 108, 114,
120, 700, 0)
TTINT1_MaxCols = 18

Dim oFS As Object
Dim oFile As Object

Set oFS = CreateObject("Scripting.FilesystemObject")
'On Error GoTo TTINT1NoFile: 'temporarily suppressed, to get real error
message
Set oFile = oFS.getfile(DataFileLocation) 'This is where it errors; run
time error 53 'file not found'
On Error Resume Next
If Not oFile Is Nothing Then
'get date of file
DateTTINT1Modified = CDate(oFile.DateLastModified)
TTINT1OldDate = GetProperty("TTINT1Date", PropertyLocationCustom)
'if file is not new then alert user and don't bother to re-load file
If Not (DateTTINT1Modified > TTINT1OldDate) Then
MsgBox TTINT1 & " has not been modified since last data load;
file will not be reloaded until next updated file is available", , "Data
already loaded"
Exit Sub
End If
Else
Exit Sub
End If
On Error GoTo 0

Application.StatusBar = "Loading " & TTINT1
Set mywrksht = Sheet8
'Clear old data- this is intentionally placed /after/ verifying there is
new data!
mywrksht.Activate
' mywrksht.Select
mywrksht.Rows("2:65536").Select
Selection.ClearContents

' LongFN = DataFileLocation & TTINT1
rw = 0

Dim X As Long
Dim FileLines() As String
FileLines = SplitFileIntoLines(DataFileLocation & TTINT1)
For X = 0 To UBound(FileLines)
'Debug.Print FileLines(X)
TextLineStartChar = Left(Trim(FileLines(X)), 1)
If (Len(FileLines(X)) = 0) Or (TextLineStartChar = "=") Then
'do nothing
Else
rw = rw + 1
Application.StatusBar = "Loading " & TTINT1 & " Row: " &
CStr(rw)
' now parse LineofText according to the column widths and
' put the values in an array.
If rw > 6 Then
For j = 1 To TTINT1_MaxCols
ParseStart = TTINT1_Parse(j)
ParseEnd = TTINT1_Parse(j + 1)
If ParseEnd > 0 Then
TotalDataArray(j, rw) = Trim(Mid(FileLines(X),
ParseStart, ParseEnd - ParseStart))
ConvertCol (j)
mywrksht.Range(Usecol & CStr(rw - 5)).Value =
TotalDataArray(j, rw)
Else
Exit For
End If
Next
End If
End If
Next

SetProperty "TTINT1Date", PropertyLocationCustom, DateTTINT1Modified,
False
Application.StatusBar = False

strNow = Format(Month(Date), "00") & Format(Day(Date), "00") &
Format(Year(Date), "0000")
Name DataFileLocation & TTINT1 As DataFileLocation & "OldRawDataFiles\"
& "TTINT1_" & strNow & ".txt"

Application.StatusBar = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Exit Sub
TTINT1NoFile:
'error handling code
MsgBox "TTINT1.txt was not found at the expected location. Unable to
load updated TTINT1 data." & vbCrLf & vbCrLf & _
"TTINT files are removed once they have been processed, so if the TTINT
'upload' has already occured today you may ignore this error.", , "File Not
Found"
Resume Next

End Sub

 

[ker_01]

Nevermind, I'm an idiot. I had the path, but due to replicating this code
for two data files with identical format, I append the filename later in the
sub, where is should be at the top. Life is good.

Thanks all,
Keith