Error

PeterTaylor

Board Regular
Joined
Aug 5, 2010
Messages
158
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

Well-known Member
Joined
Dec 30, 2008
Messages
10,853
Office Version
  1. 2019
Platform
  1. Windows
Which line is coming up with the yellow highlight when the error happens?
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

PeterTaylor

Board Regular
Joined
Aug 5, 2010
Messages
158
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

Well-known Member
Joined
Dec 30, 2008
Messages
10,853
Office Version
  1. 2019
Platform
  1. Windows
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
Code:
On Error Goto 0
Directly after
Code:
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

Board Regular
Joined
Aug 5, 2010
Messages
158
Jason
I change the code to
Code:
 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

Well-known Member
Joined
Dec 30, 2008
Messages
10,853
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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.
 
Last edited:

PeterTaylor

Board Regular
Joined
Aug 5, 2010
Messages
158
Jason I have resolved the problem with the following code:
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

Well-known Member
Joined
Dec 30, 2008
Messages
10,853
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,257
Messages
5,527,658
Members
409,780
Latest member
Sudheer121

This Week's Hot Topics

Top