MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Specifying Excel Return Code

  Asked By: Navin    Date: Jan 02    Category: MS Office    Views: 2154

I have an Excel application that makes EXTENSIVE use of VBA macros.
(Two files have a little over 11,000 lines of code, with not NEARLY
enough comments!)

Now, this application makes use of some (7) data files that are
generated by another application that I have no control over.
One file reads in the data and does some compilation, formatting, and
generates a report that is posted in a shared folder. Then, the
second file "kicks in" and extracts a subset of data from the shared
file and generates a report that is posted for yet another audience.
The whole process takes a little over an hour to execute manually.
(which is a HUGE savings over 6-8 hours it took the way they USED to
do it before I stuck my nose in)
After 4 years, I FINALLY got the PTB (Powers That Be) to agree to
schedule a job that creates the data files every night rather than
having to do it manually. (Hooray!)

I used VBScript to create a file to control the sequencing and set it
up to run as a Scheduled Task on my workstation. If I can get this
to run smoothly, I'll actually be able to take a vacation that
includes a Friday in which I don't call in and walk someone through
the steps to update the reports!
The whole job seems to run smoothly.
However, I've discovered that some of the data collected in the first
file may be incomplete, or in error.
Now, that doesn't affect the first report (because that's one of the
things I'm REPORTING) but, if one of these errors occur, I want to
skip the second report.

Now, I use the VBA method: Application.Quit to exit Excel and return
control to the VBScript, but I don't have any control over
the "return" code.

As in: RetVal = WshShell.Run ("Appname.xls",1,true)
RetVal always has 0...

I realize I can have the first Excel VBA "Call" the second Excel file,
but there are some socio-political reasons why I'm being asked NOT to
do it this way (the files are on different servers, and never-the-
twain-shall-meet kind-of thing)

so.. After all of that, the question is:
Does anyone know how to force VBA to issue a return value when
exiting either the file or excel?

My other option is to use VBS to search through 60,000 lines of text
in the data files and determine if any of the "suspect" data is
present before launching the second process, but that's undesireable
for a LOT of reasons.

any thoughts?



4 Answers Found

Answer #1    Answered By: Cais Nguyen     Answered On: Jan 02

An easy technique we use a lot here is to create (empty) text files  with
specific names as different steps in a long process are completed. For example:

Sub CreateCSV(WhichOne As String, OK As Boolean)
Dim fs As Object, a As Object
Set fs = CreateObject("Scripting.FileSystemObject")
Application.DisplayAlerts = False
If WhichOne = "PL" Then
If OK = True Then
Set a = fs.CreateTextFile("C:\temp\diffpl_dfs_iszero.csv", True)
a.Close ' Close the text file
Set a = fs.CreateTextFile("C:\temp\diffpl_dfs_isnotzero.csv", True)
a.Close ' Close the text file
End If
ElseIf WhichOne = "BS" Then
If OK = True Then
Set a = fs.CreateTextFile("C:\temp\diffbs_dfs_iszero.csv", True)
a.Close ' Close the text file
Set a = fs.CreateTextFile("C:\temp\diffbs_dfs_isnotzero.csv", True)
a.Close ' Close the text file
End If
End If
Set a = Nothing
Set fs = Nothing
End Sub

WhichOne identifies the process (in my example, either BS or PL); OK is a
good/bad status indicator. Your first process can create the appropriate text
file before it ends, and your second process can check for the expected file
when it starts. You will need to delete the files created the previous day

The above sub might be called like this:
Call CreateCSV("PL", True)
Call CreateCSV("PL", False)
Call CreateCSV("BS", True)
Call CreateCSV("BS", False)

Then I could use code  like the following to check for the existence of any of

Sub BBB()
Dim Fyle As String, FylePath As String
Fyle$ = "diffpl_dfs_iszero.csv"
FylePath$ = "C:\Temp\"
If Not FileExists(FylePath$, Fyle$) Then
MsgBox Fyle$ & " was not found", vbExclamation, "ERROR"
Exit Sub
End If
End Sub

Public Function FileExists(InPath As String, InFile As String) As Boolean
'Use Dir function to see if the desired file is found in the specified path.
'Declare variables for this macro.
Dim FyleName As String
'Get the names of all the files in the specified folder. Have to get the first
'one outside of the loop (quirk of the Dir function).
FyleName$ = Dir(InPath$ & "*.*")
Do While FyleName$ <> ""
If FyleName$ = InFile$ Then
FileExists = True
Exit Function
End If
FyleName$ = Dir()
FileExists = False
End Function

Answer #2    Answered By: Jaspreet Kapoor     Answered On: Jan 02

There's an extra possibility maybe as well... Using a text file as a log
file either in addition or instead of Hutch's solution. The file could have
all sorts of stuff in it including the date and time of various processes,
amounts of data processed and so on. Examining a text file is very quick
even with hundreds of lines.

The text file could even be emailed to a person to inspect, either in the
mornings or when something happens that needs intervention... glitches,
bottlenecks and so on.

If you got really fancy you could make a front end to list files  or look at
them in a list box on a user form!

Answer #3    Answered By: Elaine Stevens     Answered On: Jan 02

i am interested to learn VBA, still i am doing
via recording macro but i dont how to write code, and to use other things in
VBA, so kindly guide me for same and give some basic learning sites to achieve
for macro programmer

Answer #4    Answered By: Alexis Castillo     Answered On: Jan 02

See these beginner articles in TechTrax by Mark Thorpe. They are great to
get newbies rolling with Excel VBA...


Didn't find what you were looking for? Find more on Specifying Excel Return Code Or get search suggestion and latest updates.