data logging

[Guest Dale1627]

I have set up several AtuomationDirect DL-05's on our network and can now view my data in excel. My problem now is writing macros to move my data from cells on sheet one to cells on sheet 2 into a log every six minutes. I would like to make this macro run whenever the workbook is open as well as save it as the date/time at a predetermined time, then clear cells and start agian. I have a spreadsheet that logs my data now but I have been using "ontime + timevalue" and cannot get it to log at the times I want, plus it just acts plain goofy. I have downloaded the spreadsheets from the downloads by chakorules and still cannot figure this out. Anyone willing to give a poor, VBA illiterate guy a hand? Dale

[Chris Elston 79]

Can you zip up and post the Excel sheet you have now?

[Guest Dale1627]

I played around with it some this morning trying to make it log at predetermined times. Remember that I am still trying to figure out what exactly I am doing. I am starting slow and going to try to add functions as I understand them. Sub LoggingFunction() Dim nextCell As Integer If ThisWorkbook.Sheets(3).Cells(1, 1) = "" Then nextCell = 5 Else nextCell = ThisWorkbook.Sheets(3).Cells(1, 1) End If ThisWorkbook.Sheets(2).Cells(nextCell, 1) = Now() ThisWorkbook.Sheets(2).Cells(nextCell, 2) = ThisWorkbook.Sheets(1).Cells(3, 2) ThisWorkbook.Sheets(2).Cells(nextCell, 3) = ThisWorkbook.Sheets(1).Cells(4, 2) ThisWorkbook.Sheets(2).Cells(nextCell, 4) = ThisWorkbook.Sheets(1).Cells(5, 2) ThisWorkbook.Sheets(2).Cells(nextCell, 5) = ThisWorkbook.Sheets(1).Cells(6, 2) ThisWorkbook.Sheets(2).Cells(nextCell, 6) = ThisWorkbook.Sheets(1).Cells(7, 2) ThisWorkbook.Sheets(2).Cells(nextCell, 7) = ThisWorkbook.Sheets(1).Cells(8, 2) ThisWorkbook.Sheets(2).Cells(nextCell, 8) = ThisWorkbook.Sheets(1).Cells(9, 2) nextCell = nextCell + 1 ThisWorkbook.Sheets(3).Cells(1, 1) = nextCell Application.OnTime TimeValue("18:00:00"), "LoggingFunction" Application.OnTime TimeValue("18:01:00"), "LoggingFunction" Application.OnTime TimeValue("18:02:00"), "LoggingFunction" Application.OnTime TimeValue("18:03:00"), "LoggingFunction" Application.OnTime TimeValue("18:04:00"), "LoggingFunction" Application.OnTime TimeValue("18:05:00"), "LoggingFunction" Exit Sub End Sub I want this to run as soon as the spreadsheet is opened but cannot figure out how to refernce it to a workbook_open. Dale

[Chris Elston 79]

This should work for you. Private Sub Workbook_Open() LoggingFunction End Sub

[Chris Elston 79]

Or you can: Name your macro Auto_Open i.e. Sub Auto_Open() 'your code End Sub

[Timotheos 0]

Hi Dale, For the time interval try this... 'string holding polling interval (i.e. 1:00 = 1 minute) 'this could be set in a form or a cell dim strPollIntervall 'Schedule the next poll time NextTime = Now + TimeValue(strPollInterval) Application.OnTime NextTime, "LoggingFunction" Tim

[Timotheos 0]

Here's the subroutine I use to copy the data to a new worksheet and then start over. My main sheet is called Log and any ones that are created get datetime stamped. I have a variable intRowIndex which keeps track of how many rows have been filled in. I usually trigger this when a sheet is full but you could easily set something up to if rows = 6 then run this subroutine. Hope that helps, Tim Sub CopySheet()         Dim strRowRange As String Dim intMaxColumns As Integer Dim intColumn As Integer On Error GoTo ErrorCopySheet    'Find the number of columns    intMaxColumns = 3 'Initialize the start    'Loop until the column is empty    Do While Sheet1.Cells(1, intMaxColumns).Formula <> ""        If Sheet1.Cells(2, intMaxColumns).Value = "" Then Sheet1.Cells(2, intMaxColumns).Value = 1        intMaxColumns = intMaxColumns + 1    Loop            'Copy what's in the sheet        Sheet1.Range(Cells(3, 1), Cells(intRow + intRowOffset, intMaxColumns - 1)).Copy                'Make a new sheet and rename it        Sheets.Add after:=Worksheets(Worksheets.Count)        ActiveSheet.Name = Month(Now) & "." & Day(Now) & "." & Year(Now) & " Log " & Worksheets.Count - 2        'Paste in the values from the main sheet        ActiveSheet.Range("A1").PasteSpecial                    'Format the columns to auto fit            intColumn = 1            For intColumn = 1 To intMaxColumns                Columns(intColumn).Select                Selection.Columns.AutoFit                ActiveSheet.Cells(1, 1).Select            Next intColumn                'Go back to the main sheet        Worksheets("Log").Select        ActiveSheet.Cells(intRowOffset, 1).Select                'Clear the content of the main sheet and reinitialize        strRowRange = intRowOffset & ":" & intRow + intRowOffset        Sheet1.Rows(strRowRange).ClearContents        ThisWorkbook.Worksheets("Row").Cells(1, 1).Value = 0        intRow = 0         Exit Sub ErrorCopySheet:    MsgBox "Whoops!", vbCritical, "Error" Exit Sub End Sub

[Guest Dale1627]

Hey, I really appreciate both of you giving me a hand. I haven't gotten a chance to try either of them but hopefully tomorrow(8-31-04) I will. I'll let you know how it works. I tried to find a class that would teach VBA this semester but it looks like I am going to have to resort to buying a good book and teaching myself. Dale

[Guest Dale1627]

Chakorules, I took your advice and renamed it Auto_Open. Seems to work great except that I had to change my ontime method to Application.OnTime Now + TimeValue("00:10:00"), "Auto_Open" I have a clear log macro that causes it to go goofy if I use my original ontime method. I'll try to remember to zip and post it on monday when I go back to work so everyone can laugh and poke fun at my elementary macro..... Thanks for the help. Dale

[Guest Dale1627]

I finally managed to get back onto this project and I think that I have a good spreadsheet set up for data logging. It begins logging automatically and logs an entry every 10 minutes. I now have it set up for two different printing presses. I'll attach so anyone interested can critique it and hopefully tell me of any flaws in it. It seems to work fine for me. Thanks for the help. Dale P.S. When you open the spreadsheet, click "Don't Update" or it will try to open DSData and loose the values saved on sheet 1 "Current". PressLog.zip Edited 10 Jan 2005 by Dale1627