none
save an exel line every second and pase it in another exel every second RRS feed

  • Question

  • Hi guys. I have a line with like 20-30 variables. (It changes regarded to time etc...I mean the variables in it.)

    I want that line to like "copy paste" it every second in a new line below.

    Basically I want a new line with the new data every second.

    If the program works for 10 seconds than I have 10 lines.

    If I have 1000seconds than it has 1000lines etc.

    How can I do that?


    Wednesday, May 3, 2017 10:25 PM

All replies

  • Hi ZsoltNagy01,

    You should provide the further information more clearly.
    Programming language (VBA, VB.NET, C#...)? 
    target is the same file, the same sheet?
    "copy paste"... copy what?
    ______________
    Ashidacchi
    Thursday, May 4, 2017 8:59 AM
  • Hi,

    For your requirement, some Macro codes may be helpful to do it. To get more suggestions, I would move the thread to Excel for developers forum for more help.

    https://social.msdn.microsoft.com/Forums/office/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,

    Winnie Liang


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

    Thursday, May 4, 2017 9:10 AM
  • Hi ZsoltNagy01,

    Have your question been answered?
    I've made a sample VBA in a button. It enables:
    (1) open a target Excel file (to be pasted)
    (2) set date-time in column 1 (rows from 1 to 10) on source Excel file (where the button is on it) in every 1,000 milliseconds
    (3) and copy column 1 (rows from 1 to 10) and paste it onto the target file

    VBA code in [Copy & Paste] button:
    Private Sub btn_CopyPaste_Click()
        Dim loopCnt As Integer
        ' -- open Past_Target.xlsx
        Dim fileName As String
        Dim targetBook As Workbook
    ' --- fileName (target file): change it according to your environment fileName = "X:\00 北窓舎\01 PCサポート\00 MSDN_TechNet\00 Excel copy in every second\Paste_Target.xlsx" Set targetBook = Workbooks.Open(fileName) targetBook.Activate ' --- For loopCnt = 1 To 10 Cells(loopCnt, 1).Value = Format(DateTime.Now, "mm/dd hh:nn:ss") targetBook.Worksheets(1).Cells(loopCnt, 2).Value = Cells(loopCnt, 1).Value Sleep 1000 ' -- wait for 1,000 milliseconds ("Sleep" is declared in Module1) Next ' --- MsgBox "completed: Copy & Paste in every second (for 10 seconds)" End Sub
    [Note: don't forget making Sub in Module1 ]
    ' --- Sleep
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

    _______________
    Ashidacchi
    Friday, May 5, 2017 4:04 AM
  • Hello,

    I suggest you use Application.OnTime to call a macro repeatedly.

    E.g.

    Public t As Date
    Dim i As Integer
    Sub TimerLoop()
    Dim ws As Worksheet
    i = i + 1
    Debug.Print i
    'Set ws = Sheets("Sheet2")
    'i = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
    'ws.Range(ws.Cells(i, 1), ws.Cells(i, 6)).Value = Sheets("Sheet1").Range("A1:F1").Value
    t = Now + TimeValue("00:00:01")
    Application.OnTime t, "TimerLoop"
    End Sub
    
    Sub stopLoop()
    Application.OnTime EarliestTime:=t, _
    Procedure:="TimerLoop", Schedule:=False
    End Sub
    

    Regards,

    Celeste


    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.

    Friday, May 5, 2017 8:13 AM
    Moderator