SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by shashank bhide / Let the Excel Play / Latest Posts

RE: Let the Excel Play

Manie Verster — Thu, 09 Oct 2008 01:06:17 GMT

shashank, thanks for the reply and sorry for only comming back to you now. MDAC is installed on my computer and I did come right thank you. I had the provider wrong in my connection string and when I fixed it, it worked thanks to Adrian Jardin (a fellow South African) that helped me. Thanks Adrian!

RE: Let the Excel Play

shashank-666535 — Tue, 07 Oct 2008 03:20:13 GMT

Also make sure that MDAC components are installed on the machine

RE: Let the Excel Play

shashank-666535 — Tue, 07 Oct 2008 02:46:55 GMT

Plz send me the code to analyze

RE: Let the Excel Play

Manie Verster — Tue, 07 Oct 2008 02:20:28 GMT

sashank, thanks for this article. For me it came at just the right time. I have one problem though. When I try to open the connection string "con.Open connstr" I get the following error. Run-time error: '3001' Application-defined or object-defined error. Any ideas? I checked the references in vba but all looks fine. Please help!!Further to the comments in this post. Get external data is ok but I want to with VB place my data and make it look like a real report and the client wants it in Excel. So, I think this is a cool way to do it.

RE: Let the Excel Play

DPhillips-731960 — Mon, 06 Oct 2008 10:23:15 GMT

[quote][b]Jeff Moden (10/5/2008)[/b][hr]"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."- and - "You can either spend time writing custom automations for all 50 users, or you can teach them to become self sufficient.[/quote]"I fully and whole-heartedly believe in the principle, "Teach them correct principles and they govern themselves." Seeing your point to a point, but then it has not stopped the problems of needing to dubunk what they do with the data.In so many words or less you are actually taking TWO steps: 1. Teaching 2. Creating controlled sources of output they can use to effectively limit what they may want to do to munge the data.Herein lies the the delta: Judging how much time to spend creating the various views and rights to view, and dealing with how they rollup, pivot, and analyze the data.When the numbers do not match, it is back in your lap anyway. Notice I did not use "If". And remember, I noted that I have these issues more with those who feel they know Excel... not the new learners... the new ones do not usually know enough to even get themselves into trouble. The problem lies in the fact that both the Views they need, and the report crunching layout needs to be handled.So when a report becomes adopted between more than a couple people, I mold it on report server, so that all they have to do is copy it down (if they must) with little or no munging. If the analytical mistakes of others did not come to haunt me so, then I would be more apt to just teach... heh... would even prefer to just teach.

RE: Let the Excel Play

shashank-666535 — Mon, 06 Oct 2008 02:04:29 GMT

Hey don't get me wrong, I respect you and it was out of that respect only :)))))))))))))))))))))

RE: Let the Excel Play

Jeff Moden — Sun, 05 Oct 2008 22:05:55 GMT

[quote][b]shashank (10/5/2008)[/b][hr]I know that, an SSChampion won't :D[/quote]Heh... OK. If you're going to throw a conversation on the floor with stuff like that, I guess this conversation is over.

RE: Let the Excel Play

shashank-666535 — Sun, 05 Oct 2008 21:19:47 GMT

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"

RE: Let the Excel Play

shashank-666535 — Sun, 05 Oct 2008 21:07:58 GMT

I know that, an SSChampion won't :D

RE: Let the Excel Play

Jeff Moden — Sun, 05 Oct 2008 20:42:03 GMT

[quote][b]David.Poole (10/5/2008)[/b][hr]Does it work? YesShould 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.[/quote]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.

RE: Let the Excel Play

Jeff Moden — Sun, 05 Oct 2008 20:37:04 GMT

[quote][b]shashank (10/5/2008)[/b][hr]...why would you write plain text queries for what can be achieved through a stored procedure.[/quote]I never said I would. ;)

RE: Let the Excel Play

shashank-666535 — Sun, 05 Oct 2008 11:20:33 GMT

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.

RE: Let the Excel Play

David.Poole — Sun, 05 Oct 2008 09:15:14 GMT

Does it work? YesShould 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.

RE: Let the Excel Play

Jeff Moden — Sun, 05 Oct 2008 07:35:02 GMT

Don't get me wrong... I'm not bad mouthing your code or your article... I actually gave it a good mark. :DThere'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:

RE: Let the Excel Play

shashank-666535 — Sun, 05 Oct 2008 07:14:28 GMT

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

RE: Let the Excel Play

shashank-666535 — Sun, 05 Oct 2008 06:59:25 GMT

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 [i]taught[/i] 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..

RE: Let the Excel Play

Jeff Moden — Sun, 05 Oct 2008 06:39:23 GMT

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. [i]Teach [/i]the users how to support themselves using "Get External Data". It saves a huge amount of time in the long run.

RE: Let the Excel Play

shashank-666535 — Sun, 05 Oct 2008 04:37:37 GMT

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..

RE: Let the Excel Play

shashank-666535 — Sun, 05 Oct 2008 04:33:11 GMT

Jeez... doesn't anyone know how to use "Get External Data" on a simple view anymoreIN MSOFFICE-2000 BELOW???

RE: Let the Excel Play

Gosta Munktell — Sun, 05 Oct 2008 03:07:04 GMT

"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 Excelbut 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 promotedby 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 usingsomething 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 inthe 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

RE: Let the Excel Play

Jeff Moden — Sat, 04 Oct 2008 13:12:43 GMT

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

RE: Let the Excel Play

DPhillips-731960 — Fri, 03 Oct 2008 12:15:43 GMT

[quote][b]Yelena Varshal (10/3/2008)[/b][hr]I prefer SSRS (Reporting Services) with its Export button where you can export to Excel, PDF and other formats.[/quote]Agreed. As long as the end user sticks to the source, the export for walking is fine in Excel (with minimal caveats).

RE: Let the Excel Play

Yelena Varshal — Fri, 03 Oct 2008 11:50:31 GMT

I prefer SSRS (Reporting Services) with its Export button where you can export to Excel, PDF and other formats.

RE: Let the Excel Play

DPhillips-731960 — Fri, 03 Oct 2008 10:04:05 GMT

[quote][b]hug.newsletter (10/3/2008)[/b][hr]This is exactly the type of solution I come across every day in our organization and I absolutely hate it![/quote]Oiye... Wow. I whole-heartedly agree! I am sitting here reading this post with some small amount of terror... of the pain of experience in dealing with such issues. It seems that every organization has some self appointed Excel evangelist that I spend half my time having to DEBUNK the values s/he contorted into their PIVOT and postulated to management as a whole. I despise having to go and fix their range calls, rounding errors, truncated zeros, and etcetera, merely because "our numbers do not match".As a standard of practice, anytime someone comes to me with an Excel reporting issue, I lay it aside and go DIRECTLY to the source of the data. They are usually saying, "But that is not what my Excel sheet says...". It takes far less time to get the facts straight than trying to absorb their particular self-inflicted Excel predicament.And the awful plentitude of those that think Excel is a good format to ship data in... Lesson #1: Zip Codes do NOT play well in Excel without some careful hand-holding, which never seems to happen at the sender end.--- soap box mode = off ---Please pardon my rant...

RE: Let the Excel Play

jin lin — Fri, 03 Oct 2008 09:02:01 GMT

I use ExcelWriter and combine with VBA through application to display SQL data to the Excel. This is more secure to data server I think.

RE: Let the Excel Play

ppeterson — Fri, 03 Oct 2008 08:47:53 GMT

This type of a solution is extraordinarily powerful. Being able to retrieve data directly from the database for use in Excel has been a huge help in trying to get information out to the users. Once the data is in VBA, there are lots of other nice things that can be done with the data via VBA that doesn't work quite as well with SQL (my opinion). In addition, formatting of the spreadsheet is endless with VBA. With that said, i would like to add some caveats...PROTECT YOUR CODE. In your example, the connection string had the userid and password in the clear. One person already mentioned this. This is really, really bad. If you don't have very tight controls over who can access data in your database, you have opened yourself up to data corruption from nefarious users that have free reign in your SQL Server once they find the userid and password. Several ways to work with this - PASSWORD PROTECT your VBA code - don't let your users see this. It is more work for the developer - you need to write the code to retrieve information, but it keeps people from doing bad things. Your reports become in effect, read only. Use an ODBC connection. It would need to be set up on the users machine, and depending on how many you have, this could be problematic (or not). Create a generic userid / password that gives access to selected files as READ ONLY. You do not have to put userid / password into the connection string.

RE: Let the Excel Play

daft — Fri, 03 Oct 2008 06:54:03 GMT

[quote][b]hug.newsletter (10/3/2008)[/b][hr]This is exactly the type of solution I come across every day in our organization and I absolutely hate it! I'm not criticize it from a technical point of view but it is very dangerous when such applications spread in an organization. Imagine to work in such an organization with hundreds of such tools and every user asks for support and new features. It can be helpful for a very specific and one off solution but never ever let it become something where you value chain is depending on without taking care of who will support it, where does it fit in your application landscape and others questions you have to ask you.Sorry to spoil your day with this.[/quote]Some valid points there, have (in fact still do) encountered this type of "solution" loads in the past. If managed and supported correctly then it might not to be a problem, but the danger is when users are let loose on it themselves and you end up doing a cleanup job a year or two down the line.Still, good article.Agreed on moving the SQL into a Stored Proc. Also it's good practice to avoid hard-coding your connection string.

RE: Let the Excel Play

scott mcnitt — Fri, 03 Oct 2008 06:52:55 GMT

VBA is a good item to have in your tool kit. A couple of warnings:-I have worked in environments where everyone was able to connect to a live instance of production and write whatever query they want. It can be difficult to monitor, track down, and generally to police performance problems that *will* happen (unless you have a server with unlimited resources).-Be careful what permissions you give your users. In your example they must have SELECT permissons granted only. You may find one day that *someone* deleted all the rows in your bank_fraud_case table even if they do not use your Excel reporting solution. You can google anything these days.;)

RE: Let the Excel Play

chris.ingram — Fri, 03 Oct 2008 05:40:24 GMT

Consider Sharepoint Excel Services instead - it's the way to go!

RE: Let the Excel Play

liebesiech — Fri, 03 Oct 2008 03:56:42 GMT

This is exactly the type of solution I come across every day in our organization and I absolutely hate it! I'm not criticize it from a technical point of view but it is very dangerous when such applications spread in an organization. Imagine to work in such an organization with hundreds of such tools and every user asks for support and new features. It can be helpful for a very specific and one off solution but never ever let it become something where you value chain is depending on without taking care of who will support it, where does it fit in your application landscape and others questions you have to ask you.Sorry to spoil your day with this.

RE: Let the Excel Play

Madhivanan-208264 — Fri, 03 Oct 2008 03:45:19 GMT

[quote][b]r.hensbergen (10/3/2008)[/b][hr]I'm also a great fan of SQL data displayed in Excel, and I use it daily. However, I prefer to have the SQL in a stored procedure in the database, so you need only one line in VBA.For all the rest, just how I like it!Ronald[/quote]Here it is [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926[/url]

RE: Let the Excel Play

Manie Verster — Fri, 03 Oct 2008 02:42:22 GMT

In MSAccess there is a tool called Microsoft Office Spreadsheet that you can insert in a form and write pretty much similar code to populate it with wanted data. Now, I am currently working on billings reporting spreadsheets where the parameters for the queries is captured in textboxes on the form and then a button that you click to populate the spreadsheet. I have about 6 different spreadsheets to create and the way I currently do it is a bit heavy and must eventually be exported to Excel. I will try your way and hope it will work well for me.Thanks for a great article!:D:D:D:D:D:D:D:D

RE: Let the Excel Play

Ronald H — Fri, 03 Oct 2008 02:18:58 GMT

I'm also a great fan of SQL data displayed in Excel, and I use it daily. However, I prefer to have the SQL in a stored procedure in the database, so you need only one line in VBA.For all the rest, just how I like it!Ronald

RE: Let the Excel Play

Anipaul — Fri, 03 Oct 2008 02:09:39 GMT

Nice article.

RE: Let the Excel Play

AdrianJ — Fri, 03 Oct 2008 00:41:32 GMT

Hi,I am a great fan of using Excel for reporting.I have made many similar reporting tools. With a little bit of extra VBA, you can easily format the returned data and make it look like a "formal" report.One command I have found useful is :[code]rng.CopyFromRecordset rst[/code]where you can dump the entire contents of the recordset at a specified range. This is useful if you don't want to take action on returned data such as substituting "--" for returned nulls.Another variation of this sort of reporting tool I use a lot when checking data is a tool that allows me to write a SQL query as text in a few cells at the top of a particular worksheet. I then need to click a "Get Data" button which rolls the text in the adjacent cells together into a single string which I then pass on to the server. The resulting recordset is returned and I apply simple formatting to it. Not for your average end-users. As it stands, it handles all valid SQL queries, but I never use it to run DELETE, INSERT, UPDATE or DROP queries.Adrian

Let the Excel Play

shashank-666535 — Thu, 02 Oct 2008 23:17:29 GMT

Comments posted to this topic are about the item [B]Let the Excel Play[/B]