Getting Help with Microsoft Excel – My Recent Experience
By my own admission I am NOT a Microsoft Excel expert. I know enough about the application to make me dangerous.
I use Excel pretty extensively in my day to day work activities, and in my continuing effort to streamline my day I decided that I needed to add some functionality to my project management spreadsheet. This spreadsheet has really become my single point of information for all of the projects that I work on and I wanted to enhance it even further by integrating it with Outlook.
The integration that I had in mind was to read through the TASKs that I have detailed in my Excel worksheet and IF there was a due date to create a task for me in Outlook using this date as the due date and various other information from the spreadsheet as my subject. I certainly didn’t have the expertise needed to complete this task on my own so I headed out on the Internet to find a solution.
Google is a great thing! Within a matter of minutes I had any number of resources available to me from my search terms about Creating Outlook Tasks from Excel. Many of them included quoted scripts that gave me a head-start on what I wanted to do but ultimately none of them quite met my specific criteria. I decided what I should probably do is simply pose the question to the experts in some of the forums that Google was pointing me to.
Now keep in mind that your mileage may vary at these sites. I’m simply telling you of my experience. I’m sure with the thousands and thousands of posts that each of these sites have that either would be a good place to get solutions.
I went to http://www.excelforum.com/to start with. This particular site had helped me a number of months prior with a question I had about populating a cell with a date when I clicked on a check-box. Within a matter of hours I was up and running with the script that I needed to perform this task. My Outlook task on the other hand did not get the same type of response. I was provided with virtually the same code that I had found doing Google searches and when it came to customizing that code my post sat unanswered.
A few weeks later I decided that I would try to reinvigorate the project and again get on my trek to higher productivity. I Googled again and this time I found a post at http://www.ozgrid.com/forums- It actually had a few Google hits that led me to believe there were people there that knew exactly what I was wanting to do. I can tell you that on this site you need to heed the warnings of reading the RULES. And that the rules are Strictly enforced. You have plenty of opportunity because they are part of the main page, you hear about it in the registration, and even when you are ready to post.
I joined the ozgrid site which is like most vBulletinsites. You enter your login information, they send you an email, you confirm it and you are able to then login. Easy enough – again with much encouraging about reading the rules which are strictly enforced. I composed that first post… describing what I wanted to do, choosing my keywords specifically to meet the rules, including a screenshot of an example spreadsheet, clicked the review button was warned again about the rules, everything looked great so I decided to POST. Through the magic of the Internet my help request was there for thousands to see and I hoped that soon someone here would be able to provide me with the magic script that would do what I needed or at least help me figure out what it was that I needed to do. I’d already struck out on the Excelforum so I was hoping for something positive.
I went back to ozgrid.com the next day, logged in and was met with a message that I was banned because I had posted my request in the wrong forum and that my ban would be in effect until the 14th of December. I thought there must be a mistake… I checked the forums that were available and the only thing I could think of is that instead of the Generic Excel folder I should have posted my Outlook Task questions in the Excel Email folder… which I had considered but didn’t because technically it was not an Email question. Long story short… my assumption was the case, and when I made inquiry about it using the site’s Contact form let’s just say I now have a lifetime ban because I had a differing opinion than the forum owner on what was email particularly since he mentioned all of the other Microsoft Office applications by name except Outlook.
Interesting thing about the Internet… It isn’t a democracy. Site owners can run their sites however they want to run them.
So it was back to the Excelforum for me. Perhaps after this many weeks someone might be able to help. I’d pieced together some snippets of what I wanted to do and sure enough after a few email exchanges, and some additional Googling, I now have a complete resolution to my problem. I can click a button in my Excel worksheet, it checks to see if the tasks had already been added to Outlook and if not determines the column the button is in and begins processing the due dates in that column creating Outlook tasks for each one of them. (I am going to share a few of these Excel scripts in future postings for those that might be interested.)
I guess the moral of the story here is to not give up. I knew there must be a way to do what I wanted and even though it was painful, frustrating and not the most user friendly at times, there are a ton of Internet resources that are willing to help, you just have to find the ones that will work best for you. I learned so much in working through the creation of this script and a big thanks should go out to all of those on the Excelforum that offered that help and assistance.
As I stated above, you may have entirely different experiences at the sites that I mention.
You have missed out where you agreed NOT to cross post without supplying a link to the cross posts and that, combined with posting in the wrong forum, was the reason for you initial time limited ban.
You have also missed out the fact that in your email to me you stated you were going to blog in a negative way about the Ozgrid forum. That was the reason for the ban being made permenant.
RE: “Site owners can run their sites however they want to run them.”
You post is living proof of that fact!
Couple of comments and the end of the pissing match at least here…
1. Cross Post… It had been weeks, scope of project changed, I now had specific spreadsheet example, etc… While the intent was similar the project was different.
2. Wrong forum I’ve already addressed – I truly thought an Outlook Task was not an email and he lists all of the other Microsoft products by name so when Outlook was not mentioned I thought he was being specific about “email”.
3. My email said that I would keep him updated of my rantings about the NON USER FRIENDLY policy. (Which I still believe to be true.)
As a side note:
I want all of my readers to know that I think these types of sites/forums on the Internet are extremely helpful and productive to the masses and that I believe they absolutely need to have some form of organization and order or chaos will ensue and the viability of the site becomes questionable. The balance between order and chaos is handled differently from site to site to site…and it truly is the reader’s choice as to which environment they prefer. As I said in my post above your mileage may vary… I was just giving you a review of my experience.
For those that want to know what the script ultimately ended up looking like I have posted it here. Feel free to do what you want with it… It works in my environment… I have commented it where I thought it might be helpful. I don’t support it and can’t tweak it for your environment… If you need that try ozgrid or excelforum they may be able to help.
Sub CreateTask()
'Setup all of the variables
Dim B As Object
Dim C As Integer
Dim R As Integer
Dim Rng As Range
Dim olApp As Outlook.Application
Dim olTsk As TaskItem
Dim cl As Range
Dim chkRng As Range
Dim counter As Integer
C = 0
R = 0
'Assign the row and column indexes to the variables R and C
Set B = ActiveSheet.Buttons(Application.Caller)
With B.TopLeftCell
C = .Column
R = .Row
End With
'Set Rng value to be the date cell below the button
Set Rng = ActiveSheet.Cells(R + 3, C)
'Setup Outlook
Set olApp = New Outlook.Application
'Set the range (rows and column) to search in the loop look for Due dates.
Set chkRng = Range(Cells(12, C), Cells(104, C))
'Check to see if we've already added the Tasks to Outlook
If Rng.Value = "" Then
counter = 0
'Look at each Value in the Range and add Task if a date.
For Each cl In chkRng
Set olTsk = olApp.CreateItem(olTaskItem)
If IsDate(cl.Value) Then
With olTsk
.Subject = Cells(cl.Row, 2) + " - " + Cells(3, C) + " - " + Cells(3, C + 1)
.Status = olTaskInProgress
.Importance = olImportanceHigh
.DueDate = Format(cl.Value, "mm/dd/yy")
olTsk.Save
counter = counter + 1
End With
End If
Next cl
Else
MsgBox "Tasks have already been added to Outlook"
Exit Sub
End If
MsgBox ("Tasks added to Outlook:" & counter)
'Cleanup
Set olTsk = Nothing
Set olApp = Nothing
'Stamp the date below the button - Need to build the check for this field being blank in the above loop.
Rng.Value = Date
End Sub
i got banned from Ozgrid for no reason as well, i posted a well thought out title – and thought about what to post – and they/he banned me. Maybe they should have a section for ‘these were banned’ – for goodness sake though – this guy is so petty! his loss though. I always put ‘-ozgrid’ when i do a search…
anyone want to start a ‘not ozgrid’ site? no, i cant be bothered either….
and if you are reading Mr Ozgird – you can take your site and stuff it – the email address you hold for me is out of date – and i wont be accessing it from this pc as i leave this place in a week… whats your next plan?????