Solved

Open SharePoint Excel Workbook

Posted on 2014-02-24
4
2,292 Views
Last Modified: 2014-02-25
I would like to open an Excel workbook on a SharePoint site using Access VBA. I need to refresh the workbook and save back to the SharePoint site. The workbook is located at http://mysites.cdc.gov/personal/xbn8/Shared Documents and the name of the file is Metrics_SharePoint.xlsx -- Thanks
0
Comment
Question by:shieldsco
  • 2
  • 2
4 Comments
 
LVL 10

Accepted Solution

by:
broro183 earned 500 total points
ID: 39885048
hi,

I haven't been saving files to SP for ~3 years so I've done a quick Google search to refresh my memory, and the below code that I found stirs some vague memories for me. I've made some changes to it but I haven't tested it so it may or may not work for you. Note, I've included various different string formats which you can test by commenting & uncommenting each "xlfile =..." line of code.

Option Explicit

Sub OpenSPFileRefreshSaveAndClose()
'25/02/2014,RB, "Sub UseCanCheckOut" modified after being sourced from:
'http://www.pcreview.co.uk/forums/vba-code-open-excel-file-sharepoint-site-t3612085.html

Dim xlApp As Excel.Application
Dim wb As Excel.Workbook
Dim xlFile As String
    
    'xlFile = "http://test:8080/PWA/Project Documents/00 MS Excel File/BookTest.xls"
    xlFile = "http://mysites.cdc.gov/personal/xbn8/Shared Documents/Metrics_SharePoint.xlsx"
    ''or try
    'xlFile = "http://mysites.cdc.gov/personal/xbn8/Shared%20Documents/Metrics_SharePoint.xlsx"
    ''or try
    'xlFile = "\\mysites.cdc.gov\personal\xbn8\Shared Documents\Metrics_SharePoint.xlsx"
    ''or try
    'xlFile = "\\mysites.cdc.gov\personal\xbn8\Shared%20Documents\Metrics_SharePoint.xlsx"

    'Determine if workbook can be checked out.
    Set xlApp = New Excel.Application    'RB: moved outside the If statement to allow explicit referencing to the Application level.
    If xlApp.Workbooks.CanCheckOut(xlFile) Then
        xlApp.Workbooks.CheckOut xlFile
        'RB: original location of this line: Set xlApp = New Excel.Application
        xlApp.Visible = True
        Set wb = xlApp.Workbooks.Open(xlFile, , False)
        MsgBox wb.Name & " is checked out to you."
        '*********************************************************
        'RB: do what you need to do with the open file, for example:
        With wb
            .RefreshAll
            'RB: the following is sourced from http://www.codeproject.com/Questions/403293/Checking-Out-files-in-Sharepoint-from-Excel-VBA
            If .CanCheckIn Then
                .save 'RB: I'm not sure if this line is needed
.CheckIn
                .Close
                MsgBox "The file has been checked in."
            Else
                'code to handle error and close destination workbook
            End If
        End With
        '*********************************************************
    Else
        ' MsgBox "You are unable to check out this document at this time."
        'RB: I've added the next two lines of code
        xlApp.Quit
    End If

    Set wb = Nothing
    Set xlApp = Nothing

End Sub

Open in new window


hth
Rob
0
 

Author Comment

by:shieldsco
ID: 39885774
I modified the code and is working for me, however I need to save and shut down the application.  Since the spreadsheet is connected to Accesss tables I just turned on the Refresh on Open option in Excel. When I run the code below it prompts me to save.

Dim xlApp As Excel.Application
Dim wb As Excel.Workbook
Dim xlFile As String
   
   
    xlFile = "http://mysites.cdc.gov/personal/xbn8/Shared Documents/Metrics_SharePoint.xlsx"
   
    Set xlApp = New Excel.Application
   
       
        xlApp.Visible = True
        Set wb = xlApp.Workbooks.Open(xlFile, , False)
             
    xlApp.Quit
           
           
    Set wb = Nothing
    Set xlApp = Nothing

End Sub
0
 

Author Closing Comment

by:shieldsco
ID: 39885978
Works good - Thanks
0
 
LVL 10

Expert Comment

by:broro183
ID: 39886783
Try changing
    Set wb = xlApp.Workbooks.Open(xlFile, , False)
             
    xlApp.Quit
     

Open in new window


to

    Set wb = xlApp.Workbooks.Open(xlFile, , False)
wb.close true             
    xlApp.Quit
     

Open in new window

0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now