?
Solved

Open SharePoint Excel Workbook

Posted on 2014-02-24
4
Medium Priority
?
2,421 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

764 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