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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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.
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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:
Upvote 0
Jason,
I don't understand your last post. When I try to run the sub it cannot find the "filenotfound".
Regards
Peter
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top