Excel query import

[ramirez]

Hi all

I've just searched for answer but can't find one.

I've a webpage that prints some data that is refreshed every 5 sec.

I need to get the data from that page to format it in diferent colors, size, etc...

What I can do until now is to use the page as an external DB so the excel can query it. One sheet is the query and the other is the formated output.

I need to print the result in a LCD so this have to be simple, not an excel sheet with cells and lines and whith a shorter refresh time (excel give me only > 1 min).

I was thinking in use the autoit to get the data from the excel and to print in a frontend (could make this on a GUI).

Can anyone give me some ideas?

Thanks in advance.

[Tvern]

If I understand you correct you are trying to read the data from a website and view it on an LCD screen.

If that's right I'd advise you to cut the middle man and eliminate the excel completely.

There are many ways to grab information from a website. Which one suits you best depends on the level of access you have and the way the website is put together. If you could be more specific it'll be easier to give a good answer.

As for displaying data on an LCD I know it can be done, but don't ask me how.

Tom

[ramirez]

Displaying the info in the LCD is easy. I have a PC connected to the LCD.

Can get a deeper access to the website. It's a page that is geting results from an internal DB that I can't get access.

I think that I have only three options:

1 - through the excel (this one querys the webpage) and then put the excel in fullscreen

2 - through the excel (this one querys the webpage) and then use the autoit to get the info from sheets

3 - using the Autoit to get info directly from the webpage

[ramirez]

Any ideas?

[PsaltyDS]

Displaying the info in the LCD is easy. I have a PC connected to the LCD.

Can get a deeper access to the website. It's a page that is geting results from an internal DB that I can't get access.

I think that I have only three options:

1 - through the excel (this one querys the webpage) and then put the excel in fullscreen

2 - through the excel (this one querys the webpage) and then use the autoit to get the info from sheets

3 - using the Autoit to get info directly from the webpage

4 - Use the IE.au3 UDF and the _IE* functions to get the data straight from the web page.

I think that's what Tvern was suggesting.

If you want to stick with Excel, then look at the Excel.au3 UDF and the _Excel* functions in the help file.

[ramirez]

Hi all

I'v manage to make a script to read from excel but don't know how to make it refresh the data (the sheet refreshes every minute). I also need help on "$Input3" in the excel this cell is a time format but in the script it loses all formating.

expandcollapse popup

#include <Excel.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <DateTimeConstants.au3>

$sFilePath1 = "D:\Documents and Settings\user\My Documents\Book1.xlsx"
Local $oExcel  = _ExcelBookOpen($sFilePath1)
$sCellValue1 = _ExcelReadCell($oExcel, 5, 2)
$sCellValue2 = _ExcelReadCell($oExcel, 5, 3)
$sCellValue3 = _ExcelReadCell($oExcel, 5, 4)

$Form1 = GUICreate("Form1", 633, 454, 192, 114)
$Input1 = GUICtrlCreateInput("Input1", 56, 88, 89, 82)
GUICtrlSetFont(-1, 44, 400, 0, "Lucida Sans Unicode")
GUICtrlSetData(-1, $sCellValue1)

$Input2 = GUICtrlCreateInput("Input2", 232, 88, 121, 82)
GUICtrlSetFont(-1, 47, 400, 0, "MS Sans Serif")
GUICtrlSetData(-1, $sCellValue2)

$Input3 = GUICtrlCreateInput("Input3", 448, 88, 121, 82)
GUICtrlSetFont(-1, 47, 400, 0, "MS Sans Serif")
GUICtrlSetData(-1, $sCellValue3)

$Label1 = GUICtrlCreateLabel("EM CONVERSAÇÃO", 56, 56, 104, 17)
$Label2 = GUICtrlCreateLabel("EM ESPERA", 264, 56, 66, 17)
$Label3 = GUICtrlCreateLabel("TEMPO MEDIO DE ESPERA", 440, 56, 144, 17)

$Edit1 = GUICtrlCreateEdit("", 424, 336, 185, 89)
GUICtrlSetData(-1, $DTS_TIMEFORMAT)

GUISetState(@SW_SHOW)

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit ; Will exit the script 
    EndSwitch
WEnd ; end of loop

[99ojo]

Hi,

i have had the same problem. In excel it was shown like 06.09.2009 11:30 and after import the value was 2009/09/06 11:30.

I wrote this function to get the desired format:

GUICtrlSetData(-1, _transform ($sCellValue3)) ; out of your code

;add this function to your script, you may change it, because i don't know how your format was
; and how it looks like after import. It shows only a way...
Func _transform ($string)
    Local $year, $month, $day, $hour, $min
    $year = Stringleft ($string, 4)
    $month = Stringmid ($string, 5, 2)
    $day = Stringmid ($string, 7, 2)
    $hour = Stringmid ($string, 9, 2)
    $min = Stringmid ($string, 11, 2)
    Return $day & "." & $month & "." & $year & " " & $hour & ":" & $min
EndFunc

This may help you.

;-))

Stefan

Edited November 18, 2009 by 99ojo

[ramirez]

Thanks 99ojo.

Get the idea but the problem here is the time format.

My question here is how to convert the excel time "0,007638889" to correct time "0:11" (min:sec).

Still have trouble in the first task:

"I've manage to make a script to read from excel but don't know how to make it refresh the data (the sheet refreshes every minute)"

Anyone?

[PsaltyDS]

Thanks 99ojo.

Get the idea but the problem here is the time format.

My question here is how to convert the excel time "0,007638889" to correct time "0:11" (min:sec).

The function _ExcelReadCell() returns the .value property. You could try getting the .text property instead, which should be the converted value according to your localization settings. For example:

Func _ExcelReadCellText($oExcel, $sRangeOrRow, $iColumn = 1)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If Not StringRegExp($sRangeOrRow, "[A-Z,a-z]", 0) Then
        If $sRangeOrRow < 1 Then Return SetError(2, 0, 0)
        If $iColumn < 1 Then Return SetError(2, 1, 0)
        Return $oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).Text
    Else
        Return $oExcel.Activesheet.Range($sRangeOrRow).Text
    EndIf
EndFunc   ;==>_ExcelReadCellText

Still have trouble in the first task:

"I've manage to make a script to read from excel but don't know how to make it refresh the data (the sheet refreshes every minute)"

Anyone?

Just read the cells again in a loop.

Edited November 18, 2009 by PsaltyDS