none
Error 1004: Excel Macro fails to open workbook intermittently RRS feed

  • Question

  • I am using Excel macros in a spreadsheet to retrieve data from master spreadsheet. We have both the spreadsheets on the same SharePoint 365 folder. when I access the file and update the spreadsheet, the macro is able to intermittently connect to the master file and update. The code being used is below:

        Dim fileString As String
        Dim wb As Workbook
        
            Application.ScreenUpdating = False
            
            fileString = "\\office365siteurl\Shared%20Documents\Migration\Master%20Workbook.xlsx"
           
            Set wb = Workbooks.Open(fileString)
           Application.ScreenUpdating = True

        
        wb.Close False
        Set wb = Nothing

    I get the error on Workbooks.open line. This error occurs randomly as:

    Run-time Error '1004':

    Method 'Open' of object 'Workbooks' failed

    Thursday, October 13, 2016 9:18 PM

All replies

  • Hi VJ-A,

    According to your description, I suggest that you could check this file, refer to this link below:

    http://stackoverflow.com/questions/19505513/open-an-excel-file-from-sharepoint-site

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Thanks for your understanding.
    • Proposed as answer by David_JunFeng Thursday, October 20, 2016 1:23 AM
    Friday, October 14, 2016 8:09 AM