Can I save device values into a Excel spreadsheet ?

[Uncle Michael]

The command appends data to an existing file. Does the file "Powerdata.csv" exist? A file that simply contains column headings would be a place to start.

[jon00]

The method to save/append a file in the script is old VBScript stuff and does not create the file if missing.

You can update the script as below:

Public Sub Main(ByVal Parms As Object)

Dim strFileName As String = "w:\HS4 Data\Powerdata.csv"
Dim strFileName2 As String = "c:\HS4 Data\Powerdata.csv"
Dim strData As String = ""
Dim dteDate As Date = DateTime.Now
Dim decTRain As Decimal = hs.DeviceValueEx(2593)
Dim GridPowerKWh As Decimal = hs.DeviceValueEx(3335)
Dim SolarPowerKWh As Integer = hs.DeviceValue(3334)

strData = dteDate.ToShortDateString & "," & dteDate.DayOfYear & "," & decTRain.ToString & "," & GridPowerKWh.ToString & "," & SolarPowerKWh.ToString & vbCrLf
My.Computer.FileSystem.WriteAllText(strFileName2, strData, True)

End Sub

[alexbk66]

I have tried adapting your script. But I am getting a error
Running script DataValuestoExcel.vb :Exception has been thrown by the target of an invocation.

When you get an exception - obvious solution would be to add try/catch block around your code - then you get the exact location of the problem, instead of trying to guess

[mikee123]

When you get an exception - obvious solution would be to add try/catch block around your code - then you get the exact location of the problem, instead of trying to guess

How / where would I do that ?

[mikee123]

The method to save/append a file in the script is old VBScript stuff and does not create the file if missing.

You can update the script as below:

I have updated the script. I am now getting data written to the csv file. One problem.The problem is the way its writte to file, and I think I know why. Its writing data to column A,B,C,and D. Thats ok, but the next time I run the script, rather than writing now to A2,B2,C2 and D2 its writing to E,F,G,and H. (That is when I open the csv file in Excel)

Public Sub Main(ByVal Parms As Object)

Dim strFileName As String = "w:\HS4 Data\Powerdata.csv"
Dim strFileName2 As String = "c:\HS4 Data\Powerdata.csv"
Dim strData As String
Dim dteDate As Date = NOW
Dim strDay As String
Dim decTRain As Decimal = hs.DeviceValueEx(2593)
Dim GridPowerKWh As Decimal = hs.DeviceValueEx(3335)
Dim SolarPowerKWh As Integer = hs.DeviceValueEx(3334)


strData = dteDate.ToShortDateString & "," & dteDate.DayOfYear & "," & decTRain.ToString & "," & GridPowerKWh.ToString & "," & SolarPowerKWh.ToString & ","

My.Computer.FileSystem.WriteAllText(strFileName, strData, True)
My.Computer.FileSystem.WriteAllText(strFileName2, strData, True)


hs.WriteLog("Write to Ecel", "Power data exported to Excel")


End Sub

[jon00]

As I posted before, your strdata line should be:

strData = dteDate.ToShortDateString & "," & dteDate.DayOfYear & "," & decTRain.ToString & "," & GridPowerKWh.ToString & "," & SolarPowerKWh.ToString & vbCrLf

The vbCrLf creates a new line....

[alexbk66]

How / where would I do that ?

Read about exceptions. https://www.tutorialspoint.com/vb.ne...n_handling.htm
You should always have exception handling, especially during development. I.e.

Try
  your code here
Catch e As Exception
  Console.WriteLine("Exception caught: {0}", e)
End Try

Detailed info here https://docs.microsoft.com/en-us/dot...ally-statement

[mikee123]

As I posted before, your strdata line should be:

strData = dteDate.ToShortDateString & "," & dteDate.DayOfYear & "," & decTRain.ToString & "," & GridPowerKWh.ToString & "," & SolarPowerKWh.ToString & vbCrLf

The vbCrLf creates a new line....

Yes that did it

[mikee123]

Read about exceptions. https://www.tutorialspoint.com/vb.ne...n_handling.htm
You should always have exception handling, especially during development. I.e.

Try
your code here
Catch e As Exception
Console.WriteLine("Exception caught: {0}", e)
End Try

Detailed info here https://docs.microsoft.com/en-us/dot...ally-statement

Ok that should help me in the future trying to find faults