Announcement

Collapse
No announcement yet.

Excel sheet cell to Homeseer

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    Excel sheet cell to Homeseer

    I am VERY new to script and I am trying to get a value from an excel sheet in to Homeseer.

    I have an application called Doorway which reads temperatures from a controller for my heating/ltg using a trend controller (www.trend-controls.com)& (www.doorway.co.uk). Doorway has DDE capabilitity and when I set up DDE and copy link into excel it comes up with: {=DOORWAY|DISPLAY!'datapoint(5)' in the formula bar and displays a dynamic value in the excel cell I put it in.

    Can anyone tell me how I could get homeseer to read this value preferably from Doorway itself or an excel spreadsheet using script. Eventually I would also like to write to the doorway values from Homeseer but I need to walk before I can run!!!.

    Can any of you guys please help??

    Thanks

    Steve Herbert

    #2
    Try this script to read a cell in Excel
    <pre class="ip-ubbcode-code-pre">
    sub main()

    File1 = "C:\temp\Book1.xls"

    Set appXL = CreateObject("Excel.Application")

    appXL.Visible = True

    Set wkb = appXL.Workbooks.Open(File1)

    xlsWorkSheet = wkb.Worksheets(1).Name

    rngXL = wkb.ActiveSheet.Range("A1")

    hs.writelog "excel value", rngXL

    wkb.Close True, File1

    appXL.Quit

    Set appXL = Nothing

    End Sub
    </pre>
    💁‍♂️ Support & Customer Service 🙋‍♂️ Sales Questions 🛒 Shop HomeSeer Products

    Comment


      #3
      Thanks for that. Tried it but the script runs and then comes up with:

      Script error in file: excel test.txt 13: Type mismatch 'hs.writelog' in line 15.

      I cant see anything wrong at all as I have copied it as is.

      Also does anyone know a way to stop the excel pop up box asking if I want to update the link?

      Thanks

      Steve

      Comment


        #4
        Steve,
        Did you modify the path to a valid XLS spread sheet?
        Did you modify the line:
        rngXL = wkb.ActiveSheet.Range("A1")
        to point to a valid cell with data?

        If you do not want it "poping up" change the visible line to false instead of true.
        💁‍♂️ Support & Customer Service 🙋‍♂️ Sales Questions 🛒 Shop HomeSeer Products

        Comment


          #5
          Hi

          It is pointing to a valid excel sheet and there is a value in 'A1' cell.

          The pop up box refers to a dde link to the doorway application and I have already tried setting the visible line to false.

          Excel does not open but it still asks if I want to update the link.

          Steve

          Comment


            #6
            How would I get that info into a device value instead of just showing in the log. The info shows perfect in the log
            Thanx
            Tim
            FB Page - https://www.facebook.com/pages/Capt-Tim/209398425902188

            HSTouch Layouts - https://www.facebook.com/media/set/?...5902188&type=3

            Comment


              #7
              fungun,
              Did you mean device value or device string?
              To insert the value into the device string and value:

              below this line:
              hs.writelog "excel value", rngXL

              Put
              hs.SetDeviceString "A1", rngXL
              hs.SetDeviceValue "A1", cint(rngXL)
              This last line could error if there is only text in the field.
              💁‍♂️ Support & Customer Service 🙋‍♂️ Sales Questions 🛒 Shop HomeSeer Products

              Comment


                #8
                Thanks Rupp worked pefect.
                It was set device string.
                Tim
                Why would SetDeviceValue error if only text?
                What would it not error on. Just don't get this scripting stuff quit yet.
                Thanx again
                FB Page - https://www.facebook.com/pages/Capt-Tim/209398425902188

                HSTouch Layouts - https://www.facebook.com/media/set/?...5902188&type=3

                Comment


                  #9
                  Tim,
                  Device value is just that a numeric value where device string can be a string of test or a string of numbers or a mix. So if you tried to store "test" in deviceValue it will error. Did this help?
                  💁‍♂️ Support & Customer Service 🙋‍♂️ Sales Questions 🛒 Shop HomeSeer Products

                  Comment


                    #10
                    yes thank you again. Much clearer now
                    The thing that messed me up the first time is that I was putting hs.SetDeviceString "A1", "excelvalue" instead of "rngXL" . How would a person know that what I did was wrong and what you did was right?

                    your script-sub main()

                    File1 = "C:\temp\Book1.xls"

                    Set appXL = CreateObject("Excel.Application")

                    appXL.Visible = True

                    Set wkb = appXL.Workbooks.Open(File1)

                    xlsWorkSheet = wkb.Worksheets(1).Name

                    rngXL = wkb.ActiveSheet.Range("A1")

                    hs.writelog "excel value", rngXL

                    hs.SetDeviceString "A1", rngXL

                    wkb.Close True, File1

                    appXL.Quit

                    Set appXL = Nothing

                    End Sub

                    What I did--sub main()

                    File1 = "C:\temp\Book1.xls"

                    Set appXL = CreateObject("Excel.Application")

                    appXL.Visible = True

                    Set wkb = appXL.Workbooks.Open(File1)

                    xlsWorkSheet = wkb.Worksheets(1).Name

                    rngXL = wkb.ActiveSheet.Range("A1")

                    hs.writelog "excel value", rngXL

                    hs.SetDeviceString "A1", excelvalue

                    wkb.Close True, File1

                    appXL.Quit

                    Set appXL = Nothing

                    End Sub



                    Tim
                    FB Page - https://www.facebook.com/pages/Capt-Tim/209398425902188

                    HSTouch Layouts - https://www.facebook.com/media/set/?...5902188&type=3

                    Comment


                      #11
                      fungun,
                      It comes with practice. You were attempting to store a variable that was not defined (excelvalue) and I was storing a variable that was defined (rngXL)
                      💁‍♂️ Support & Customer Service 🙋‍♂️ Sales Questions 🛒 Shop HomeSeer Products

                      Comment


                        #12
                        Hi

                        Right, i'm almost there. I have found the problem. Excel reads a DDE link from my doorway application and Homeseer throws up an error when it references the linked value.

                        As soon as i put a static value in it reads fine so there is something wrong with reading the linked value (link as first post). Excel askes if the link wants updating and I think that this is when HS generates the error. If Excel could open the worksheet without asking to update the link I think it might sort it.

                        Any suggestions?

                        Thanks

                        Steve

                        (I said I was new to this!!)

                        Comment


                          #13
                          I have a similair need to read a dde value and put it in a homeseer variable. Did you guys ever figure that out? I would like to do it without having to go through the excel step. Is that possible?
                          I am using OneSix DDE Server. My dde query in excel looks like this:
                          onesix|temp1!input
                          where temp1,2 etc are my sensor names and input is the value I want to put in a variable in homeseer.

                          Comment


                            #14
                            Revisiting this now and I cannot get the script above to work.

                            Error-ActiveX component can't creat object-Excel.Application

                            I don't have Excel on the HSpc, but I do have Microsft Works, which will open .xls files. I did associate .xls files with the Works Spreadsheet app.
                            So how can I get this script to work now?
                            HS 2.3.0.19

                            Thanks,
                            Tim
                            FB Page - https://www.facebook.com/pages/Capt-Tim/209398425902188

                            HSTouch Layouts - https://www.facebook.com/media/set/?...5902188&type=3

                            Comment


                              #15
                              I don't know the call, but you'll have to change the excel app call to point to the works program...IF works can accept VB control.

                              i.e. CreateObject("Excel.Application")
                              would be something like
                              CreateObject("Works_Spresheet_program.Application")

                              HTH gets you to the correct Google search.

                              --Dan
                              Tasker, to a person who does Homeautomation...is like walking up to a Crack Treatment facility with a truck full of 3lb bags of crack. Then for each person that walks in and out smack them in the face with an open bag.

                              Comment

                              Working...
                              X