writing to excel

[MrPike]

It appears the IO stream writer won't handle the DateTime.Now function string.  I get the attached error.  Is there way I can create or save the file with the date & time in the filename?

[Noe]

Maybe if you try to get that converted to a string before?

[MrPike]

I'm still trying to work this out.  The project I created is used on many computers owned by others.   That creates an issue because if the PC date has the slash in it such as 11\2\15, then excel does not accept it since you cannot have a slash in the file name. I cant get everyone to change their date format so I need to create a different solution.  I would like to create a file that is "save as" but not sure how.  Also I would like to overwrite the old data rather than append it.

Archie, is there a code change that I can apply that would overwrite the data in the log each time the log is started?  How would I make this "save as" so that the user could name the file when they stop the datalogger on a button click?, thank you.   

[Archie]

You can format the Now() function like this:

Now.ToString("ddMMyyyy")

That will eliminate the slash problem

[MrPike]

Ok, I almost have this working.  Just a couple issues.
1)  What is the proper way to disable the BasicdataLogger ?  I want to enable/disable based on a checkbox.  This is the code I have and it kind of works.

 Private Sub CheckBox1_CheckedChanged(sender As Object, e As EventArgs) Handles CheckBox1.CheckedChanged
        BasicDataLogger21.LogInterval = TextBox6.Text
        If CheckBox1.Checked = True Then
            BasicDataLogger21.CommComponent = ModbusTCPCom1
            BasicDataLogger21.FileName = String.Concat(Date1, time1, file1)

        Else
            BasicDataLogger21.CommComponent = Nothing
        End If

    End Sub

2)  when I enable the Basicdatalogger using the checkbow, the filename created is a concat of Date, time, and Device name.  However I cannot get the Now() function to change time unless I close the project and reopen it.  How can I get the Now() function to update when I uncheck and recheck the checkbox?

Sorry for any confusion.  My goal is to create a unique name for the log each time the Checkbox1 is checked.  Thanks

[Archie]

If you use the BasicDataLogger2, it has a PauseLogging property. Otherwise the best way would be to create a new driver instance to be used solely for the BasicDataLogger. After adding the new driver to the form, set the ComComponent property of the data logger to that driver. Then you can use DisableSubscriptions property of the driver instance.

[MrPike]

Ok I have the files created being created the way I need.  Now I need to populate the first row to create the units for the data below.  I thought about a header but thought it would be just as easy to write to the first cell of each column with the "units'.  Such as column 1 is KW, column 2 as KVAR etc...  I added this code but get the error attached.  What am I doing wrong?  Thx


  Private Sub Button7_Click(sender As Object, e As EventArgs) Handles Button7.Click
        Try
            Dim sr As New System.IO.FileInfo("C:\DSLC2View\Logs\05Nov2015_08.34.20_GEN1 DATALOG")
            EP = New OfficeOpenXml.ExcelPackage(sr)

            EP.Workbook.Worksheets(1).Cells(1, 1).Value = "KW"
            EP.Workbook.Worksheets(1).Cells(1, 2).Value = "KVAR"



            EP.Save()


        Catch ex As Exception
            MsgBox("Failed to write. " & ex.Message)
        End Try


    End Sub

[MrPike]

I have found that this works if I write to an .xlsx file not a .csv file.   How do I modify this code to do the same thing to a .csv file?

  Private EP As OfficeOpenXml.ExcelPackage
   
    Private Sub Button7_Click(sender As Object, e As EventArgs) Handles Button7.Click
        Try
            Dim sr As New System.IO.FileInfo("C:\DSLC2View\Logs\GEN1_DATALOG.xlsx")
            EP = New OfficeOpenXml.ExcelPackage(sr)

            EP.Workbook.Worksheets(1).Cells(1, 1).Value = "KW"
            EP.Workbook.Worksheets(1).Cells(1, 2).Value = "KVAR"
            EP.Save()


        Catch ex As Exception
            MsgBox("Failed to write. " & ex.Message)
        End Try

[MrPike]

Archie, seems I'm having big trouble with a little problem.  I finally was able to create a new datalogger file as I want it except for the following issue:

I want to populate the first row of the columns with the units for the data below it, like seen in "correctpic"

The trouble I have is the first line of data is shifted as seen in "wrongpic"

I see why it is by the code seen in "code" but don't know how to correct it and make the first row of data start at column 1.

  Is there a easier or better way to put these "units" in the first row?
  Am I missing a field of the DataLogger that does this? ....Prefix?

I feel like I'm making it harder than necessary.  Please help, thanks. 

[DougLyons]

Mr. Pike,

Try changing your w.Write statement to this:

Code: [Select]

       w.Write("TIME, KW, KVAR, VAB, VBC, VCA, IA, IB, IC, HZ" & Chr(13) & Chr(10))

This adds a Carriage Return and Line Feed to your write to get down to the next line.

Doug Lyons

[MrPike]

Thank you, I was able to correct this by replacing w.write with w.writeline().  This command issues a line break and the datalogger will begin on the next line.  Thank you for the reply.  I'm learning slowly LOL

[MrPike]

Does anyone know if writing text to the .csv as  column headers can be set to the property bindings of the Datasubscriber so that the code would not be necessary?  Thank you