wrote:
hahah wow Jay i didnt know there were really living breathing thinking
excel dorks.

i'll make a better example tonight.

-aaron

Still waiting for the better example. Care to back up your claim with
a specific and testable solution?

As I mentioned, I do not require any comparison to an Excel solution.
Assume that I only have Access installed. Just get the job done in
Access, please. Not an outline, not unfinished ideas. Not code that
you still have to translate. A working solution. As you claim, you
can do this much easier using a database than any Excel dork can, so a
detailed post should not take long, either.

Please do not use Harlan's suggestion of setting a reference to the
Excel object model. That is not a pure Access solution.

Tony

if you could post on here without being a clueless MDB newbie; that
would help also

i dont need responses.

i need people to start speaking the TRUTH.

you guys slam DAP and ADP all day long. you slam VB.

if you dont want an argument; then you guys need to stop talking trash.

all im doing is giving you 10% of your own egocentricism back in your
face.

aaron -

Unlike Mr. Toews, I note that you are neither valuable, nor
professional.

If your use of spreadsheets involves re-creating them several times a
week or 'copying and pasting' 100 times over, perhaps you should get
back to basics.

Press 'F1' on your keyboard, and type the word 'macro' into the speech
bubble that the cute animated assistant offers.
You may well learn something.

Wow man, don't be all mad at me cause your lack of people skills (which you
are showing right this second) is screwing up your life.

Besides you have no idea what I do for a living. But I'll give you a hint.
One of them is devolop databases. I just happen to feel excel is less labor
intensive for a lot of things. I mean why go to all the trouble to use a
database and set things up manually when it's frequently all prebuilt and at
your fingertips.

Remeber the idea is to get the job done in a reasonable amount of time. Not
prove how smart you are. Or do the "optimum" solution. More over you CAN
email out reports from excel automaticlly you are just ignorant as to how.
Finally even if you email your database reports out, how pray tell would you
make them interactive without emailing out the entire database or setting up
access to said. This get's even more complicated if you have personal
offsite.


wrote in message
ups.com...
and just for the record; you trendy asshole

just because there's more people doing it; it doesn't mean it's the
right thing to do.

that is why you are a disease. GROUPTHINK numbnuts.

do you really believe that the sheer count of people has anything to do
with what people should learn?
i mean-- you idiots have been coasting for too ****ing long

and you aren't worth minimum wage.

eat **** spreadsheet dip****

you can't crunch numbers better than i can

you can't 'ANALYZE DATA' better than i can.

you sit there and copy and paste the same ****ing thing 100 times. big
****ing deal. you dont deserve 10 cents an hour.

BECAUSE YOU GUYS SIT THERE AND BUILD THE SAME SPREADSHEET WEEK IN AND
WEEK OUT.

I mean.. what the **** who do you think you are?

Excel is a disease; and you all are lepers

Gee I don't know about that Tony... This DOES seem to be the most active
thread in the group :-S
"Tony Toews" wrote in message
...

and just for the record;

Aaron

If you could respond in a decent fashion without flipping your lid you'd
get better
responses.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

"Oorang" wrote:

Gee I don't know about that Tony... This DOES seem to be the most active
thread in the group :-S

<chuckle

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

you're a dip****; it's the wrong platform kids

spreadsheets; all spreadsheets-- involve cutting and pasting the same
formula 100 times.

-Aaron

EXCEL DOESNT HAVE REPORTS THAT WHY I HATE IT AND I THINK THAT ALL
SPREADSHEET DORKS SHOULD DIE A PAINFUL DEATH.

just for the record; no i dont get any specs; i deliver solutions

half of my work is fixing spreadsheet messes. the other half is fixing
MDB messes.

SQL Server is the answer to all your problems; you guys need to lose
the training wheels and start doing real dev.
i mean-- if you're going to write it; write it correctly the first time
instead of having a 2gb limit and crap performance

wrote...
....
spreadsheets; all spreadsheets-- involve cutting and pasting the same
formula 100 times.

It's the spreadsheet equivalent of iteration.

What's the matter? Having some difficulty getting matrix inversion code
programmed in VBA?

wrote:

spreadsheets; all spreadsheets-- involve cutting and pasting the same
formula 100 times.


You are right -- that is all that can be done with a spreadsheet. Very
good, Aaron.

However, why are you still discussing spreadsheets at this point in the
thread? *You* were going to offer a solution to Harlan's matrix
challenge without using Excel. You would be able to show some
competency with databases, rather than just talk about how good you
are.

I, for one, am still waiting for your solution.

wrote...
just for the record; no i dont get any specs; i deliver solutions
....

Oh, so you prey on small companies that don't really have a clear idea
why they've hired you.

How's the matrix inversion coding going?

wrote...
i know vba like the back of my hand.
....

But you never post any VBA code to newsgroups to show off your
*claimed* but unsubstantiated VBA coding brilliance.

Google Groups search on newsgroup * from author aaron.kempf containing
the phrase 'End Function' - none of the 3 threads found showed any VBA
code from you. Switching to the phrase 'End Sub' showed 1 of 3 matching
threads with this contribution from you.

Public Sub CaptureExcelValues()

Dim xlApp
Dim wb
Dim ws
set xlApp = createobject("EXCEL.APPLICATION")
set wb = xlApp.workbooks.open("c:\myspreadsheet.xls")
set ws = wb.sheets("MyHappySheetName")

then you can just do anything that you want with it.

End Sub

Hardly original, hardly complicated. And you never followed up with the
OP when it was clear your response didn't get the OP anywhere close to
what s/he needed.

http://groups.google.com/group/micro...a7d053582ad88e

(or http://makeashorterlink.com/?M5BA2382C ).


what does harlan do?

HE MAKES THE SAME FRIGGIN SPREADSHEET WEEK IN AND WEEK OUT.
IT'S IMPOSSIBLE to automate excel and make it half as efficient as
Access.
....

This is your constant claim. Because *YOU* know nothing more than
recreating spreadsheets day after day (someday you may get one right,
accidents can happen), you claim that's what *I* do. You have no clue
what I do.

LITTLE GIRLIE-MEN use excel

I'll leave it up to others to figure out what to call people like you
who claim database expertise and the ability to do ANYTHING Excel can
do in either VBA or Access or SQL Server but just can't seem to figure
out how to prove it. Loudmouth, ignoramus and bull****ter aren't
inappropriate, but they're inadequate to sum up the full extent of your
militant ignorance.

wrote...
....
you're the one-trick pony.. i mean.. what are you going to do; make a
spreadsheet of all the people that swear in a newsgroup and block me??
....

No one in their right mind would dream of blocking you. No matter how
ignorant and misanthropic the rest of us may be, we can read your posts
and realize we've still a long ways to go before we'd reach your level.
It's a testament to your, er, character that there's no limit to how
low you'll go. Keep on impressing the rest of us!

hahaha good stuff harlan

so you measure my brilliance based on how many threads i have with end
function, huh?

END FUNCTION

i'll end your function spreadsheet dork

wrote...
hahaha good stuff harlan

so you measure my brilliance based on how many threads i have with end
function, huh?
....

Actually, I measure your credibility by the quantity of VB[|A|S] code
you've posted. Minimal code, so minimal credibility.

Do you know how to do anything other than rant?

BTW, how's the matrix inversion coding going? Need some help?

i prey on small companies?

hahaha

no i develop small apps on a small budget and i do a damn good job

i couldn't find the UDF i was looking for; all i know is that
subqueries are about 100 times more powerful than anything that excel
can do

so go and play with your little barbies kids

YAY let's play with perl and python and excel

(i'll have a chance to do the inversion thing soon)

wrote..
....
(i'll have a chance to do the inversion thing soon)

More unsubstantiated blather. You've already spent a chunk of time
since last Friday making these signature responses of yours, but
apparently no time on what you claim would be so easy to do. Some
people may begin to believe you know squat all.

harlan

just for the record; im not a programmer; im db folk

and i eat spreadsheet dorks like you for breakfast

and btw, how is you report against 66k rows?

rofl

what you gonna do when you hit the 64k limit, harlan?

i'll find a function that does this magic math you talk about.. i
mean-- it's NOT rocketscience

wrote...
just for the record; im not a programmer; im db folk
....
and btw, how is you report against 66k rows?

You seem to need repetition, not that it's likely to work: I DON'T
PRODUCE REPORTS. I use a few canned spreadsheet templates that combine
some time series forecasting with discounted cashflow analysis, and a
few others that read in output from external simulation programs
(.EXEs). I maintain a few others for use by field office users
(including myself) that are little more than simple UIs for
interpolating various factors from table lookups. None of these use
more than 3000 rows.

I'll repeat: use the best tool for the task. Managing tens of thousands
of records isn't something spreadsheets do well, and I don't misuse
spreadsheets for that sort of thing. If you do, that's your problem.

what you gonna do when you hit the 64k limit, harlan?

Since I never will (since I won't misuse spreadsheets for the type of
data that spans that many records), moot point.

i'll find a function that does this magic math you talk about.. i
mean-- it's NOT rocketscience

No, not rocket science, but anything more than counting your fingers
and toes (and your written records in newsgroups suggests you're one of
the lucky inbred few with more than 10 and 10) seems to take you a LONG
TIME to figure out.

Since I'm getting tired of waiting, I'll help you out. Since you've
proven you can't recognize Pascal, I'm going to assume you're only
competent in BASIC. Here's a link to a PDF (no doubt more bitching &
griping to come) showing some old style BASIC code (with LINE NUMBERS)
showing a workable procedure.

http://www.cs.berkeley.edu/~wkahan/MathH110/gji.pdf

Perhaps you're competent to translate BASICA into VBA (unproven, so
surprise me). The devil's still going to be in reading source matrices
from DBMS tables and writing result matrices back to DBMS tables.

i call bull**** on that

you make the same friggin spreadsheet week in and week out

sure you change some numbers

it just would be much mroe efficient to automate it with a real program

spreadsheets are for girlie-men and perl and python aren't the answer
to your problems.

fine.. screw microsoft; use crystal reports and mysql for all i care.

all i know is that you sit there and make the same-- or similiar
spreadsheets all the time.
and it would be in YOUR best interests to start doing things with VALUE
instead of throwing time and money at copy and pastedom

http://www.fmsinc.com/tpapers/genaccess/DBOD.asp
---------------
Millions of databases are created in Excel spreadsheets each year, but
only a tiny percentage "graduate" to the next level: Access.
Similarly, only a tiny percentage of Access applications graduate to a
more sophisticated solution. In the interim, a huge number of database
needs are solved completely by Access. Access is simply the best at
what it does.

An IT manager needs to understand and use Access tactically, and
anticipates that some Access applications migrate over time. This is
not an indictment on Access, but rather the natural process of database
evolution as business needs change. Sure, it would have been better to
build that Access application with a more sophisticated platform from
the beginning, but it was impossible to predict it would be that
important when it was first created. Similarly, is it possible to
predict which 2% of databases created this year need to migrate three
years from now? Most will run perfectly fine in Access forever or go
extinct. Making a big investment today makes no sense when a simpler,
less risky Access solution is possible. Let time determine which
databases evolve and require additional investment to take them to the
next level. The key is to anticipate this.

Even when Access applications evolve to another platform, Access scales
by supporting the migration of Jet to SQL Server while preserving the
application development investment. The features developed for Access
can be rolled into the new platform guaranteeing the success of the new
system (or at least minimizing end-user objections). In that case,
Access proved to be a great prototype.

The savvy IT manager learns when Access is effective and when it's
not. If it can be done in Access, the ROI is superior to alternate
technologies. Taking advantage of the strengths of Access gives your
organization a significant competitive advantage both financially and
in response to user, market, and customer conditions.

i can write matrices and read matrices all the time

'oh no, you need to handle multi-dimensinal sources'

im the king of multidimensional asshole

you can't even do real pivotTables kid

wrote...
i call bull**** on that

So much easier than figuring out how to deal with my challenge.

you make the same friggin spreadsheet week in and week out

No, you only claim I do. You know nothing about what I do, and I know
you do nothing useful. But you do seem to claim a lot!

sure you change some numbers

You. That's what templates are for. Perform the same calculations on
different sets of numbers. You may finally be beginning to figure out
what spreadsheets can do. Progress, unless of course you fail to grasp
that this is what spreadsheets do well.

it just would be much mroe efficient to automate it with a real program

So entering a few hundred strings and numbers in a database form rather
than into a worksheet form? Hard to see much of a difference there. You
probably need to be reminded that most of the data I work with comes
from customers, so I get it from e-mail. It's not in any of my
company's databases, so no matter how powerful their query facilities,
if the data ain't there, they ain't gonna fetch it.

And *AS* I'm entering the data in a worksheet, some of the calculations
update in real time. And I don't have to run anything when I've
completed data entry. the calculations are already done. I'd have to
click a button or run a menu command in a database form to indicate
that I'd completed data entry, and then it'd make me wait as it churned
through the calculations.

fine.. screw microsoft; use crystal reports and mysql for all i care.

Still inappropriate tools for what I do. Again, I DON'T PRODUCE
REPORTS. It must be sad having to work with reports all the time, but
maybe you don't mind drudgery.

all i know is that you sit there and make the same-- or similiar
spreadsheets all the time.

I perform similar calculations most days. That's the nature of most
jobs: doing the same thing repeatedly. In my case, the bulk of my job
could be described as forecasting. Given the nature of my company's
products (financial services), there's a fairly narrow set of
calculations that are pertinent (though there's ongoing research into
refinement and alternative approaches). So, yes, highly repetitive. So
is econometric forecasting. Or tax accounting.

As for making spreadsheets, I use canned templates. Yes, that means I
save many different workbooks that share the same formulas. I also
share many of those workbooks with different coworkers in my own and
other field offices, and none of those coworkers has Access (or an
account on any of the company's RDBMSs). Mind telling me how they'd get
anything useful if they have no database front-end to use?

and it would be in YOUR best interests to start doing things with VALUE
instead of throwing time and money at copy and pastedom
....

An opinion founded on militant ignorance. If copy & paste is the most
efficient form of data entry, it's be foolish not to use it. If you
mean creating new workbook models, then it's still a rather efficient
way to deal with propagating similar calculations. As I've mentioned
before, formula copy & paste is the spreadsheet form of iteration.

Millions of databases are created in Excel spreadsheets each year, but
only a tiny percentage "graduate" to the next level: Access.
....

So you believe all spreadsheet models are just database applications?
More evidence that your perspective is extremely limited, and that
you're so ignorant you can't realize how little you know.

I don't dispute that Excel is often misused as a database. I do dispute
that I misuse it so, and you can keep on claiming I do, but that won't
make it so.

And until you *PROVE* otherwise, there's considerable and mounting
evidence that you can't figure out how to program elementary matrix
operations, thus disproving your claim that *YOU* can do anything in
Access that I can do in Excel. [Yes, I do, implicitly, invert matrices
several times a day as part of LINEST and LOGEST function calls.]

You might want to reconsider your relative priorities between
frequently responding with your normal vacuous rants and spending some
time figuring out my challenge. Throw a challenge at me if you want.
Even something involving more than 65536 rows. I may need to use
multiple worksheets, but I'll come up with an answer for you a lot
quicker than you've managed to answer me - still waiting after 5 days.

hey im not claiming anything

you're a frigging idiot and you make the exact same spreadsheet 3 times
a week.

eat **** and get a real job and go and play with your python and perl
you wimp

i mean-- there are better ways to do your job that with excel..
i would reccommend using paper and pencil instead of excel

wrote...
i can write matrices and read matrices all the time

So it'd be easy for you to show a stub VBA function, taking a query and
a destination table reference as arguments, running the query,
converting the resulting table to a VBA array, passing that array as an
argument to a placeholder matrix inversion function, then writing the
resulting array to the destination table. You can provide the matrix
inversion code later. Just wow us with your interface function as soon
as practicable. Surely you must have one lying around some place. Just
copy & paste it into a response. That can't be too difficult for you?

im the king of multidimensional asshole

I can't dispute this. Far be it from me to suggest you have no grasp of
grammar, so it must be the case that you reign supreme over your
multidimensional backside. That's where your brain (such as it is) is
located too, no?

wrote...
hey im not claiming anything

you're a frigging idiot and you make the exact same spreadsheet 3 times
a week.
....

I see. Since you know exactly what I do, you should be able to provide
the filenames of the most recent 3 identical spreadsheets I've saved.
Go ahead and let whe world know their filenames.

i mean-- there are better ways to do your job that with excel..
i would reccommend using paper and pencil instead of excel

So that's how you'd try to do my job? That you can't even figure out
the benefits of Excel as a n ad hoc calculator seems to sum up the full
extent of your practicality and the depth of your, er, wisdom.

oh harlan

you're sooooo cute

i mean.. just because we're both slinging mud at each other; it doesn't
mean you shoudl try to attack me.

you see; im a programmer; i make six figures and i have work coming out
of my ears.

you're a worthless spreadsheet dork that should be living on the
streets.

do you honestly think that learning perl and python are in the best
interests of excel dorks?
i mean seriously.. what's the point-- the most practical language to
learn is VB6 / VBA.

im sorry that MS is too drunk to support their existing, current
programming languages.

but you guys are sitting around; without a hope in the world. and you
really think that your math is too hard for my FREE databases?

grow up harlan; lose the training wheels.
im sorry that you work for a company that is too cheap to give you MS
access. i mean-- it IS the most popular database in the world; and you
kids sit around and make the same damn xls week in and week out

it's like.. grow the hell up; either use crystal or Access or stop
crunching numbers.

wrote...
....
do you honestly think that learning perl and python are in the best
interests of excel dorks?
i mean seriously.. what's the point-- the most practical language to
learn is VB6 / VBA.

VB6, no. VBA yes.

For cleansing data in text files, nothing is better than Perl. A Perl
command line script under 80 characters can do more than Excel or most
databases to fix most common data formatting headaches. So, yes, I'd
recommend learning simple Perl scripts for anyone who wants to avoid
having to use VBA to run procedures that'd have to process text files
via Open/Line Input, Print #/Close.

Python isn't as compact, and its regular expression syntax isn't as
comprehensive as that of Perl, but may people find it easier to use.
For those who just can't stand Perl (and there are plenty of them),
Python is the most reasonable alternative.

BTW, check this out.

http://www.tiobe.com/tpci.htm

Leads me to wonder who in their right mind wastes time with VBScript.

im sorry that MS is too drunk to support their existing, current
programming languages.

You don't get it. VB6, like Office 97, was good enough that too many
people haven't upgraded. Solution? Spread FUD among corporate IT buyers
that managed code and .Net are necessities. Get 'em to upgrade. In 4 or
5 years Microsoft will unveil another Great New Paradigm when too many
programmers start to believe Visual Studio 2005 is good enough not to
need to upgrade.

Microsoft isn't irrational (but an irrational fool such as you can't be
expected to understand this). They just place a much higher priority on
revenues and profits than on the quality of their software. If quality
is required to sell product, they'll improve the quality of their code,
but that's necessarily a last resort compared to adding new features
that introduce new bugs but give their marketing department much more
to work with.

but you guys are sitting around; without a hope in the world. and you
really think that your math is too hard for my FREE databases?
....

Seems to be. Where's your code, genius? Unclear on the concept PUT UP
OR SHUT UP?

it's like.. grow the hell up; either use crystal or Access or stop
crunching numbers.

If only it were possible to do anything slightly complicated using
either. Until you show us the way, I'll be stuck believing Access is
capable of little more than counting and summing. I do believe
databases make good storage subsystems, but they're just not up to
serious analysis. And you're not up to figuring out the difference
between analysis and report generation.

WHAT HAPPENS WHEN YOU HIT 64K ROWS, KID?
little script kiddie

Aaron, The fact that you would even ask the question regarding 65,536
rows, just goes to show your ignorance. Like, I said the right tool for
the right job... By the way when I hit 64K rows I would still have
1,536 to go!
I will use access, mysql, sas, oracle, notepad, or even crayons and
construction paper! Whatever serves my needs the best, but when you
make statements like "[I would never use] Excel in the real world"
-- it kind of makes you out to be the "One Trick Pony". You
obviously are not a professional, because you clearly do not understand
the needs of the "Real World", or customers in the "Real
World". You can pretend that "Real World" only needs what you are
endorsing, but you are wrong. Chances are you are the "Kiddie"
here, judging by the immaturity of your responses. And the verdict is
still out on whether or not you are the "Script Kiddie".

Go get them tiger, the whole world is against you, but you know that
you are right! So, keep striking out in angst -- you really are a sad,
sad boy.

for cleansing things in text files, nothign works better than PERL?

HOW ABOUT ACCESS ASS-MUNCH?

how about VISUAL BASIC?

wouldn't you rather know ONE language than a dozen?
I mean-- you can use ONE language for excel macros, outlook macros,
access macros, etl in DTS.. you can use ONE language for everything you
need to do from server-side scripting to clientside scripting..

I mean-- gag me with a spoon, harlan.

so sorry that I got the 65536 limit confused with the 64k limit of
childen in a level (for real pivotTables).. sue me

Access doesn't have those types of limits; so i just consider anything
to do with Excel as being inferior for my needs.

and again, Harlan. you can sit there with your cush job and drive your
BMW.. and you can pretend that you're VALUABLE because you are an
'analyst'.

I call hogwash on your ass; all you do is crunch numbers; and you do it
poorly. I mean.. you sit there and copy and paste the same formulas
1000 times. what happens when you need to change a calculation?

you open up a dozen spreadsheets and change the formula in about 10,000
different places

it's just a waste of time

i eat excel dorks like you for breakfast

wrote...
for cleansing things in text files, nothign works better than PERL?

HOW ABOUT ACCESS ASS-MUNCH?

How? By importing into multiple fields and praying that the result
isn't too badly screwed up? Or importing into a single-field table,
cleansing using SELECT queries, then splitting into fields using
another SELECT query? That's easy?!

how about VISUAL BASIC?

BASIC, including Visual Basic, sucks for text processing. And you mean
using Open, Line Input, Print # and Close statements? Again, that's
easy?!!

Try these 2 CSV-like records where comma is usually the field
separator, but currency values include comma formatting, in which case
the leading and trailing field separators are comma-space sequences.

123,456, 789,012.00, xyz
abc,999, 5.37, 23

I have to deal with text files like this on a regular basis. All it
takes using Perl is the command line script

perl -pe "while (s/( [-+\d]+),(\d)/$1$2/) { ; }" raw.csv cleansed.csv

and if I want to remove the spaces after the commas,

perl -pe "while (s/( [-+\d]+),(\d)/$1$2/) { ; }; s/, /,/g;" raw.csv
cleansed.csv

Yes, you do have to learn regular expressions to get the most out of
Perl, but the investment pays off quickly.

wouldn't you rather know ONE language than a dozen?

Not if that one language were any form of BASIC.

I mean-- you can use ONE language for excel macros, outlook macros,
access macros, etl in DTS.. you can use ONE language for everything you
need to do from server-side scripting to clientside scripting..
....

More add-on softwa DTS.

Maybe if all users had a terabyte of additional software at an
incremental cost of $10,000 or more per seat, then maybe there'd be
less reason to use Excel.

Then again, maybe you should try to view things from a REAL WORLD
perspective. (Yeah, that'll happen!)

And it seems VBS is widely deployed only in your dreams, so client or
server side there'd need to be at least one more language.

Access doesn't have those types of limits; so i just consider anything
to do with Excel as being inferior for my needs.
....

Note: **YOUR** *NEEDS*. Not **MY** *NEEDS*, nor in all likelihood
anyone else's needs. **YOU** don't like using Excel. Fine. Don't use
it. No one is forcing you to use it. If you want to use Access (and a
few dozen additional software packages you seem the have difficulty
distinguishing from Access), go ahead. But most other users, at least
not the ones in the newsgroups you pollute with your presence, won't
find it useful.

I call hogwash on your ass; all you do is crunch numbers; and you do it
poorly. I mean.. you sit there and copy and paste the same formulas
1000 times. what happens when you need to change a calculation?

you open up a dozen spreadsheets and change the formula in about 10,000
different places

Macros. Every workbook template (loosely defined) I write has a
distinct hidden defined name. I have a batch update workbook that uses
a common set of macros and a description of fixes entered in its sole
worksheet. The worksheet starts off with the distinct name of the
template it's intended to fix and the drive/directory path in which to
search for files (recursing through subdirectories) followed by a blank
row followed by a table (yes, a table) with fields for worksheet name
(or blank for workbook-level defined names), range address or defined
name, and replacement formula as literal text (R1C1 for cell formulas,
A1 for defined names). The macros search through the specified
directory, using ExecuteExcel4Macro to fetch the distinct name. If it
matches the distinct name sought, it opens the workbook and iterates
through the table replacing the formulas for the specified defined
names or ranges with the replacement formulas.

I know batch processing is old-fashioned, but it works for me.

it's just a waste of time
....

Well, if you don't understand batch processing, I suppose it might seem
so. But I don't see why anyone else should limit themselves to the few
(the very few) tools you seem to know.

wow harlan

you really are on crack

i can parse whatever you can; only easier.

and i DONT know any products that cost $10,000 per seat. Analysis
Services ships free with SQL Server standard edition which is like $6k
per processor.

one olap servers to go for 100 excel dorks like yourself so you can
have 'real pivot tables'.

Excel isn't free.

Excel is a disease; and im sorry that management at your company are
lepers. they are infected.

there is only one place for you harlan; and all spreadsheet dorks like
you.. living on a street; drinking wine out of a brown paper bag.

that is where your career is heading.. you guys 'choose not to
participate' in some of the most exciting things to ever happen in the
computer industry.

spreadsheets didn't allow amazon.com

spreadsheets dont run gmail

spreadsheets dont run ebay

i just think that you're whacked in the head, harlan

spreadsheets are for girlie men like yourself
i mean.. what are you going to do when you hit the 65536 limit? lol

gag me with a spoon; i mean-- this isn't 1994 anymore

wrote...
....
i can parse whatever you can; only easier.
....

OK, given my 2 record example, show how you'd eliminate the commas in the
currency fields or import 4 fields in each record into a database table.
Maybe you're up to that challenge since matrix inversion is beyond your
capabilities (minimal as they are).

ok i will. just a coupel of dlookups (or subqueries) and a couple of
cartesians there's not a damn thing i cant do

wrote...
ok i will. just a coupel of dlookups (or subqueries) and a couple of
cartesians there's not a damn thing i cant do

Just a few acronyms and some jargon, and there's nothing you can't
**CLAIM** to do.

Still no evidence that you actually know how to do anything other than
rant.

If 2 records isn't enough for you, randomly generate some. In Excel,
I'd do this using formulas like so: press [F5], enter A1:A10000, press
[Enter], then type the formula

=IF(RAND()<0.7,CHAR(65+26*RAND()),CHAR(48+10*RAND( )))
&IF(RAND()<0.7,CHAR(65+26*RAND()),CHAR(48+10*RAND( )))
&IF(RAND()<0.7,CHAR(65+26*RAND()),CHAR(48+10*RAND( )))&","
&TEXT(1000*RAND(),"000")&", "&TEXT(10000000*RAND(),"#,##0.00")&", "
&LEFT(IF(RAND()<0.7,CHAR(65+26*RAND()),CHAR(48+10* RAND()))
&IF(RAND()<0.7,CHAR(65+26*RAND()),CHAR(48+10*RAND( )))
&IF(RAND()<0.7,CHAR(65+26*RAND()),CHAR(48+10*RAND( ))),INT(1+3*RAND()))

and press [Ctrl]+[Enter]. There'd be 10,000 records in the perverse
format I mentioned. Note: no copy & paste.

Show *IN* *DETAIL* the steps you'd take to create a DBMS table from
this data with 4 fields, the first and last text, the second integer
and the third floating point or fixed point with 2 decimal places.

This doesn't involve dlookups or cartesians, genius. Just removing
unwanted commas embedded in the third field of some but not all
records. This is a simple text transformation exercise (a Perl
one-liner as I've already shown). Show us, *WITH* *DETAILS*, how easy
it is using either plain Access, VBA or, if you're at a loss using
those, SQL Server DTS.

wrote:
ok i will. just a coupel of dlookups (or subqueries) and a couple of
cartesians there's not a damn thing i cant do

You've got to be kidding. All these posts with specific questions for
you, plus explicit requests to provide details and this is what you
reply? Wow!

This should be quite embarrassing, yet you keep going. You are in a
deep hole and still you keep digging.

haha im not in a hole

im not the one that spends week in and week out building the same damn
spreadsheet

you guys are in a hole

i can do this; i just have a fulltime job and a bunch of contracting
thigns on the side; and 3 kids to deal with at home.. i dont have a ton
of time to look into this; but i will try this weekend (in between
clients)

i haven't ever had any math in the db world that is even challenging; i
mean.. sum this sum that

none of it is slightly challenging

i took 3 semesters of college calc 15 years ago; i know i can do this

all i know is that subqueries and views and user-defined functions are
more powerful than copying and pasting formulas around.

i know it and you guys are crazy for not seeing the light

wrote...
haha im not in a hole

Quite so. You *are* a hole, an metaphysical absence of anything
remotely associated with intellect. Then there are the holes in your
head, more and larger than those normal humans possess.

i can do this; i just have a fulltime job and a bunch of contracting
thigns on the side; and 3 kids to deal with at home.. i dont have a ton
of time to look into this; but i will try this weekend (in between
clients)

Excuses. I already gave you an out: start off by showing code which you
must *already* have to convert a table of, say, 4 records and 4 fields
of double precision floating point numbers into a VBA array perhaps
named SourceArray, then a stub function call like

ResultArray = MatrixInverseStillToBeWritten(SourceArray)

then create a new DBMS table from ResultArray. Someone who claims to be
such an expert at Access, DBMSs in general and VBA must have written
such procedures before. Do you lack access to the code?

You've claimed that you can do any calculations in Access or VBA that I
can do in Excel, and you've claimed you can do them faster. It seems
the only small chink in this edifice of calculation proficiency you've
proclaimed for yourself is writing the code needed to reinvent Excel's
existing functionality. So if you're as good at VBA as you've claimed
you are, but you can't provide a working solution after a whole week,
what chance would anyone who's never programmed in BASIC or any other
procedural or OO language have writing code to get Access to do what
Excel can do out of the box?

You don't see any defects in your arguments to date?!

i haven't ever had any math in the db world that is even challenging; i
mean.. sum this sum that

Yup. That's what databases are good at: summing, counting,
categorizing. They're not really meant for linear algegraic operations.
Neither is VBA. [If only Microsoft would add the MAT statements from
PowerBASIC or TrueBASIC.]

none of it is slightly challenging

i took 3 semesters of college calc 15 years ago; i know i can do this

Oh, certainly. Why anyone can write robust numerical code. They just
won't take as long as you seem to need.

All you need to do is translate existing code into VBA. However, since
you apparently don't know any other languages, and there's a dearth of
VB[A] matrix inversion routines on the web, you're screwed. So much for
VB[A] being the only language you need to know.

Wassa madda, you couldn't even figure out the BASICA code to which I
posted a link in

http://groups.google.com/group/micro...1e6c06cb50add0

?!

all i know is that subqueries and views and user-defined functions are
more powerful than copying and pasting formulas around.

For counting, summing and categorizing, sure. For inverting matrices or
generating all permutations of sets of distinct tokens, no way. No
single software tool or language is any more universally appropriate
for any & all applications than any single hand-held tool is
universally appropriate for any & all woodworking tasks. You know how
to use a hammer, and you think your hammer is a better saw,
screwdriver, drill, . . .

i know it and you guys are crazy for not seeing the light

The light you see is the train coming at you in the dark tunnel into
which you've blundered.

ALL MATH IS SUMMING, CATEGORIZING DIP****

i mean seriously here.. is your math 'magic math'

are you harry potter?

friggin idiot

I can generate permutations easier than you. it's called a CARTESIAN.

I'm not the one that sees that one tool fits the need for everything.

you're the spreadsheet dork that uses spreadsheets for EVERYTHING
you sit around and build the same damn XLS week in and week out.

I'm not the one that uses a one-size fits all mentality.

YOU ARE HARLAN.

and when stuff doesn't fit your peachy little app you run out and use
PERL and PYTHON?

****ing idiot use VBA / VBS / VB6

and you REALLY think that arrays are that complex?

i mean-- seriously harlan.. what are you looking for 'yes, you are
right.. databases can't handle arrays'

****ing idiot.

spreadsheets can't handle arrays buddy

databases handle 2-dimensional stuff just fine.. and everything you've
ever touched is 2-dimensional by definition..

if you really want the heavy math; you do OLAP
i mean. .it's got a lot more functions that you spreadsheet dorks are
familiar with