The MCTS Certification is a symbol of your up to date knowledge in the industry. Our MCTS certification practice exams are designed by industry experts – who are at the forefront of IT industry. Accomplishing the exams for the MCTS certification is hence guaranteed when you use Certkingdom exam preparation products and specially practice exams.
The MCTS certification lets you improve your job prospects. It gives you instant recognition from prospective employers and clients. It validates you knowledge and skill sets.
MCTS Certification Training:MCTS certification training products, by us, gives you a great deal of freedom to plan your studies around your existing schedule. The practice tests, study material and sample questionnaires are all available instantly, right after the moment you purchase them. They products portable and hence you can carry them with you if you are travelling or working.
The MCTS practice exams simulate the exam environment as it is expected to be in real exams. If you already have a thorough understanding of the subject, you can use this time to brush up your knowledge. Otherwise you can work your way from the basics upwards with our complete study material, the royal study pack. Check out the relevant exams below and explore futhur the possibilities that we offer for successful MCTS certification preparation.
Certkingdom 70-620 exam products prepares you for 70-620 certification final exam. It prepares you for the kind of questions that you could expect with your final try. The 70-620 practice exam frames the questions and sets the paper the way you can expect in the real certification exam. The 70-620 practice tests are a great tool for you to practice on before your final attempt at the 70-620 certification. The kind of questions, the format of questions, the time limit etc all relate to the final 70-620 certification exams. So you can prepare yourself for the final exam by understanding what level of knowledge is required. And you can test your exam taking abilities – whether you can finish in the set period of time by practicing with the 70-620 Exam first.
Convenience with Certkingdom 70-620 Exam Tools:
The 70-620 Exam questions are available in pdf format. This makes it very convenient for you to follow the course study and exam whenever and wherever you want. The 70-620 Practice Exam follows the exact paper pattern and question type of the actual 70-620 certificate exam. Which is why it is such a valuable tool – it lets you recreate the exact exam scenario, so you are armed with the correct information for the 70-620 certification exam.
Microsoft 70-620 Exam
Certkingdom 70-620 exam products prepares you for 70-620 certification final exam. It prepares you for the kind of questions that you could expect with your final try. The 70-620 practice exam frames the questions and sets the paper the way you can expect in the real certification exam. The 70-620 practice tests are a great tool for you to practice on before your final attempt at the 70-620 certification. The kind of questions, the format of questions, the time limit etc all relate to the final 70-620 certification exams. So you can prepare yourself for the final exam by understanding what level of knowledge is required. And you can test your exam taking abilities whether you can finish in the set period of time by practicing with the 70-620 Exam first.
The 70-620 Exam questions are available in pdf format. This makes it very convenient for you to follow the course study and exam whenever and wherever you want. The 70-620 Practice test Exam follows the exact paper pattern and question type of the actual 70-620 certificate exam. Which is why it is such a valuable tool it lets you recreate the exact exam scenario, so you are armed with the correct information for the 70-620 certification exam.
Friday, August 12, 2011
Thursday, August 11, 2011
Facebook, Microsoft Partnership Deepens With Skype Announcement
Facebook's new Skype integration is another marker in its long-deepening relationship with Microsoft, as both companies seek to battle Google.
When Microsoft acquired Skype for $8.5 billion in May, it kicked off a good deal of analyst chatter about how Redmond would choose to integrate the communications company’s assets into its product lineup.
How exactly Skype will appear in products like Office 365 remains to be seen, but the acquisition could end up paying dividends for Microsoft in its competition against Google, by giving Facebook—in which Redmond owns a minority stake—another tool with which to battle for social-networking hearts and ad dollars.
Starting July 6, Facebook users can video-chat with one another using Skype. (The social network is also introducing a retooled people sidebar, supposedly to make initiating chats easier, as well as a way to initiate group instant messaging.)
“We are now making it possible to video chat with your friends right from within Facebook,” read a note on Skype’s corporate blog. “The partnership with Facebook makes fantastic business sense for Skype and gives us an unprecedented opportunity to offer Skype’s voice and video calling products to more than 750 million active users on Facebook.”
During a July 6 presentation at Facebook headquarters, Facebook CEO Mark Zuckerberg claimed his company had been working with Skype on the project for the past six months, before Tony Bates assumed the CEO slot or Microsoft announced the acquisition.
“This is going to be something that’s rolled out to everyone that you can integrate immediately,” he told the audience. “It’s so minimal and it’s so easy to use.”
More to the point, it offers Facebook a counter to Google’s own video-chat service, and perhaps even Apple’s FaceTime conferencing feature. Although Facebook and Apple aren’t direct competitors, the two companies apparently had a disagreement over allowing Apple’s Ping, a social-networking service centered on music, to import Facebook contacts. Facebook is also rumored to be prepping an HTML5 mobile-app platform that would conceivably challenge Apple’s App Store.
Last week, Google offered a limited number of people the ability to start a profile on Google+, its nascent social-networking service. The search-engine giant likely sees Facebook as its major competitor for online ad revenue, and CEO Larry Page has reportedly tied in employee bonuses to success in social networking. Whether or not Google+ becomes an existential threat to Facebook, it certainly raises the specter of increased competition—and boosts the pressure on Facebook to create new features that will hold its 750-million-member base.
Microsoft and Facebook have been deepening their relationship in recent months. When Microsoft decided to evolve its Bing search engine by “infusing the emotional into it,” in the words of Bing director Stefan Weitz, the company chose to do so by integrating Facebook features such as the “Like” button.
When users query Bing for specific people, for example, the search engine can offer Facebook information on the results page. If they’re traveling to a new city, such as Paris, Bing will tell them which Facebook friends live there. Bing will also notify users of airfare deals for places they’ve liked on Facebook, and let users post Bing Shopping pages on their Facebook wall (“Should I buy this?”).
In a March interview with eWEEK, Weitz suggested that the Web’s social layer has come to mimic the same sort of behaviors that people exhibit in the real world. Even before the addition of new social features, Facebook and Microsoft had already collaborated on Facebook Profile Search, which leveraged a user’s Facebook connections to deliver more relevant results for people searches; they could also post messages to their Facebook walls via Bing’s pages.
During his July 6 talk, Zuckerberg also painted a portrait of a Web increasingly focused on the social—specifically, the ability to share loads of content. “We’ve seen this trend since [Facebook] began,” he said. In terms of how much data people share with those in their social circles, “it’ll be about twice as much a year from now, and twice as much a year after that.” That will affect everything from app development to the tools that people use to interact.
Skype is evidently a vital part—at least for the moment—of that Facebook evolution, and Microsoft owns Skype. More than ever, Facebook and Microsoft find themselves bound together in a growing battle for the Web.
Tuesday, August 9, 2011
Top 8 ways feds (and corporate users) can take advantage of green IT
GAO report outlines key federal green IT initiatives
How does the federal government go about implementing green IT? According to a report out today from the Government Accountability Office, the feds have adopted a number of practices that are useful not just for government IT but all manner of private and public company IT groups as well. These include everything from dedicated funding for green products, to improved employee training and reducing use of paper.
4. Procure IT equipment that meets the most stringent Electronic Product Environmental Assessment Tool (EPEAT) standard available, if economically practical. EPEAT is a tool to help purchasers in the public and private sectors evaluate, compare and select electronic products based on their environmental attributes. EPEAT-registered products must meet 23 required environmental performance criteria. The products are then rated gold, silver or bronze based on whether the products met 75% or greater, 50% to 74%, or less than 50%, respectively, of 28 optional criteria. The three EPEAT level ratings differ to a small, but measurable, extent in their environmental benefits. As the GAO reported in 2009, if federal agencies replaced 500,000 non-EPEAT rated laptop computers and computer monitors with either EPEAT bronze-rated, silver-rated or gold-rated units, the federal government would achieve energy savings equivalent to 182,796 U.S. households, 183,151 households or 183,570 households, respectively. In the non-federal government sector, in March 2009 the city of San Francisco upgraded its environmental requirement for IT purchases to the EPEAT gold level as its procurement baseline whenever possible.
5. Provide appropriate personnel with sufficient green IT training. As part of a 2010 private-sector survey of federal chief information officers, industry officials also offered some observations, including that agencies should work with the Office of Personnel Management to improve the IT workforce. The survey noted that, in doing so, government organizations should use existing best practices, such as those found at the Department of Defense, to train employees and develop new leaders.
6. Evaluate and prioritize green IT options. With various green IT options available, lifecycle return on investment can be a useful tool for determining which options provide the greatest return on investment in an environment of reduced agency budgets. According to a 2009 survey of IT professionals by a national IT services and solutions provider, IT departments may be foregoing large, long-term savings by ranking factors such as cost over energy efficiency in their purchasing decisions. One recommendation from the survey is that organizations need to prioritize their actions based on costs and benefits.
7. Align green IT with the organization's budget. According to a 2007 industry report on creating a green IT action plan, green IT must fit within an organization's anticipated budget. In recognition of the importance of adequate funding to program success, the 2009 executive order states that, starting in fiscal year 2011, strategic sustainability efforts, which include electronics stewardship, should be integrated into the agency's strategic planning and budgeting process, including the agency's strategic plan.
8. Obtain senior management commitment. Senior management commitment can remove potential obstacles when implementing green IT initiatives and establishing goals. For example, according to a 2009 study of the key drivers of green IT, research showed that identifying an executive sponsor who will champion the green IT initiative will help to remove the road blocks to implementation.
How does the federal government go about implementing green IT? According to a report out today from the Government Accountability Office, the feds have adopted a number of practices that are useful not just for government IT but all manner of private and public company IT groups as well. These include everything from dedicated funding for green products, to improved employee training and reducing use of paper.
4. Procure IT equipment that meets the most stringent Electronic Product Environmental Assessment Tool (EPEAT) standard available, if economically practical. EPEAT is a tool to help purchasers in the public and private sectors evaluate, compare and select electronic products based on their environmental attributes. EPEAT-registered products must meet 23 required environmental performance criteria. The products are then rated gold, silver or bronze based on whether the products met 75% or greater, 50% to 74%, or less than 50%, respectively, of 28 optional criteria. The three EPEAT level ratings differ to a small, but measurable, extent in their environmental benefits. As the GAO reported in 2009, if federal agencies replaced 500,000 non-EPEAT rated laptop computers and computer monitors with either EPEAT bronze-rated, silver-rated or gold-rated units, the federal government would achieve energy savings equivalent to 182,796 U.S. households, 183,151 households or 183,570 households, respectively. In the non-federal government sector, in March 2009 the city of San Francisco upgraded its environmental requirement for IT purchases to the EPEAT gold level as its procurement baseline whenever possible.
5. Provide appropriate personnel with sufficient green IT training. As part of a 2010 private-sector survey of federal chief information officers, industry officials also offered some observations, including that agencies should work with the Office of Personnel Management to improve the IT workforce. The survey noted that, in doing so, government organizations should use existing best practices, such as those found at the Department of Defense, to train employees and develop new leaders.
6. Evaluate and prioritize green IT options. With various green IT options available, lifecycle return on investment can be a useful tool for determining which options provide the greatest return on investment in an environment of reduced agency budgets. According to a 2009 survey of IT professionals by a national IT services and solutions provider, IT departments may be foregoing large, long-term savings by ranking factors such as cost over energy efficiency in their purchasing decisions. One recommendation from the survey is that organizations need to prioritize their actions based on costs and benefits.
7. Align green IT with the organization's budget. According to a 2007 industry report on creating a green IT action plan, green IT must fit within an organization's anticipated budget. In recognition of the importance of adequate funding to program success, the 2009 executive order states that, starting in fiscal year 2011, strategic sustainability efforts, which include electronics stewardship, should be integrated into the agency's strategic planning and budgeting process, including the agency's strategic plan.
8. Obtain senior management commitment. Senior management commitment can remove potential obstacles when implementing green IT initiatives and establishing goals. For example, according to a 2009 study of the key drivers of green IT, research showed that identifying an executive sponsor who will champion the green IT initiative will help to remove the road blocks to implementation.
Monday, August 8, 2011
Microsoft to release 13 patches covering 22 vulnerabilities next week
In another big Patch Tuesday, Microsoft will release 13 patches next week covering 22 vulnerabilities, including two critical patches to prevent remote code execution attacks in Windows and Internet Explorer. Three other less critical patches will close holes that would allow denial-of-service attacks on Windows, and a majority of the 13 patches will require a restart.
Microsoft Office, .NET and Visual Studio will also be patched.
The 13 patches is a large total but short of the 16 released in June, and short of the record 17. However, nine of the 16 June patches were rated as critical, whereas only two this month merit the most severe rating. The 22 vulnerabilities to be closed this month is just a fraction of the record 64 set in April 2011.
Microsoft patching: Still painful after all these years
Only four patches were issued last month, because Microsoft alternates between big and small releases to relieve the pressure on IT administrators.
Bulletin #1 on next week's list of 13 looks to be the most serious, as it is rated critical on Windows 7, Vista and XP, Windows Server 2003 and 2008 (including R2, the most recent release), and Internet Explorer versions 6 through 9. The patch requires a restart and is designed to prevent remote code execution.
Bulletin #2 will also require a restart and prevent remote code execution, but is rated critical only on Windows Server 2008, and Windows Server 2008 R2. The patch is rated only as important on Windows Server 2003, slightly unusual because Microsoft says vulnerabilities are typically less serious in newer versions of its products. The desktop versions of Windows are not affected.
Overall, nine patches affect Windows, one affects Internet Explorer, one affects Office, and two each affect the .NET Framework and Microsoft Developer Tools. Four of the 13 patches involve preventing remote code execution, three elevation of privilege, three prevent denial-of-service attacks, and three prevent information disclosure.
In other news, Microsoft released an annual security report which incorrectly claimed that vulnerabilities allowing remote code execution - a critical problem - are declining. Microsoft has since updated the report, and it turns out remote code execution flaws are actually on the rise, and the total number of reported vulnerabilities is rising as well.
We'll be back with more on the Microsoft Subnet after the patches are released Tuesday. Microsoft will issue them around 1 p.m. Eastern Time.
Sunday, August 7, 2011
Dell Joins Hadoop Crowd With Cloudera Partnership
Dell will add new-generation PowerEdge C servers and networking components, services and its vast channel and sales networks to Cloudera's software in the new implementation.
Dell on Aug. 4 joined the growing Apache Hadoop commercial implementators' club by announcing a new partnership with Cloudera -- by far the oldest and most production-utilized distribution of the celebrated open source data analytics package.
Cloudera's was the first commercial implementation of the open source data analytics package that came out of Yahoo's R&D division in 2006. For its part, Dell will supply new-generation PowerEdge C servers and networking components, services and its vast channel and sales networks to complete the new implementation.
Within those services, Dell will include management tools, training, technology support and other professional services.
"This is a defined reference architecture with a point of view that helps our customers very quickly identify a strategy for the implementation of a Hadoop presence inside of their corporation," Dell Executive Director of Cloud Solutions John Igoe told eWEEK.
"We see Cloudera is the leader in this particular space. Our vision was to combine their leadership in the Hadoop area with our leadership in hyperscale computing environments. We have a great deal of differentiation here by taking the abilities of both companies and putting them together."
Specifically, Dell/Cloudera for Apache Hadoop consists of Cloudera, Dell Crowbar software, and Cloudera Enterprise combined with a Dell PowerEdge C2100 server (other models will be added later) and PowerConnect 6248 48-port Gigabit Ethernet Layer 3 switch. Joint service (either Cloudera or Dell) and support and a deployment guide are also included.
Dell/Cloudera for Apache Hadoop can be used in many verticals but will be aimed first at financial services, energy, utility and telecom companies, research institutions, retail businesses, and Internet/media groups, Igoe said.
The new implementation is designed to reduce the complexity of deploying, configuring, and managing Hadoop systems that process large amounts of data enterprises can use to help manage themselves at a generally lower cost than older-school analytics packages and consultancies.
Making Hadoop More Usable Is the Goal
The bottom line is this: Hadoop is complicated software machinery to deploy and utilize, and it lacked a relatively usable front end until Cloudera and others came in to add their expertise. Dell's idea is to give customers a single source to deploy, manage, and scale a comprehensive Apache Hadoop-based stack, Igoe said.
There are a growing number of companies offering commercial implementations and/or providing support for Hadoop. Cloudera, IBM, Platform Computing were among the first to develop their own commercial Apache Hadoop implementations in recent years. EMC, NetApp, SGI and Yahoo (with its Hortonworks spinoff) are some of the others.
Igoe said the new reference architecture and all its hardware will be available later in August.
Dell on Aug. 4 joined the growing Apache Hadoop commercial implementators' club by announcing a new partnership with Cloudera -- by far the oldest and most production-utilized distribution of the celebrated open source data analytics package.
Cloudera's was the first commercial implementation of the open source data analytics package that came out of Yahoo's R&D division in 2006. For its part, Dell will supply new-generation PowerEdge C servers and networking components, services and its vast channel and sales networks to complete the new implementation.
Within those services, Dell will include management tools, training, technology support and other professional services.
"This is a defined reference architecture with a point of view that helps our customers very quickly identify a strategy for the implementation of a Hadoop presence inside of their corporation," Dell Executive Director of Cloud Solutions John Igoe told eWEEK.
"We see Cloudera is the leader in this particular space. Our vision was to combine their leadership in the Hadoop area with our leadership in hyperscale computing environments. We have a great deal of differentiation here by taking the abilities of both companies and putting them together."
Specifically, Dell/Cloudera for Apache Hadoop consists of Cloudera, Dell Crowbar software, and Cloudera Enterprise combined with a Dell PowerEdge C2100 server (other models will be added later) and PowerConnect 6248 48-port Gigabit Ethernet Layer 3 switch. Joint service (either Cloudera or Dell) and support and a deployment guide are also included.
Dell/Cloudera for Apache Hadoop can be used in many verticals but will be aimed first at financial services, energy, utility and telecom companies, research institutions, retail businesses, and Internet/media groups, Igoe said.
The new implementation is designed to reduce the complexity of deploying, configuring, and managing Hadoop systems that process large amounts of data enterprises can use to help manage themselves at a generally lower cost than older-school analytics packages and consultancies.
Making Hadoop More Usable Is the Goal
The bottom line is this: Hadoop is complicated software machinery to deploy and utilize, and it lacked a relatively usable front end until Cloudera and others came in to add their expertise. Dell's idea is to give customers a single source to deploy, manage, and scale a comprehensive Apache Hadoop-based stack, Igoe said.
There are a growing number of companies offering commercial implementations and/or providing support for Hadoop. Cloudera, IBM, Platform Computing were among the first to develop their own commercial Apache Hadoop implementations in recent years. EMC, NetApp, SGI and Yahoo (with its Hortonworks spinoff) are some of the others.
Igoe said the new reference architecture and all its hardware will be available later in August.
Saturday, August 6, 2011
Microsoft patching: Still painful after all these years
We asked readers to tell us if Microsoft’s efforts have made the patching process any easier.
The spring of 2011 has seen some of the largest Microsoft Patch Tuesdays ever. In April, Microsoft tied its all-time record with 17 updates that fixed 64 vulnerabilities. In June, the company issued another biggie, with 16 updates that fixed 34 vulnerabilities.
Microsoft knows that patching Windows, Office and its other software is a hardship on its users. The company says it has tried to limit the pain in a number of ways: setting aside a monthly day to issue patches, alternating months in which it issues many patches with months in which it issues fewer patches, and creating a rating system to help IT professionals know which patches to test and install first and which can wait. It issues a heads up on the patches the week prior to Patch Tuesday, too.
Have its efforts made patching systems any easier? We asked readers to tell us. Short answer: no. Readers say patching is a big a drain on resources as always. And this is true even for companies who have invested in third-party patch management tools. On the other hand, this also means that overall patching hasn’t gotten worse.
In a short survey of 171 IT professionals, we gleaned how people are handling patching.
The following series of PDFs includes the full survey responses, as well as some cross sections of the responses by company size and other factors.
The spring of 2011 has seen some of the largest Microsoft Patch Tuesdays ever. In April, Microsoft tied its all-time record with 17 updates that fixed 64 vulnerabilities. In June, the company issued another biggie, with 16 updates that fixed 34 vulnerabilities.
Microsoft knows that patching Windows, Office and its other software is a hardship on its users. The company says it has tried to limit the pain in a number of ways: setting aside a monthly day to issue patches, alternating months in which it issues many patches with months in which it issues fewer patches, and creating a rating system to help IT professionals know which patches to test and install first and which can wait. It issues a heads up on the patches the week prior to Patch Tuesday, too.
Have its efforts made patching systems any easier? We asked readers to tell us. Short answer: no. Readers say patching is a big a drain on resources as always. And this is true even for companies who have invested in third-party patch management tools. On the other hand, this also means that overall patching hasn’t gotten worse.
In a short survey of 171 IT professionals, we gleaned how people are handling patching.
The following series of PDFs includes the full survey responses, as well as some cross sections of the responses by company size and other factors.
Thursday, August 4, 2011
Getting data from Outlook Address Lists into Excel
The Outlook Object Model
Before I get started on the topic of this article - how to get outlook address list data into Excel - a few words on Outlook. Unlike some other proprietary software, Outlook has been constrained by the need to be compatible with the rest of the world, to be forever backwards compatible, and yet to be moving forward with other application in the same Office Family. This means that over the years its object model has become labyrinthine, and difficult to navigate. Certainly new capabilities are continually added to give the developer new choices, but whilst all the old choices are still there too, we are both overwhelmed by the multiplicity of methods of doing the same thing and also frustrated that most are only partially implemented, leaving unexposed properties. This has led to a multitude of techniques to get the same information - as well as the use of other approaches like Redemption Objects and CDO to fill the gap. All in all, it's alphabet soup.
Getting Data into Excel.
I had a need to get data into Excel , so that I could do some visualization work on various subsets of the global address list. Since I already have the capability to create Google Visualizations straight from Excel I figured I could do a one shot, "create visualizations straight out of Outlook" type of solution. First though, I needed to generalize and abstract data matching from Global Address List to Excel. As usual there is a downloadable example of this finished project for your use and comment.
Approach
In a sheet with column headings that match an abstracted view of the Outlook Address Book, one of the columns is going to contain the 'key'; (The unique item that is going to be used to find the contact information for each row in the table, for example the email address or the alias or some other outlook field). An inital sheet will look like this, in this case using the alias field as the key field to match on, and the task will be to populate the other columns from our exchange global address list.
Getting the data
In any VBA project, I generally abstract the sheet data from its physical location by using these Data Manipulation Classes. This abstraction of the worksheet is what will be used by the outlook classes. Another advantage of this is that changes are only made to the sheet at the very end, and are not committed if there was a problem during execution. Using this abstraction therefore makes rollback easy (actually unnecessary), and avoids messing around with application.screenupdating.
Creating a class to access outlook
To abstract the outlook address data from its underlying object model, I will create 2 new classes, cOutlookAddressBook (to perform the lookup and populate a cDataSet with matched data from the Outlook address book) and cOutlookApp (to kick off an outlook session). The code for these is below
'cOutlookApp
Option Explicit
Private pWasClosed As Boolean
Private pOutlookApp As Outlook.Application
Public Property Get OutlookApp() As Outlook.Application
Set OutlookApp = pOutlookApp
End Property
Public Function Init() As Boolean
Set pOutlookApp = GetObject(, "Outlook.Application")
'if it wasnt already running, create one
pWasClosed = pOutlookApp Is Nothing
If pWasClosed Then
Set pOutlookApp = New Outlook.Application
End If
Init = True
End Function
Public Sub Destroy()
Set pOutlookApp = Nothing
End Sub
'cOutlookAddressBook
Option Explicit
Private pAddressBook As Outlook.AddressList
Private pOutlookApp As cOutlookApp
Private pDirty As Boolean
Public Property Get AddressBook() As Outlook.AddressList
Set AddressBook = pAddressBook
End Property
Public Function Init(oa As cOutlookApp, sBook As String) As Boolean
Set pOutlookApp = oa
' open the requested address book
Set pAddressBook = pOutlookApp.OutlookApp.Session.AddressLists(sBook)
Init = True
End Function
Public Sub Destroy()
'nothing to do
End Sub
Public Function Populate(ds As cDataSet, target As String) As Boolean
Dim e As Outlook.AddressEntry, ex As Outlook.ExchangeUser, sh As String
Dim sl As String, dr As cDataRow, dc As cCell, sa As String, ltoGo As Long
' this is the target key
sl = LCase(target)
ltoGo = ds.RowCount
' clear out any existing data, except for key
For Each dr In ds.Rows
dr.CustomField = False
For Each dc In ds.Headings
If LCase(dc.toString) <> sl Then
dr.Cell(dc.Column).Value = vbNullString
End If
Next dc
Next dr
' since it takes a while to retrieve exchaneguser, we'll make that outer loop
For Each e In pAddressBook.AddressEntries
If ltoGo <= 0 Then Exit For ' if all done lets leave
Set ex = e.getExchangeUser
If Not ex Is Nothing Then
sa = LCase(getValue(e, ex, sl)) ' the key for this record
For Each dr In ds.Rows
If Not dr.CustomField Then ' used to determine whether we've been here already
If sa = LCase(dr.Cell(sl).toString) Then
' we have a match- we're using the custom field to track completion
dr.CustomField = True
ltoGo = ltoGo - 1
' need to fill in the lookup fields
For Each dc In ds.Headings
sh = LCase(dc.toString)
' not the key
If sh <> sl Then
dr.Cell(dc.Column).Value = getValue(e, ex, sh)
If pDirty Then Exit Function
End If
Next dc
End If
End If
Next dr
End If
Next e
Populate = True
End Function
Private Function getValue(e As Outlook.AddressEntry, ex As Outlook.ExchangeUser, colName As String) As String
getValue = vbNullString
Select Case colName
Case "alias"
getValue = ex.Alias
Case "firstname"
getValue = ex.FirstName
Case "lastname"
getValue = ex.LastName
Case "officelocation"
getValue = ex.OfficeLocation
Case "stateorprovince"
getValue = ex.StateOrProvince
Case "streetaddress"
getValue = ex.StreetAddress
Case "department"
getValue = ex.Department
Case "email"
getValue = ex.PropertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x39FE001E")
Case "country"
getValue = e.PropertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x3a26001e")
Case Else
MsgBox (colName & " data not implemented")
pDirty = True
End Select
End Function
Execution
To get a field filled in from outlook, just create a column with the name of the abstracted field. You will see from the code above it is pretty straightforward to add additional Outlook fields if they are not yet implemented in the example. Note that use is made of the exchangeUser object as well as the propertyAccessor method and that the column headings should exactly match the case statements in the getValue function, whose purpose is to abstract their Outlook Object model location away from the column data required.
The application procedure
Below is an example of an application that uses these classes (you can download from here) . It expects to execute on the active sheet, and the function, getLikelyColumnRange will assume that everything on the sheet is subject to lookup. You would also probably amend od.Populate(ds, "alias") to identify which is they key field to use for lookup.
Option Explicit
Const sBook = "Global Address List"
Public Sub getOutLookData()
Dim od As cOutlookAddressbook
Dim oa As cOutlookApp
Dim rData As Range, dSets As cDataSets, ds As cDataSet
' kick off an outlook session
Set oa = New cOutlookApp
If oa.Init Then
' get the required addresslist
Set od = New cOutlookAddressbook
If od.Init(oa, sBook) Then
' you could replace this with a dialogue to get the range to be processed
Set rData = getLikelyColumnRange
'create a cdataset of that range
Set dSets = New cDataSets
With dSets
.Create
.Init rData, , "data"
End With
Set ds = dSets.DataSet("data")
' now populate - will only actually commit to the sheet if all was well
' using the alias field/column as the key
If od.Populate(ds, "alias") Then
ds.Commit
End If
od.Destroy
Else
MsgBox ("Couldnt open address book " & sBook)
End If
' clear up
oa.Destroy
Set od = Nothing
Set oa = Nothing
Else
MsgBox ("Couldnt start outlook")
End If
End Sub
Summary
Finding data in the outlook object model was the most time consuming activity in all this. No doubt I (or you, if you make use of this code) will want to extend the getValue function to pick up more data from the address list, which could involve the use of PropertyAccessor.GetProperty() for properties normally unexposed in the Outlook Object Model. I had a lot of trouble finding definitive documentation on where to find what uses what, and reading this 'explanation' from Microsoft along with all the references in other articles to DASL,DAV ,Proptags, schemas and so on, made my head ache, so if anyone knows where to find something complete, simple and defintive - meaning a list of how to get every field displayed by the method ExchangeUser.Details, please comment on this article, or post it here.
Comments, improvements, discussion welcome as usual, and downloadable stuff related to this article is here. In a future article I will probably cover connecting all this to google visualizations right out of Outlook.
Before I get started on the topic of this article - how to get outlook address list data into Excel - a few words on Outlook. Unlike some other proprietary software, Outlook has been constrained by the need to be compatible with the rest of the world, to be forever backwards compatible, and yet to be moving forward with other application in the same Office Family. This means that over the years its object model has become labyrinthine, and difficult to navigate. Certainly new capabilities are continually added to give the developer new choices, but whilst all the old choices are still there too, we are both overwhelmed by the multiplicity of methods of doing the same thing and also frustrated that most are only partially implemented, leaving unexposed properties. This has led to a multitude of techniques to get the same information - as well as the use of other approaches like Redemption Objects and CDO to fill the gap. All in all, it's alphabet soup.
Getting Data into Excel.
I had a need to get data into Excel , so that I could do some visualization work on various subsets of the global address list. Since I already have the capability to create Google Visualizations straight from Excel I figured I could do a one shot, "create visualizations straight out of Outlook" type of solution. First though, I needed to generalize and abstract data matching from Global Address List to Excel. As usual there is a downloadable example of this finished project for your use and comment.
Approach
In a sheet with column headings that match an abstracted view of the Outlook Address Book, one of the columns is going to contain the 'key'; (The unique item that is going to be used to find the contact information for each row in the table, for example the email address or the alias or some other outlook field). An inital sheet will look like this, in this case using the alias field as the key field to match on, and the task will be to populate the other columns from our exchange global address list.
Getting the data
In any VBA project, I generally abstract the sheet data from its physical location by using these Data Manipulation Classes. This abstraction of the worksheet is what will be used by the outlook classes. Another advantage of this is that changes are only made to the sheet at the very end, and are not committed if there was a problem during execution. Using this abstraction therefore makes rollback easy (actually unnecessary), and avoids messing around with application.screenupdating.
Creating a class to access outlook
To abstract the outlook address data from its underlying object model, I will create 2 new classes, cOutlookAddressBook (to perform the lookup and populate a cDataSet with matched data from the Outlook address book) and cOutlookApp (to kick off an outlook session). The code for these is below
'cOutlookApp
Option Explicit
Private pWasClosed As Boolean
Private pOutlookApp As Outlook.Application
Public Property Get OutlookApp() As Outlook.Application
Set OutlookApp = pOutlookApp
End Property
Public Function Init() As Boolean
Set pOutlookApp = GetObject(, "Outlook.Application")
'if it wasnt already running, create one
pWasClosed = pOutlookApp Is Nothing
If pWasClosed Then
Set pOutlookApp = New Outlook.Application
End If
Init = True
End Function
Public Sub Destroy()
Set pOutlookApp = Nothing
End Sub
'cOutlookAddressBook
Option Explicit
Private pAddressBook As Outlook.AddressList
Private pOutlookApp As cOutlookApp
Private pDirty As Boolean
Public Property Get AddressBook() As Outlook.AddressList
Set AddressBook = pAddressBook
End Property
Public Function Init(oa As cOutlookApp, sBook As String) As Boolean
Set pOutlookApp = oa
' open the requested address book
Set pAddressBook = pOutlookApp.OutlookApp.Session.AddressLists(sBook)
Init = True
End Function
Public Sub Destroy()
'nothing to do
End Sub
Public Function Populate(ds As cDataSet, target As String) As Boolean
Dim e As Outlook.AddressEntry, ex As Outlook.ExchangeUser, sh As String
Dim sl As String, dr As cDataRow, dc As cCell, sa As String, ltoGo As Long
' this is the target key
sl = LCase(target)
ltoGo = ds.RowCount
' clear out any existing data, except for key
For Each dr In ds.Rows
dr.CustomField = False
For Each dc In ds.Headings
If LCase(dc.toString) <> sl Then
dr.Cell(dc.Column).Value = vbNullString
End If
Next dc
Next dr
' since it takes a while to retrieve exchaneguser, we'll make that outer loop
For Each e In pAddressBook.AddressEntries
If ltoGo <= 0 Then Exit For ' if all done lets leave
Set ex = e.getExchangeUser
If Not ex Is Nothing Then
sa = LCase(getValue(e, ex, sl)) ' the key for this record
For Each dr In ds.Rows
If Not dr.CustomField Then ' used to determine whether we've been here already
If sa = LCase(dr.Cell(sl).toString) Then
' we have a match- we're using the custom field to track completion
dr.CustomField = True
ltoGo = ltoGo - 1
' need to fill in the lookup fields
For Each dc In ds.Headings
sh = LCase(dc.toString)
' not the key
If sh <> sl Then
dr.Cell(dc.Column).Value = getValue(e, ex, sh)
If pDirty Then Exit Function
End If
Next dc
End If
End If
Next dr
End If
Next e
Populate = True
End Function
Private Function getValue(e As Outlook.AddressEntry, ex As Outlook.ExchangeUser, colName As String) As String
getValue = vbNullString
Select Case colName
Case "alias"
getValue = ex.Alias
Case "firstname"
getValue = ex.FirstName
Case "lastname"
getValue = ex.LastName
Case "officelocation"
getValue = ex.OfficeLocation
Case "stateorprovince"
getValue = ex.StateOrProvince
Case "streetaddress"
getValue = ex.StreetAddress
Case "department"
getValue = ex.Department
Case "email"
getValue = ex.PropertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x39FE001E")
Case "country"
getValue = e.PropertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x3a26001e")
Case Else
MsgBox (colName & " data not implemented")
pDirty = True
End Select
End Function
Execution
To get a field filled in from outlook, just create a column with the name of the abstracted field. You will see from the code above it is pretty straightforward to add additional Outlook fields if they are not yet implemented in the example. Note that use is made of the exchangeUser object as well as the propertyAccessor method and that the column headings should exactly match the case statements in the getValue function, whose purpose is to abstract their Outlook Object model location away from the column data required.
The application procedure
Below is an example of an application that uses these classes (you can download from here) . It expects to execute on the active sheet, and the function, getLikelyColumnRange will assume that everything on the sheet is subject to lookup. You would also probably amend od.Populate(ds, "alias") to identify which is they key field to use for lookup.
Option Explicit
Const sBook = "Global Address List"
Public Sub getOutLookData()
Dim od As cOutlookAddressbook
Dim oa As cOutlookApp
Dim rData As Range, dSets As cDataSets, ds As cDataSet
' kick off an outlook session
Set oa = New cOutlookApp
If oa.Init Then
' get the required addresslist
Set od = New cOutlookAddressbook
If od.Init(oa, sBook) Then
' you could replace this with a dialogue to get the range to be processed
Set rData = getLikelyColumnRange
'create a cdataset of that range
Set dSets = New cDataSets
With dSets
.Create
.Init rData, , "data"
End With
Set ds = dSets.DataSet("data")
' now populate - will only actually commit to the sheet if all was well
' using the alias field/column as the key
If od.Populate(ds, "alias") Then
ds.Commit
End If
od.Destroy
Else
MsgBox ("Couldnt open address book " & sBook)
End If
' clear up
oa.Destroy
Set od = Nothing
Set oa = Nothing
Else
MsgBox ("Couldnt start outlook")
End If
End Sub
Summary
Finding data in the outlook object model was the most time consuming activity in all this. No doubt I (or you, if you make use of this code) will want to extend the getValue function to pick up more data from the address list, which could involve the use of PropertyAccessor.GetProperty() for properties normally unexposed in the Outlook Object Model. I had a lot of trouble finding definitive documentation on where to find what uses what, and reading this 'explanation' from Microsoft along with all the references in other articles to DASL,DAV ,Proptags, schemas and so on, made my head ache, so if anyone knows where to find something complete, simple and defintive - meaning a list of how to get every field displayed by the method ExchangeUser.Details, please comment on this article, or post it here.
Comments, improvements, discussion welcome as usual, and downloadable stuff related to this article is here. In a future article I will probably cover connecting all this to google visualizations right out of Outlook.
Subscribe to:
Posts (Atom)