Excel COM: Copy values from 2 sheets?

[NoGuest]

Hi,

I need your help.
My aim is to copy data from two sheets of an Excel table (which is active) and to export them to a text file.
The good news: It almost works.
The bad news: It seems there is a problem with changing the sheet.
It doesn't copy the values of sheet 2.

Does sheet2 have to have a specific name, or doesn't its name matter?
I think sheet names don't matter here (I made some tests.)

That's the code:

Code: Select all

#SingleInstance force
F3::
Loop, 4
{
  i++ ; Counter for Array
  xl        := ComObjActive("Excel.Application") ; Connect to the active Excel application
  wb        := excel.ActiveWorkbook  ; Connect to active workbook
  ws        := excel.ActiveSheet ; Connect to active worksheet
  cellArray := ["A1", "B1", "C1", "D1"] ; List of cells
  cell      := cellArray[i] ; Variable which accesses values of the array
  
  Book.ws("Sheet1").Select ; Select first sheet
  Sleep, 5
  xl.Range(cell).copy ; Copy value of cell to Clipboard
  Sleep, 5
  a := Clipboard ; Copy Clipboard content to variable a
  Sleep, 5
  Book.ws("Sheet2").Select ; Select second sheet - DOES NOT WORK
  Sleep, 5
  xl.Range(cell).copy ; Copy value of cell to Clipboard
  Sleep, 5
  b := Clipboard ; Copy Clipboard content to variable b
  Sleep, 5
  FileAppend, %a% %b%, %A_ScriptDir%\Array.txt ; Write content of variable a and b to an external file
  Sleep, 5
}
return

P.S: Do I need "a := Clipboard" and "b := Clipboard"? a := xl.Range(cell).copy didn't work (.value didnt work eitheyr)

Thanks for any help!

[NoGuest]

Any help or advice would be great.
I am pretty new to Excel COM, but I've made good progress so far.
At the moment I can't continue, though...

[FanaticGuru]

I need your help.
My aim is to copy data from two sheets of an Excel table (which is active) and to export them to a text file.
The good news: It almost works.
The bad news: It seems there is a problem with changing the sheet.
It doesn't copy the values of sheet 2.

Does sheet2 have to have a specific name, or doesn't its name matter?
I think sheet names don't matter here (I made some tests.)

The below code will loop through the same address range of two sheets and put the values from each cell in a display string.

Code: Select all

xlApp := ComObjActive("Excel.Application")
xlWS1 := xlApp.Sheets(1) ; worksheet index 1, the first worksheet
xlWS2 := xlApp.Sheets("Other Data") ; worksheet named exactly "Other Data"

Display := ""
for Cell in xlWS1.Range("A1:D1")
	Display .= Cell.Value "`t" xlWS2.Range(Cell.Address).Value "`n"

; display items from first and second range side-by-side
MsgBox % Display
; can save this to a text file

FG

[NoGuest]

Hi!

Thanks for your reply.
Your post helped me!

The main issue (in the code I posted) was the wrong variable names .
WRONG:

Code: Select all

  xl        := ComObjActive("Excel.Application") ; Connect to the active Excel application
  wb        := excel.ActiveWorkbook  ; Connect to active workbook
  ws        := excel.ActiveSheet ; Connect to active worksheet

CORRECT:

Code: Select all

  xl        := ComObjActive("Excel.Application") ; Connect to the active Excel application
  wb        := xl.ActiveWorkbook  ; Connect to active workbook
  ws        := xl.ActiveSheet ; Connect to active worksheet

Later, in the code, I just wrote:

Code: Select all

  xl.Sheets(1).Select ; Select first sheet
  Sleep, 5
  xl.Range(cell).copy ; Copy value of cell to Clipboard
  Sleep, 5
  a := Clipboard ; Copy Clipboard content to variable a
  Sleep, 5
  xl.Sheets(2).Select ; Select second sheet
  Sleep, 5

Thanks again!