Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Open SharePoint Excel Workbook

Posted on 2014-02-24
4
Medium Priority
?
2,445 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 10

Accepted Solution

by:
broro183 earned 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

618 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