How to copy and paste excel sheet on office 2106

[Tiger]

hi,

i "think" you still not "activate" Cell of Sheet

Code: Select all

   oWorkBook := oExcel:activeWorkBook
   oExcel:Application:Worksheets(1):activate()
   oSheet := oExcel:Worksheets(1):cells
   oWorkBook:workSheets(1):usedRange:Select

I attached a file that incuded a program and two excel files, could you please take a look? Thanks.
I try several ways but it still fail to use array to paste data to excel ...

Code: Select all

#include "hmg.ch"
#define xlCSVMSDOS        24                        // MSDOS CSV
#define xlCSV            6                          // CSV
#define xlExcel8          56                        // Excel8 56 XLS
#define xlWorkbookDefault   51                      // XLSX
#define xlText             -4158                    // TAB TXT
#define xTAB             Chr( 9 )                   // TAB ASCII

REQUEST HB_CODEPAGE_BIG5
PROCEDURE main()
  LOCAL aExcel:={}
  G_PATH :=GetCurrentFolder()
  oExcel:=CreATeObject("Excel.ApplicATion")
  oExcel:DisplayAlerts:=.F.
  oExcel:Visible:=.F.

  oExcel:Application:Workbooks:open(G_PATH + "\OLD.XLSX")
  oWorkBook:= oExcel:activeWorkBook()
  oExcel:Application:Worksheets(1):activate()
  oSheet := oExcel:Worksheets(1):cells()
  oWorkBook:workSheets(1):usedRange:Select()
  numRows    := oWorkBook:workSheets(1):usedRange:Rows:Count
  numColumns := oWorkBook:workSheets(1):usedRange:Columns:Count

  // create empty Array
  FOR i := 1 TO numRows
    AADD(aExcel,ARRAY(numColumns))
  NEXT

  // convert numColumns to A-Z
  cEnde := ZAHL2CHR(numColumns)

  // fill Array this Way
  aExcel :=oSheet:range("A1:"+cEnde+LTRIM(STR(numRows))):value()

  oExcel:WorkBooks:OPEN(G_PATH + "\NEW.XLSX")
  oExcel:Sheets:Add()
  oSheet:=oExcel:ActiveSheet()
  oSheet:range( "A1:"+cEnde+LTRIM(STR(numRows)) ):value := aExcel
  oExcel:ACTIVEWORKBOOK:SAVE()
  oExcel:ActiveWorkBook:Close()
  oExcel:Workbooks:Close()
  oSHEET := NIL
  oExcel:Quit()
  Do Events
  oExcel := NIL
  Release oExcel
RETURN

FUNCTION ZAHL2CHR(numColumns)
  LOCAL nMal
  LOCAL cEnde
  IF numColumns > 26
    nMal  := INT(numColumns/26)
    cEnde := CHR(nMal+64)+CHR((numColumns-(nMal*26))+64)
  ELSE
    cEnde := CHR(numColumns+64)
  ENDIF
  RETURN cEnde