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]

Oops, that might be cos I forgot to put it back in

This is where it needs to be

FileNotFound:
a = a + 1
            Windows("openfile.xlsx").Activate
Loop
End Sub

 

[PeterTaylor]

Dear Jason,
That cause the proc to run but it stops on the when it encounters the the first file that is not found.
Regards
Peter

 

[jasonb75]

This could take a while to sort out, I'll need to go through the rest of the code thoroughly.

In the current format, the code should execute normally, only passing on the current filename in the event of an error.

What happens if you kill the error handle after it's been executed?

FileNotFound:
On Error Goto 0
a = a + 1
            Windows("openfile.xlsx").Activate
Loop
End Sub

I can't see that making a difference but it's worth a shot.

 

[PeterTaylor]

Dear Jason,
adding that line produced the same response. I am going to sign off now as it is 1:30 in the morning here and I need to be up early in the morning.
Thank you for all your help so far; I will re-join the forum tomorrow.
And hopefully master the nuances of error trapping then. Wouldn't programming be easy if we lived in a perfect world!
Regards
Peter

 

[jasonb75]

Peter, I've tested some, but not all of this code, as I don't know the layout of your data, it's impossible to say what this will do, please use with caution!

As far as I can see it replicates your original, I've added in some comments to show what each stage is doing, and message boxes to report progress at several points.

Hopefully it will do what you need, or if not, the progress messages should identify the point at which it fails.

Sub testimport()
    ' Declare variables, open "openfile", define starting row for mylist
    
    Dim mylist As String, myFilename As String, aRow As Integer, bCol As Integer, TestBlank As Integer
        Workbooks.Open Filename:="C:\Users\Peter Taylor\Documents\testdata\openfile.xlsx"
            aRow = 1
    ' Start loop
Do
    ' Define values to mylist and myFilename for current pass of Do - Loop cycle
        mylist = Cells(aRow, 1).Value
        myFilename = Cells(aRow, 2).Value
    ' check length of current mylist value,for a zero length
    If Len(mylist) = 0 Then
    ' in case of stray blanks in unsorted data, check next 10 rows
    If TestBlank < 10 Then
    TestBlank = TestBlank + 1
    ' kick back to loop start
    GoTo NoError:
    Else
        MsgBox "Blank found in mylist at " & Cells(aRow, 1).Address & " Procedure will now exit"
Exit Do
    End If
    End If
    ' a valid entry needed to get here so reset testblank to 0
    TestBlank = 0
    ' for a non zero length value of mylist, report current value to user and attempt to open file
        
On Error GoTo FileNotFound
        MsgBox "Opening " & mylist
        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))
        ' Search for H1000 copy containing row to A18, end up
            Cells.Find("H1000", , xlFormulas, xlPart).Activate
                MsgBox "The active cell row is " & ActiveCell.Row
                    Rows(ActiveCell.Row).Copy Range("A18").End(xlUp).Insert
        ' Search for H1001 copy containing row to A2
            Cells.Find("H1001", , xlFormulas, xlPart).Activate
                ActiveCell.Row.Copy Range("A2").Insert
        ' insert blank into row 3
                    Rows("3:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        ' define col 1 to start concatenation array
            bCol = 1
        ' set to run while row 1 not blank
    While Len(Cells(1, bCol)) > 0
        ' set formula if row 2 not blank, else copy row 1 into row 3
        If Len(Cells(2, bCol)) > 0 Then
            Cells(3, bCol).FormulaR1C1 = "=R[-2]C&""_""&R[-1]C"
        Else: Cells(3, bCol) = Cells(1, bCol)
        End If
        ' next column
            bCol = bCol + 1
    Wend
        ' on exit bCol will be on the first empty column, bCol - 1 = last used column and counter value
            Range("G7") = bCol - 1
        ' concatenate string myFilename to filepath, save and close
            myFilename = "C:\Users\Peter Taylor\Documents\testdata\data\" & myFilename
                ActiveWorkbook.SaveAs Filename:=myFilename, FileFormat:=xlOpenXMLWorkbook
                    ActiveWorkbook.Close
        ' if code has reached this point then no error so bypass error handle
GoTo NoError
        ' error handle
FileNotFound
        ' Report error to user
        
            MsgBox "File " & mylist & " was not found, moving to next file"
        ' return to normal procedure
NoError:
        ' increase row and look to next mylist entry
            aRow = aRow + 1
        ' return to "openfile"
                Windows("openfile.xlsx").Activate
        ' restart loop
Loop
End Sub

I'll check back in morning to see if it's been successful.

 

[PeterTaylor]

Thanks Jason that works well (notwithstanding a couple minor typos).
The debugger did not like

"ActiveCell.Row.Copy Range("A2").Insert"
I changed this to:-

Rows(ActiveCell.Row).Copy
Range("A18").Select
Selection.End(xlUp).Select
Selection.Insert Shift:=xlDown
Probably a bit clunky but it works.

You have given me some good programming pointers; Thanks again.

One last thing on program flow
When a file is not found and the program skips to
"Filenotfound:"
does the program then execute all lines of code from this point
to the loop command?
Regards
Peter
</pre>

 

[jasonb75]

That was the one point I wasn't sure about, thought maybe I had trimmed the code a little too much there.

With the error handle, "workbooks.opentext mylist etc" is the line that will cause the error if the file doesn't exist.

Think of it like Monopoly, and the "Go to jail" corner if you dont hit it, you carry on as if nothing happened, if you do hit it you miss everything between you and it.

Once you're out you carry on as normal.

 

[PeterTaylor]

Thanks Jason
I have made a mistake and started a new post on hte same problem would you be able to check it out before the moderator nabs me.
Regards
Peter
Ps
apologies to the moderator!

 

[jasonb75]

Peter, I think it might be a small typo in the code that I missed

FileNotFound:

the : is missing in the code, if you add that in it should resolve the issue, if not, try adding

On Error Goto 0

directly below it.

 

[PeterTaylor]

Dear Jason
The ":" was already there ( one ofthe typos I found). I added the "on error GoTo 0" but the proc still stops on the second error.
Regards
Peter