![](/i/fill.gif) |
![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
But there may be someone here that could and would give me a quick answer:
Suppose there is an Excel file, oh let's say posted on a website daily.
The file contains licensing information and can be downloaded.
Suppose you have a separate list of license numbers you want to match
against those on the file.
Does Excel itself have the means to automate this match?
Post a reply to this message
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
Jim Charter schrieb:
> Does Excel itself have the means to automate this match?
Well, Excel has macros - so yes, quite definitely so...
Post a reply to this message
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
Jim Charter <jrc### [at] msn com> wrote:
> But there may be someone here that could and would give me a quick answer:
>
> Suppose there is an Excel file, oh let's say posted on a website daily.
>
> The file contains licensing information and can be downloaded.
>
> Suppose you have a separate list of license numbers you want to match
> against those on the file.
>
> Does Excel itself have the means to automate this match?
Yes it does. You can even make a refreshable web query that will pull the Excel
file from the website and make the matches.
I don't know how to do it, but I've heard of that sort of thing being done.
-Reactor
Post a reply to this message
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
Jim Charter wrote:
> Does Excel itself have the means to automate this match?
You could write a VBA macro on the first sheet, along with your license
numbers, and have the macro use an XMLHttpRequest to pull down the excel
file locally, then import it into the second sheet and do the appropriate
match. You'd have to wander around help files and MSDN to see exactly how, tho.
--
Darren New, San Diego CA, USA (PST)
I ordered stamps from Zazzle that read "Place Stamp Here".
Post a reply to this message
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
Thanks for the quick response guys.
In New York, the Taxi and Limousine Commission has instituted a new set
of regulations for For Hire Vehicle bases making the base owners
directly responsible for any cars they dispatch that do not have current
valid licenses. On their side the TLC is posting the list of valid
licenses on said Excel file. Bases in violation of this requirement
could be shut down. Even a one day shut-down of one of the larger
operations could easily impact them in the six figures range.
I have heard that some of these guys have purchased a program, priced as
much as $10,000, to check the validity of their drivers' licenses!
My boss (where I work training taxi drivers) sees an opportunity because
there are hosts of smaller bases who can't afford that, and, truth be
told, really don't get it that the TLC means business. He asked me if I
could write such a program we could distribute cheaply, both to make
some money for us, and to help educate these smaller guys who could
really get slammed. Frustrating to me because while scripting such an
app is well within my capability, writing something that could be
compiled, secured, licensed, and distributed, is not.
But at least, for one of our former instructors who is now in this
situation, I would like to put together something minimal for him to
use. If I could do it with an Excel macro that would be perfect.
Now I know that it is worth my time looking into. Thanks again.
-Jim
Post a reply to this message
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
Jim Charter wrote:
> Frustrating to me because while scripting such an
> app is well within my capability, writing something that could be
> compiled, secured, licensed, and distributed, is not.
If that's what you're trying to do, I'd suggest writing a program that
invokes Excel via COM automation. That is, write a program in a higher-level
language (Perl, Python, Tcl, C#, anything.NET, etc) that downloads the excel
spreadsheet to the local drive, starts Excel running without it opening a
window, and tells Excel to extract the list you're interested in and save it
as a CSV file, then script all you like from there. Alternately, continue
using COM to drive Excel and simply ask it what the value in each cell of
the spreadsheet is, comparing against your list.
I.e., treat reading the excel file in much the same way you'd read XML and
process it with javascript DOM stuff. *That* would be the part you could
compile and distribute. (This is the sort of thing .NET would be very good
for - Excel spreadsheets turn into just another object you access in C# with
array-index syntax and things like that.)
It would be pretty easy to tell the people using it that they need a
sufficiently-recent version of excel installed, and to install the ".NET
automation libraries" from the add/remove program details for Excel, and
then run your program.
--
Darren New, San Diego CA, USA (PST)
I ordered stamps from Zazzle that read "Place Stamp Here".
Post a reply to this message
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
Jim Charter wrote:
> But there may be someone here that could and would give me a quick answer:
>
> Suppose there is an Excel file, oh let's say posted on a website daily.
>
> The file contains licensing information and can be downloaded.
>
> Suppose you have a separate list of license numbers you want to match
> against those on the file.
>
> Does Excel itself have the means to automate this match?
Too bad it relies on Excel. It would be a lot easier with SQL.
-Mike
Post a reply to this message
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
SharkD wrote:
> Too bad it relies on Excel. It would be a lot easier with SQL.
If I'm not entirely mistaken, MS Access can execute SQL queries on Excel
spreadsheets. (And CSV files, in fact.)
--
http://blog.orphi.me.uk/
http://www.zazzle.com/MathematicalOrchid*
Post a reply to this message
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
SharkD wrote:
> Too bad it relies on Excel. It would be a lot easier with SQL.
SQL is a language, not a file format. You can't download SQL off the intarwebs.
Now, posting it as a CSV would have made more sense, were it really just
tabular data.
--
Darren New, San Diego CA, USA (PST)
I ordered stamps from Zazzle that read "Place Stamp Here".
Post a reply to this message
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
Jim Charter <jrc### [at] msn com> wrote:
> Frustrating to me because while scripting such an
> app is well within my capability, writing something that could be
> compiled, secured, licensed, and distributed, is not.
>
GPL the spreadsheet, then sell a "service" of teaching people how to use it. The
scenario here however is that the more well-written your little macro is, the
lesser chance of getting revenue for service.
Post a reply to this message
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |