Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Non-Interactive Excel

  Asked By: Rabiah    Date: Dec 15    Category: MS Office    Views: 3237
  

I've got a situation which I doubt is unique, so I'm hoping there's
LOTS of help out there!

I'm running Windows XP Professional, SP2 and MS Excel 2003 SP2.

I have an Excel file that has just over 9,000 lines of VBA code.
This file merges data from 9 ASCII files that are exported from other
applications.

My goal is to automate this process so that it can run unattended.
I've managed to get the "Powers That Be" to schedule jobs to export
the data I need on a daily basis.

Using VBScript, I was able to create a scheduled task that fires off
every day at 3:00am and collects the data, then launches my Excel
application, which then proceeds to do it's "thing". It runs
beautifully. Almost brings a tear to the eye!

But (isn't there always a "but"?) I must leave my workstation logged
in, with the screen locked, for it to work. If I log out or reboot
and the screen is at a "login" prompt, the VBScript starts off as
scheduled, but stops when it launches the Excel application. (The
scheduled task has my userid and password stored to provide
authentication)

I suspect that Excel is running in "interactive" mode. This would
require a User Interface to be active. Which it is not.

So... my question is this: Has anyone ever set up an Excel
application to run in an environment where no user is logged in?

VBA Help mentions an "Interactive" property which it says is "useful
if you're using DDE or OLE to communicate with MS Excel from another
application".

But I'm not sure if it's what I'm looking for.

I'm going to give it a try, but testing is time consuming. Since I
have to set up a scheduled task, log off. Wait until I think it's
done. Then log back in to check it out.

If anyone out there has any insight, I'd be thrilled to hear it!

Share: 

 

11 Answers Found

 
Answer #1    Answered By: Sophia Campbell     Answered On: Dec 15

you need to rephrase your question, although I did
understand what you were asking. I have lots of "insight" about
all kinds of things, so I'll be happy to share some of it with you
whenever it's relevant, although most of it is about music and how
to play the guitar. You have been a wonderful mentor to me here
in the group, among all the rest, and that's one of my insights.

As for running a VB script while logged off, no I don't have any
insight about that particular part of experience, to my dismay. It
did occur to me to be a very useful thing to be able to do, though,
so please post any solutions you find. I was wondering if you could
set up a DOS-prompt level batch file to do that, using the windows
scheduler to fire it? Just a thought to toss out in the the
mix . . .

 
Answer #2    Answered By: Andrew Brown     Answered On: Dec 15

Your suggestion is... germane?(relevant and appropriate).

However, that's what the VBScript does.
If you're not familiar with VBScript, Windows has a Visual Basic Script engine
(actually two, wscript.exe and cscript.exe)
It's actually a poor-man's Visual Basic compiler.
It has much of the functionality of Visual Basic, but it doesn't have forms.
I've got several of these that do things like:
-Load data from an ASCII file into an Oracl database.
-List files from a CD and write the list to a text file for search libraries.
-Locate folders for document numbers (I maintain 400,000+ documents in 250,000
folders. Rather than have user "drill down" through the folders to locate the
files they want, they just enter the document number, and the VBScript
determines the folder where the file is stored and opens the folder. Or, if not
found, it searches the archives and reports if the file has been archived)

For this application, I use the VBScript to locate the files on their "home"
server and copy them to my local server, then "clean up" the files (fields are
padded with spaces, and use a "pipe" (|) delimeter). At this point, it launches
the Excel application which uses it's own 9,000 lines of code to do it's
"magic".

As you suggested, I used the Task Scheduler to schedule the VBScript to run at a
specific time of day, then reboot the machine.

This works great.. as long as I "lock" my workstation when I leave for the day.
When I come in the next day, my machine is waiting patiently for me to log in,
and all is right with the world (at least my boss's world).

Problems arise when I (gasp!) take some vacation time! (or am sick).
Now, I tied the VBScript to my vacation schedule, so that if I'm scheduled to be
off, the script skips the reboot cycle (too bad I.T. guys, you can yell at me
when I get back from my fishing trip, right after I reboot my machine)

So, as you can see, the problem is NOT with running VBScript when I'm logged
off.
That runs nicely, thankyouverymuch.

The problem is that, if I am not logged in on the workstation, the VBScript runs
until it launches Excel. At this point, the process "hangs up". The process is
still RUNNING, but not going anywhere. Hence, my suspicion is that Excel is
requiring a "User Interface" to function.

Now, with this long-winded explanation, I'm sure I've lost many of the readers.
What I'm hoping for is to find someone who's run Excel in an "unattended" mode.

Thanks for your input.
Like I e-heard you mention in another posting: "compared to someone who has used
it for
decades, I'm new, too." I've used Excel macros for.. well, almost decades
(started with Lotus 123 back in the 80's!!!), and I'm still learning new things.

 
Answer #3    Answered By: Gustavo Costa     Answered On: Dec 15

I used lotus back in the 80's too, and yes, this group is
indeed a great teacher. I was continuing to think a bit, though
(my mistake, sorry, I really need to stop trying that thinking
thing) and yes, I am familiar with the VBScript part of Windows, but
haven't had a lot of time making mistakes with it yet (stay tuned,
though, since that's likely going to happen soon) . . .
Well, even the simplist of suggestions might be germane and hit
on the one thing that you or anyone else had seen before. If I
understand this group, everyone tosses out all the ideas they have
and maybe one in 10 hit the mark and unlock the need. Please be
patient with me about that.

 
Answer #4    Answered By: Tommy Thompson     Answered On: Dec 15

Can you share the code for VBScript to locate file on their "Home" Server and
copy them to "Local" Servier (Local Hard Disk)?

I am currently using DOS Batch file. I believe I can use the VBScript code in
Excel VBA so that I don't have to work with multiple files (Bat and Exe)

 
Answer #5    Answered By: Adelmo Fischer     Answered On: Dec 15

It sounds like you're wanting to take my VBScript
and use it in VBA, I already do that.
Perhaps it would be easier to just give you the VBA !!

Do you have their "home" server mapped to a drive letter?

Are you talking about copying some "other" file rather than the Excel file
you're running the VBA code in?

Let me know what you're currently doing with the bat file and perhaps I already
have something that is doing something similar that I can modify to do what
you're looking for.

 
Answer #6    Answered By: Mansur Bashara     Answered On: Dec 15

I was enlighted by the following post and I was able to accomplish what I need
to get done.

http://www.webmasterworld.com/forum10/5090.htm

I would appreciate if you could also enlighten me on how to accomplish the FPT
in VBA and VBScript. I am familiar with FileObjectSystem but have little idea of
networking.

1. I just need to get Excel files from FPT address to local hard drive.
2. I don't have the "home" server mapped to a local drive.

 
Answer #7    Answered By: Farah Khan     Answered On: Dec 15

Actually, I haven't done raw FTP for quite a while.
I'm doing SFTP from a Unix box, but that doesn't help  here.

If you're currently doing it with a bat file though,
is the bat file "fixed"? or do you have to modify it each time?

if it's fixed:
You can have VBA use the "Shell" function to launch the bat file.

If it needs to be changed:
What I've done in the past is this:
I needed to first use ftp to list the files in the folder at the ftp site, then
download the most recent.
I first used the opentextfile method to open a bat file.
then I write the ftp commands to the bat file, then use Shell to run the ftp.
The results of the ftp are written to a specific log file.
I then use the opentext file to open the log file.
I read the log and determine the filename I need to download.
Then, using OpenTextFile once again, I write the Ftp commands to download
the file, and use Shell to execute it.

If you need help writing the code for this approach,
send me a copy of the ftp file you want to create and I'll throw something
together for you.

 
Answer #8    Answered By: Eline Bakker     Answered On: Dec 15

What you mentioned is exactly what I am doing now. I thought the process is very
tedious. Anyway, I will keep on searching for a better and easier solution.

 
Answer #9    Answered By: Harriet Hughes     Answered On: Dec 15

I'm sorry, you said it was "tedious"??
What I meant when I said that I use ftp to list the contents of the folder, then
find the most recent file, then download it, was that I did it PROGRAMATICALLY.

I had VBA create the bat file, execute it, open the log file, read and process
the list, determine the most recent file, create a new bat file, execute it,
then proceed.

I wouldn't call it "tedious" at all. When I used to do that, I wrote it once,
then it ran for almost 3 years before I had to look at the code again because
they changed FTP sites...

I'm concerned that if you call that "tedous" maybe you misunderstood what I
said..

 
Answer #10    Answered By: Blandina Garcia     Answered On: Dec 15

I misunderstood what you said. I thought you had the knowledge to
write code in VBA language to do FTP, avoiding batch files, or to use VBScript
language to fatch files from network. My understanding is that VBScript can
accomplish a lot in networking. As I said before I don't understand a thing
about networking. I wish I could learn from you.

Boils everything down, the codings are all tediouse 0's and 1's. But I would
think, you would agree with me, that when you can write one line of code to get
things done, you don't write two lines. I am looking for ways to eliminate that
tediouse ".bat" procedure.

I am participting in this group to improve my skills, or if I can, help  peers. I
am extremely amzed by what you can accomplish by looking at your posting. Also
what you laid out for me is very clear and helpful. Thanks.

I don't undstand what you mean by "tomAYto", "tomAHto", would you
elaborate.

 
Answer #11    Answered By: Addison Campbell     Answered On: Dec 15

Sorry about the "tomAYto", "tomAHto" thing.
I shouldn't have done that.
There are a few phrases in english that have similar meanings.
For instance, I think it was a shakespear's Romeo and Juliet:
"What's in a name? that which we call a rose
By any other name would smell as sweet"
For the word "tomato". one pronunciation is "tomAYto" another is "tomAHto".
But they're both the same thing.
So the phrase has been shortened to something like:
"You say tomAYto, I say tomAHto"...

 
Didn't find what you were looking for? Find more on Non-Interactive Excel Or get search suggestion and latest updates.




Tagged: