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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,833
Office Version
  1. 2019
Platform
  1. Windows
You could try adding
Rich (BB code):
On Error Resume Next
which simply skips the part causing the error, but this could cause problems if the rest of the code in the loop is still executed, a better option might be to try adding the lines below into the existing code at the points shown.

Rich (BB code):
your code
 
Do While myLen > 0
On Error Goto Line1
 
 
your other code
 
Line1:
Loop

Hope this helps.
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,276
Maybe something like this...

Code:
Dim wb as WorkBook

On Error Resume Next
Set wb = Workbooks.Open(Filename:= mylist)
On Error Goto 0

If wb is Nothing Then 
MsgBox "Workbook doesn't exist."
Else
' Do something here.
End If
 
Last edited:

PeterTaylor

Board Regular
Joined
Aug 5, 2010
Messages
158
Dear Jason,
I did this:
Do While myLen > 0
On Error GoTo FileNotFound
'"C:\Users\Peter Taylor\Documents\testdata\WIC_WADL1_2003A.txt"
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
FileNotFound:
Windows("openfile.xlsx").Activate
ActiveCell.Offset(1, 0).Select
mylist = ActiveCell.Value
myLen = Len(mylist)
ActiveCell.Offset(0, 1).Select
myFilename = ActiveCell.Value
ActiveCell.Offset(0, -1).Select
Loop

Hoping that if the file was not found the sub would go get the next filename and carry on. but the compiler is looking for another "do while". How do ifix this?
Regards
Peter
 

jasonb75

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

ADVERTISEMENT

Peter, the way I'm reading this, your code gets mylist from A1 and myfilename form B1

IF the filename in A1 is found open and execute code, if it returns error (not found)
then get mylist from A2 and myfilename from B2, rinse and repeat.

Is that what you think it should be doing but isn't, or is that what it is doing, but not what you expected?
 

PeterTaylor

Board Regular
Joined
Aug 5, 2010
Messages
158
Yes Jason that is correct I want the code to resume with the new values from A2 & B2.
 

jasonb75

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

ADVERTISEMENT

I've given your code a few tweaks but haven't tested it, see if this does the trick, note that this should replace the code from your original post, and from reply #4, I combined the 2 sections before making changes to it.

Code:
Workbooks.Open Filename:="C:\Users\Peter Taylor\Documents\testdata\openfile.xlsx"
    a = 1
Do
        mylist = Cells(a, 1).Value
        myFilename = Cells(a, 2).Value
If Len(mylist) = 0 Then Exit Do
        On Error GoTo FileNotFound
 
        '"C:\Users\Peter Taylor\Documents\testdata\WIC_WADL1_2003A.txt"
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
 
FileNotFound:
        a = a + 1
            Windows("openfile.xlsx").Activate
    Loop
 
Last edited:

PeterTaylor

Board Regular
Joined
Aug 5, 2010
Messages
158
Dear Jason,
You have shown me a much more elegant way of collecting the values of "mylist" and "myfilename" my problem is where in the code do I place the on error "filenotfound" section to make the sub run with new values.
I have included the whole sub so that you can get the overview.
Thanks
Peter

Sub testimport()
'
'
Dim mylist, myFilename As String
Dim myLen As Integer

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
a = 1
mylist = Cells(a, 1).Value
myFilename = Cells(a, 2).Value

Do While myLen > 0
On Error GoTo FileNotFound

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
FileNotFound:
Windows("openfile.xlsx").Activate
ActiveCell.Offset(1, 0).Select
mylist = ActiveCell.Value
myLen = Len(mylist)
ActiveCell.Offset(0, 1).Select
myFilename = ActiveCell.Value
ActiveCell.Offset(0, -1).Select

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


Cells.Find(What:="H1000", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
'MsgBox "The active cell row is " & ActiveCell.Row
Dim myID As Integer
myID = ActiveCell.Row
Rows(myID).Select
Selection.Copy
Range("A18").Select
Selection.End(xlUp).Select
Selection.Insert Shift:=xlDown

Cells.Find(What:="H1001", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
myID = ActiveCell.Row
Rows(myID).Select

Selection.Copy
Range("A2").Select
Selection.Insert Shift:=xlDown

Rows("3:3").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

'concatenate fields iterate until no values left in the top row

Dim myCounter, myH1000Len, MyH1001Len As Integer

Range("A1").Select
myH1000Len = Len(ActiveCell.Value)
ActiveCell.Offset(1, 0).Select
MyH1001Len = Len(ActiveCell.Value)
ActiveCell.Offset(-1, 0).Select
myCounter = 0
Do While myH1000Len > 0
ActiveCell.Offset(2, 0).Select
myCounter = myCounter + 1

If MyH1001Len > 0 Then
ActiveCell.FormulaR1C1 = "=R[-2]C&""_""&R[-1]C"
Else
ActiveCell.Offset(-2, 0).Select
Selection.Copy
ActiveCell.Offset(2, 0).Activate
ActiveSheet.Paste
End If

ActiveCell.Offset(-2, 1).Select
myH1000Len = Len(ActiveCell.Value)
ActiveCell.Offset(1, 0).Select
MyH1001Len = Len(ActiveCell.Value)
ActiveCell.Offset(-1, 0).Select
Loop
Range("G7").Select
ActiveCell.Value = myCounter
' do the rest of the ops here

myFilename = "C:\Users\Peter Taylor\Documents\testdata\data\" & myFilename
'select control finished
'Windows("openfile.xlsx").Activate
'MsgBox myFilename
ActiveWorkbook.SaveAs Filename:=myFilename, _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
mylist = Cells(a, 1).Value
myFilename = Cells(a, 2).Value
ActiveWorkbook.Close
Windows("openfile.xlsx").Activate
a = a + 1
mylist = Cells(a, 1).Value
myFilename = Cells(a, 2).Value

'ActiveCell.Offset(1, 0).Select
'mylist = ActiveCell.Value
'myLen = Len(mylist)
'ActiveCell.Offset(0, 1).Select
'myFilename = ActiveCell.Value
'ActiveCell.Offset(0, -1).Select
Loop

End Sub
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,833
Office Version
  1. 2019
Platform
  1. Windows
I've literally thrown the edit into the rest of your code to see if it produces the desired result, if it's good then time to start giving the rest a vanity makeover.

Code:
Sub testimport()
Dim mylist, myFilename As String
Dim myLen As Integer
Workbooks.Open Filename:="C:\Users\Peter Taylor\Documents\testdata\openfile.xlsx"
    a = 1
Do
        mylist = Cells(a, 1).Value
        myFilename = Cells(a, 2).Value
If Len(mylist) = 0 Then Exit Do
        On Error GoTo FileNotFound
 
        '"C:\Users\Peter Taylor\Documents\testdata\WIC_WADL1_2003A.txt"
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
Cells.Find(What:="H1000", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
'MsgBox "The active cell row is " & ActiveCell.Row
Dim myID As Integer
myID = ActiveCell.Row
Rows(myID).Select
Selection.Copy
Range("A18").Select
Selection.End(xlUp).Select
Selection.Insert Shift:=xlDown
Cells.Find(What:="H1001", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
myID = ActiveCell.Row
Rows(myID).Select
Selection.Copy
Range("A2").Select
Selection.Insert Shift:=xlDown
Rows("3:3").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
'concatenate fields iterate until no values left in the top row
Dim myCounter, myH1000Len, MyH1001Len As Integer
Range("A1").Select
myH1000Len = Len(ActiveCell.Value)
ActiveCell.Offset(1, 0).Select
MyH1001Len = Len(ActiveCell.Value)
ActiveCell.Offset(-1, 0).Select
myCounter = 0
Do While myH1000Len > 0
ActiveCell.Offset(2, 0).Select
myCounter = myCounter + 1
If MyH1001Len > 0 Then
ActiveCell.FormulaR1C1 = "=R[-2]C&""_""&R[-1]C"
Else
ActiveCell.Offset(-2, 0).Select
Selection.Copy
ActiveCell.Offset(2, 0).Activate
ActiveSheet.Paste
End If
ActiveCell.Offset(-2, 1).Select
myH1000Len = Len(ActiveCell.Value)
ActiveCell.Offset(1, 0).Select
MyH1001Len = Len(ActiveCell.Value)
ActiveCell.Offset(-1, 0).Select
Loop
Range("G7").Select
ActiveCell.Value = myCounter
' do the rest of the ops here
myFilename = "C:\Users\Peter Taylor\Documents\testdata\data\" & myFilename
'select control finished
'Windows("openfile.xlsx").Activate
'MsgBox myFilename
ActiveWorkbook.SaveAs Filename:=myFilename, _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
mylist = Cells(a, 1).Value
myFilename = Cells(a, 2).Value
ActiveWorkbook.Close
Windows("openfile.xlsx").Activate
a = a + 1
mylist = Cells(a, 1).Value
myFilename = Cells(a, 2).Value
'ActiveCell.Offset(1, 0).Select
'mylist = ActiveCell.Value
'myLen = Len(mylist)
'ActiveCell.Offset(0, 1).Select
'myFilename = ActiveCell.Value
'ActiveCell.Offset(0, -1).Select
a = a + 1
            Windows("openfile.xlsx").Activate
Loop
End Sub

Try typing (without spaces between the [ and ] )[code ] before and [/code ] after any code you post on here, it seperates it from the message text making it easier for people to read.
 
Last edited:

PeterTaylor

Board Regular
Joined
Aug 5, 2010
Messages
158
Jason,
I don't understand your last post. When I try to run the sub it cannot find the "filenotfound".
Regards
Peter
 

Watch MrExcel Video

Forum statistics

Threads
1,108,854
Messages
5,525,225
Members
409,637
Latest member
LT TASL

This Week's Hot Topics

Top