Jump to content

Recommended Posts

Hello,

I'm trying to be able to switch back and forth between multiple excel spreadsheets and I can't seem to get the WinActivate function to work, and bring the desired window the be the active window.

Could I please get some assistance, I've tried a few things and nothing seems to work quite right. Below is a test case where I'm just trying to make the first excel sheet that was opened become the active window, and testing it by grabbing a cell value off that workbook. The message box produces the correct answer if both files are closed before running but the 2nd test file will appear to be the active window. If the code is run again without closing the excel files, nothing works (file does not appear to be active and message box will not give an answer).

 

#include <Excel.au3>

Opt("WinTitleMatchMode", 2)     ;1=start, 2=subStr, 3=exact, 4=advanced, -1 to -4=Nocase

;Open Test1 Excel Workbook
local $oExcel = _Excel_open()
Local $ofile = @ScriptDir & "\test1.xlsx"
Local $oWorkbook = _Excel_BookOpen($oExcel,$ofile)


;Open Test2 Excel Workbook
local $mExcel = _Excel_open()
Local $mfile =  @ScriptDir & "\test2.xlsx"
Local $mWorkbook = _Excel_BookOpen($mExcel,$mfile) ; This workbook is completely blank


WinActivate($oWorkbook); should make Test1 the active window
local $read1 = _Excel_RangeRead($oWorkbook,Default,"B2"); Cell B1 in Test1 workbook contains the word Test
MsgBox(0,0,$read1);Should returns the word test

 

Share this post


Link to post
Share on other sites

Do _Excel_Open once

Then the WorkBooks.

Don't need to switch Windows to get the values:

#include <Excel.au3>
Local $oExcel = _Excel_Open()
;Open Test1 Excel Workbook
Local $ofile = @ScriptDir & "\test1.xlsx"
Local $oWorkbook = _Excel_BookOpen($oExcel, $ofile)
;Open Test2 Excel Workbook
Local $mfile = @ScriptDir & "\test2.xlsx"
Local $mWorkbook = _Excel_BookOpen($oExcel, $mfile)
;Read Workbook data and display
Local $read1 = _Excel_RangeRead($oWorkbook, Default, "B2") ; Cell B1 in Test1 workbook contains the word Test1
Local $read2 = _Excel_RangeRead($mWorkbook, Default, "B2") ; Cell B1 in Test2 workbook contains the word Test2
MsgBox(0, 0, $read1 & @CR & $read2) ;Returns the correct values.

 

Share this post


Link to post
Share on other sites

To check if the workbook is already opened you can use the following:

#include <Excel.au3>

Local $sFileName1 = @ScriptDir & "\test1.xlsx"
Local $sFileName2 = @ScriptDir & "\test2.xlsx"

Local $oExcel = _Excel_Open()

Local $oWorkbook1, $oWorkbook2

;Open Test1 Excel Workbook
$oWorkbook1 = _Excel_BookAttach($sFileName1)
    If @error Then $oWorkbook1 = _Excel_BookOpen($oExcel, $sFileName1)

;Open Test2 Excel Workbook
 $oWorkbook2 = _Excel_BookAttach($sFileName2)
    If @error Then $oWorkbook2 = _Excel_BookOpen($oExcel, $sFileName2)

;Read Workbook data and display
Local $sWB1CellB2, $sWB2CellB2
$sWB1CellB2 = IsObj($oWorkbook1) ? _Excel_RangeRead($oWorkbook1, Default, "B2") : "" ; Cell B2 in Test1 workbook contains the word Test1, if $oWorkbook1 is not an object then return empty string
$sWB2CellB2 = IsObj($oWorkbook2) ? _Excel_RangeRead($oWorkbook2, Default, "B2") : "" ; Cell B2 in Test2 workbook contains the word Test2, if $oWorkbook2 is not an object then return empty string
MsgBox(0, 0, $sWB1CellB2 & @CR & $sWB2CellB2) ;Returns the correct values.

 

Share this post


Link to post
Share on other sites

@JoHanatCent That didn't work, I just get back the value from 2nd workbook

@Nine Couldn't get that to work either, kept getting an error

@Subz Works great for being able to grab values from both workbooks. I was able to use your code to figure out how write between workbooks.

How could I go from here to copy over charts?

 

I found from you Subz on another forum this code for copying charts into Outlook and got it to work:

#include <Excel.au3>

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Chart.xlsx")
Local $oRange = $oWorkbook.ActiveSheet.Range("A1:G34").Select
    $oExcel.CopyObjectsWithCells = True
    $oExcel.Selection.Copy

Local $oOutlook = ObjCreate("Outlook.Application")
Local $oMail = $oOutlook.CreateItem(0)
    $oMail.Display
    $oMail.To = "sample@example.com"
    $oMail.Subject = "Sample Subject"

Local $sBodyHeader = "Hello" & @CRLF & @CRLF & "Please find charts below." & @CRLF & @CRLF
Local $sBodyFooter = @CRLF & @CRLF & "Regards Subz"

Local $oWordEditor = $oOutlook.ActiveInspector.wordEditor
    $oWordEditor.Range(0, 0).Select
    $oWordEditor.Application.Selection.TypeText($sBodyHeader)
    $oWordEditor.Application.Selection.Paste
    $oWordEditor.Application.Selection.TypeText($sBodyFooter)

$oMail.Display

But I can't seem to get it to work with Excel WB1 to WB2.

Local $oRange = $oWorkbook1.ActiveSheet.Range("A7:H20").Select    creates an error, It probably would be better just to call Chart 1 but since I can't get it to compile I can't test to see if something like Local $oRange = $oWorkbook1.ActiveSheet.Charts("Chart 1").Select would work.

#include <Excel.au3>

Local $sFileName1 = @ScriptDir & "\test1.xlsx"
Local $sFileName2 = @ScriptDir & "\test2.xlsx"

Local $oExcel = _Excel_Open()

Local $oWorkbook1, $oWorkbook2

;Open Test1 Excel Workbook
$oWorkbook1 = _Excel_BookAttach($sFileName1)
    If @error Then $oWorkbook1 = _Excel_BookOpen($oExcel, $sFileName1)

Local $oRange = $oWorkbook1.ActiveSheet.Range("A7:H20").Select
    $oExcel.CopyObjectsWithCells = True
    $oExcel.Selection.Copy


;Open Test2 Excel Workbook
 $oWorkbook2 = _Excel_BookAttach($sFileName2)
    If @error Then $oWorkbook2 = _Excel_BookOpen($oExcel, $sFileName2)

;Read Workbook data and display
Local $sWB1CellB2, $sWB2CellB2
$sWB1CellB2 = IsObj($oWorkbook1) ? _Excel_RangeRead($oWorkbook1, Default, "B2") : "" ; Cell B2 in Test1 workbook contains the word Test1, if $oWorkbook1 is not an object then return empty string
;$sWB2CellB2 = IsObj($oWorkbook2) ? _Excel_RangeRead($oWorkbook2, Default, "B2") : "" ; Cell B2 in Test2 workbook contains the word Test2, if $oWorkbook2 is not an object then return empty string
MsgBox(0, 0, $sWB1CellB2 & @CR & $sWB2CellB2) ;Returns the correct values.

;_Excel_RangeWrite($oWorkbook1,$oWorkbook1.activesheet,"test","C4")
$oWorkbook2.ActiveSheet.Range("A7:H20").paste

 

 

 

Share this post


Link to post
Share on other sites

Just the last line doesn't wok, unfortunately don't have a lot of time, but just replace the last line with:

$oWorkbook2.ActiveSheet.Range("A7").Select
$oWorkbook2.ActiveSheet.Paste

 

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

  • Similar Content

    • By VinMe
      Dear all, i am unable to open a xml file to excel in the "xml table format" Please help me out in where i am missing
      Local $strFileToOpen = _WinAPI_OpenFileDlg('Select xml file', @WorkingDir, 'All Files(*.*)', 1, '', '', BitOR($OFN_PATHMUSTEXIST, $OFN_FILEMUSTEXIST, $OFN_HIDEREADONLY)) Global $xlXmlLoadImportToList = 2 ; Places the contents of the XML data file in an XML table $oExcel = _Excel_Open() $oWorkbook1=$oExcel.Workbooks.OpenXML($strFileToOpen, "", $xlXmlLoadImportToList) If $strFileToOpen <> False Then     Local $oWorkbook1 = _Excel_BookOpen($oExcel, $strFileToOpen) EndIf Error i am getting is:
      ......\81e_Compare_v1.au3" (46) : ==> The requested action with this object has failed.:
      $oWorkbook1=$oExcel.Workbooks.OpenXML($strFileToOpen, "", $xlXmlLoadImportToList)
      $oWorkbook1=$oExcel.Workbooks^ ERROR
      >Exit code: 1    Time: 7.338
    • By VinMe
      Dear all, 
      I am unable to get the right result after applying the filter to the excel. please let me know on the same.
      issue: After applying the filter the output $lastRow11 not giving the right output of complete visible rows. (its breaking at row skips)
       
      ;DATA EXTRACTION FROM LOC EXCEL
      ;=============================================================================
      $oWorkbook = _Excel_BookAttach($sWorkbook)
      Local $sMSN = InputBox("MSN NO", "Enter MSN in XX FORMAT", "")
      ;~ Local $LastRow1 = ($oWorkbook.ACTIVESHEET.Range("A1").SpecialCells($xlCellTypeLastCell).Row)
      $LastRow1 = $oWorkbook.ActiveSheet.UsedRange.Rows.Count
      MsgBox(0, "lastrow1", $LastRow1)
      _Excel_FilterSet($oWorkbook, $oWorkbook.activesheet, "AF1", 32, "*" & $sMSN & "*")
      Local $oLocDS = $oWorkbook.ActiveSheet.Range("S1:S" & $LastRow1).SpecialCells($xlCellTypeVisible)
      Local $LastRow11 = $oLocDS.rows.count    ;error output
      MsgBox(0, "lastrow11", $LastRow11)
      Local $aLocDS1 = _Excel_RangeRead($oWorkbook, Default, $oLocDS)
      Local $oLocNr = $oWorkbook.ActiveSheet.Range("A1:A" & $LastRow1).SpecialCells($xlCellTypeVisible)
      Local $aLocNr1 = _Excel_RangeRead($oWorkbook, Default, $oLocNr)
      _ArrayDisplay($aLocDS1)
      _ArrayDisplay($aLocNr1)
      _ArrayTrim($aLocDS1, 6, 1)
      _ArrayTrim($aLocNr1, 6, 1)
      _ArrayTrim($aLocNr1, 6, 0)
      _ArrayDisplay($aLocDS1)
      _ArrayDisplay($aLocNr1)
    • By VinMe
      I am unable to execute the below script, my requirement is to copy the content from active excel sheet and to display the same.
      Please let me know where i am missing!
      #include <Excel.au3>
      #include <MsgBoxConstants.au3>
      #include <Array.au3>
      #include <StringConstants.au3>
      Local $oExcel = _Excel_Open()
      $LastRow2 = $oExcel.UsedRange.Rows.Count
      $Tissue = _Excel_RangeRead($oExcel, Default, "E1:E" & $LastRow2)
      $TshNr = _Excel_RangeRead($oExcel, Default, "F1:F" & $LastRow2)
      _ArrayDisplay($Tissue)
      _ArrayDisplay($TshNr)
    • By _leo_
      Hey there! 😃
      I am having a problem with the _Excel_RangeFind. I am trying to search for a value in a particular cell range. The script copies the value from the internet. Copying and saving as a variable is working fine, but as soon as it should find the value in excel, nothing happens. ( I am not getting an error)
      #include <Excel.au3> Func Excel() Send("{CTRLDOWN}") Send("{c}") Send("{CTRLUP}") Local $sName = ClipGet() ;Text Local $sShortName = StringTrimRight ( $sName, 1) ;delete one letter Local $bOpenWorkBook = False, $oExcel = _Excel_Open() Local $sFilePath = "C:\Users\Acer\OneDrive\xyz.xlsx" Local $oWorkbook $oWorkbook = _Excel_BookAttach($sFilePath) If @error Then $oWorkbook = _Excel_BookOpen($oExcel, $sFilePath) $bOpenWorkBook = True EndIf sleep(15000) Send("{LWINDown}") Send ("{up}") ;maximize window Send("{LWINup}") sleep(1000) _Excel_RangeFind ($oWorkbook, $sShortName, "A3:A56") EndFunc Is anyone familiar with this problem or am I just missing some basic stuff? 
      Thanks for help!
    • By _leo_
      Hey guys
      I'm new to autoit, so this could be a simple question. I'm trying to read the value from the currently selected cell in Excel. I read on the forum and tired to find videos, but I couldn't quite get to it. 
      This is what I have got so far:
       
      Local $oExcel_1 = _Excel_Open()
      Local $var = "C:\Users\Acer\xy"
      Local $oWorkbook = _Excel_BookOpen($oExcel_1,$var)
       
      Local $_read1 = _Excel_RangeRead($oWorkbook, Default.Application.ActiveCell.Address)
       
      Whatever I try, I either get an error or it only reads "0".
       
      Thank you very much for any helpful thoughts!
×
×
  • Create New...