none
Problem to open file RRS feed

  • Question

  • Hi,
    I get issue


    due to last line below

        Application.DisplayAlerts = False
        Workbooks.Open (File0)



    while the file is one Macro-enabled Excel, which is existing inside Sharepoint folder.

    Many Thanks & Best Regards, Hua Min

    Wednesday, April 6, 2016 1:45 AM

Answers

  • Hi HuaMin,

    >> while the file is one Macro-enabled Excel, which is existing inside Sharepoint folder

    Do you mean the file is in the folder of SharePoint Documents?

    I suggest you open the file in desktop excel, and then run the code below to get the real file path.

    Sub test()
    Dim s As Workbook
    Set s = ActiveWorkbook
    a = s.FullName
    Debug.Print a
    End Sub

    And then test with this FullName.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by Jackson_1990 Friday, April 8, 2016 2:01 AM
    • Unmarked as answer by Jackson_1990 Friday, April 8, 2016 2:16 AM
    • Marked as answer by Jackson_1990 Friday, April 8, 2016 2:17 AM
    Thursday, April 7, 2016 1:41 AM

All replies

  • The file path that you shown includes host name.
    If so, try to use IP address, instead of host name.
    Wednesday, April 6, 2016 3:16 AM
  • I use IP address instead but I've still got the same problem.

    Any other help?

    Many Thanks & Best Regards, Hua Min


    Wednesday, April 6, 2016 4:03 AM
  • Would you provide code related to "File0", i.e. its declaration and setting file path.
    Wednesday, April 6, 2016 5:49 AM
  • It is declared as "String" and it is having such value 

    \\10.55.0.221\sites\a4\IT\Projects\ExcelTool\BudgetExcelTool\China\AsiaPacific_Budget_HO_China_2016-Feb.xlsm



    inside, when I'm getting the error.

    Many Thanks & Best Regards, Hua Min


    Wednesday, April 6, 2016 6:18 AM
  • Thank you for information.
    Can you selectedaccess the file manuallyeck whether it exists or not?
    It may be like this
    Dim File0 As String
    File0 = Application.GetOpenFilename("Excel book", "*.xlsm")
    If File0 <> "False" Then
        Workbooks.Open File0
    End If

    Wednesday, April 6, 2016 7:02 AM
  • I put your codes then have got this


    due to this line

        File0 = Application.GetOpenFilename("Excel book", "*.xlsm")

    Many Thanks & Best Regards, Hua Min

    Wednesday, April 6, 2016 7:42 AM
  • 1. Where the error has occurred?
        "File0=" or "Workbooks.Open"

    2. Can you open the file manually, instead of by code?
    Wednesday, April 6, 2016 7:52 AM
  • Yes, the error is due to this line

    File0 = Application.GetOpenFilename("Excel book", "*.xlsm")


    and I'm able to manually open that macro-enabled Excel file.



    Many Thanks & Best Regards, Hua Min

    Wednesday, April 6, 2016 7:57 AM
  • I'd tried to write real code. This worked.
    File0 = Application.GetOpenFilename("Excel book, *.xlsm")
    Wednesday, April 6, 2016 8:12 AM
  • I really have got the current issue, due to that line. Where does your Excel file reside? Is it existing within one Sharepoint folder, as that is my case?

    Many Thanks & Best Regards, Hua Min


    Wednesday, April 6, 2016 8:23 AM
  • I don't use Sharepoint. My files are in NAS.
    So, your issue should be related to Sharepoint.
    Wednesday, April 6, 2016 8:58 AM
  • Thanks. Any other help?

    Many Thanks & Best Regards, Hua Min

    Wednesday, April 6, 2016 9:12 AM
  • Search the web with such keywords as:
    vba sharepoint open file
    Many articles will be found.  Good luck! 
    Wednesday, April 6, 2016 9:24 AM
  • Thanks. I'm still getting the same problem (currently shown in above), due to last line below

        Dim WB0 As Workbook
        Set WB0 = Workbooks.Open(File0)
    


    Many Thanks & Best Regards, Hua Min

    Wednesday, April 6, 2016 9:45 AM
  • I saw some articles describing: file path of Sharepoint begins with "https://". 
    Have you tried?
    Wednesday, April 6, 2016 9:58 AM
  • Hi HuaMin,

    >> while the file is one Macro-enabled Excel, which is existing inside Sharepoint folder

    Do you mean the file is in the folder of SharePoint Documents?

    I suggest you open the file in desktop excel, and then run the code below to get the real file path.

    Sub test()
    Dim s As Workbook
    Set s = ActiveWorkbook
    a = s.FullName
    Debug.Print a
    End Sub

    And then test with this FullName.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by Jackson_1990 Friday, April 8, 2016 2:01 AM
    • Unmarked as answer by Jackson_1990 Friday, April 8, 2016 2:16 AM
    • Marked as answer by Jackson_1990 Friday, April 8, 2016 2:17 AM
    Thursday, April 7, 2016 1:41 AM
  • Thanks all.
    Edward,

    I run your codes
    Sub test()
    Dim s As Workbook, a As String
    Set s = ActiveWorkbook
    a = s.FullName
    Debug.Print a
    End Sub


    and get this FullName below
    http://hkgmoss/sites/a4/IT/Projects/ExcelTool/BudgetExcelTool/China/AsiaPacific_Budget_HO_China_2016-Feb.xlsm

    which is same as that I use to open it. I do get this

    due to last line below

        Debug.Print File0
        Dim WB0 As Workbook
        Set WB0 = Workbooks.Open(File0)


    while File0 is also having this
    http://hkgmoss/sites/a4/IT/Projects/ExcelTool/BudgetExcelTool/China/AsiaPacific_Budget_HO_China_2016-Feb.xlsm

    inside.

    Many Thanks & Best Regards, Hua Min



    Thursday, April 7, 2016 6:33 AM
  • Hi HunMin,

    I suggest you do as below:

    1. Open Excel Record Macro
    2. File->Open->Sites
    3. Open this excel file from Excel Open function
    4. Check the recorded macro

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, April 8, 2016 1:42 AM