locked
How to prevent VBA from attempting to reopen excel file being written to. RRS feed

  • Question

  • I am relatively new to VBA. I wrote a sub procedure to populate cells in another workbook created from an excel template by the previous sub procedure.

    I did this at home using excel in office 365 and everything worked well. I tried  to used the same identical code at work by changing the file path to the template folder and destination folder. The first sub for creating the workbook and corresponding folder work well, however as soon as data is pasted to the first cell of the worksheet, I get the prompt  "myworkbook.xlsx is already open. Reopening will cause any changes you made to be discarded. Do you want to reopen myworkbook.xlsx".

    When I select yes, the data in the previously filled cells are discarded as expected and when I select 'no' the sub  procedure ends with an error. I tried to work around this by saving the file after each copy/paste. For some reason this doesn't work, instead, I get "runtime error 9... subscript out of range".

    At work, I use office 2013 on a network server through remote desktop. What baffles me is why the code still runs perfectly on my computer running office 365 , but fails to work where I actually need it?

    The prompts begin to appear after the second paste:

    RheinhardtWs.range(Cathode.Offset(0, -1).Address, Anode.Offset(0, -1).Address).Copy Workbooks.Open(final_form_name).Worksheets("fabSheet").range("C15") '1st paste
    RheinhardtWs.range(Cathode.Address, Anode.Address).Copy Workbooks.Open(final_form_name).Worksheets("fabSheet").range("D15") '2nd paste

     "myworkbook.xlsx is already open. Reopening will cause any changes you made to be discarded. Do you want to reopen myworkbook.xlsx".

    In summary, the code works without issues on Office 365 which I use at home. At work, every copy and paste in the code attempts to reopen the file being written to.

    The only difference between my setup at home and at work is that;

    At home, I use office 365 on Windows 10 Pro, and

    At work, I use office 2013 through remote desktop on Server 2012

    Saturday, October 26, 2019 9:37 PM

Answers

  •  

    To:  Em'Oh
    re:  copy and paste

    The code you posted shows two Open statements.
    That would be why Excel is asking you if you want to reopen.
    You only need to open a workbook once.  It stays open until you close it.
    So something like this should work (not tested)...
    '---
    [Edited]
    Workbooks.Open(final_form_name)
    With Workbooks(final_form_name)
      RheinhardtWs.Range(Cathode.Offset(0, -1).Address, Anode.Offset(0, -1).Address).Copy _
      Destination:=.Worksheets("fabSheet").Range("C15")   '1st paste

      RheinhardtWs.Range(Cathode.Address, Anode.Address).Copy _
      Destination:=.Worksheets("fabSheet").Range("D15")   '2nd paste
    End With
    '---
    I think that the problem is with Office 365 at home.
    Also, the argument name (Destination) is not required, but it does explain what is going on.


    '---
    Excel programs (now free) at MediaFire...
    http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents



    • Edited by Nothing Left to Lose Sunday, October 27, 2019 12:19 AM
    • Marked as answer by Em'Oh Wednesday, October 30, 2019 7:22 AM
    Sunday, October 27, 2019 12:08 AM

All replies

  •  

    To:  Em'Oh
    re:  copy and paste

    The code you posted shows two Open statements.
    That would be why Excel is asking you if you want to reopen.
    You only need to open a workbook once.  It stays open until you close it.
    So something like this should work (not tested)...
    '---
    [Edited]
    Workbooks.Open(final_form_name)
    With Workbooks(final_form_name)
      RheinhardtWs.Range(Cathode.Offset(0, -1).Address, Anode.Offset(0, -1).Address).Copy _
      Destination:=.Worksheets("fabSheet").Range("C15")   '1st paste

      RheinhardtWs.Range(Cathode.Address, Anode.Address).Copy _
      Destination:=.Worksheets("fabSheet").Range("D15")   '2nd paste
    End With
    '---
    I think that the problem is with Office 365 at home.
    Also, the argument name (Destination) is not required, but it does explain what is going on.


    '---
    Excel programs (now free) at MediaFire...
    http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents



    • Edited by Nothing Left to Lose Sunday, October 27, 2019 12:19 AM
    • Marked as answer by Em'Oh Wednesday, October 30, 2019 7:22 AM
    Sunday, October 27, 2019 12:08 AM
  • Thank you, It worked.
    Wednesday, October 30, 2019 7:22 AM