Hi, Sublime One

Please take a look at #313696 & tell me quickly is it a goer or not?

Many thanks

Bryan

---

Clarification of Question by probonopublico-ga on 07 Mar 2004 08:40 PST

Hi, Sublime One

Not the answer I had hoped for but it's not entirely unexpected.

Please post an answer for a job well done.

Many thanks & regards

Bryan

Bryan...

Thanks for authorizing my answer. I sincerely hope aht-ga
can build on the glimmer of hope he has offered, but I'll
formally post my answer nonetheless... :)

---------------------------------------------------------

In short - it seems unlikely, given what I know, which may
not be enough. All the automating options of which I'm
aware will open a program and run it, but unless the 
program has command line options built into the code,
allowing you to perform the specific actions you want,
it won't do much more than sit there, reminding you that
you need to do something with it (now what was it?).

There may well be command line options allowing you to 
open a particular document in Excel and print it, but 
you mentioned sorting the data, without saying exactly
how. I'm not aware of any way to automate the sorting 
of the data in a particular document using a batch file,
and, since I'm aware of a couple of researchers who are
wizards with Excel, and they haven't spoken up, I'm 
guessing they're not aware of the possibility either.

There are 'macros' that you can set up, which essentially
record a series of activities you perform on the cells
of the worksheet. I am not fluent in their use, however
I'm pretty sure they would depend on performing the exact
same operations on the exact same cells in the exact
order, every time. This may be your best option, if 
you use the same cells each time, and I believe you can
get the macro to run automatically when you open the
document, or you might be able to get it to run from a
command line - I just don't know enough about this.
But the likelihood of designing a macro and then getting
it to run when you open the document with a batch file,
and *then* printing the document, all from the same
command line batch file, seems very slim, to me.

There are similar programs which are independent of Excel,
which can record a series of mouse movements and clicks
and duplicate them on demand, but they have the same 
limitation of using the identical cells in the exact order
as before. Additionally, the placement of the program window
must be *exactly* where it was on the screen when you recorded
the movements, since a slight error in location will have the 
mouse clicking on the wrong cells - often with disastrous, or
amusing, results, depending on your point of view.

So, in short, it *might* be possible, but from my experience
it's probably not worth the effort to find out.

And that's just in regards to automating Excel.
In re-reading #313696, I noticed that you also want
to automate the searches and the transfer of the 
data from the search results to the document, which
makes it even less likely that you can automate the
entire process.

It is possible to accomplish this, but the only way
of which I'm aware is to hire someone to do it...  :)

sublime1-ga

Hi, Again, Sublime One

I appreciate your advice ... 

Maybe aht will come up with some further insights which we can handle separately.

I am grateful for the trouble that you have taken.

Warmest regards

Bryan

Hi Bryan...

I hesitate to post this as an answer, since I can't give 
you a definitive 'yes' or 'no' - though I should collect
my fee, if for nothing more than researching English slang
sites for the term 'goer'.  :)

In short - it seems unlikely, given what I know, which may
not be enough. All the automating options of which I'm
aware will open a program and run it, but unless the 
program has command line options built into the code,
allowing you to perform the specific actions you want,
it won't do much more than sit there, reminding you that
you need to do something with it (now what was it?).

There may well be command line options allowing you to 
open a particular document in Excel and print it, but 
you mentioned sorting the data, without saying exactly
how. I'm not aware of any way to automate the sorting 
of the data in a particular document using a batch file,
and, since I'm aware of a couple of researchers who are
wizards with Excel, and they haven't spoken up, I'm 
guessing they're not aware of the possibility either.

There are 'macros' that you can set up, which essentially
record a series of activities you perform on the cells
of the worksheet. I am not fluent in their use, however
I'm pretty sure they would depend on performing the exact
same operations on the exact same cells in the exact
order, every time. This may be your best option, if 
you use the same cells each time, and I believe you can
get the macro to run automatically when you open the
document, or you might be able to get it to run from a
command line - I just don't know enough about this.
But the likelihood of designing a macro and then getting
it to run when you open the document with a batch file,
and *then* printing the document, all from the same
command line batch file, seems very slim, to me.

There are similar programs which are independent of Excel,
which can record a series of mouse movements and clicks
and duplicate them on demand, but they have the same 
limitation of using the identical cells in the exact order
as before. Additionally, the placement of the program window
must be *exactly* where it was on the screen when you recorded
the movements, since a slight error in location will have the 
mouse clicking on the wrong cells - often with disastrous, or
amusing, results, depending on your point of view.

So, in short, it *might* be possible, but from my experience
it's probably not worth the effort to find out.

sublime1-ga

P.S. And that's just in regards to automating Excel.
In re-reading #313696, I noticed that you also want
to automate the searches and the transfer of the 
data from the search results to the document, which
makes it even less likely that you can automate the
entire process.

It is possible to accomplish this, but the only way
of which I'm aware is to hire someone to do it...  :)

sublime1-ga

probonopublico:

The website you referenced in that question unfortunately is not
accessible again until tomorrow, so I cannot take a further look.
Depending on the web-based interface, it *may* be possible look up,
retrieve, sort, and collate all of the data using Excel and a Visual
Basic for Applications program/macro. Actually, that should be a big
*MAY*. It'll have to wait until tomorrow before I can see for myself
(sorry, didn't look earlier when you'd posted the question, as paf had
already asked for Clarification).

As a guideline, I can refer to a situation from my own past. Being a
Canadian, there is only one sport worth following/watching/betting on,
and that would be ice hockey. Prior to the advent of various free
online hockey pool sites, I tasked myself with creating a
fully-automated hockey pool reporting system for the pool in my
company. It was a labour of love created entirely in Excel VBA, and
was complete with locally-served web pages for each pool member's
status and ranking.

The hardest part of the entire program was finding an online data
source with data that could be easily scraped from web pages, and
providing enough flexibility in the program to parse that data and
sort it intelligently without requiring artificial intelligence in the
PC.

So, if time permits, I can take a look at the source website tomorrow
evening, and advise you if I think that there is a possible solution.
The solution will most likely take someone at least a day to create,
and a couple of weeks to test and fine-tune (since I am assuming that
the website does not exactly get updated on a minute-by-minute basis).

How important is this to you? 

aht-ga
Google Answers Researcher

Hi, aht

Many thanks for your interest.

I will appreciate any pointers that you can throw up.

Actually, this particular application is not very important but I can
think of other similar stuff that could be more important.

Warmest regards

Bryan

probonopublico:

OK, I'll let you know how viable I think it would be, after I look at
the website. In any case, it would take a custom program written in
Excel VBA to accomplish the task; the part that remains to be seen, is
how difficult that program will be to write, based on how the website
displays the information. If, for example, they take the same approach
that some domain registrars have taken to prevent 'bots from scraping
domain-name expiration dates from their databases by only displaying
search results as graphic files, then the task will be much more
difficult (not impossible, just more trouble than it is worth). So,
we'll see! I'll post my opinion here, in this question's comment
section.

Regards,

aht-ga
Google Answers Researcher

Bryan...

Thanks very much for the tip! From what I've seen of aht-ga's
computer savvy, you're in good hands. I hope it works out for
you!

Best regards...

sublime1-ga

Hi, Again, Sublime One

I agree ...

aht is so good it's scary

But I would not like him to find out.

Hi probonopublico:

The time zone difference between the UK and my part of the world does
come in handy every now and then. For example, I was able to look at
the Company Search webpage just now, so that you do not have to wait
until tomorrow for my reply.

Unfortunately, the Company Search page uses several methods to prevent
easy automation. First, they use a form POST to a CGI script,
something that isn't easy to duplicate without resorting to licensed
third-party software.

Secondly, and most annoyingly, they use session IDs to track visitor
sessions. So, even though the actual results pages might still be
accessible to an automated system as long as the company names and
company numbers are precisely known, the session ID effectively means
that all searches must start at the form on the homepage.

Therefore, the short answer is that no, this process cannot be easily
automated. The effort required to make this work, far exceeds the
utility.

Now, that's just my opinion; somewhere, out there, is a programmer who
could probably tackle this with one hand tied behind his/her back, and
all the keys on their keyboard remapped. The limitation for me is that
I prefer to generate code that the client can actually modify in the
future; this means using the Visual Basic for Applications
capabilities built into the MS Office suite. While VBA is highly
capable, it can't handle this one without needing third-party
software.

Sorry!

aht-ga
Google Answers Researcher

Hmm... the plot thickens. Apparently, Microsoft slipped in a package
in some of their developer packages that adds a redistributable
control which would allow a VBA program to work with forms. Only
problem is, I don't have any developer packages installed at the
moment that have this control (MSINET.OCX, for the record). Time to go
searching...

Oh well, false hope. Just checked the legal language regarding
redistribution of msinet.ocx; I can only redistribute it if the
program that uses it is developed within one of the developer
packages. Since I want to use VBA, which does not reside necessarily
within one of the developer environments, I am not allowed to
redistribute it. Close!

aht-ga

Oh, what a shame, aht

But very many thanks for looking into this for me, I'll throw you an
easier one as soon as one comes to mind.

Warmest regards

Bryan