Outlook Macro to Update Sharepoint Excel File

[Jeffclanders]

Hi All,

I have a macros listed under code below that takes basic data from Outlook 2010 and adds it to an excel file in Sharepoint. It sends the data to a "Raw data" tab where I can then use that data to fill in other workbooks. This was intended to take the same data from multiple areas for people located across the U.S. and then send it to their own Sharepoint Workbooks where I would then send that data to a master file for senior managers to look at the same data for all areas at once. I originally tried to use a userform listbox to do this, by having the user for each area select their respective area to decide which workbook the data would be extracted to. This became difficult because choosing a wrong area by mistake filled the data in the wrong workbook. The cojnstraints that I have on this is that this must be a push button macro, which I have added to the ribbon in outlook, and the only requirement for running the macro is to highlight and select the specific emails that the macro will look at. I am not able to use userid to associate with a specific area and in some cases, others may be filling oin for someone while they are on vacation. This second person would be doing the work from another area to help out. All areas have their own email alias such as mine, which begins with CNY for (Central New York), so this may be an option. I reversed the process to eliminate the need to select an area so that a user could simply push the button and the data woulkd then fill in the master file. From the master file, I could then send the specific area data to separate workbooks for users to see their own areas' information, and not others. This solved the notion of sending each areas information to separate workbooks by choosing an area, which is frought with possible errors. The code below gets the data I need to the sharepoint excel file, but it is slow. I dont necessarily need to open the excel file oin sharepoimnt, and actually, it is a master file that area usewrs will not have access to anyway. How can I make this faster or better? Any thoughts would be much appreciated:

Code:

Option Explicit
Sub CopyToExcel()
Dim xlApp As Object
Dim xlWB As Object
Dim xlSheet As Object
Dim rCount As Long
Dim bXStarted As Boolean
Dim enviro As String
Dim strPath As String

Dim currentExplorer As Explorer
Dim Selection As Selection
Dim olItem As Outlook.MailItem
Dim obj As Object
Dim strColA, strColB, strColC As String

'the path of the workbook
strPath = enviro & "http://share.twcable.com/sites/EntC...er/Aging Weekly Tracking Template Master.xlsx"
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Application.StatusBar = "Please wait while Excel source is opened ... "
Set xlApp = CreateObject("Excel.Application")
bXStarted = True
End If
On Error GoTo 0
'Open the workbook to input the data
Set xlWB = xlApp.Workbooks.Open(strPath)
Set xlSheet = xlWB.Sheets("Raw Data")
' Process the message record

On Error Resume Next
'Find the next empty line of the worksheet
rCount = xlSheet.Range("A" & xlSheet.Rows.Count).End(-4162).Row + 1


' get the values from outlook
Set currentExplorer = Application.ActiveExplorer
Set Selection = currentExplorer.Selection
For Each obj In Selection

Set olItem = obj

'collect the fields
strColA = olItem.SenderName
strColB = olItem.SenderEmailAddress
strColC = olItem.ReceivedTime

'write them in the excel sheet
xlSheet.Range("A" & rCount) = strColA
xlSheet.Range("B" & rCount) = strColB
xlSheet.Range("C" & rCount) = strColC
'Next row
rCount = rCount + 1

Next

xlWB.Close 1
If bXStarted Then
xlApp.Quit
End If

Set olItem = Nothing
Set obj = Nothing
Set currentExplorer = Nothing
Set xlApp = Nothing
Set xlWB = Nothing
Set xlSheet = Nothing
End Sub

 

[Diane Poremsky]

Based on my own experience, I'm guessing the slow part is accessing it in SharePoint. If the SharePoint file was synced locally, it would/should be faster. But at this point, it only works with the old sync client, which isn't all that robust, and changes may not sync up.

if you aren't using enviro to set the local user folder, you can remove it from the string - but it's not going to have an appreciable effect on the speed.
strPath = "http....

Beyond that, I don't have any ways to speed it up. Someone else might have some ideas that will help though.