none
Using Macro to populate values in to excel sheet RRS feed

  • Question

  • I have one excel workbook and originally it got two sheets named R1 and R2. There was some vba code (macro) to manipulate the data based on some interval. Workbook has below codes. This is basically DDE (Dynamic Data Exchange ) Excel.

    under ThisWorkBook

    Private Sub Workbook_Open()
    Application.OnTime Now + TimeValue("00:00:02"), "Main"
    End Sub

    under Module1

    Public Time1, Time2, Time3 As Date
    Sub Main()

        Time1 = Now + TimeValue("00:00:10")
        Time2 = Now + TimeValue("00:00:04")
        Time3 = Now + TimeValue("00:00:04")
        Application.OnTime Time1, "Main"

       '' do something

        Application.OnTime Time2, "R1"
        Application.OnTime Time3, "R2"

    End Sub

    Sub R1()

    //do something

    Dim rngR1 As Range

    Dim wc as integer

    wc=45

    Set rngR1 = Sheets("R1").Range(Range("D" & (wc+ 4)), Range("N63"))
    rngR1.Value = ""

    //do something

    End Sub

    Sub R2()

    //do something

    Dim rngR2 As Range

    Dim wc as integer

    wc=45

    Set rngR2 = Sheets("R2").Range(Range("D" & (wc+ 4)), Range("N63"))
    rngR2.Value = ""

    //do something

    End Sub

    ----------------------

    Now I want to create two excel work books separated for two methods - R1 and R2. I had created this and kept both the excel open to verify if it is working. I found that when both the excels are open, i am getting exception for in the line of range selection. Can anyone help to resolve this issue?


    Rgds, Abhishek

    Thursday, December 6, 2018 3:44 PM

All replies

  • Sheets("R1").Range(Range("D" & (wc+ 4)), Range("N63")) refers to a worksheet R1 in the activeworkbook. If it is in another workbook, you must specify that:

    Workbooks("SomeBook1.xlsx").Sheets("R1").Range(Range("D" & (wc+ 4)), Range("N63"))

    where SomeBook1.xlsx is the name of the workbook containing R1.

    Similar for R2.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, December 6, 2018 4:34 PM
  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Friday, December 7, 2018 7:07 AM
  • Hi Abhishek37558,

    Thanks for your asking. Please remember to mark the replies as answers if they helped and please help us close the thread.

     

    Thank you for understanding. If you have any question, or update, please feel free to let us know.

     

    I wish you a happy life!

     

    Best Regards,

     

    Lina


    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.

    Wednesday, December 12, 2018 2:53 AM