Read data from 2 sheets

[Vikramjeet]

expandcollapse popup

#include-once
#include <File.au3>
#include <MsgBoxConstants.au3>

Global $filePath

$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible =  0

WinActivate("Notepad")
Global $hWnd = WinWaitActive("Notepad", "", 5)

$filePath = _PathFull("Details.xlsm")

$oExcel.Application.WorkBooks.Open ($filePath)

Global $HomeStation1 = $oExcel.Application.Cells(7,2).Value
Global $AccountCode1 = $oExcel.Application.Cells(7,3).Value
Global $OAC1 = $oExcel.Application.Cells(7,4).Value
Global $Suffix1 = $oExcel.Application.Cells(7,5).Value
Global $AUTH1 = $oExcel.Application.Cells(7,6).Value

START ()
Func START ()
    Send("{ESC}")
    Sleep(700)
    Send("I")
    Sleep(700)
    Send("{ENTER}")
    Sleep(2000)
    ;I want to pick up data from Sheet 2, cell B2 and send it to notepad
    Send("{ESC}")
    Sleep(700)
    Send("AAA"& $HomeStation1) ;This data comes from Sheet 1
    Sleep(2000)
    Send("{ENTER}")
    Sleep(2000)
    Send("{ESC}")
    Sleep(700)
    $oExcel.Application.Quit
    MsgBox(4096, "INFORMATION", "Processing Complete")
EndFunc

I am trying a script that picks data from an excel file and sends it to an open notepad file. I am not able to get my head around how to switch between 2 sheets within one Excel file. My Excel file 'Details.xlsm' has 2 sheets. I want to be able to read data from both the sheets without opening the file.
Your help with script is much appreciated

[Subz]

Why not just use the _Excel functions, they have a number of examples within the help file?  Also it would probably easier to just use FileWrite to write the data to a text file.

Edit: If you post an example spreadsheet I can look into it further.

Edited March 20, 2020 by Subz

[Vikramjeet]

Thank you for your help. Excel file attached

Details.xlsm

[Subz]

Here are the basics, hopefully you can follow, just look at the help file for more info:

#include <Array.au3>
#include <Excel.au3>
Local $sWorkbook = @ScriptDir & "\Details.xlsx"
Local $oExcel = _Excel_Open()
Local $oWorkbook
$oWorkbook = _Excel_BookAttach($sWorkbook)
    If @error Then $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
Local $aEPR_Data = _Excel_RangeRead($oWorkbook, $oWorkbook.Sheets("EPR Data"), "B7:F7")
_ArrayDisplay($aEPR_Data)
Local $sSheet1B2 = _Excel_RangeRead($oWorkbook, $oWorkbook.Sheets("Sheet1"), "B2")
MsgBox(4096, "Sheet1", "Month: " & $sSheet1B2)
_Excel_Close($oExcel, False)

FileWrite(@ScriptDir & "\Details.txt", "I" & @CRLF & $sSheet1B2 & "AAA" & $aEPR_Data[0][0] & @CRLF & _ArrayToString($aEPR_Data, @CRLF))

 

[Vikramjeet]

Getting Error: Variable must be type 'Object'. on line 8. See attached

[Subz]

Did you change the name from Details.xlsx to Details.xlsm (I renamed it for security purposes).

[Vikramjeet]

Got it. Thank you so much!!!

Edited March 20, 2020 by Vikramjeet