none
Problem to open relevant file RRS feed

  • Question

  • Hi,
    Using

    Dim File4 Debug.Print File0 File4 = Replace(File4, "\", "/") File4 = Replace(File4, "//", "http://") Debug.Print File4

        Set WB0 = Workbooks.Open(File4) ...

    I do not know why replace does not make any effect, as here are what I get

    \\hkgmoss\sites\a4\IT\Projects\ExcelTool\BudgetExcelTool\CHINA\AsiaPacific_Budget_HO_CHINA_2016-Feb.xlsm
    \\hkgmoss\sites\a4\IT\Projects\ExcelTool\BudgetExcelTool\CHINA\AsiaPacific_Budget_HO_CHINA_2016-Feb.xlsm

    And here is the relevant error

    got, due to last line, in above.


    Many Thanks & Best Regards, Hua Min



    Thursday, June 9, 2016 2:51 PM

Answers

  • Hi HuaMin  Chen,

    I am not talking about to open a file from Excel.

    I told you that just try to open a file with following code

    Sub dem2()
    Workbooks.Open ("http://hkgmoss/sites/a4/IT/Projects/ExcelTool/BudgetExcelTool/China/AsiaPacific_Budget_HO_CHINA_2016-Feb.xlsm")
    End Sub

    when I try to run this code I get following error.

    Does it have some security on that server. like you have to provide username and password before you access ?

    but if you can able to open with code above then problem is in your code.

    the other thing you can check that try to open a file that stored on your machine locally if it open then we can say maybe the problem related with server.

    when I try to open file stored locally it worked.

    Regards

    Deepak


    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.


    Tuesday, June 14, 2016 5:29 AM
    Moderator

All replies

  • Any help?

    Many Thanks & Best Regards, Hua Min

    Friday, June 10, 2016 1:35 AM
  • Hi HuaMin  Chen,

    I try to make a test on your above code .

    see the results below I get.

    The following is the code I used.

    Sub demo()
    Dim File4 As String
    File4 = "\\hkgmoss\sites\a4\IT\Projects\ExcelTool\BudgetExcelTool\CHINA\AsiaPacific_Budget_HO_CHINA_2016-Feb.xlsm"
        File4 = Replace(File4, "\", "/")
        File4 = Replace(File4, "//", "http://")
        Debug.Print File4
    End Sub
    

    The following is the results I get.

    so here I find that it is working as expected.

    in your above code you have print File0 and after replace File4.

    but you did not mentioned the value you had stored in File4.

    please provide us a value of File4 before you replace that.

    you get error maybe because the path is not in correct format.

    for that you can try to give the correct path directly for only testing purpose.

    so that we can know it worked or not.

     Regards

    Deepak


    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, June 10, 2016 3:01 AM
    Moderator
  • I did not assign other value to File4 (other than the above codes) and I did newly declare File4 in above. I do not know why the error happens.

    Many Thanks & Best Regards, Hua Min


    Friday, June 10, 2016 3:27 AM
  • Hi HuaMin  Chen,

    if you did not assign value to File4 then how it will replace with "//".

    it means that File4 variable is empty.

    and you are just displaying the value stored in File0.

    in your original post you have mentioned 2 lines of output so I understand that 1st line prints the value of File0 and 2nd line of output comes from File4.

    but now its looks like you have run the code 2 times so it will just displays the value stored in File0 2 times and we get same value so you think it doesn't get changed.

    Here I recommend you to store the value in File4 and then try to replace.

    The another thing you can do is that File0 contains the value and if you not need that value in future then you can replace the value in File0. For that you have to replace the File4 with File0.

    but if you are using the value stored in File0 at some another place then I would recommend you to assign a value in File4.

    The example of that I already mentioned above. you can use it.

    Regards

    Deepak


    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, June 10, 2016 8:39 AM
    Moderator
  • Hi,
    I have these
        Dim File4
        Debug.Print "8x" & File0
        File4 = File0
        File4 = Replace(File4, "\", "/")
        Debug.Print "8xc" & File4
        File4 = Replace(File4, "//", "http://")
        File4 = Replace(File4, "/CHINA", "/China")
        Debug.Print "8y" & File4
        Set WB0 = Workbooks.Open(File4)


    but I do not know why I get the problem, due to last line, in above, while the relevant Excel file, is existin there, which is showing the same path if I display this

    ActiveWorkbook.FullName

    when opening the same Excel file.

    Many Thanks & Best Regards, Hua Min


    Friday, June 10, 2016 8:59 AM
  • Such problem does exist within Citrix, and can it be related to Citrix server? How can I check this?

    Many Thanks & Best Regards, Hua Min

    Monday, June 13, 2016 1:41 AM
  • Hi HuaMin  Chen,

    can you show me what is the path value in File4 at the last line where you try to open file?

    the another thing I want to recommend you that try to provide path manually in workbook.open and check if file opens or not or you get error?

    if file opens successfully maybe problem is in your code.

    if you get error then maybe there is some problem related to server.

    Regards

    Deepak


    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.

    Monday, June 13, 2016 8:50 AM
    Moderator
  • Path value is

    http://hkgmoss/sites/a4/IT/Projects/ExcelTool/BudgetExcelTool/China/AsiaPacific_Budget_HO_CHINA_2016-Feb.xlsm

    when the error happens. And I'm able to open the file by putting the same value, within Excel. But the codes fail, when opening the same file, using the same path value.


    Many Thanks & Best Regards, Hua Min

    Monday, June 13, 2016 9:21 AM
  • Hi HuaMin  Chen,

    I am not talking about to open a file from Excel.

    I told you that just try to open a file with following code

    Sub dem2()
    Workbooks.Open ("http://hkgmoss/sites/a4/IT/Projects/ExcelTool/BudgetExcelTool/China/AsiaPacific_Budget_HO_CHINA_2016-Feb.xlsm")
    End Sub

    when I try to run this code I get following error.

    Does it have some security on that server. like you have to provide username and password before you access ?

    but if you can able to open with code above then problem is in your code.

    the other thing you can check that try to open a file that stored on your machine locally if it open then we can say maybe the problem related with server.

    when I try to open file stored locally it worked.

    Regards

    Deepak


    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.


    Tuesday, June 14, 2016 5:29 AM
    Moderator
  • I can open the relevant file by your suggested way (put the full path directly), and do not know why I get the above problem. 

    When I manually open the same file, using Excel, I get this

    if you want, you can leave me your Email address, for me to send you the relevant Workbook.


    Many Thanks & Best Regards, Hua Min

    Tuesday, June 14, 2016 6:47 AM
  • Hi HuaMin  Chen,

    I search regarding this error and find that this error comes when vba is not installed on the machine.

    but here I don't think that this could be a reason for that because you are running a vba code so vba is installed.

    here I again make a test with your above mentioned code.

    the replace is working correctly as you can see it in a result but I get error when opening file.

    I want to ask you one thing that can I have a privilege to open file?

    I think I don't have right or permission to access the file on this server that's why I get this error.

    if we don't have right then if you share your file it is also not working we will get same error.

    then also if you want to share file then you can use free file sharing websites to share your file.

    and paste the link here of your uploaded file so that other community members can also access it and look into that and try to help you.

    I would recommend you to don't share your confidential data in a file. share file that have demo content and can able to reproduce the issue.

    Regards

    Deepak


    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.

    Wednesday, June 15, 2016 4:13 AM
    Moderator
  • Hi HuaMin  Chen,

    is your issue solved? do you get any solution for that?

    if so would you like to share the solution with our community so that other members who are facing same issue like you can also get solution by your post.

    if you still have a problem regarding that then please let us know so that we can try to provide a further solution.

    last time you had asked to share your file privately but now we did not allowed to get file using our personal ids.

    so you need to use free websites for that.

    Regards

    Deepak 


    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.

    Thursday, June 16, 2016 7:26 AM
    Moderator