Jump to content
Sign in to follow this  
ramirez

Excel query import

Recommended Posts

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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
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.

#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

Share this post


Link to post
Share on other sites
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 by 99ojo

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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 by PsaltyDS

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.