Excel Zone Expert Discussion, Number 38

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
CLEAN-UP VOLUNTEERS:


The site Admins and Moderators are more than happy for this thread to stay open for as long as byundt wishes to continue the discussion. Whilst we do appreciate that this sort of circumvents the "abandoned question" remit we feel that we can make a special dispensation now and again and this is one of those time when we are willing to make that dispensation. We'd kindly ask that if you come across this thread during clean-up, just skip it.

Thank you kindly for your continued assistance with this.

thermoduric
Community Support Moderator
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

This thread is for general discussion about all things related to the Excel TA and its experts. Both regular contributors and newcomers are always welcome. Topics might include:

       Asking for assistance with specific questions.

       Issues, announcements, and discoveries related to this zone.

       Discussing Experts Exchange features and functions that are of specific interest to this TA's experts and do not require moderator participation.

       Recognition of new experts, amazing posts, and accomplishments related to this TA.

        Getting to know your fellow Excel experts.

       Planning meets and greets with other Excel experts when traveling.

       And, occasionally, a little humor and other completely unrelated topics that are of interest to the Excel community.


Drivel posts such as "First Post" or "Subscribing" will be deleted. There is a "Monitor" link (looks like an eye) in the left sidebar near the top of the question. Click it. It does magical things.

This thread must not be used for any of the following:

        Presenting any specific member in a negative light.

       Suggesting that any specific member needs to change their behavior.

       Suggesting that others blacklist any OP for any reason.


Whenever an issue arises with a specific question, avoid engaging with others in that question and use the "Request Attention" function to request moderator assistance. More general issues and concerns should be taken up privately with the site moderators, all of whom are listed here (select "Community Volunteers"). You can contact admins, moderators, topic advisors, and page editors using their member name: membername@experts-exchange.com. This will not work for cleanup volunteers.

If you have a question about how this site functions or a problem using this site, please post a separate question in one of the community support TAs. Doing so helps keep this thread focused on Excel related topics and gives other members with similar problems a better opportunity of finding the solution assuming there is one and it's posted to your question.

Previous discussion thread: http:/Q_28428578.html
Next discussion thread: None

Topic Advisors
When this thread reaches a page size of about 100K (about 300 comments):

1.  Create a new "Expert Discussion" thread using this content as a template, incrementing the number in the title, and setting the "Previous Expert Discussion" link to this thread.
2.  Edit the new thread and set the points to zero.
3.   Edit this question and set the "Next Expert Discussion" to link to the new thread.
4.  Make a final post in this thread with a link to the new thread "Next Expert Discussion: http:Q_xxxxxxxxx.html" and accept that post as the solution.
LVL 81
byundtMechanical EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Why not create a group for this?
zorvek (Kevin Jones)ConsultantCommented:
We tried that and it failed to gain traction. I also proposed that EE create a special thread for each zone or whatever the they call these areas now these days just for the regulars to yammer about this and that. Never happened.
zorvek (Kevin Jones)ConsultantCommented:
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

byundtMechanical EngineerAuthor Commented:
I may be tilting at windmills, but I decry the trend towards Facebook. I don't use it. I get irritated when 90% or more of the posts on it are reposts of material found elsewhere--the social equivalent of chain letters.

Since Yammer seems just like Facebook (except for being more exclusive), I make zero use of that. And when I read the weekly digests of Yammer posts (via email), there never seems to be any content worth clicking on. Google + and Experts Exchange Groups seem to be other variations on the Facebook theme, so I ignore them as well.

Since February, there have been 11 posts in the Excel Topic Area group. None of them are recent. Kevin is 100% correct that it never got traction.

We have had 38 of these threads for the Excel regulars to post in. All of them get 100 to 300 posts before they get so big we open a new one. These threads do their job. Questions that need help receive it. Interesting Excel factoids get discussed. Major life events are celebrated. The wheel aint broke, and I see no need to replace it with a Group that has been proven not to work.
[ fanpages ]IT Services ConsultantCommented:
We could always make more use of "Experts-Exchange Volunteers" [ https://www.linkedin.com/grp/members?gid=1124807 ], &/or create a(nother) dedicated group that mimics the function of this thread... but, as mentioned, if it works well (& has done so for some time), why mess with it?
rspahitzCommented:
Back to Excel...anyone heard anything interesting about Office 2016? Is it just same-old-same-old?
JohnBusiness Consultant (Owner)Commented:
I have Office 2016 running (auto upgrade from Office 2013). Excel and Word are very similar to their counterparts in Office 2013 except that Office 2016 offers collaboration in a way that Office 2013 and prior did not. I have not yet tried this out (I do not have any colleagues yet in Office 2016).

Outlook is quite different and I have to use Single Line view to make any sense of it.
JohnBusiness Consultant (Owner)Commented:
Here is a resource I have been working through

https://blogs.office.com/2015/09/22/thenewoffice/
byundtMechanical EngineerAuthor Commented:
Excel was first released for commercial sale on September 30, 1985. So today is its 30th birthday. Woohoo!

Microsoft Excel MVP Boriana Petrova wrote an interesting history of Excel, with emphasis on its predecessors and early years. The first release (version 1.0) was for Macintosh only. Excel took advantage of the Mac's graphical user interface, and had an edge in speed over competing spreadsheets due to a recalc engine that only recalculated those cells that needed it. A PC version (version 2.0) wasn't available until 2 years later.
rspahitzCommented:
Happy birthday Excel!
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
To think that I used to work with Multiplan, Excel's predecessor ... As the saying goes: time flies when you're having fun!
JohnBusiness Consultant (Owner)Commented:
I have not heard of Multiplan, but a number of my household spreadsheets were originally VisiCalc spreadsheets.

By the way, there is no more customizable ribbon in Office 2016. It has gone.

Adobe DC PDF Maker does not work with Office 2016 (normal for the Adobe sloths) and no sign of it being fixed.
byundtMechanical EngineerAuthor Commented:
Office 2016 is apparently incompatible with Project 2013 and Visio 2013. Older versions of standalone Office apps like Project and Visio will be uninstalled when you install or upgrade to Office 2016. You can still use your older copies of Project and Visio on a different computer or virtual machine because their licenses remain valid--but you will need to upgrade to Project 2016 and Visio 2016 on your box with Office 2016. Microsoft is apparently offering a free upgrade for this purpose. "We need to remove some older apps" error

I haven't installed Office 2016 because of the aforementioned issue with Visio and Project, but I certainly hadn't heard anything about the Office ribbon losing its customizability. Those features are being added to Macintosh Office 2016, so it would be quite surprising if Windows Office 2016 was losing them.
byundtMechanical EngineerAuthor Commented:
Congratulations to Excel MVPs who were awarded (or reawarded) on October 1. Receiving that award means that you have been providing excellent service to the technical community over the past year, and I am really happy that you have done some of that by participating in our TA.
zorvek (Kevin Jones)ConsultantCommented:
Bite me. I've graduated. There is a world out there.
zorvek (Kevin Jones)ConsultantCommented:
I skulk in the shadows.
zorvek (Kevin Jones)ConsultantCommented:
A book.
zorvek (Kevin Jones)ConsultantCommented:
We hired an editor.
zorvek (Kevin Jones)ConsultantCommented:
Yep. My spelling sucks and Zack isn't much better.
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
John, what do you mean by "can't customize the ribbon". I have added groups and icons to the Insert ribbon (to bring back the Power View icon, which has disappeared in 2016) and had no problems doing that.

I'm on a road trip with no access to 2016 for another week, so can't verify, with the RTM version, but in the technical preview, I could do the same ribbon customizations that I was used to from 2013.
[ fanpages ]IT Services ConsultantCommented:
A book.

(only six left in stock... order soon, etc.)

A video.
JohnBusiness Consultant (Owner)Commented:
John, what do you mean by "can't customize the ribbon".  <--I was wrong. I had read this in Microsoft Answers, but I went back and find I CAN customize the ribbon.  I was incorrect above.
aikimarkCommented:
Congratulations, Kevin.  Are you doing a book tour?
zorvek (Kevin Jones)ConsultantCommented:
I just did. Now I need to replace the igniter in my potato cannon so I can have some fun. Next week it's the Dakotas and shooting sh*t. Then Microsoft so I can tell them how to fix Excel. Any suggestions? Any peeves?
[ fanpages ]IT Services ConsultantCommented:
Interesting approach to a member's Profile...

[ http://www.experts-exchange.com/members/israellopez215.html ]

...yet no [Hire Me] button available! :)
[ fanpages ]IT Services ConsultantCommented:
Hi N,

Sorry... yes, that is what I meant.

(Are you aware of the Copyright discussions I have been having with some of your colleagues, by the way?  If so, this particular profile is a prime example of not remaining anonymous behind a member name at this site).

PS. If you have the time, take a look at the last two questions (the most recent of which was posted within the last six hours or so).  Do they look the similar to you &, hence, should the oldest be closed?
byundtMechanical EngineerAuthor Commented:
Netminder,
I've responded in the "unique identifier" thread, and hope you don't delete it in favor of the earlier question.

The real issue isn't those pesky periods, but rather how the Asker wants the code to function. As fanpages kept pointing out, there wasn't really any question asked--just a statement of overall goal. I took some guesses as to what might satisfy the need, but anticipate a lot of back and forth before the Asker is satisfied.

Brad
[ fanpages ]IT Services ConsultantCommented:
I think a similar "question" was posted a few days ago...

[ http://www.experts-exchange.com/questions/28754599/Financial-Modeling-Comprehensive-Training.html ]

Would anybody (with sufficient privileges) care to remove this one from the site?
byundtMechanical EngineerAuthor Commented:
I deleted the Financial Modeling Comprehensive Training question as advertising

Brad
[ fanpages ]IT Services ConsultantCommented:
Hi N,

Somebody had already requested attention on that question & had not received any assistance when I posted above.

The same is true of this advertisement....

[ http://www.experts-exchange.com/questions/28823099/Excel-Password-Recovery.html ]
byundtMechanical EngineerAuthor Commented:
I've gotten emails from Microsoft regarding several ideas I voted for on Excel's UserVoice suggestion pages. Most of the responses were favorable. The ideas are being taken seriously, and the more votes they receive (including future votes), the higher their priority will be.

The one exception was a suggestion that VBA be brought into the modern age. Microsoft demurred, saying that VBA will continue to be a part of Windows and Macintosh desktop Excel, but it won't be extended to other Excel endpoints (web Excel, iPad, iPhone, etc.). Nor does Microsoft intend to put much investment in improving the Windows VBA Editor (though Mac Excel 2016 VBA Editor will be brought closer to Windows Excel 2016). No matter how well or how much we argue for VBA, the path forward will be JavaScript, folks.

The threshold for Microsoft to look at an idea is 20 votes; all such ideas will be reviewed by a Microsoft Product Manager. Beyond that, more votes equals higher priority. Ideas that are hard to implement and don't offer a big improvement to the user will need more votes to gain priority than ideas that are either easy to implement or promise big gains.

If you want to stuff the ballot box, you can apply up to 3 votes for an idea. You can also stand on a soapbox (as I do below) and encourage friends and colleagues to vote up your suggestions.

To make sure you are serious about ideas you vote for, you only get 10 votes in each product category (e.g. desktop Excel). You will recover previously used votes when Microsoft takes action on an idea or dismisses it.

Things you might consider voting for:
"Bring back offline VBA Help" http://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10617966-bring-vba-offline-help-back 

"VLOOKUP with exact match, binary search and sorted data" http://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10623393-vlookup-with-exact-match-and-sorted-data

"Allowing data literals in functions and expressions" http://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10623201-allow-date-literals-in-functions
[ fanpages ]IT Services ConsultantCommented:
...No matter how well or how much we argue for VBA, the path forward will be JavaScript, folks.

...It's almost VBScript, that is very similar to (Classic) ASP, & that is almost ASP.net, so really it's VBA dotNET.

Python support seems to be getting a lot of interest:

[ http://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10549005-python-as-an-excel-scripting-language ]

(See also: [ http://xlwings.org/ ])
[ fanpages ]IT Services ConsultantCommented:
...No matter how well or how much we argue for VBA, the path forward will be JavaScript, folks.

...It's almost VBScript, that is very similar to (Classic) ASP, & that is almost ASP.net, so really it's VBA dotNET.

Python support seems to be getting a lot of interest:

[ http://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10549005-python-as-an-excel-scripting-language ]

(See also: [ http://xlwings.org/ ])
Martin LissOlder than dirtCommented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for byundt's comment #a41018781

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
zorvek (Kevin Jones)ConsultantCommented:
Why oh why do we have to endure this ongoing monkey business. Why can't EE create a special thread for experts to huddle around and shoot the shit. A thread that just goes on and on without being subjected to the autobots and lazy moderators. Oh, wait, I already proposed this very thing years ago and then gave up trying. Yes, Erik, at the end of the day it is very much like repeatedly banging one's head against the proverbial thick concrete wall. Well, it isn't all that bad, we did get some pretty cool features in before the door closed again.
byundtMechanical EngineerAuthor Commented:
Regarding Python, Ashvini Sharma (one of the Program Managers for Excel at Microsoft) posted in the Python as a Scripting Language thread in User Voice:
We’re going to investigate this for a future update to Excel. Of course the probability of getting it done goes up proportionately to the votes we get on this item, so please gather as many votes as you can!

Also, would love to hear if you have preferences for ordering between Python or R.

This user data-driven emphasis on evaluating potential new features is something new at Microsoft, and quite beneficial in my opinion. The Python suggestion had 6806 votes as of this post, so the User Voice  voting machine is certainly getting a lot of attention from the user community.

I also found it interesting that Sharma asked about preferences between Python and R. That comment certainly suggests that Microsoft had been thinking about the issue ahead of time, and how the user community might try to use Python (or R) and for what types of problems.
rspahitzCommented:
Looks like we need an auto-bot to automatically post a dummy message every 15days to keep this thread alive. Just make sure to update it when we go to #39
JohnBusiness Consultant (Owner)Commented:
We could use this thread, Expert Discussion 39, or a Discussion Group (which I think might already exist). Discussion Groups are better for this type of activity.
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
We could simply close this thread and link to the next thread, which is really not a thread but a discussion that will never get closed. It has already been created. By Kevin.

http://www.experts-exchange.com/discussions/462/General-Discussion.html

Brad, would you mind closing this Q?
byundtMechanical EngineerAuthor Commented:
Discussion Groups may appear to be better on the surface, but the one for Excel isn't being used. It has only 11 posts since inception in January. In the same time that the Discussion Group has lain fallow, this vibrant series of Questions has received 181 posts.

Microsoft faced a similar issue with Windows 8. The new version of Windows looked better to Management because it checked all the boxes that Product Managers thought were important, and the Start menu was deemed archaic. Only one problem--people continued to use Windows 7 because it did what they needed it to do. Users placed little value on the new features and resented the more difficult approach needed for many routine tasks.

So please, please, please--let us continue to run our discussions through this series of Q&A threads. And let us treat it as a Sticky, outside the purview of Cleanup and easily referenced when one of the regulars has something interesting to say or needs help in a thread.
zorvek (Kevin Jones)ConsultantCommented:
We didn't use it because we didn't believe it. All we have to do is all move over there and stop posting here and it should work just peachy. At least that is what I was led to believe. My only concern is what happens to the thread when it gets large: does it convert into multiple pages like answers.com and we have to navigate (slowly) the the end every time we want to view a post or make a new post?

Kevin
JohnBusiness Consultant (Owner)Commented:
I am happy to stop here and move there. Other discussions work fine. We just have to DO IT.
zorvek (Kevin Jones)ConsultantCommented:
We can start the process by posting a final post here that links to there with the text that we all packed up and moved.
rspahitzCommented:
I knew nothing about the other area or I would have visited...ugh...apparently I was the first to post...shows how memorable it was !
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
in this post: capital D Discussion is the Discussion feature of EE. Lower case d discussion is this thread.

Kevin, re:

My only concern is what happens to the thread when it gets large: does it convert into multiple pages like answers.com and we have to navigate (slowly) the the end every time we want to view a post or make a new post?

By default, a Discussion will show you only the most recent posts. I think it will remember where you last left off and will continue from there. If you click an email notification, the Discussion will show the threads from that point onwards. Older items are not showing by default, but can be loaded with a click if you want to go back.

From a usability point of view I really don't see why we should continue abusing this question thread for discussions that don't have an "answer".  The look and feel  of a Discussion is almost identical, i.e. post after post in the middle of the screen. Discussions show posts in chronological order. You can monitor a Discussion and get email notifs. Discussions don't load ALL messages since the beginning of the thread, so they are mobile friendly.

The only real difference is that Discussions don't end up on the Cleanup Volunteers' list when there has been a quiet period.

I'm happy to participate in the Excel Expert Discussion thread instead of artificially keeping this thread alive.  That way the Cleanup Crew has one item less to handle and we don't get bugged by them all the time.

My appeal to Brad: Once we hit the typical post count to close (or maybe even now), just close this question and post a link to continue in the Discussion thread instead of continuing in a new question thread. Not so big a deal, really, AFAIC.

People have found the new Excel Experts zone discussions from links in old Excel Experts zone discussions in the past. I'm positive people will find the link to continue the chat in the Discussion feature.
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Brad,

this vibrant series of Questions has received 181 posts

We used to close a Excel Experts zone discussion question after about 150 posts and start a new one so the load time would not impact speed and data plans. Looks to me that this thread is long overdue to be closed.
zorvek (Kevin Jones)ConsultantCommented:
The post count is not necessarily the guide. It was the size of the page. We found that about 100K was the point at which folks started complaining. This page is about 50K now.

Kevin
rspahitzCommented:
A few thoughts...I discovered this discussion group by circumstance...looking for questions to answer and one day there it was for me to investigate.  I "joined" and stayed because it was interesting.  If not for being here in the Excel question area, I never would have discovered it (since I typically don't go wandering around EE to keep myself entertained or looking for friends...although I've met some wonderful contributors over the years.)
I used to visit the "Lounge" on occasion (does that still exist?) but found it unexciting.
So I guess the question comes down to how to promote the Discussion area so people like myself can find it rather than clog up the question area with ongoing threads that must be managed every few weeks by mods and every 6 months by us.  Or maybe we don't care if people "like me" find the Discussion area as long as enough other wanderers find it.
Another thought (hopefully not considered blasphemous): is EE going to survive? It was originally created as a forum to find answers. Way back it tapped into experts' knowledge for things that were relatively easy but not obvious.  Then along came serious search engines that essentially did the same thing.  Now it appears that EE is the place for "solutions".  While that's good for EE, it's bad for experts like myself who don't have time to spend 30-60 minutes solving someone else's problems without compensation.  I'd love to do that when I retire but that still a way off. Anyone else feel this way? Is there a solution to help EE and people like us?
zorvek (Kevin Jones)ConsultantCommented:
Regarding the free solutions: yep.

Regarding finding this thread: I included in my original proposal/design the creation of an "expert discussion thread" promotion of same on the topic home page that was visible only to regular posters of solutions with some rank/points. By the time I proposed the solution the people actually interested in making EE better with regards to the core Q&A model had been pushed out and I became disillusioned with the whole scene as EE started a path toward becoming the next Facebook of the technical expert forum world.

At this point I believe adding a link to the discussion thread on the topic area/zone home page based on a few simple criteria would be a rather simple endeavor given the flexibility now embodied in the site's underlying technology. But it needs a champion and I don't have any interest any more.

Another possibility is to post a "pointer" question once a month addressed to any new experts. It's an old technique used by OPs to re-generate interest in a languishing question. It was made obsolete by one of my other proposals: the neglected question function.

Kevin
Saurabh Singh TeotiaCommented:
Guys,

I need a bit of help here in this question as i'm struck and not able to figure out why it's behaving in this manner...

Question

My real problem is that if i paste the values of str from immediate window in place of str it works..but it fails at str which is so weird..

Also is their another way to do this apart then playing with slicer as this is adding a powerpivot which complex the entire scheme of things..and i will definitely want to understand or solve this puzzle myself too..

Saurabh...
Martin LissOlder than dirtCommented:
We'd kindly ask that if you come across this thread during clean-up, just skip it.
Since I do cleanup most every day that would mean I would have to Skip this every day and since I'm a curmudgeonly old man I find that annoying and so I'd rather just post bump style posts like this one instead.
Me
rspahitzCommented:
Nice self-portrait :)
Saurabh Singh TeotiaCommented:
Anyone has any thoughts on this one...As i'm struck on this and can't figure out..

http://www.experts-exchange.com/questions/28914816/Slicing-and-Dicing-of-Data-by-powerpivot.html

Saurabh
Martin LissOlder than dirtCommented:
Nice self-portrait :)
Yes, on one of my better days.
Martin LissOlder than dirtCommented:
....
rspahitzCommented:
I tried using excel online the other day and was disappoint that some features I use were not there. Has anyone else found that?
byundtMechanical EngineerAuthor Commented:
I gave a presentation in January, posted the workbook used for it in my OneDrive folder and gave the link to participants. After the meeting, they started complaining that all the explanatory discussion (which I had placed in textboxes) was missing from the file when they downloaded it. I was frosted to learn that Microsoft stripped any feature (such as VBA code or textboxes) that wasn't supported in Excel online from any workbook that people downloaded from OneDrive.

My response to this lunacy was to post the workbook on DropBox, and to modify the version on OneDrive by putting the DropBox URL underneath the textbox along with a suggestion to get it from there instead of OneDrive. When Excel online gets around to supporting texboxes, the commercial for DropBox will be automatically hidden.

The list of missing features in Excel online should be growing shorter with each passing month. Microsoft updates Excel online monthly, and feature parity is a big goal.

That said, you shouldn't be holding your breath for VBA to be added. Microsoft has gone on record that VBA will continue to be supported on its existing platforms (Windows and Mac desktops), but will not be extended to Excel online, iOS, Android or Windows phone.
Martin LissOlder than dirtCommented:
Cleanup bump.
Martin LissOlder than dirtCommented:
Cleanup bump again 3 weeks later.
Martin LissOlder than dirtCommented:
Again
Martin LissOlder than dirtCommented:
Cleanup comment - No real activity here for 105 days.
rspahitzCommented:
Wow. So here's a thought. Has anyone ever done a large conversion project from excel to access? How did you start and proceed, and what challenges did you find along the way?
zorvek (Kevin Jones)ConsultantCommented:
Now why would anyone want to do that? SQL Server with an Excel front end seems more practical and keeps the existing UI parts of Excel.

Kevin
rspahitzCommented:
Small businesses typically have Access but not SQL server and don't want to pay for a dba
Maybe you misinterpreted by intent of large scale where large in the excel world can be small in the db world.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Then Excel as FrontEnd plus Access DB is a smaller change. You can keep the formulas and "just replace" the "database".
[ fanpages ]IT Services ConsultantCommented:
In case this helps (or hinders!) the discussion...

[ http://www.experts-exchange.com/questions/28742700/Should-I-create-the-database-directly-in-Excel-or-use-Access-and-then-create-a-connection.html#a41040608 ]

(fanpages: 14 October 2015 at 21:47:37; ID: 41040608
---
Disclaimer: I use both products professionally on a daily basis; in some cases MS-Excel is the lead development platform &, in others, MS-Access).

Re: Excel vs Access

Both have their own pros & cons, but ultimately the decision to develop in either comes down to:

1) Security Model required (if any)
2) Quantity of data to be stored (rows x columns) rather than actual byte capacities
3) Speed of development (i.e. costs in short term versus long term)
4) Ease of deployment (into user environment)
5) Stability of product
6) Multi-user usage/capabilities
7) Impact (costs) of Downtime due to failure
8) Familiarity with product/development environment
9) Speed of execution
10) User interface

Given the choice of both products mentioned, personally I find MS-Excel a better product for quick development and more pleasing user interface is MS-Excel (given that users are probably more familiar with a spreadsheet tabular layout than a database with many "tables").

However, if the speed of execution, stability, storage capabilities and security of your data are all primary concerns, then MS-Access is the route to take.

When both solutions have been tested thoroughly, issues with an Access-based variant are ultimately down to data (that can ordinarily be fixed with SQL statement updates), whereas Excel presents a steeper curve of investigation as formulae and lookup-based approaches take time to debug and correct.

The multi-user facilities that MS-Access provides far outweigh the "shared" workbook option in MS-Excel (and Access ultimately proves the more stable platform in this respect).


That said, I prefer MS-Excel as a development tool, but in terms of speed of execution MS-Access wins every time.


In summary, if your users prefer a solution they are at ease with (read: can relate to), that is quicker to develop, but is not as secure/stable then go down the MS-Excel route.  If data integrity and a robust application are paramount at the sacrifice of a lengthier development process, then MS-Access is your tool of choice.
---

PS. I am presently involved in a project converting a multi-MS-Access-based application/system with ASCII (CSV/Pipe-delimited Values) file exports/import to/from MS-Excel, into an MS-Access front-end with an MS-SQL Server back-end & using a revised version of the existing MS-Excel workbook to connect directly to the same MS-SQL Server database (removing the need for any external files).
zorvek (Kevin Jones)ConsultantCommented:
Why the love for Access?

1) Access is a dying platform. Much like, unfortunately, VBA.

2) SQL Server Express is free which is ample for most small companies' internal requirements and certainly any migration from an Excel-based DB.

3) Access is not designed to be accessed by multiple users. Each user must have the Access engine on their own box and each Access instance uses the "shared" file on the server to coordinate multi-user activity--an extremely inefficient model. The SQL Server engine runs on a server and handles all requests locally on that box. Don't have a server box to run SQL Server? Where are you putting the "shared" Access file?

4) SQL Server is no more complicated or uncomplicated than Access. Installation is an automated process and there are many knowledgeable consultants that can deal with any maintenance needs on a part time basis if an in-house resource is not available.

I've been building Excel/Access/SQL Server solutions for a long time and, at this point, I avoid Access like the plague and encourage SQL Server wherever Excel's DB capabilities are stretched thin or multi-user access is a requirement.

Kevin
[ fanpages ]IT Services ConsultantCommented:
Hi Kevin,

I didn't say I loved Access :)  Just having used it for the past 25 years it has grown on me (like a wart would).  That was grown, not groan, by the way.  Although it does offer a fair share of groans too.

1) It is dying, but a long way of being completely dead.  All the major financial institutions I have visited for the past 25 years all use MS-Access somewhere in their organisation!

2) Not all Corporate strategies consider free products.

3) Sorry, I disagree with you there.  MS-Access was originally designed for multiple concurrent users.  I once worked on a project (MS-Windows 95, MS-Access 2.0, 95, & 97) where 800 (yes, eight hundred) users may connect to the same server-side (local area network hosted) ".mdb" file at the same time (via a client-side ".mdb" file running Forms & Queries).  I am not disagreeing with your regarding MS-SQL Server (or any other server-based object-relational database management system), but MS-Access can be used to support client-server implementations.

(The MS-Access databases I am presently involved with migrating to MS-SQL Server are all standalone files deployed to local C: drives, though).

4) The installation may not be complicated, but the programming interface, & the lack of a graphical user interface in-built with the product, may deter those that are used to drag'n'drop/wizard/Query By Design programming approaches.

If an MS-Access user is not familiar with Data Definition/Description Language syntax, that is quite a steep learning curve (too).
zorvek (Kevin Jones)ConsultantCommented:
Regarding multi-user access, I didn't say it didn't support it, I said it wasn't designed to support it. 800 users concurrently accessing a single Access file will pretty much drop each and every client to it's knees--so I suspect that each of those 800 users accessed the DB only occasionally. The issue is that, not being designed to support concurrent access or, in other words, requiring each and every Access instance to communicate their intentions with the database (e.g., record locking) requires reading and writing multiple times to the database over a network connection while waiting for any other process to complete whatever it is doing. This form of coordination is very inefficient and can have serious performance issues with as few as two concurrent users--let alone 800. SQL Server is designed to be used by many concurrent users and optimizes performance in these cases.

Kevin
[ fanpages ]IT Services ConsultantCommented:
The 800 users were in a 24-hour call centre.

They may have been using the database at ad hoc intervals throughout a call with a single customer, but all users were connected & reading/writing concurrently (& locking records with an explicit "lock" column handled programmatically in Visual Basic for Applications) otherwise the 4K page-locking provided as standard by the product would have caused a lot of issues).

The system was eventually replaced with an Oracle database solution.
Martin LissOlder than dirtCommented:
blah, blah, blah
byundtMechanical EngineerAuthor Commented:
Please leave this question open
Saqib Husain, SyedEngineerCommented:
The site Admins and Moderators are more than happy for this thread to stay open for as long as byundt wishes to continue the discussion. Whilst we do appreciate that this sort of circumvents the "abandoned question" remit we feel that we can make a special dispensation now and again and this is one of those time when we are willing to make that dispensation. We'd kindly ask that if you come across this thread during clean-up, just skip it.
[ fanpages ]IT Services ConsultantCommented:
Martin Liss:
I've requested that this question be closed as follows:

Accepted answer: 500 points for byundt's comment #a41018781

for the following reason:

I received an email requesting that I "Help resolve this question" so as a good EE member that's what I did.

Better solution: Ignore the e-mail.
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
I don't know what's so difficult about keeping this question open. We've been through this time and time again. What is the problem???

So, this Q comes up in the cleanup queue. Well, just hit "skip" or whatever the button reads right now and be done. Don't start a storm every few weeks.
Martin LissOlder than dirtCommented:
I didn't close this as a part of my cleanup duties and the last time I "start[ed] a storm" was in January. Why not replace this with a group discussion? Isn't that what this is in effect?
[ fanpages ]IT Services ConsultantCommented:
^^^
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
CLEAN-UP VOLUNTEERS:

The site Admins and Moderators are more than happy for this thread to stay open for as long as byundt wishes to continue the discussion. Whilst we do appreciate that this sort of circumvents the "abandoned question" remit we feel that we can make a special dispensation now and again and this is one of those time when we are willing to make that dispensation. We'd kindly ask that if you come across this thread during clean-up, just skip it.

Thank you kindly for your continued assistance with this.

thermoduric
Community Support Moderator
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
rspahitzCommented:
Didn't we try a group discussion and it never went anywhere?  And how about jumping to #39?
JohnBusiness Consultant (Owner)Commented:
There already is an Excel Discussion group meant to replace this.
[ fanpages ]IT Services ConsultantCommented:
There already is an Excel Discussion group to co-exist with this.

Fixed.
zorvek (Kevin Jones)ConsultantCommented:
Nigel is a brat.

We started a group discussion a while back but, for some reason, it didn't keep our collective interest. I just revisited it and it seems to be just what we need to replace these series of threads. It displays only the last six posts below the original post which solves one huge problem here: the thread getting so long that it takes too long to load and we have to start a new thread--this is the 38th such thread. The second problem it solves is we don't have to deal with the cleanup rascals and Nigel's teasing the badgers. A third (new) problem it solves is that none of the links to the previous/next threads work now.

Here is the link to the general discussion:

https://www.experts-exchange.com/discussions/462/General-Discussion.html

We asked for it and we got pretty much exactly what we asked for. What say we all just start using it and post a final closing comment here? These have been fun but we have better tools now. I think at this point we can all agree that it's just a question of habit and critical mass.

Does anyone have any reason for us not to move to the general discussion?

Kevin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
[ fanpages ]IT Services ConsultantCommented:
Does anyone have any reason for us not to move to the general discussion?

The discussion forum is incredibly difficult to use on a mobile device.
zorvek (Kevin Jones)ConsultantCommented:
How so? It seems to have the same basic layout as this thread. I would think it would be better because only the last six or so comments are displayed by default with the "question" displayed at the top.

With a regular question thread like this one, every post is always displayed.

Kevin
[ fanpages ]IT Services ConsultantCommented:
How so?

Perhaps it has changed recently, but I do recall posting a few images when the discussion thread first started to demonstrate how it did not render correctly & there was no room to read the text posted because of the presentation of the page.

With a regular question thread like this one, every post is always displayed.

Yes, I like that; especially if viewing a thread for the first time &/or not having followed the progress of the discussion within the last six or so comments.
zorvek (Kevin Jones)ConsultantCommented:
Perhaps it has changed recently, but I do recall posting a few images when the discussion thread first started to demonstrate how it did not render correctly & there was no room to read the text posted because of the presentation of the page.

Then let's try it again and, if there are issues, let's get them resolved.

Yes, I like that

One click on "View Previous Comments" and you can see more. The team actually put some thought into it. This makes sense since, once caught up, you don't want to see the earlier posts unless you decide you need to research.

Anything else?

Anyone else?

Kevin
rspahitzCommented:
...investigating whether that link is phone-ready.  Although this is a long thread, I can read it on the phone...
zorvek (Kevin Jones)ConsultantCommented:
OK meatbags, it's official. Brad has closed this last and final thread for the Excel experts general discussion. We're packing up and moving to the Excel Topic Area Expert Discussion group.

NIgel, Wayne tested posting an image and it seemed to work well.

Here is the general discussion for all things we've traditionally covered in these old-style "question" threads: https://www.experts-exchange.com/discussions/462/General-Discussion.html

I have some ideas for making the group even better--some involve additional functionality which I already sent to EE and posted in the discussion.

This should be the last post here.

Kevin
[ fanpages ]IT Services ConsultantCommented:
...Wayne tested posting an image and it seemed to work well.

The reason I mentioned posting images was that they demonstrate how bad the discussion group thread rendered on a mobile device; posting images was not the problem!
Saqib Husain, SyedEngineerCommented:
Wow!!! almost six years since he died!!! Dave Miller
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.