Jump to content
Sign in to follow this  
AnonymousX

Activate Excel Window

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
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By GOSM
      Hey,
      as part of a larger Programm I am trying to copy the values from one cell in an Excel file to another. The code I am using is the folowing.
       
      Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_RangeCopyPaste($oExcel.ActiveSheet, "V4:W43", "X4", False, $xlPasteValues, Default, True) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "Error copying rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "2 Rows successfully pasted from the clipboard to row 1.") As I understood it the option $xlPasteValues should do the trick  but it is still copying the inserted formulas. I attached a test code and excel file.
      Thanks for the help Simon
      Copy Values Test.zip
    • By Chimp
      An "improper" "unusual" use of Excel
      the script allows the creation of artistic images by simply coloring the background of the individual cells of the Excel workbook.
      Although definitely useless ... I find it quite funny though
      have a good time
      many thanks to @UEZ , @Malkey , @water
      p.s.
      I think the pixelite + color to array process can be simplified, but I used the two ready-made functions provided by UEZ and Malkey. I thank both of you (credits  and links in listing)
      p.p.s.
      strange behaviour: while excell is filling cells, if you move the mouse pointer off the excell window, the fill speed increases ... (?)

       
      ; =============================================================================================================================== ; Name ..........: Excel in art ; Description ...: This script allows the creation of artistic images in Excel from a choosed picture. ; The picture is done by simply coloring the background of the individual cells of an Excel workbook. ; Although definitely useless, I find it quite funny though ; ; Return values .: An artistic image in an Excel workbook ; Author ........: Addiego Gianni (chimp) ; Modified ......: ; Remarks .......: Many thanks to UEZ, Malkey and Water ; Related .......: ; Link ..........: ; Example .......: ; =============================================================================================================================== #include <GDIPlus.au3> #include <Excel.au3> _PixelsToCells(50) ; <-- Parameter 50 is the cumber of horizontal Excel cells to be filled MsgBox(64, "All done", "Excel art is ready") Func _PixelsToCells($iHcells = 50) ; Check application object Local $oExcel = _Excel_Open() If Not IsObj($oExcel) Then MsgBox(16, "Error", "Sorry, You need to have 'Excel' intalled") ; Choose Image File Local $sPath = FileOpenDialog("Choose Image File", @ScriptDir & "", "Images (*.gif;*.png;*.jpg;*.bmp)| All (*.*)") If $sPath = '' Then Exit MsgBox(16, "Error", "Sorry, no image was chosen") ; Create a new Excel workbook $oWorkbook = _Excel_BookNew($oExcel, 1) ; initialize GDI+ _GDIPlus_Startup() Local $hBmp = _GDIPlus_BitmapCreateFromFile($sPath) Local $iWidth = _GDIPlus_ImageGetWidth($hBmp) ; get image width ; Local $iHeight = _GDIPlus_ImageGetHeight($hBmp) ; not needed here Local $iStep = $iWidth / $iHcells ; calculate the pixelation factor Local $hBitmap_new = _GDIPlus_PixelateBitmap($hBmp, $iStep) ; pixelate the image Local $aPixelColors = _FileImageToArray($hBitmap_new) ; get pixel colors ; reduce Excel columns width Local $xx = 1, $yy = 1 For $iCol = 0 To UBound($aPixelColors, 2) - 1 Step $iStep $oWorkbook.Sheets(1).Columns($xx).ColumnWidth = 1 $xx += 1 Next ; reduce Excel rows height For $iRow = 0 To UBound($aPixelColors) - 1 Step $iStep $oWorkbook.Sheets(1).Rows($yy).RowHeight = 9 $yy += 1 Next $xx = 1 $yy = 1 For $iRow = 0 To UBound($aPixelColors) - 1 Step $iStep For $iCol = 0 To UBound($aPixelColors, 2) - 1 Step $iStep $oWorkbook.Sheets(1).Range(_Excel_ColumnToLetter($xx) & $yy).Interior.Color = Number("0x" & $aPixelColors[$iRow][$iCol]) $xx += 1 Next $yy += 1 $xx = 1 Next _GDIPlus_BitmapDispose($hBmp) _GDIPlus_Shutdown() EndFunc ;==>_PixelsToCells ; by UEZ ; https://www.autoitscript.com/forum/topic/167707-imagepixelate/?do=findComment&comment=1227509 Func _GDIPlus_PixelateBitmap($hBitmap, $iPixelate, $bSmooth = 1) Local $iWidth = _GDIPlus_ImageGetWidth($hBitmap), $iHeight = _GDIPlus_ImageGetHeight($hBitmap) Local $iNewW = Round($iWidth / $iPixelate, 0), $iNewH = Round($iHeight / $iPixelate, 0) Local $hBitmap_scaled = _GDIPlus_BitmapCreateFromScan0($iNewW, $iNewH) Local $hCtxt = _GDIPlus_ImageGetGraphicsContext($hBitmap_scaled) Local $iInterpolation = 5 If $bSmooth Then $iInterpolation = $GDIP_INTERPOLATIONMODE_BILINEAR _GDIPlus_GraphicsSetInterpolationMode($hCtxt, $iInterpolation) _GDIPlus_GraphicsDrawImageRect($hCtxt, $hBitmap, 0, 0, $iNewW, $iNewH) _GDIPlus_GraphicsDispose($hCtxt) Local $hBitmap_pixelated = _GDIPlus_BitmapCreateFromScan0($iWidth, $iHeight) $hCtxt = _GDIPlus_ImageGetGraphicsContext($hBitmap_pixelated) _GDIPlus_GraphicsSetInterpolationMode($hCtxt, $GDIP_INTERPOLATIONMODE_NearestNeighbor) _GDIPlus_GraphicsDrawImageRectRect($hCtxt, $hBitmap_scaled, 0, 0, $iNewW, $iNewH, -$iPixelate, -$iPixelate, $iWidth + 2 * $iPixelate, $iHeight + 2 * $iPixelate) _GDIPlus_GraphicsDispose($hCtxt) Return $hBitmap_pixelated EndFunc ;==>_GDIPlus_PixelateBitmap ; by Malkey ; https://www.autoitscript.com/forum/topic/112540-is-there-a-function-for-reading-images-into-2d-arrays/?do=findComment&comment=788472 Func _FileImageToArray($hImage) Local $Reslt, $stride, $format, $Scan0, $iIW, $iIH ; , $hImage Local $v_Buffer, $width, $height ; _GDIPlus_Startup() ; $hImage = _GDIPlus_ImageLoadFromFile($sFileName) $iIW = _GDIPlus_ImageGetWidth($hImage) $iIH = _GDIPlus_ImageGetHeight($hImage) ProgressOn("Progress Bar", "Filling a " & $iIW & " x " & $iIH & " size array.", "0 percent") $Reslt = _GDIPlus_BitmapLockBits($hImage, 0, 0, $iIW, $iIH, $GDIP_ILMREAD, $GDIP_PXF32ARGB) ;Get the returned values of _GDIPlus_BitmapLockBits () $width = DllStructGetData($Reslt, "width") $height = DllStructGetData($Reslt, "height") $stride = DllStructGetData($Reslt, "stride") $format = DllStructGetData($Reslt, "format") $Scan0 = DllStructGetData($Reslt, "Scan0") Local $aArray[$height][$width] For $j = 0 To $iIH - 1 For $i = 0 To $iIW - 1 $v_Buffer = DllStructCreate("dword", $Scan0 + ($j * $stride) + ($i * 4)) $aArray[$j][$i] = StringRegExpReplace(Hex(DllStructGetData($v_Buffer, 1), 6), "(.{2})(.{2})(.{2})", "\3\2\1") ; To RGB format Next ProgressSet(Int(100 * $j / ($iIH)), Int(100 * $j / ($iIH)) & " percent") Next _GDIPlus_BitmapUnlockBits($hImage, $Reslt) ProgressOff() _GDIPlus_ImageDispose($hImage) Return $aArray EndFunc ;==>_FileImageToArray  
    • By Zaoka
      Hi guys
      Need little help with filtering.
      I'm trying to filter specific weeks from power pivot table using this code recorded with Excel VBA :
       
      #include <Excel.au3> Global $oExcel = _Excel_Open() Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\....\Orders.xlsb") $oWorkbook.PivotTables("PivotTable1").PivotFields( _ "[Report 2].[Week].[Week]").VisibleItemsList = Array( _ "[Report 2].[Week].&[10]", "[Report 2].[Week].&[11]", _ "[Report 2].[Week].&[12]", "[Report 2].[Week].&[13]", _ "[Report 2].[Week].&[14]", "[Report 2].[Week].&[15]") But get error
      error: Array(): undefined function. Not sure how to resolve this.
    • By Rajat231
      I am trying this code to create multiple workbooks eachone  shall be copy of one worksheet from a workbook having multiple sheets ( keeing the name same)
      SavingWorksheets.au3
    • By Zaoka
      Hi guys,
      i have simple report in PowerPivot that shows Orders (Values) by Regions (Row) and Weeks (Columns). In Filter field is WeekDAYS (Monday,Tuesday,Wednesday,Thursday etc )
      how to filter WeekDAYS Filed on WEEKDAYYesterday with autoit ?
      my junky try
      #include <Date.au3> #include <Excel.au3> Local $sWEEKDAYYesterday = _DateDayOfWeek(@WDAY-1) Global $oExcel = _Excel_Open() Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\.......\Orders.xlsb") Sleep (5000) $oExcel.ActiveWorkbook.RefreshAll Sleep (5000) $oExcel.Application.Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("WeekDAYS").PivotFilters($sWEEKDAYYesterday)  
      Error result
      $oExcel.Application.Sheets("PivotTable1").PivotTables("PivotTable1").PivotFields("WeekDAYS").PivotFilters($sWEEKDAYYesterday) $oExcel.Application^ ERROR  
×
×
  • Create New...