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 Taxyo
      Hi,
       
      I've been trying to automate modification of an excel file and the last thing I am stuck on is deleting all the rows where the value of Column 13 is 0. 
      I believe the error is due to me not fully understanding the syntax so this is where I'm stuck: 
       
      Func Hotkey2() Global $aUsedRange = _Excel_RangeRead($oWorkbook, 1) _ArrayDisplay($aUsedRange) For $iRow = UBound($aUsedRange) - 1 to 3 Step -1 If $aUsedRange[$iRow][13] = 0 Then _Excel_RangeDelete($oWorkbook.Worksheets(1), $aUsedRange[$iRow] & ":" & $aUsedRange[$iRow], default, 1) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeDelete Example 2", "Error deleting rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended) EndIf Next EndFunc  
      While my script properly locates the row which contains value 0 in Column 13, I am not sure how to set it to the corresponding row in the excel workbook?  My above experiment gives me $vRange error and I've been toying around with it to no avail. The only way I get the Script to delete a row is by actually specifying "4:4" or "6:8" etc. 
      Where am I going wrong?
       
      Thanks! 
    • By Most
      #include <Array.au3> #include <Excel.au3> #include <MsgBoxConstants.au3> ; Create application object and open an example workbook Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\trans.xlsx") If @error Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "\trans.xlsx'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oExcel) Exit EndIf ; ***************************************************************************** ; Read data from a single cell on the active sheet of the specified workbook ; ***************************************************************************** Local $sResult = _Excel_RangeRead($oWorkbook, Default, "A1") If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 1", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 1", "Data successfully read." & @CRLF & "Value of cell A1: " & $sResult) Hi, all.
      Ok, here is the deal. I have simple excel file called trans.xlsx. It's located in the directory of script. In general i don't care where to store it. 
      What i do need is to open excel file and copy one by one numbers from cells. I've tried different ways, examples. But i only get error, says: error = 3, extended = 1. I saw different posts from different years. I even tried to use simple example from manual file. But always get error.

      In general my goal get numbers one by one and post it to let's say search filed in my PC one by one. Or to notepad (but one by one, in kind of loop). 
      I've learned how to copy or show in message box some info from other apps. But with excel i'm stuck. 

      I'm able to open needed window based on "title" of excel. But i don't succeed of copying info from cells. 

      Would be appreciate for any help. 
      So, in this code i'm trying at least to read from cell A1. Doesn't matter what Sheet. 

      I use Windows 10, Excel for Office 365. 
      Thank you in advance. 
    • 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)
×
×
  • Create New...