none
Using Macros within Excel documents stored in SharePoint document library RRS feed

  • Question

  • Hello,

    We recently migrated a client's data from an on-premise fileserver to SharePoint online. They have a set of excel documents that contain a lot of varied data. There is one master document that contains a macro which they need to run. The master document opens ok from SharePoint however when we try to run the Macro it give an erro '52 Bad file name or number'. If the master file and the relevant spreadsheets are copied locally then everything works fine.

    Looking at the debug information it highlights the following (in bold):

    Dim Sht As Worksheet
    Path = Application.ActiveWorkbook.Path
    Filename = Dir(Path & "\*.xlsx")
      Do While Filename <> ""
      Workbooks.Open Filename:=Path & "\" & Filename, ReadOnly:=True
         For Each Sheet In ActiveWorkbook.Sheets
         Sheet.Copy After:=ThisWorkbook.Sheets("Total Sheet")
      Next Sheet
         Workbooks(Filename).Close savechanges:=False
         Filename = Dir()
      Loop

    I realise it might just be the format of the code that needs to be changed.

    Does anyone have any idea of how to fix this?

    We didn't design this macro and to be honest I don't have much knowledge on visual basic.

    Any help appreciated.

    Thursday, February 1, 2018 3:05 PM

Answers

  • Hi thegnic,

    I can see that you are trying to run VBA code on SharePoint Online.

    You cannot run VBA code Online.

    You can only run it on local machine.

    So It is not possible to run your macro code on SharePoint online.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by thegnic Wednesday, February 7, 2018 2:20 PM
    Friday, February 2, 2018 6:22 AM
    Moderator

All replies

  • Hi thegnic,

    I can see that you are trying to run VBA code on SharePoint Online.

    You cannot run VBA code Online.

    You can only run it on local machine.

    So It is not possible to run your macro code on SharePoint online.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by thegnic Wednesday, February 7, 2018 2:20 PM
    Friday, February 2, 2018 6:22 AM
    Moderator
  • Hi thegnic,

    >> The master document opens ok from SharePoint however when we try to run the Macro it give an erro '52 Bad file name or number'

    How did you open the master document? Did you open it from local?

    How did you run the Macro? Could you share us the sample documents and detail steps to reproduce your issue?

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 6, 2018 5:48 AM
  • Hi thegnic,

    Is your issue solved?

    I find that you did not follow up this thread after posting the issue.

    If your issue is solved then I suggest you to post your solution and mark it as an answer.

    If your issue is still exist then try to refer the solution given by the community members.

    If then also you have any further questions then let us know about it.

    We will try to provide further suggestions to solve the issue.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 7, 2018 9:40 AM
    Moderator
  • Hello Tao Zhou

    Thank you for your reply. We are testing opening the master document from a SharePoint document library, we then tried running the Macro from within Excel.

    I can see that Deepak has stated that VBA code is not compatible with SharePoint Online - our client is happy now knowing this. They are now updating the document's data whilst in SharePoint and then downloading a copy of the files, then running the Macros without any issue.

    Thanks for your help.

    Wednesday, February 7, 2018 2:20 PM