Error

[PeterTaylor]

Dear All

I have list of files in a spreadsheet which I am progressively opening with the code fraagment:

Workbooks.Open Filename:= _
"C:\Users\Peter Taylor\Documents\testdata\openfile.xlsx"
Range("A1").Select
mylist = ActiveCell.Value
myLen = Len(mylist)
ActiveCell.Offset(0, 1).Select
myFilename = ActiveCell.Value
ActiveCell.Offset(0, -1).Select

Do While myLen > 0
Workbooks.OpenText Filename:=mylist, Origin:= _
xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:= _
False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1) _
, Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), TrailingMinusNumbers:=True

how can I capture the error condition when the file does not exist?
I thought of an If.. Then statement but not sure how to construct.

Regards,
Peter Taylor

 

[jasonb75]

Which line is coming up with the yellow highlight when the error happens?

 

[PeterTaylor]

This one:
Workbooks.OpenText mylist, xlMSDOS, 1, xlDelimited, xlDoubleQuote, Tab:=True, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1))

 

[jasonb75]

I must be missing something unbelievably obvious, just wish I knew what.

Are you stopping and restarting the procedure when the error occurs? Trying to continue from that point after making changes elsewhere will not work.

Try moving

On Error Goto 0

Directly after

Do

so that errors are live at the start of the Do- Loop cycle.

Are you getting the messagebox to report the filename from mylist prior to the error, and if so is it definitely a non existant filename?

 

[PeterTaylor]

Jason
I change the code to

 Do
on error GoTo 0

got completely out of excel re-entered then run the macro got the message that the file was about to open the first nonexistant file.
The sub wrote to the errortrap spreadsheet the file name. The proc then open a file that does exist. But when it tried to open the second nonexistant file the the message box came up saying it was going to open it (your message) then after pressing "OK" the proc stopped.

 

[jasonb75]

That doesn't make sense, maybe I'm using the wrong error procedure, I'll look into it in more detail, this could take a while to sort out.

The way the messages are set, it should give the "opening file ???" message even if it doesn't exist, then report the failure after.

 

[PeterTaylor]

Jason
It does give that message before the error happens. In both cases.

 

[PeterTaylor]

Jason I have resolved the problem with the following code:

FileNotFound:

        ' Report error to user
        
            MsgBox "File " & mylist & " was not found, moving to next file"
            Windows("errortrap.xlsx").Activate
            Cells(zRow, ZCol).Select
            ActiveCell.Value = mylist
            ZCol = ZCol + 1
            Cells(zRow, ZCol).Select
            ActiveCell.Value = "File not Found"
            ZCol = ZCol - 1
            zRow = zRow + 1
            [COLOR=Red]Resume NoError[/COLOR][\code]
The resume clears the error state
Thank you again for all you help
Regards
Peter

 

[jasonb75]

Hi Peter, my apologies for not replying earlier, Sundays just never seem to have enough hours to get everything done.

Thanks for letting me know you resolved the issue, to be honest, it's not a method I would have thought of using, but it works, and looking at various reference sources it seems like the ideal choice.