Solved

Open SharePoint Excel Workbook

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

726 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