Select specific Excel file from 3 opened. ComObjActive Topic is solved

[ciapas]

Hi!

It's obvious I'am new here, but I'm using AHK for about 6 months (just basic stuff, nothing fancy). I came to a problem now I can't solve.
So I have 3 excel files open and i would like the script to operate on specific file from those 3 but when i minimize the window I want it to operate on, it automaticly goes to one of two left (I know it's ComObjACTIVE, but I don't want to use ObjComCreate, besause it shows the window after completing the script and I would like to have the window for script minimized and another one opened to work on it).
It would be great help for me if someone could at least point any direction to look at.

Here's the code I;ve got so far:

Code: Select all

^3::
	;copying the folder name and spliting it
 Send {F2}
 Sleep 150
 Send ^c{Esc}
 Sleep 150
 czlon := clipboard
 czlon_array := StrSplit(czlon, [".", "-", "_", "/"])
 Sleep 200
	;opening excel application and putting values in cells to find if the name provided is already somewhere in excel (there are 'index' formulas and others)
  Xl := ComObjActive("Excel.Application")
  Xl.Range("C3").Value := czlon_array[1]
  Xl.Range("D3").Value := czlon_array[2]
  Xl.Range("F3").Value := czlon_array[4]
  Xl.Range("E3").Value := czlon_array[3]
  Xl.Range("C4:D4").Copy()
  data := clipboard
  Xl.Range("C5:D5").Copy()
  asort := clipboard
  Xl.Range("C6:D6").Copy()
  wykon := clipboard
  Xl.Range("C7:D7").Copy()
  dz := clipboard
  Xl.Range("B10").Copy()
  bwb := clipboard
  MsgBox %pos1%`n%pos2%`n%pos3%`n%dz%`n%bwb%
 return

And the code above works only if the window was used as last or is active now.
I've also tried this:

Code: Select all

^3::
	;copying the folder name and spliting it
 Send {F2}
 Sleep 150
 Send ^c{Esc}
 Sleep 150
 czlon := clipboard
 czlon_array := StrSplit(czlon, [".", "-", "_", "/"])
 Sleep 200
	;opening excel application and putting values in cells to find if the name provided is already somewhere in excel (there are 'index' formulas and others)
  Xl := ComObjActive("Excel.Application")	;is there a way to choose specific excel file from 3 open? i'd rather not use ObjComCreate as with using that, the excel shows at the end of script, which i dont want to happen.
  Xl := ComObjGet("poprawny")	;here i've tried to choose specific sheet from all open excel files but it doesn't work, just dont know what to do here
  Xl.ActiveSheet.Range("C3").Value := czlon_array[1]
  Xl.ActiveSheet.Range("D3").Value := czlon_array[2]
  Xl.ActiveSheet.Range("F3").Value := czlon_array[4]
  Xl.ActiveSheet.Range("E3").Value := czlon_array[3]
  Xl.ActiveSheet.Range("C4:D4").Copy()
  pos1 := clipboard
  Xl.ActiveSheet.Range("C5:D5").Copy()
  pos2 := clipboard
  Xl.ActiveSheet.Range("C6:D6").Copy()
  pos3 := clipboard
  Xl.ActiveSheet.Range("C7:D7").Copy()
  dz := clipboard
  Xl.ActiveSheet.Range("B10").Copy()
  bwb := clipboard
  MsgBox %pos1%`n%pos2%`n%pos3%`n%dz%`n%bwb%
 return

[rommmcek]

Just for coping at the start of your script:

Code: Select all

 Send {F2}
 Sleep 150
 clipboard:=""
 Send {End}^+{Home}^c
 clipwait, 1
 Send, {esc}
 Sleep 150

would be a little better.
If you want to apply it to a minimized window, replace e.g. Send {End}^+{Home}^c with ControlSend,, {End}^+{Home}^c, WinTitle
However, I suggest you to publish your XlCom code for retrieving the cell content and ask how to prevent window to be activated. Probably .visible:="false" or some other "silent" method. I'm sure there is a way!

[awel20]

Code: Select all

workbookName := "Book1.xlsx"
Xl := ComObjActive("Excel.Application")
objWorkbook1 := Xl.Workbooks(workbookName)
; Note: Xl and objWorkbook1 are different types of objects. Xl is a application object, objWorkbook1 is a workbook.
; https://docs.microsoft.com/en-us/office/vba/api/excel.application%28object%29
; https://docs.microsoft.com/en-us/office/vba/api/Excel.Workbook
objWorkbook1.ActiveSheet.Range("C4").Value := "ASDFGHJKL"

[ciapas]

However, I suggest you to publish your XlCom code for retrieving the cell content and ask how to prevent window to be activated.

Like I said, I'am not doing any fancy stuff and I don't know what my "XlCom code" is, apart from what I've already posted
But I'd like to thank you for your answer, as I implemented your suggestion about copying to my script, works the same to my eye, but if it's better behind the scenes, then I shall use it, so thanks again!

Code: Select all

workbookName := "Book1.xlsx"
[...]

Thank you for this, awel20! This does what I wanted it to do! It really helped me a lot, thanks