Loop code, capture exceptions, and display pop-up
 

Hello -

I've built some code to pull in data from Workbooks submitted by my
stores. These workbooks have been in use for some time and several
'versions' have been released, with some format differences (ie data
in different cells). For this reason, it's important that workbooks
NOT in the correct version be 'rejected'. The code below skips these
workbooks, but I'd like to have the code capture these exceptions and
display a pop-up box at the end of the loop to tell the user which
workbooks have been skipped.

The pop-up should say something like:
"The following workbooks were not imported" .. and then list the
stores (values of 'getstore') one below the other. So, like this:
100
101
102
rather than
100 101 102

Can you help to modify my code to do this?

TIA,
Ray


The 'meat' of the code (assume all variables are properly defined):

If Fnum 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum), 0,
True)

Application.StatusBar = "Now processing File " & Fnum & "
of " & total

' Isolates the store number from the workbook name
getstore = mybook.Sheets("Dashboard").Range("E13").Value
getstore = Format(getstore, "000")
getversion = mybook.Sheets("Dashboard").Range("K2").Value
If getstore < "259" And getversion = "v5.0" Then

Set myC = basebook.Worksheets("DATA"). _
Range("e4:an4").Find(getstore, LookIn:=xlValues,
LookAt:=xlWhole)

If Not myC Is Nothing Then
Tcol = myC.Column
Else
MsgBox getstore & " wasn't found"
'Ot"her action to take when getstore is not
found
End If



basebook.Sheets("DATA").Activate

ActiveSheet.Range("A1").Offset(4, Tcol - 1).Value =
mybook.Sheets("Exec Summary").Range("N29").Value
ActiveSheet.Range("A1").Offset(5, Tcol - 1).Value =
mybook.Sheets("GPR").Range("T21").Value +
mybook.Sheets("GPR").Range("T22").Value
ActiveSheet.Range("A1").Offset(5, Tcol - 1).Value =
ActiveSheet.Range("A1").Offset(5, Tcol - 1).Value /
ActiveSheet.Range("A1").Offset(4, Tcol - 1).Value
ActiveSheet.Range("A1").Offset(6, Tcol - 1).Value =
mybook.Sheets("Exec Summary").Range("P44").Value
ActiveSheet.Range("A1").Offset(7, Tcol - 1).Value =
mybook.Sheets("Exec Summary").Range("N39").Value
ActiveSheet.Range("A1").Offset(8, Tcol - 1).Value =
mybook.Sheets("P&L Acct Detail").Range("W381").Value
ActiveSheet.Range("A1").Offset(9, Tcol - 1).Value =
mybook.Sheets("Exec Summary").Range("P22").Value * -1

ActiveSheet.Range("A1").Offset(0, Tcol - 1).Value =
mybook.Sheets("Dashboard").Range("K2").Value

mybook.Close savechanges:=False
End If
Next Fnum
End If

Loop code, capture exceptions, and display pop-up
 

Hi,

You could append all your messages into a big string and then show
this in the msgbox at the end.


dim strMsg as string

If Not myC Is Nothing Then
Tcol = myC.Column
Else
strMsg = strMsg & myBook.name & vbcrlf
End If

And then at the very bottom:

if not strMsg = "" then
msgbox("The following files did not import:" & vbcrlf & strmsg)
else
msgbox("All files imported ok.")
endif

Loop code, capture exceptions, and display pop-up
 

If Fnum 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum), 0, True)

Application.StatusBar = "Now processing File " & Fnum & "of " &
"total"

' Isolates the store number from the workbook name
getstore = mybook.Sheets("Dashboard").Range("E13").Value
getstore = Format(getstore, "000")
getversion = mybook.Sheets("Dashboard").Range("K2").Value
If getstore < "259" And getversion = "v5.0" Then

Set myC = basebook.Worksheets("DATA"). _
Range("e4:an4").Find(getstore, LookIn:=xlValues,
LookAt:=xlWhole)

If Not myC Is Nothing Then
Tcol = myC.Column
Else
MsgBox getstore & " wasn't found"
'Ot"her action to take when getstore is not found
End If

basebook.Sheets("DATA").Activate

ActiveSheet.Range("A1").Offset(4, Tcol - 1).Value = _
mybook.Sheets("Exec Summary").Range("N29").Value
ActiveSheet.Range("A1").Offset(5, Tcol - 1).Value = _
mybook.Sheets("GPR").Range("T21").Value + _
mybook.Sheets("GPR").Range("T22").Value
ActiveSheet.Range("A1").Offset(5, Tcol - 1).Value = _
ActiveSheet.Range("A1").Offset(5, Tcol - 1).Value / _
ActiveSheet.Range("A1").Offset(4, Tcol - 1).Value
ActiveSheet.Range("A1").Offset(6, Tcol - 1).Value = _
mybook.Sheets("Exec Summary").Range("P44").Value
ActiveSheet.Range("A1").Offset(7, Tcol - 1).Value = _
mybook.Sheets("Exec Summary").Range("N39").Value
ActiveSheet.Range("A1").Offset(8, Tcol - 1).Value = _
mybook.Sheets("P&L Acct Detail").Range("W381").Value
ActiveSheet.Range("A1").Offset(9, Tcol - 1).Value = _
mybook.Sheets("Exec Summary").Range("P22").Value * -1

ActiveSheet.Range("A1").Offset(0, Tcol - 1).Value = _
mybook.Sheets("Dashboard").Range("K2").Value

mybook.Close savechanges:=False
Else

msg = msg & getstore & vbNewLine
End If
Next Fnum

If msg < "" Then

MsgBox "The following workbooks were not imported..." &
vbNewLine & msg
End If
End If

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Ray" wrote in message
...
Hello -

I've built some code to pull in data from Workbooks submitted by my
stores. These workbooks have been in use for some time and several
'versions' have been released, with some format differences (ie data
in different cells). For this reason, it's important that workbooks
NOT in the correct version be 'rejected'. The code below skips these
workbooks, but I'd like to have the code capture these exceptions and
display a pop-up box at the end of the loop to tell the user which
workbooks have been skipped.

The pop-up should say something like:
"The following workbooks were not imported" .. and then list the
stores (values of 'getstore') one below the other. So, like this:
100
101
102
rather than
100 101 102

Can you help to modify my code to do this?

TIA,
Ray


The 'meat' of the code (assume all variables are properly defined):

If Fnum 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum), 0,
True)

Application.StatusBar = "Now processing File " & Fnum & "
of " & total

' Isolates the store number from the workbook name
getstore = mybook.Sheets("Dashboard").Range("E13").Value
getstore = Format(getstore, "000")
getversion = mybook.Sheets("Dashboard").Range("K2").Value
If getstore < "259" And getversion = "v5.0" Then

Set myC = basebook.Worksheets("DATA"). _
Range("e4:an4").Find(getstore, LookIn:=xlValues,
LookAt:=xlWhole)

If Not myC Is Nothing Then
Tcol = myC.Column
Else
MsgBox getstore & " wasn't found"
'Ot"her action to take when getstore is not
found
End If



basebook.Sheets("DATA").Activate

ActiveSheet.Range("A1").Offset(4, Tcol - 1).Value =
mybook.Sheets("Exec Summary").Range("N29").Value
ActiveSheet.Range("A1").Offset(5, Tcol - 1).Value =
mybook.Sheets("GPR").Range("T21").Value +
mybook.Sheets("GPR").Range("T22").Value
ActiveSheet.Range("A1").Offset(5, Tcol - 1).Value =
ActiveSheet.Range("A1").Offset(5, Tcol - 1).Value /
ActiveSheet.Range("A1").Offset(4, Tcol - 1).Value
ActiveSheet.Range("A1").Offset(6, Tcol - 1).Value =
mybook.Sheets("Exec Summary").Range("P44").Value
ActiveSheet.Range("A1").Offset(7, Tcol - 1).Value =
mybook.Sheets("Exec Summary").Range("N39").Value
ActiveSheet.Range("A1").Offset(8, Tcol - 1).Value =
mybook.Sheets("P&L Acct Detail").Range("W381").Value
ActiveSheet.Range("A1").Offset(9, Tcol - 1).Value =
mybook.Sheets("Exec Summary").Range("P22").Value * -1

ActiveSheet.Range("A1").Offset(0, Tcol - 1).Value =
mybook.Sheets("Dashboard").Range("K2").Value

mybook.Close savechanges:=False
End If
Next Fnum
End If

Loop code, capture exceptions, and display pop-up
 

Hi Stuart -

That worked perfectly ... THANKS!

br/ray