Jump to content

Need help getting position in Excel file


Recommended Posts

I have embeded an Excel file in my autoit Script and want to use it as part of my GUI. I want to be able to select a range of cells and add the cell data to an edit box. Then dump that data to an array. I am using GUICtrlCreateObj to embed the spreadsheet. I cannot find any way to let my program see what cells are active so I can add that data to the edit box. 

 

#include <WindowsConstants.au3>
#include <GUIConstantsEx.au3>
#include <excel.au3>

$FileName = 'C:\VLog\book1.xlsx'
If Not FileExists($FileName) Then
    MsgBox(0, "ERROR", "File not found")
    Exit
EndIf

;Basic GUI
$oExcelDoc = ObjGet($FileName) ; Get an excel Object from an existing filename
If IsObj($oExcelDoc) Then
    $mainGUI = GUICreate("Production Room 2", 1800, 1200, 10, 10, $WS_MINIMIZEBOX + $WS_SYSMENU + $WS_CLIPCHILDREN)
    $GUI_ActiveX = GUICtrlCreateObj($oExcelDoc, 10, 70, @DesktopWidth - 250, @DesktopHeight - 260)
    $data = GUICtrlCreateEdit("", @DesktopWidth - 225, 75, 100)
    $btn = GUICtrlCreateButton( "GO", @DesktopWidth - 100, 75, 75)
Else
    MsgBox(0, "", "failed")
EndIf
;------------------

;Turns off all command bars in excel to prevent user from making changes
For $Bar In $oExcelDoc.CommandBars
    If $Bar.Enabled = True Then $Bar.Enabled = False
    If $Bar.Visible = True Then $Bar.Visible = False
Next
$oExcelDoc.Application.DisplayFormulaBar = False
$oExcelDoc.Application.CommandBars("Shadow Settings").Visible = False
$oExcelDoc.Application.DisplayScrollBars = True
$oExcelDoc.Application.DisplayStatusBar = False

GUISetState()

 

Link to comment
Share on other sites

Something like this?

#include <GUIConstantsEx.au3>
#include <excel.au3>
#include <WindowsConstants.au3>
#include <Array.au3>

Opt("GUIOnEventMode", 1);<=gui event mode

$FileName = 'C:\VLog\book1.xlsx'
If Not FileExists($FileName) Then
    MsgBox(0, "ERROR", "File not found")
    Exit
EndIf

;Basic GUI
$oExcelDoc = ObjGet($FileName) ; Get an excel Object from an existing filename
If IsObj($oExcelDoc) Then
    $mainGUI = GUICreate("Production Room 2", @DesktopWidth-100, @DesktopHeight-100, 10, 10, $WS_MINIMIZEBOX + $WS_SYSMENU + $WS_CLIPCHILDREN)
    GUISetOnEvent($GUI_EVENT_CLOSE, "CLOSEButton");<=gui close event

    $GUI_ActiveX = GUICtrlCreateObj($oExcelDoc, 10, 70, @DesktopWidth - 350, @DesktopHeight - 360)
    $data = GUICtrlCreateEdit("", @DesktopWidth - 325, 175, 100)
    $btn = GUICtrlCreateButton( "GO", @DesktopWidth - 200, 175, 75)
    GUICtrlSetOnEvent($btn, "GetData");<=button function

Else
    MsgBox(0, "", "failed")
EndIf
;------------------

;Turns off all command bars in excel to prevent user from making changes
For $Bar In $oExcelDoc.CommandBars
    If $Bar.Enabled = True Then $Bar.Enabled = False
    If $Bar.Visible = True Then $Bar.Visible = False
Next
$oExcelDoc.Application.DisplayFormulaBar = False
$oExcelDoc.Application.CommandBars("Shadow Settings").Visible = False
$oExcelDoc.Application.DisplayScrollBars = True
$oExcelDoc.Application.DisplayStatusBar = False

GUISetState()


While 1;<=loop to keep gui alive
    Sleep(10)
WEnd

Func GetData();<=get data grom selected range
    local $Mydata
    $selectedRange = $oExcelDoc.Application.Selection

    For $cel In $selectedRange.Cells
        ConsoleWrite($cel.Address&"="&$cel.Value&@CRLF)
        $Mydata &= $cel.Value&@CRLF
    Next
    GUICtrlSetData($data, $Mydata)
    $arr = StringSplit($Mydata,@CRLF,1)
    _ArrayDisplay($arr)
EndFunc

Func CLOSEButton()
    Exit
EndFunc

 

Edited by bogQ

TCP server and client - Learning about TCP servers and clients connection
Au3 oIrrlicht - Irrlicht project
Au3impact - Another 3D DLL game engine for autoit. (3impact 3Drad related)



460px-Thief-4-temp-banner.jpg
There are those that believe that the perfect heist lies in the preparation.
Some say that it’s all in the timing, seizing the right opportunity. Others even say it’s the ability to leave no trace behind, be a ghost.

 
Link to comment
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
 Share

×
×
  • Create New...