Let the Excel Play

Question

Let the Excel Play

Viewing 15 posts - 16 through 30 (of 35 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003893 More actions · Answer 1

Jeez... doesn't anyone know how to use "Get External Data" on a simple view anymore? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 2

"Jeez... doesn't anyone know how to use "Get External Data" on a simple view anymore? "

I like that.

The problem is however bigger. In my part of the world (Sweden) I know many people using Excel

but very few (not IT-professional) how can get data from anywhere. Even if it is simple for use

"Get External data etc" it is not for a person like a controller and this method may also not be promoted

by the IT-department. The worst that can happen is if the "controller" starts to develope an "application"

in VBA with macros etc. He she is desparately in need for something which more then one question using

something like "Get extenal data etc"

There are a bunch of reporting/analysing tool on the market so the question why use Excel?

Excel is an excellent reporting tool. But may be Excel is considered as a toy tool and nobody in

the IT-department will promote professional use of it.

Microsoft is also unclear about VBA in future releasis of Excel. I also use Excel and VB.net but that makes it more difficult !?

//Gosta

shashank-666535 Old Hand Points: 348 More actions · Answer 3

Jeez... doesn't anyone know how to use "Get External Data" on a simple view anymore

IN MSOFFICE-2000 BELOW???

shashank-666535 Old Hand Points: 348 More actions · Answer 4

FOR ALL THOSE WHO GREAT FANS OF "Get External Data" my suggestion is to read the article again as the installation dependency of "microsoft query" made me write this article as an alternativ..

Jeff Moden SSC Guru Points: 1003893 More actions · Answer 5

So? It's a great tool and it's a hell of a lot better than redistributing code everytime you need to make a change. Teach the users how to support themselves using "Get External Data". It saves a huge amount of time in the long run.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

shashank-666535 Old Hand Points: 348 More actions · Answer 6

And i guess the full support of VBA (data formatting API, [UN]limited UI) would also b there with "Get External Data", And i don't think i should've taught my users to use "Get External Data" using the sqlserver(server=srvrname,pswd=gift4u) , this i had to use with 50 users only.

i'd appreciate a list of -ve impacts a few lines of VBA code lines would produce (don't include "connection string is in plain text" every responsible developer will protect the macro with a password)

i'd also appreciate the list of benefits of "Teach Get External Data" approach for non-technical MS-OFFICE only kind of users who are dying for the word "Automation" & Data on single click feature..

shashank-666535 Old Hand Points: 348 More actions · Answer 7

Didn't get your point my friend, are u in favour of the VBA approach coz.. it is difficult to individually teach n number of user how to use Get External Data or ...........

Jeff Moden SSC Guru Points: 1003893 More actions · Answer 8

Don't get me wrong... I'm not bad mouthing your code or your article... I actually gave it a good mark. 😀

There's only one item on a list of benefits that you asked for that I can think of...

1. Users become self supporting

Most users can "survive" in MS Excel. Most of them can write simple formulas to accomplish what they need to accomplish. "Single button automation" with VBA is a really cool thing, but most users can't even spell VBA never mind do something with it other than recording simple macros. The end result is that whenever the users want a change in the automation or want something new to be automated, they have to come to you. You become the very busy single point of failure because, as you already know, everybody likes to make it easy on themselves and your fine automation definitely makes it easy.

If you get those folks together for a "Lunch-n-learn" and show them how to both setup MS Query and use "Get External Data" through a View to do their jobs, your users will become self supporting (their pride in doing something really cool will see to that) and you can concentrate on your primary job as well as thinking of what to teach them next. If you limit what they can read to simple views, you also keep control of the queries so they don't crush the server performance wise.

About the "Lunch'n'Learns"... not everyone actually needs to show up for those, although they should all be invited. We had a huge problem with users writing some really ignorant queries where dates were concerned and they were not only taking a million years to run, the results were often incorrect because the didn't actually know the right way to use dates (and all it's abbreviated forms) as criteria. I created a nice little "How to" booklet and not only gave it to them as handouts, but published it on the company WIKI. Only about 30% of the people actually attended the course but 100% of the people learned how to do it correctly because the people who attended the course actually taught it to their peers.

You can either spend time writing custom automations for all 50 users, or you can teach them to become self sufficient. And, I've found, that if you teach them, you also and very suddenly become very well known and indispensible as a source of knowledge. That helps a lot in today's job market. 😉 Hmmm... so maybe there's other advantages on the list of benefits you wanted, eh? :w00t:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David.Poole SSC Guru Points: 75905 More actions · Answer 9

Does it work? Yes

Should it be used? Not if you want a robust scaleable solution.

In the environment where I work we had to buy and install a server solely to let users query data away from the live environment.

We found that our live servers had strange locking and timeout problems which were solved by replicating data to a reporting server.

However, this has simply shifted the problem elsewhere. The other issue is that we now have an unknown number of "mission critical" applications that are uncatalogued and unsupported. I am currently working on a project where we have to put a compromise solution in place to cater for such unknown mission critical apps.

There is nothing wrong with the article. It presents a solution that works.

LinkedIn Profile

shashank-666535 Old Hand Points: 348 More actions · Answer 10

I understand your concern Jeff, infact you made a valid point by saying that

"You can either spend time writing custom automations for all 50 users, or you can teach them to become self sufficient".

Also, i'm not advocating excel+VBA as a new full scale reporting tool, but my case was altogether different, all the reports were already running (partially) fine in our web application where users would click on the "Download to excel" and use the excel sheet thus generated..

BUT!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

After this they spent another 15 min for formatting it the way they wanted,

for example whenever a large number like 7856556645452 gets exported in excel it doesn't appear as it should rather it takes the exponential form 7.85e10 also it get rounded off and bla bla bla.......

I even solved it by modifying the stored proc which fetched this data as ' ' + 7856556645452 but after a couple of days later i found that the stored procedure was doing more on the part of formatting the data than on fetching it, hence finally pestered with the daily arising new issues i took the help of VBA.

Some people have commented that code written this way is not maintainable, i agree coz any change in the data requirement would require the query in the macro to be modified but again why would you write plain text queries for what can be achieved through a stored procedure.

Jeff Moden SSC Guru Points: 1003893 More actions · Answer 11

shashank (10/5/2008)
...why would you write plain text queries for what can be achieved through a stored procedure.

I never said I would. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003893 More actions · Answer 12

David.Poole (10/5/2008)
Does it work? Yes
Should it be used? Not if you want a robust scaleable solution.
In the environment where I work we had to buy and install a server solely to let users query data away from the live environment.
We found that our live servers had strange locking and timeout problems which were solved by replicating data to a reporting server.
However, this has simply shifted the problem elsewhere. The other issue is that we now have an unknown number of "mission critical" applications that are uncatalogued and unsupported. I am currently working on a project where we have to put a compromise solution in place to cater for such unknown mission critical apps.
There is nothing wrong with the article. It presents a solution that works.

I agree... nothing wrong with the article.

Not sure why you think you'd end up with uncatalogued and unsupported apps, though. The method I recommended would be done through supported views (or procs). The Excel spreadsheet is just to make the output "pretty".

I do, very much, agree that this type of reporting should probably be done through a reporting database rather than the production database for the very reasons you mentioned.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

shashank-666535 Old Hand Points: 348 More actions · Answer 13

shashank-666535

Old Hand

Points: 348

October 5, 2008 at 9:07 pm

#880951

I know that, an SSChampion won't 😀

shashank-666535 Old Hand Points: 348 More actions · Answer 14

I agree with everyone i definitely can't run in defiance of the facts by SSChamps and gurus infact this article doesn't need this much attention , but i'd say only 1 thing in the end that if the credit card number 4324244355455656 starts appearing as 4.32e10 or 43242400000000 on your final excel sheet than whatever u do won't be related anywhere to make the excel "pretty"

Jeff Moden SSC Guru Points: 1003893 More actions · Answer 15

shashank (10/5/2008)
I know that, an SSChampion won't 😀

Heh... OK. If you're going to throw a conversation on the floor with stuff like that, I guess this conversation is over.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)