Pasting to a range of cells in Excel

[stearno]

My Objective: clean data, copy and paste it below previous data in another workbook.

My struggle is it is pasting only to the active worksheet. It is not pasting in the specified sheet.

Code: Select all

  XLGetSheetDims>anl,GBPUSD,rowss,colss
   Wait>0.5
  Let>firstblankrow=%rowss%+1
  Let>lastrow=%firstblankrow%+%rows%
  Let>range1=G%firstblankrow%:G:%lastrow%
  Wait>0.5
  XLSelectRange>anl,GBPUSD,range1

I appreciate the guidance on what I need to do to fix it.

Note: I did not use DDE because I need it to go to a specific cell. I did not use SetCell because then it copies one cell at a time, which takes too long.

Code: Select all

//Set IGNORESPACES to 1 to force script interpreter to ignore spaces.
//If using IGNORESPACES quote strings in {" ... "}
//Let>IGNORESPACES=1
Let>AnalysisFile=C:\Users\stear\OneDrive - Mazars in Oman\Trading\Systems\FibMatrix\Testing Trade Analysis\Trades Analysis v3-6.xlsx
Let>AnalWindow=Trades Analysis v3-6 - Excel
//Input>firstdate,"Enter the first date of the week.",defval
Let>firstdate=2020.07.20
IfNotWindowOpen>Trades Analysis v3-6 - Excel
  XLOpen>AnalysisFile,1,anl
  WaitWindowOpen>Trades Analysis v3-6 - Excel
  EndIf
WindowAction>1,Trades Analysis v3-6 - Excel
SetFocus>Trades Analysis v3-6 - Excel
//---------------------------------------GBPUSD-----------------------------------------------------
  Let>filename=C:\Users\stear\OneDrive - Mazars in Oman\Trading\Systems\FibMatrix\Testing Trade Analysis\Data\GBPUSD1.csv
  IfFileExists>filename
  XLOpen>filename,1,xl1
  WaitWindowOpen>GBPUSD1 - Excel
  WindowAction>1,GBPUSD1 - Excel
  SetFocus>GBPUSD1 - Excel
  WaitWindowFocused>GBPUSD1 - Excel
//-----------------Clean unneeded dates---------------------  
  Let>row=0
  Let>k=false
  Repeat>k
    Let>row=row+1
    XLGetCell>xl1,GBPUSD1,%row%,1,cellval
    If>cellval=firstdate
      Let>k=TRUE
    Endif
  Until>k=TRUE
 
  Repeat>row
  Let>row=row-1
  XLDelRow>xl1,GBPUSD1,%row%
  Until>row=1
  //-------------------------------------COPY & PASTE DATA-----------------------------------------
  XLGetSheetDims>xl1,GBPUSD1,rows,cols
  XLSelectRange>xl1,GBPUSD1,A1:G%rows%
  Wait>0.5
  Press CTRL
  Send> c
  Wait>0.5
  Release CTRL
  WaitClipBoard
  SetFocus>Trades Analysis v3-6 - Excel
  WaitWindowFocused>Trades Analysis v3-6 - Excel
  XLGetSheetDims>anl,GBPUSD,rowss,colss
   Wait>0.5
  Let>firstblankrow=%rowss%+1
  Let>lastrow=%firstblankrow%+%rows%
  Let>range1=G%firstblankrow%:G:%lastrow%
  Wait>0.5
  XLSelectRange>anl,GBPUSD,range1
  Wait>0.5
  Press CTRL
  Send>v
  Wait>0.5
  Release CTRL
  SetFocus>GBPUSD1 - Excel
  WaitWindowFocused>GBPUSD1 - Excel
  CloseWindow>GBPUSD1 - Excel

  //Wait for 
  WaitScreenImage>C:\Users\stear\Pictures\Macro Images\EXCEL SAVE.png,0.7,CCOEFF
  Wait>2
//Find and Left Click Middle Bottom of 
FindImagePos>C:\Users\stear\Pictures\Macro Images\EXCEL SAVE.png,SCREEN,0.7,6,XArr,YArr,NumFound,CCOEFF
If>NumFound>0
  MouseMove>XArr_0,YArr_0
  LClick
Endif


  //Wait for 
  WaitScreenImage>C:\Users\stear\Pictures\Macro Images\save copy info.png,0.7,CCOEFF
  //Find and Left Click Middle Bottom of 
  FindImagePos>C:\Users\stear\Pictures\Macro Images\save copy info.png,SCREEN,0.7,6,XArr,YArr,NumFound,CCOEFF
  If>NumFound>0
    MouseMove>XArr_0,YArr_0
    LClick
  Endif

[Dorian (MJT support)]

It sounds like what you need to use is XLRunCode.

This post describes how to get started.

[stearno]

Wow, that really opens up possibilities. Thanks, I will give that a shot.

[Dorian (MJT support)]

Of course for those who don't like using vba and XLRunCode, there's also the option to use XLGetCell and XLSetCell in a nested loop.

[stearno]

It sounds like what you need to use is XLRunCode.

This post describes how to get started.

That worked perfectly. Thanks.