Excel Zone Expert Discussion, Number 16

Kevin - Thanks for taking over that question from Tansu. - Patrick

OK, I have a question about this zone, I have given a solution to a question where the last comment was dated 12/15/09, I was wondering what I have to do to draw attention to this. It seems to me that this question should have been reviewed by now (link below):

https://www.experts-exchange.com/questions/24980101/Excel-Crosstab.html

I have a couple of those too, so it's good to know where to go.  I read the clean up page, but there was no link to volunteer.  It would be nice if they added one.

I spoke to Annie a few weeks ago, and again last week, and I'll be starting to clean up Excel questions shortly. So expect to see my name in your email a lot! There goes my answered:participated ratio :)

Yes, I saw that :)  What I'm saying is, it'd be nice to have a link in your profile or something, that you could request to become a volunteer, instead of sending an email.

If you can't muster up the energy to fire off an email, you probably can't muster up the energy to clean up.

It's not that at all, it's just more convient, what if the email gets caught in the spam filter and then you're just stuck wondering what happened.

@Kevin. Subscribed.

@ Eric - in case you missd this:
>FYI, I checked with Richard and apparently whenever he tries to use his  account here he gets prompted to purchase a subscription,
HELP

Hi Everyone (again!)

Good to be back and not having demands placed upon my credit card every time I log in ;-)

Richard

Welcome back Richard!

BTW, checking out that newsletter that just hit my mailbox, I see that teylyn is currently in the lead for four Rookie of the Year honors:

• site wide
• Excel
• Word
• Office
  

You go, girl!  :)

Hi all,

Thanks, Patrick. My first (Excel) Master T-shirt arrived on the NZ mainland (North Island, West Coast) today. Eight weeks delivery time is about right. I'm wearing it with pride.

Something has been bugging me for a while:

I understand from the EE rules that any questions re cracking passwords in Excel or recovering "lost" passwords are not au fait with the site policies and ground rules.

Whenever I see a Q along these lines, I'll hasten to comment that there are no answers to be expected from this site.

Then, a few minutes later, some of our most established experts will ignore the EE guidelines and readily give out links to either software or sites with code on how to break Excel security.

How does this gel with the EE rules/guidelines and our own code of conduct (unwritten, but maybe still in existence)? Should we not rather just point to a search engine instead of falling over each other to be the first to post the best crack solution for a few paltry points?

I hope that at least Dave will agree with me, since I've seen his posts warning experts off posting links to crack sites. But I've seen experts with Excel Genius status handing out such info like candy.

Apparently we're not all on the same page. I'm afraid it's not good practice if we all deal with these questions in a different way.

Could we possibly collect some opinions here from the experts participating in this thead, on your opinions on providing solutions to password cracking/security circumvention questions? Maybe we can then find some consensus and work as a team.

cheers, teylyn

cheers, teylyn

Teylyn,

The problem is those points. People are perhaps more interested in earning points than keeping strictly within the rules. Having said that supplying pointers to sites offering password cracking services is hardly giving away state secrets nor actually contributing to the cracking of passwords. It's just providing a pointer to a company that may or may not be able to help.

Patrick

BTW zorvek (Kevin) posted this some while back:

Experts Exchange prefers that we do not post methods for unlocking worksheets and workbooks without the password, but, in the case of Excel, posting a link to another site that publically displays the technique is acceptable. You will find complete instructions for unlocking a workbook or a worksheet without the password at http://www.zorvek.com/excel/unlocking-workbooks-and-sheets.htm.

A tool that does the above and handles workbook file passwords is Excel Password Recovery Master which is available at http://www.rixler.com/excel_password_recovery.htm. The demo version is free but is restricted to recovering passwords with a length of three characters or less. The cost of the registered version is $30. It requires Internet access to remove workbook file passwords. The $30 purchase price includes one workbook file password recovery. Additional workbook file password recoveries cost $10 each. The registered version recovers an unlimited number of workbook and worksheet passwords without additional cost.

Another tool that does the above is Excel Key which is avaliable at http://www.lostpassword.com/excel.htm. The demo version is free but is restricted to recovering passwords with a length of two characters or less. the cost for the registered version is $45. It is capable of recovering workbook file passwords but uses a brut force method that can take some time and is not always successful on more difficult passwords.

Hello Patrick,

I saw that post from Kevin, too. And I was more than slightly astonished.

A lot of the experts here make their living by consulting and writing solutions with code that they will protect with a password. Is it not against the interest of these fellow members if we make it even easier for people to find a way to crack that protection?

>> "but, in the case of Excel, posting a link to another site that publically displays the technique is acceptable."

This is where I disagree with Kevin and with probably a few other experts.

By the way, none of these sites are news to me. I've done my homework and I know how to find them.

But to just give these site addresses to people who are too lazy to do a little searching on Google somehow rubs me the wrong way.

cheers, teylyn

To be honest, I doubt that anyone who makes a living as an Excel consultant believes that password protection is worth a damn in terms of protecting their IP. I'd also be surprised if people who are paying to ask questions here are really trying to break into stuff; I think it's more likely that they have genuinely lost or forgotten the password and, as such, I have no problem with assisting them. For the most part they are going to have to pay in order to break any vaguely secure password (the one to open the workbook) which will discourage the majority of scoundrels who are looking to get something for free.
FWIW,
Rory

teylyn,

I suggest we should relax a little over the matter as anyone with half a brain will use Google to find password cracking solutions. Whether we suggest as much or keep quiet is really a personal choice but I don't see anything wrong with nudging them in the right direction.

As for consultants being concerned that their work will be opened for one and all to see I believe that is an overstated concern. Most solutions are usage specific and as such the users clearly do not have the ability to mess with what they have paid for - otherwise they would have written it themselves in the first place - rather than paying good money for a consultant's help. And they're hardly likely to make public what they've paid for.

I don't believe we should get too up-tight about the whole matter. However we should not actually provide the solutions ourselves. I'm sure we're all agreed on that matter.

Just so you've got a fuller picture, Brad (byundt) posted this a while back:
====
Worksheet passwords are very easy to crack. Workbook passwords are tougher--I suggest a Google search for one of the various commercial software packages that offer that ability.

Experts-Exchange policy on cracking passwords is discussed here: https://www.experts-exchange.com/questions/21365256/Working-EE-policy-on-cracking-open-Microsoft-files-VBA-and-protected-sheets.html

My interpretation of this discussion is that it is OK to provide a link to password cracking software for worksheet passwords, but not for passwords protecting VBA code.

To unprotect worksheets, I like the code posted by Microsoft Excel MVP Nico Sterk at http://members.chello.nl/n.sterk/ExcelPages/Files/excel_files.htm  The BreakPassword macro in the downloaded workbook will identify a substitute password in about a minute. It requires Excel 2000 and later. Here is how to use it:
1) Click on any cell in the worksheet that you want to unprotect
2) ALT + F8 to open the macro selector
3) Choose the PasswordBreaker macro (from Nico Sterk's workbook), then click the "Run" button

With Excel 97 and 2000, there is an amazing loophole discovered by MalicUK--just reset the password to an empty string using VBA:

Sub UnprotectActiveSheet()
Dim ws As Worksheet
Set ws = ActiveSheet
ws.Protect "", , , , True
ws.Range("A1").Copy ws.Range("A1")
End Sub

Brad’s code is:

Sub BreakWorksheetPassword()
'Breaks worksheet password protection. Rewritten by Brad Yundt November 6, 2007
'Based on technique posted May 22, 2001 by Bob McCormick on NG: microsoft.public.excel.misc
Dim i As Integer, k As Integer
Dim j As Long, counter As Long
Dim PossiblePassword As String
On Error Resume Next
For j = 0 To 2047
    For i = 32 To 126
        PossiblePassword = ""
        For k = 0 To 10     'Append the kth digit in binary number j as either "A" or "B"
            PossiblePassword = PossiblePassword & Chr(65 + (Int(j / 2 ^ k) Mod 2))
        Next
        counter = counter + 1
        PossiblePassword = PossiblePassword & Chr(i)
        ActiveSheet.Unprotect PossiblePassword
        If ActiveSheet.ProtectContents = False Then
            MsgBox "One usable password is " & PossiblePassword
            Cells(1, 1).Value = PossiblePassword
            Cells(2, 1).Value = CStr(counter) + " guesses"
            Exit Sub
        End If
    Next
Next
On Error GoTo 0
End Sub
====

Patrick

It's clear from the posts above that this is very subjective given the readily availability of such tools/code.
 
My position is that its better to let them go, or post the "pls   recommend google only" line. I presume  you saw my post along those   lines today

But even my subjectivity is subjective :) I don't bat an eyelid at posts like the one's above from Kevin and Brad as I know they have made a considered judgement, but I pull out a big stick if it's a noob.

Dave

I'd forgotten  https://www.experts-exchange.com/questions/21365256/Working-EE-policy-on-cracking-open-Microsoft-files-VBA-and-protected-sheets.html

Porn was the answer. I've forgotten the question!

i was a little feistier back then .....

Thanks, all.

My objective was to learn how you all think this topic should be handled, and I now have a better understanding of this.

Patrick, thanks for the link to that 5 year old discussion. I see there are supporters in both camps and that it's a topic that cannot be ruled by a simple Yes/No choice.

cheers, teylyn

>> Porn was the answer. I've forgotten the question!

I thought the answer to that was 42!

Kevin and I discussed the password cracking issue after becoming MVPs because neither of us wanted to jeopardize our new status over something that Microsoft might consider unprofessional or improper. So Kevin contacted our MVP lead and asked the question directly. His advice was to look at the facts of the situation. In most cases, cracking the workbook would be considered a valued and proper service.

Since we often don't get a full background (or may not believe what we are told), some Experts offer to remove the password if the Asker posts the workbook. The advantages of this approach are that you don't disclose the trick to the entire world, you can look at the workbook and determine whether it matches the Asker's story, and you remain responsive to the Asker's need.

Passwords are so easy to crack by brute force because they are hashed to one of only 65,536 possible values (16 bits). The actual password is not stored with the file--just the hash that results from it. Any other password that hashes to the same value will be accepted to unlock the worksheet. The code posted above is somewhat inefficient because it tests three times the required number of values. But VBA is fast enough that you can crack the worksheet password in about a minute.

FWIW, I subsequently changed the code to put the password and number of guesses into a textbox rather than cells A1 and A2. The textboxes don't wipe out the original contents of the cells, and are easy to delete before posting the file for the Asker.

Brad

Sub BreakWorkbookPassword()
'Breaks workbook password protection. Rewritten by Brad Yundt September 5, 2008
'Based on technique posted May 22, 2001 by Bob McCormick on NG: microsoft.public.excel.misc
Dim i As Integer, k As Integer
Dim j As Long, counter As Long
Dim PossiblePassword As String, sRoot As String
On Error Resume Next
For j = 0 To 2047
    sRoot = ""  'The first 11 characters of password are either "A" or "B"
    For k = 0 To 10     'Append the kth digit in binary number j as either "A" or "B"
        sRoot = sRoot & Chr(65 + (Int(j / 2 ^ k) Mod 2))
    Next
    For i = 32 To 126
        PossiblePassword = sRoot & Chr(i)
        counter = counter + 1
        ActiveWorkbook.Unprotect PossiblePassword
        If Err = 0 Then
            ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, [A1].Left, [A1].Top, 100, 50).TextFrame.Characters.Text = _
                PossiblePassword & vbLf & CStr(counter) & " guesses"
            Exit Sub
        Else
            Err.Clear
        End If
    Next
Next
On Error GoTo 0
End Sub

Select allOpen in new window

This is a useful informative link on the hash encryption. http://chicago.sourceforge.net/devel/docs/excel/encrypt.html
While my own position hasn't changed on providing cracking type answers, I am more relaxed these days as to what other Experts choose .  There are a lot more questions now, and the points are less relevant to me than they were on my first stint at EE, so I don't feel handicapped by my personal view.
That said I still don't think that password retrieval in any form is a good look for the potential perception of EE by an outsider, as the EE forum is one of the few (only?) Excel forums where password cracking solutions are fair game in certain circumstances.
I won't add any further comments to this particular debate, as I can see this (me included) getting repetitious.
Regards
Dave

Gang,

Splitting worksheets out into new workbooks, https://www.experts-exchange.com/questions/25783830/Create-excel-workbooks-out-of-many-sheets-in-one-workbook.html

Code is working splendidly for me, but Asker insists it does not work for him/her.

Thanks!

Patrick,
Have him step through the code with F8 and watch to see if the loop moves from tab to tab in the file being copied.   Have him post the code he is using to see if he has made any changes by accident.
Jerry

Try adding a debug.print on the SaveAs path, maybe he's adding in an invalid character.

Also, give him the test workbook, so he can see where to place the code (Module1).
Book3.xls

By the way, it works perfectly for me too.

Last thing, if you comment out ws.Copy, then you get the results the asker is talking about (only creating the first sheet) and you don't get the Done msgbox.  Ask him if he's getting that.

Congratulations on Barry's second million. That was fast!

Thomas

Wow... I agree that was fast man...Congrats Barry...
Saurabh...

Nicely done, barry!

If you ever learn VBA, you'll smoke us all :)

Barry, I think you'd love the VBA Evaluate method :)
well done
Dave

There have been a number of people over the years who have tried to offer help on Experts Exchange using only worksheet methods and formulas. With the larger number of questions to answer these days, barryhoudini has made a success of the worksheet methods approach. And the fact that he is undeniably brilliant means that I try to learn from his posts when we compete on difficult threads. The double Genius is no accident.

Congratulations barryhoudini on a fully merited achievement. Woohoo!

Brad

I will buy barry 2 pints in celebration when next we meet up. :) Congratulations from me too!

Thanks for all the comments above - it means a lot to me coming from those who I respect and admire.
Brad, thanks - that approach is forced on me by my relative VBA ignorance....one of these days, though...........
Rory, I may hold you to that - will it be 3 pints for 3 million?
regards, barry

Congratulations Barry!

What Rory didn't say was that the two pints are conditional on you wearing that submariner's outfit...  

;-)

barry,
yes - it's a sliding scale. Can't wait for your Savant celebration! :)
Rory

Patrick,

I believe you are well placed to offer your views in this question:

https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q__25840743.html

It's about VBA in a banking environment.

Patrick

Thanks, Patrick, but it looks like Rory has that one well in hand :)

It's interesting to put on an Asker hat

My experiences at EE have in the main been less than first rate.  Typically I need to restate my problem after the first 5 or 6 comments.  

And its very frustrating to see a google monkey or points swiper post a first response that doesn't help but will push the question down the pecking order.

I think there should be an automatic option for the Asker to request deletion of irrelevant comments. Although this would require the Asker to have met some sort of threshold to have this abillity (number of questions asked, Asker rating received by Experts etc)

Dave

I'm with you on that one Dave,
I dont normally look at questions with 3 or more comments, purely because they're already looked after.

I like the idea about deleting useless comments.
Perhaps they need to have answered more questions then they have asked?

-Nathan

Ditto.

or just a useless comment flag that would still show in the list as a zero comment question.

Thomas

When I am answering questions, I normally have QuickEE sort by number of Comments. I usually start with the fewest Comments, those being the freshest or most difficult. But when I feel contrary, I start with the most Comments--looking for threads where the Experts involved have missed the key point. Most of those threads with a lot of Comments I don't post in, but I savor the ones where I do.

Brad

> The asker? That's fine, but it'll piss of a lot of experts, and we  can never have enough of them.

QM, If applied generally yes I agree

If there was an accredited Asker program - Wizard, Sage etc based on a combined number of questions asked plus rating points received (ie a Savant Expert would have a higher rating when marking Askers than a Genius etc) then this might work. I accept that it would be complex.

It just so bloody annoying to see a question get smashed by a points sniper.

Brad,

I think you are an exception. You add significant value because you are chasing a more difficult challenge.

 When I look at the 50-100 questions that have arrived overnight my time I will only bother with looking at the few 0 comments q's, or the interesting titles. This TA runs so deep in quality terms that my experience is that it is slim pickings.  

Perhaps EE should have a flag on the question list as to the highest rated expert comment (in Excel's case in the Excel TA) level todate (ie NA, Master .... Savant etc). I would look at questions with comments from the lower rated rank only

Dave

That's a good idea Dave,
having each comment a point value depending on the Experts's rank.
But would that be Zone rank, or overall?

-Nathan

If would have to be zone rank. So if the highest rated Expert replying to a question had a Excel Master cert then the question would flag

Title     Points    Comment #    Max Expert Ranking

Dave

>>> Perhaps EE should have a flag on the question list as to the highest rated expert comment (in Excel's case in the Excel TA) level todate (ie NA, Master .... Savant etc).

Quite agree that sounds a quality way to go, (zone based) although I know of at least one highly ranked google jockey elsewhere on the site.  Whilst I am thinking it won't be perfect I do believe it will add significant benefit in a way that ought to be practical to implement.

Chris

And I still hope Brad (and the rest of you) will jump in because 'genius' or not an expert I will never be since there are too many ways a question can be misunderstood such that the asker is struggling for a quality answer ... and speaking personally I sometimes don't realise how I am floundering and have been rescued from myself by you guys stepping in.

Chris

You could also rank by Questions answered.
If a google jockey gets a high rank and still gets lots of winning answer's they must (hopefully) know there way around google.

Can someone remind me where I can get QuickEE from? I may bypass our IT policy and check it out... :)

This makes me a google jockey :s
http://quickee.webtubbs.net/

Questions answered would be good, but you'd also want to see it in relation to Questions participated in.

But then again, the Cleanup Volunteers would have a bad ratio there, since they are posting on a lot of questions that will never result in an answer for the Cleanup Volunteer. Maybe administrative comments could be subtracted from the total.

This is starting to get rather complicated. Maybe Barry can come up with a way to calculate it in Excel with a formula having less than 30 characters.

lol
only Barry could do that. :D
It may make the general load time for EE to get a bit longer though.

Rory,

if you have a tight IT policy and are behind a firewall, QuickEE will probably not work optimally. I've never really got it to work behind our company firewall. I can receive new questions all right, but posting always times out and then bombs. Also, QuickEE *really* ups your individual traffic, querying the EE site every few seconds (depending on your settings) and using a *lot* of bandwidth for that. I found email notifs almost as quick, and clicking through to the Q on EE I can get the attachments, too. QuickEE does not pass on the attachments.

Wayne, if you're reading this, it is not meant as QuickEE criticism, it's just my observations, which may be very particular to my environment. If you have any ideas how to work it out, I'd be more than game to try again.

cheers, teylyn

One question I participated in recently (which had a straightforward solution) was answered first off by someone who provided a link:

http://tinyurl.com/y6w9slm

Which ultimately goes thru Google.  The first step is to one of these sites that writes out the search for you and at the bottom of the initial page (before sending you on to Google) it had this at the bottom:

"Someone thinks you are pretty dumb because you were too stupid to check Google before asking a question."

I posted next with the steps to overcome said Excel problem and got awarded the points.  The guy who provided the link then posts back and says "That's what I said!" in indignant tone!!!

i had a good laugh at that.

QuickEE isn't really working properly at the moment due to a change EE made several months ago. Viewing and answering still work as before, but new question checking will not work. There is something in the works at the moment, and I should have QEE operating effectively soon.

>>I've never really got it to work behind our company firewall
Why aren't you working? :)

>>QuickEE *really* ups your individual traffic, querying the EE site every few seconds (depending on your settings)
That's why those settings are there. If you don't want those features, you can turn them off.

>>using a *lot* of bandwidth for that
No more bandwidth than a browser would if you refreshed the page every few seconds. For me, network utilization rarely goes over 1% (See below)

>>QuickEE does not pass on the attachments.
It does, or at least, it should, when you have the "Read Expert Skin" option set. If it's not working, let me know and I will fix it.

Untitled.png

There've been a few instances lately where sitting on my hands was not enough to avoid exploding into a google monkey's face. To overcome my impulse to post something nasty, I find it helps to walk around the block. Sometimes twice.

But hey! Time to celebrate. Excel Sage cert earned today, 92 days after my first Excel point. Free drinks for all! Caveat: to be collected in person in New Plymouth, NZ.

cheers, teylyn

Richard,

I clicked on the link you posted ... and anti-malware killed it

So I turned the virus app off - being a trustworthy sort - but FireFox couldn't open tit

Dave

Sometimes the typos greatly improve the post. Unintentionally.

Dave - it goes to iGoogle.  It's not a very polite site (as you can probably appreciate from my post).

Yeah I hate it too when my browsers can't show me tits ;)

For those wondering about the iGoogle it for you site

Untitled.png

Thanks bromy2004 :)

It does amaze me that anyone wanting to get a pat of the back would link to such a site!

>And its very frustrating to see a google monkey or points swiper post a first response that doesn't help but will push the question down the pecking order.

>Perhaps EE should have a flag on the question list as to the highest rated expert comment (in Excel's case in the Excel TA) level todate (ie NA, Master .... Savant etc).

> Quite agree that sounds a quality way to go, (zone based) although I know of at least one highly ranked google jockey elsewhere on the site.  Whilst I am thinking it won't be perfect I do believe it will add significant benefit in a way that ought to be practical to implement.

I have been promoting the notion of an Expert rating function that is fed primarily by Asker feedback. Coupled with other, less subjective, information such as highest certification, recent activity levels, question closure performance, this would give an indication of how effective that Expert is with regard to not only providing solutions, but also being cordial, being easy to understand, and being patient. Today we only only have point totals and personal experience. And we all know that pure points totals is not necessarily a good indicator of overall Expert quality. All it tells us is how effective that Expert is at winning points.

When I publicize this idea I receive both positive and negative reactions. The negative reactions seem to come from Experts who don't trust the "system" to give them a fair shake and/or who seem to have this perception that they should be appreciated simply because they are here and honoring us with their time.

Thoughts?

That sounds like a good idea to me - similar to the rep systems in place in certain other forums, wherein the amount of 'rep' an asker can give, both positive and negative, is in turn a function of their own rep. So if, for instance, a moderator with a lot of rep gives you kudos for what they think is a good answer, it carries proportionally more weight than rep (good or bad) from someone making their first post. The downside seems generally to be that many posters are unaware (or can't be bothered) to actually do this, but perhaps if it were part of the closing process, it would be used more.

>The negative reactions seem to come from Experts who don't trust the "system" to give them a fair shake and/or who seem to have this perception that they should be appreciated simply because they are here and honoring us with their time.
For those Experts, they can still answer questions and such, but their rank wont increase until they get better feedback/activity/closure %

As with any new ranking system/improvement, it takes time to get right
We may find that one of the Top 20 Experts drop in rank, because the Asker doesn't like going through to links, and they miss out from their share of the points.

I think that it would be a great idea.
Not quite how it is now (get as many points as possible)
but perhaps with a Min/Max 0/1000 and if the Experts "slacks off" they're rank slowly drops.
With 1000 getting points for every question they attempt, or something similar.
and 0 doing absolutely nothing, Lots of comments with no points.

If giving Kudos is a part of the Closing process, askers would always select "A", just to make it easier.

I'd disagree with that. If it were true, they would always give A grades, but they don't.

>As with any new ranking system/improvement, it takes time to get right

A concept that eludes some is that, however it is implemented, it would be consistent across the board. That means that even if it isn't perfect, as long as it gets the basic concept correct, everyone will be evaluated exactly the same.

>If giving Kudos is a part of the Closing process, askers would always select "A", just to make it easier.

Askers tend to grade on a literal basis (as we instruct them to do in the help topic). The instructions discuss the solution, not the Expert's performance. One idea presented a few years ago involved splitting the grade into two parts: one part for the solution and a second part for the Expert's performance. Another idea was to allow the Asker to award bonus points to the Expert outside of the points calculated from the grade.

Granted, some do give C's and B's,
From my (limited) experience they are the Askers that aren't too happy with the result, or are spiteful.

Or are following the instructions literally:

A: The Expert(s) either provided you with a thorough and informative answer or with a link to information that answered your question.

B: The Expert(s) provided an acceptable solution, or a link to an acceptable solution. May assign if you needed a bit more information to complete the task.

C: If you have given the Expert(s) ample time to respond to your clarification posts and you have responded to each of their posts providing requested information, or if the answers, after clarification, lack finality or do not completely address the issue presented, then a "C" grade is an option.
(Note: The use of a C in a vindictive manner is likely to be changed by a Moderator.)

Can you begin to see how, when taken literally, it's pretty easy to conclude that a B or even a C is OK?

That too Kevin, :s

I keep a list of anyone who gave me a 'C'... ;)

>From my (limited) experience they are the Askers that aren't too happy with the result, or are spiteful.
But these are usually few and far between. I'd love an expert ranking. I think it would go nicely with an asker ranking, except for one little issue, the askers are the ones paying EE, so you can't let any yahoos insult your customers.

On another note, I could use some help on the following question, using vba to control item selection in pivotfields. Needs VBA knowledge and XL07. Somehow, I can't get it right:

http:Q_25841244.html#a30733008.

Thomas

>  The negative reactions seem to come from Experts who don't trust the  "system" to give them a fair shake and/or who seem to have this  perception that they should be appreciated simply because they are here  and honoring us with their time

Correct.

EE has a business model where Askers get questions. Askers provide the revenue - hence they are the business
Keeping unpaid Experts and site admins happy with a community, t-shirts, certs and ego massaging is just part of making that business run properly

Too many Experts jyst don't get the fundamentals   ...... ....... ....... plus of course they fear that their brillance won't be understood by those pesky uninformed Askers.

I believe we should be vary wary of creating a system that favours the established experts. It is elitism which is unnecessary. As you already know, the best experts here don't need a special system to give them a helping hand. They're good enough to cope with what we've got.

We are all irritated by receiving a B or C grade for a perfectly good answer. Most of us are also irritated by relatively new members who have decided that the way to go is to provide links to other sites that provide the answer. I don't like that sort of contribution if it's a problem solving site as it competes directly with EE. If it's purely an informative site then I have no problem with that. There are one or two such people who have been encouraged to join the Excel zone recently.

Patrick

I probably have not been here long enough or answer questions often enough to weigh in on this topic but after reading through Dave's frustrating experience and the follow-on comments maybe EE could help the zones self manage some of these issues.    My apologies if I am out of line but just as a suggestion…
 
Have Zone Specific experts be responsible for identifying the point swipers and google jockeys and have EE provide those experts a confidential mechanism to report "repeated infractions" by these people to the Moderators.   The Moderators could review the situation and if they agree (determine competitive site vs informative site as Patrick stated), could warn the individuals.  If they continue their practices (three warnings?) EE could remove their ability to answer questions for a period of time (3 months the first time, 6 the next, etc.).  They could still ask questions but would not have the ability to answer questions.   Not being able to answer questions would force them to pay the annual fee to stay on the EE site.
 
Require the Zone Specific expert to carry at minimum a Sage (500k) or probably better a Genius (1M) status in the zone to have the ability to report the point swipers and google jockeys to the Moderators.  In the Excel group I believe that would be about 5-10 experts who are probably here most of the time and see most of the questions.  It gives the Sr. members of the zone a way to self manage but does not put you in direct contention with others who are answering questions. Some of the newbies may not fully understand they are causing problems and a warning from the MODS could be all is needed to put them on the right path.  The ones who don't care would not last.
 
This keeps the Askers out of the loop which is where EE's revenue comes from and the intention would be to improve the information and contributions that are documented for other EE Askers to search for.  I realize there are holes in this suggestion but if the intention is to improve the experience that Askers have on EE and the environment we all seem to enjoy working in, I'm sure there is a solution that EE and the Sr. Zone Experts could agree on.
 
Jerry

>I believe we should be vary wary of creating a system that favours the established experts. It is elitism which is unnecessary. As you already know, the best experts here don't need a special system to give them a helping hand. They're good enough to cope with what we've got.

This is not the intention of this idea. The objective is NOT to create another or a better avenue to elitism. It is intended to give the Asker a better handle on who is espousing what. Sort of like putting food nutrition content information on the outside of food containers so we can see what it is we are really getting before paying. A better informed Asker will be able to better sift through the chicken shit to find the chicken salad. Point accumulation alone is not always a good indicator.

The second objective, and this is where this gets interesting, is to encourage Experts to be better. Currently, the only path to elitism is by winning points. Win them any and every way we can: posting links, re-posting previous posts, bugging Askers to close questions, posting last - whatever it takes. There is little to no incentive to be polite, to stick with the question until it is resolved, or to try to use better than bad English. This is difficult for some of us to comprehend since these qualities are second nature. Yet we have all seen the gamers who come in and focus more on the points than the experience of helping someone. It's a natural byproduct of the patented grade/point system. Other sites that don't have points or other empirical rewards for performance tend to not have the same problems we have, but Askers don't get as much attention as quickly. It's a double edged sword.

The bottom line is we already have a system that enables elitism. The problem is that while it favors one aspect of service (quantity and speed) it doesn't favor other aspects that make for overall good service. Yes, we need to be careful.

>Have Zone Specific experts be responsible for identifying...

This is a slippery slope. By putting these decisions in the hands of the rule enforcers, we are introducing the risk of subjective favoritism. I would rather not do anything at all.

I also don't want to create a system that requires Askers to do any more than click a few radio buttons. For example:

"Was the Expert easy to understand?" Yes/No
"Did the Expert provide sufficient explanation of their solution?" Yes/No
"Was the Expert responsive to your subsequent questions and requests for clarification?" Yes/No

This way we gather a little information that pertains to the Expert's performance versus just the solution provided and we do so in a very homogenized manner that anonymously and consistently gives us something with which to "rate" Experts. We don't need more than a few responses and we don't need to rely on the rather unpredictable and subjective opinions of zone advisers.

The idea of helping Askers has always got to be a good idea. In particular it is the new Limited Members that need to be looked after well if they are going to be encouraged to convert to paying membership.

From that if there is a way of improving how experts look after new members then I'm all for it.

Patrick

>>>>>Most of us are also irritated by relatively new members who have decided that the way to go is to provide links to other sites that provide the answer. I don't like that sort of contribution if it's a problem solving site as it competes directly with EE. If it's purely an informative site then I have no problem with that. There are one or two such people who have been encouraged to join the Excel zone recently.



Who makes the decision between "problem solving" vs "informative"?  It's a junk distinction which just begs to create 'cronyism' problems alluded to by Kevin.  At the end of the day, EE exists (apart from to make a profit for its owners) to assist the paying Askers.  If those Askers feel they have been helped by someone providing a Google link to site xyz.com then why shouldn't they award points for it?  And I see plenty of regulars on EE who offer answers that are seriously below par, so don't let's pretend it's the be-all and end-all of internet help sites.

>Who makes the decision between "problem solving" vs "informative"?  It's a junk distinction which just begs to create 'cronyism' problems alluded to by Kevin.

I believe that misses the point. If a link directs people to other Excel problem solving sites then there is the chance that EE will lose a potential paying customer. It is really no more complicated than that. However to give a link to a site that is not interactive and that just gives information then there is little conflict with EE's commercial objectives.

Is there a rule about that or is it simply a personal perspective? From my point of view, my goal is to answer questions and I will use any and all information at my disposal to do so, so I don't really care whether I link to an informational site or a discussion board - if the answer is there, I don't see a reason not to use it. (But then, unlike EE, I have no commercial objective.)
Of course if it is a rule, someone should let me know! :)

Thanks - that's what I hoped the answer would be! :)

Experts who are interested in answering questions eventually learn that providing a complete solution (suggestion, explanation, discussion, code snippet and sample workbook) offers the best chance of earning points. Posting a vetted link offers a much lower yield because the Asker has to do more work to check it out and apply it to his problem.

I have no concerns about posting a high quality link to another site, such as for the fuzzy logic VLOOKUP on the Mr. Excel site. As long as you point directly to a possible solution, Experts Exchange has no concerns over competitive interests--the Asker got the answer here even though the link is elsewhere. The concern is rather when an expert posts a link to Mr Excel home page and suggests it as being the best place to post Excel questions.

Brad

The Asker in http:/Q_25856703.html is looking for some practical advice in using Excel 2003's Workspace feature. Not how do you do it, but rather what to watch out for. Since that feature allows more than one user to make changes to the workbook at the same time, I wonder about the possibility of file corruption like occurs with the Shared Workbook feature.

Does anyone have practical experience with Shared Workspace (found on the Tools menu) in Excel 2003? I've never used it myself. Rory has posted, but in more of a "how do you do it" modality.

Brad

The conversation has moved somewhat from my original issue, which was inadequate answers from google monkeys, ie EE from an Asker not Expert perspective

If I have an issue say with hardware or software glitches I will put in a reasonable effort to resolve it, which typically means searching the different online forums. If I reach the point of asking an EE question I'm looking for dedicated expertise as the answer is not readily available in the EE or other forum databases.

 A google monkey will just repeat this process at a high level, and post a link that may look like a ballpark solution. Consequently my question becomes less visible for the actual Experts I want to look at my issue

Whereas there is a finer distinction between an informative solution  and a basic working one. If they both work fine, this is for the Asker to arbitrate.

Dave

Experts in SQL/Excel linkage please help in this question:

https://www.experts-exchange.com/questions/25918358/Excel-Macro-to-Process-Range-Selection-based-on-table-header-and-row.html?anchorAnswerId=31094414#a31094414

Patrick

Can someone with Vista or Wndows 7 pls try running the code from my article http://www.experts-exchange.com/A_2839.html that grabs MP3 file attributes
My version of Vista found the folder ok, but using Dir on it gave an error. From my research this is a faulty Vista installation, but I would like some comfort that the code works as intended on other OS besides XP
Cheers
Dave

Dave,
I got the same issue on Wind Server 2008
Works ok in XP
For your constants, why not use a function that is easier to include future OS's
I know it might take longer to run using more arrays than Constants but...

In the code, you would need to include another line into all the arrays
i.e
osArray = Array("XP", "Vista", "7","8")
Select Case LCase(FieldType)
  Case "artist": lngArray = Array(16, 13, 13, 21)

Function GetOSConst(osType As String, FieldType As String) As Long
Dim lngArray As Variant
Dim osArray As Variant

osArray = Array("XP", "Vista", "7")
Select Case LCase(FieldType)
  Case "artist": lngArray = Array(16, 13, 13)
  Case "albumtitle": lngArray = Array(17, 14, 14)
  Case "songtitle": lngArray = Array(10, 21, 14)
  Case "tracknumber": lngArray = Array(19, 26, 26)
  Case "recordingyear": lngArray = Array(18, 15, 15)
  Case "genre": lngArray = Array(20, 16, 16)
  Case "duration": lngArray = Array(21, 17, 17)
  Case "bitrate": lngArray = Array(22, 28, 28)
End Select
GetOSConst = lngArray(Application.WorksheetFunction.Match(osType, osArray) - 1)
End Function

Sub test()
Dim x As New Collection
Dim os As String
Dim osType As String

os = "Windows XP Professional"
If Not InStr(os, " Vista ") = 0 Then osType = "Vista"
If Not InStr(os, " XP ") = 0 Then osType = "XP"
If Not InStr(os, " 7 ") = 0 Then osType = "7"
'application.WorksheetFunction.Index(array("2000","XP","Vista","7"),

With x
  .Add GetOSConst(osType, "Artist"), "Artist"
  .Add GetOSConst(osType, "AlbumTitle"), "AlbumTitle"
  .Add GetOSConst(osType, "SongTitle"), "SongTitle"
  .Add GetOSConst(osType, "TrackNumber"), "TrackNumber"
  .Add GetOSConst(osType, "RecordingYear"), "RecordingYear"
  .Add GetOSConst(osType, "Genre"), "Genre"
  .Add GetOSConst(osType, "Duration"), "Duration"
  .Add GetOSConst(osType, "BitRate"), "BitRate"
End With


'Artist=x("Artist")
End Sub

Select allOpen in new window

Hi  bromy2004,

Doesn't Windows 7 have the same attribute Fields as Vista -  your sample does show a different Song Title?

The problem with a new OS is that the code will need updating unless the new OS has the same attributes as Vista/W7. The good news is that the code will automatically find the My Music folder, and will work as intended if MSFT keep the attributes consistent going forward.

The Array appraoch would work fine, but these days I prefer the Enum style. I like the intellisense in particular

Cheers

Dave

woops,
That should be the same (10,21,21)

I'm not sure why Dir doesn't work, perhaps use FSO and File to loop.
For each objFile in objSubFolder

If anyone has SQL/Excel experience can they please look at my Q

Converting Integer to Strings

SQL Stores the Values as Integer, MS Dynamics Nav shows a string
http:/Q_25967000.html

The Dir with Vista should work - thats what I would like someone else to test

On my Vista machine, the code "finds" the My Music folder but later returns an error on the Dir search.  This appears to be an installation rather than code issue, see https://www.experts-exchange.com/questions/23284757/Dir-function-generates-error-52-bad-file-name-or-number.html

Cheers

DAve

Thats because you code is using strMyDoc & "\My Music"
Is Vista/7/Server '08 it is "Music" (No 'My')

ugggh

This line
Set objFolder = objFSO.GetFolder(strMyDoc  & "\My Music\")
Does "find" the Music Folder even with the naming difference

So I figured - maybe naively - that Dir would also be redirected. I should have caught this

I will need to revisit this

Thanks

Dave

If that does find the folder you could use
Dir(objFolder.path)

I did in an earlier option - but bizarrely that didnt work either.

Actually, on Server '08 (and & presumably) the Music folder isn't even in My Documents
C:\Users\nathanb\Documents
C:\Users\nathanb\Music

Yes - thats the same with Vista.

how about
If b_os_xp Then
  str = "C:\Document and Settings\" & Environ("Username") & "\My Documents\My Music\"
Else
  str = "C:\Users\" & Environ("Username") & "\Music\"
End If

I went back to this version as the wscript returns the (My|) Documents path automatically

If I ever solve how I got the original version half working I will post it here having spammed this thread for half the day. Thanks bromy for your feedback

Dave

If InStr(strOS, "XP") Then
        b_OS_XP = True
         strMyDoc = strMyDoc & "\My Music"
    Else
        b_OS_XP = False
        strMyDoc = Replace(strMyDoc, "Documents", "Music")
    End I

Select allOpen in new window

We have spammed this thread haven't we :s

one more change for your code
strMyDoc = Replace(strMyDoc, "My Documents", "Music")

First let me apologies for the interruption, but I need help from you all.  I have been assigned to a significant project at work and the little time I have to get on here is now all but gone.  I don't want to leave these users unassissted so when you get some time, could you possibly help these users?

https://www.experts-exchange.com/questions/25915091/Please-help-Has-to-be-simple-issue-with-Macro.html
https://www.experts-exchange.com/questions/25889349/Problem-with-html-tags-still-displayed-in-Excel-web-query.html
https://www.experts-exchange.com/questions/25887493/Is-it-possible-to-update-SQL-table-from-Excel.html

Thanks for the help!

The code works as is because the wscript finds the special folder "My Documents" for XP, or "Documents" for Vista etc automatically. Its a nice approach to avoid hard coding OS paths.

So the line above simply replaces the Documents Folder for Vista with a Music folder as they sit at the same folder hierarchy. Whereas for XP I concatenate a My Music folder onto the end of the My Documents folder.

Cheers

Dave

Yep, my mistake.
Works fine for my 9000 songs, Win 7 at home

Dave,

How does it go if I don't have any music in My Music? I store my music on an external hard drive. I wonder if it's possible to get and check all paths WMP monitors?

Wayne

Wayne,

Good question.

Currently its unsuited to a general scan

I did tinker with the idea of letting the user browse to a selected folder using Shell, but as the Article focus was also on the coding techniques (using SpecialFolders, recursion etc) I constrained the coding scope to the standard Music folder structure

Whereas it could be used to scan all drives, or even remote computers.

Cheers

Dave

quoted from way above:

"The problem is that while it favors one aspect of service (quantity and speed) it doesn't favor other aspects that make for overall good service."

Speaking as a very recent newcomer (not sure I have 10,000 points yet): I'd say the above is the issue that has most of my attention: right down to whether to remain or not. There are others, but as yet I have not fathomed where to discuss them.

Strikes me there are two basic response types: instant "explore a b c" and "in depth all you need " - and if an expert is preparing the latter only to be wiped out with an "instant response", that has to be a less than good use of expert time.

I discovered "real quick" just how damn quick one has to be in here; which is a return to the quote above. Perhaps some indicator of the depth of reply the Asker might wish for would help and "quickies/lnks get only half the points" or some such approach.

Just wondering where my feet are. :-)

Anthony
12 Noon in the Alps.

>I'd say the above is the issue that has most of my attention: right down to whether to remain or not. There are others, but as yet I have not fathomed where to discuss them.

Questions are not the best place to have general discussions nor to even have related discussions unless they help the Asker. In essence that's why the Excel zone has this series of threads. This is a place that allows us to have discussions without impingeing on an Asker's 'space'. So, Anthony I think you have now found the right place to follow-up on questions or to have whatever discussions you want - right here.

Patrick

At the end of the day, I think that to some extent you have to accept the fact that it is up to the OP which answer they accept. Non-trivial questions generally don't get closed that quickly, so there is plenty of time to write an essay, should you choose to do so. Where the posted answers are bare formulas or code, it seems to me (and it might just be my perception) that more and more of the OPs are starting to ask for an explanation, though there are still many who simply accept that it works and move on.
By and large, if you try not to get too wrapped up in the points and remember that we are all (or most, anyway) just trying to help, it's still fun! The Google monkeys and the like don't tend to stick around that long generally - I suspect largely because many of the OPs aren't interested in links, regardless of their quality or relevance (possibly because they've wasted a lot of time previously?).
At the end of the day, there's only so many T-shirts you can wear... ;)

Excel lent - thanks Patrick. What you say is certainly apparent and indeed common sense for fee paying Askers' (customers, presumably) threads. What isn't so clear is where else one can go to discuss matters, so I am somewhat relieved to have been guided here.

Is it ok to ask questions that everyone will find very basic? Is this that place? Are there any other new "Answerers", or am I all alone? Does it matter how many posts I make (in here)? Does it matter how long are my posts?  Is there somewhere i can read up on how not to be a dork in EE terms? Such as I would not wish to be a google droid or gamer or any of the other decried descriptions I see above; I just want to be liked and appropriate to this space (but don't know what that is) and enjoy a happy experience and I can modify my behaviour to suit, or disengage if I don't like what that involves. The thing is if nobody tells me, or anyone else, then peers will have to lower their expectations.

Finding my way, positively humbled by some of what I have seen and resisting temptation in others.

I'd love to know where people are on the planet as I make replies - just because it's fun to know.

Anthony
nearly 54, going on 14...

Anthony,

I haven't been active in EE for too long, either. Just take it easy. Things will take a while to get used to.

I've come across your contributions a few times recently, and I think you are doing a good job. The main thing is that you like what you do and that you feel you are adding value for the asker. Everybody stuffs up every once in a while, and none of us is always functioning 100%. There are many questions where I think I've nailed the perfect answer, only to see the asker accept someone else's comment. C'est la vie! When some experts are online, it's hard to get a word in edgewise (and I still don't understand how they get to the questions so fast. Half the time I get the email notif two minutes after the asker has already accepted barry's or rorya's solution).

But as rorya said above: It's about having a good time here! Don't put yourself under too much pressure. If you are consistently contributing quality comments, then the askers will reward you. Maybe not every time and maybe not to your complete satisfaction, but in the long run you'll see your points go up and up.

Stick around. EE is very different from other Excel online communities. It may take some time to get used to, but I find it worthwhile.

cheers, teylyn

Anthony,

Let's take your questions one by one:

>Is it ok to ask questions that everyone will find very basic? Is this that place?

Yes, this is the place and you can ask as many 'daft' questions as you like. I'm the one who usually does that so you are most welcome to take over where I left off!

>Are there any other new "Answerers", or am I all alone?

You're not alone. There are many new answerers but not all of them last too long. The new answerers differ in their experience and in how they operate here. Some are easier to relate to, others not - a bit like me no doubt.

>Does it matter how many posts I make (in here)? Does it matter how long are my posts?

Make as many posts here as you want. Make them as long as you want.

>Is there somewhere i can read up on how not to be a dork in EE terms? Such as I would not wish to be a google droid or gamer or any of the other decried descriptions I see above; I just want to be liked and appropriate to this space (but don't know what that is) and enjoy a happy experience and I can modify my behaviour to suit, or disengage if I don't like what that involves. The thing is if nobody tells me, or anyone else, then peers will have to lower their expectations.

No need to be concerned. Just relax a little and ask all the questions you want in this thread. Unlike the 'Lounge' you won't receive rude answers here. We might get a laugh but that's all.

The Excel zone is run on a very competitive basis as there are lots of truly excellent contributors (me excluded). I just like learning from those who are around and who have the patience to teach me new things. You will soon find out who is extra helpful and who is not.

Patrick

ps signed

Patrick
65, going on 18

I'm goin on 17, but I won't tell you what the base age is in my case :-)))

Welcome Anthony, there is a lot of good advice/comments from the Experts Exchange Excel Experts Ellipse committee above ......

Regards

Dave

Hi teylyn,

I do find it a great source of relief and fun, even a challenge which is part of the fun. I just don't want to upset anyone with my sometimes different views and ways. A small cross I have to bear is that my typed text persona often doesn't represent me very well, even less than for the general population apparently, so I am actively working on the issue by looking for guidelines and related social mores to keep my more competitive side at bay (which isn't a problem any more whereas 20 years ago it was a different kettle of fish.) Treading where angels fear to is something I want to not do. So to speak.

Yes that man barry isn't half clever. Whatever he's paid it's not enough.

Always enjoy reading your posts :-)

Anthony

teylyn,

You just don't sleep. Ever. Do you?

Dave

We're always a bit ahead in NZ, you know?

But it's going on 1 am, so I promise to call it a night, soon. Just a few more questions to clean up. Did you see my new decoration?

cheers, teylyn

and now I see a bunch more supportive replies and feel a certain warmth within - have to rush out, will read after and want to issue quick thank you - I will take the warm feeling out as I rush. Anthony

Your are not the only newcomer.  While I have been on EE since 2008, I just started answering questions earlier this year.

It is extremely challenging, but it is also a good feeling and VERY humbling.  Before EE I thought I knew something about Excel, but now I realize how little I know!  So I am on here to learn from the best and I figured I could help a few people along the way.  

Oddly enough, what makes it more challenging for me is I have a degree in Network Hardware but I have taken an interest in programming...go figure :)

BTW, my given name is Brad, not John :)

Patrick, thanks for all that, you can see I am a bit nervous. teylyn you are of course presumably 21 going on 17 and Dave: yes exactly what I am finding, very humbling; which is great (if humbling!) and I am wondering how ever to begin to learn the abstract thought process that goes in to barry's stuff. If I can't see it in front of me I "can't" do it.  Except I must because it is there. As it happens i just started to learn Python (as in I haven't yet managed to get everything installed correctly so mega beginner) as a first time programmer. I get frustrated because many thousands of hours writing Lotus macro applications (compiled) is programming, but it isn't the same as "real" programming. Currently have a book written for kids to learn, that's helping. (!) After that I thought maybe interface with Excel and VBA. I also have an amateur interest in networking; perhaps it's numeric bent attracts my accounting mind.

Anthony
1452 Zulu from about 1350 metres (about 4500 feet)