Solved

Open SharePoint Excel Workbook

Posted on 2014-02-24
4
2,329 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

777 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