How to copy and paste excel sheet on office 2106

General Help regarding HMG, Compilation, Linking, Samples

Moderator: Rathinagiri

Tiger
Posts: 62
Joined: Mon Aug 31, 2015 11:28 am
Location: Taipei
Has thanked: 16 times
Been thanked: 13 times

How to copy and paste excel sheet on office 2106

Post by Tiger »

Hi,
I have following codes that work on office 2007 to copy one sheet form "AML.XLSX" and paste it to another file "JOB.XLSX". But the same codes couldn't run it with office 2016. The code "oExcel:Windows (oWorkBook1:name()):Activate()" couldn't activate the "Job.xlsx" , it just make a copy of sheet from "AML.xlsx" and paste it back to "AML.xlsx".

So, for the workaround, I have to make a copy of sheet from "AML.xlsx" and close it and then open the "JOB.xlsx" and do the paste. How come I cann't open two excel files from office 2016 at the same time and switch between them ? Do anyone have such problem?

// open two excel files
oWorkBook1:= oExcel:WorkBooks:OPEN( G_PATH + "\" + "JOB.XLSX" )
oWorkBook2:= oExcel:WorkBooks:OPEN( G_PATH + "\" + "AML.XLSX" )

// copy sheet from AML.XLSX
oExcel: Windows(oWorkBook2:name()):Activate()
oSheet:=oExcel:ActiveSheet()
nRows:= ALLTRIM(STR(oSheet:UsedRange:Rows:Count(),5,0))
nColumns= oSheet:UsedRange:Columns:Count()
nColumns=ALLTRIM(FND_COLUMN_LETTER (nColumns))
oSheet:Range("A1:"+nColumns+nRows):Select()
oExcel:Selection:Copy()

// paste the sheet to JOB.XLSX
oExcel: Windows (oWorkBook1:name()):Activate() // not work with office 2016
oExcel:Sheets:Add()
oSheet:=oExcel:ActiveSheet()
oSheet:PASTE() // with office 2007, it paste to "JOB.XLSX" but with office 2106 it paste to "AML.XLSX"
oExcel:WorkSheets(1):Name ="&TXT_FNAME"
oExcel:Sheets(1):UsedRange:Columns:AutoFit()
oExcel:ACTIVEWORKBOOK:SAVEAS( G_PATH + "\&TXT_FNAME"+".XLSX", xlWorkbookDefault )
oExcel:ActiveWorkBook:Close()
oExcel:Workbooks:Close()
oExcel:Quit()
oSHEET := NIL
oExcel := NIL
Release oExcel

User avatar
andyglezl
Posts: 1248
Joined: Fri Oct 26, 2012 7:58 pm
Location: Guadalajara Jalisco, MX
Has thanked: 38 times
Been thanked: 106 times
Contact:

Post by andyglezl »

Tiger wrote:
Tue Nov 26, 2019 2:23 pm
Hi,
I have following codes that work on office 2007 to copy one sheet form "AML.XLSX" and paste it to another file "JOB.XLSX". But the same codes couldn't run it with office 2016. The code "oExcel:Windows (oWorkBook1:name()):Activate()" couldn't activate the "Job.xlsx" , it just make a copy of sheet from "AML.xlsx" and paste it back to "AML.xlsx".

So, for the workaround, I have to make a copy of sheet from "AML.xlsx" and close it and then open the "JOB.xlsx" and do the paste. How come I cann't open two excel files from office 2016 at the same time and switch between them ? Do anyone have such problem?

// open two excel files
oWorkBook1:= oExcel:WorkBooks:OPEN( G_PATH + "\" + "AML.XLSX" )

// copy sheet from AML.XLSX
oExcel: Windows(oWorkBook2:name()):Activate()
oSheet:=oExcel:ActiveSheet()
nRows:= ALLTRIM(STR(oSheet:UsedRange:Rows:Count(),5,0))
nColumns= oSheet:UsedRange:Columns:Count()
nColumns=ALLTRIM(FND_COLUMN_LETTER (nColumns))
oSheet:Range("A1:"+nColumns+nRows):Select()
oExcel:Selection:Copy()
oExcel:ActiveWorkBook:Close()

// paste the sheet to JOB.XLSX
oWorkBook2:= oExcel:WorkBooks:OPEN( G_PATH + "\" + "JOB.XLSX" )
oExcel: Windows (oWorkBook1:name()):Activate() // not work with office 2016
oExcel:Sheets:Add()
oSheet:=oExcel:ActiveSheet()
oSheet:PASTE() // with office 2007, it paste to "JOB.XLSX" but with office 2106 it paste to "AML.XLSX"
oExcel:WorkSheets(1):Name ="&TXT_FNAME"
oExcel:Sheets(1):UsedRange:Columns:AutoFit()
oExcel:ACTIVEWORKBOOK:SAVEAS( G_PATH + "\&TXT_FNAME"+".XLSX", xlWorkbookDefault )
oExcel:Workbooks:Close()
oExcel:Quit()
oSHEET := NIL
oExcel := NIL
Release oExcel
Quizás así...
*--------------------------------
Maybe so ...
Andrés González López
Desde Guadalajara, Jalisco. México.

Tiger
Posts: 62
Joined: Mon Aug 31, 2015 11:28 am
Location: Taipei
Has thanked: 16 times
Been thanked: 13 times

Post by Tiger »

Thanks.

Yes, it is the way what I doing now with excel 2016 version ...
So if I want to add 5 sheets from 5 excel files to "target.xlsx", I have to open and close the "target.xlsx" several times … :? :? :?

User avatar
AUGE_OHR
Posts: 860
Joined: Sun Aug 25, 2019 3:12 pm
DBs Used: DBF, PostgreSQL, MySQL, SQLite
Location: Hamburg, Germany
Has thanked: 129 times
Been thanked: 216 times

Post by AUGE_OHR »

hi,

when "internal copy" of Excel does not work like you want what about "external" use Array instead of Clipboard :?:
Tiger wrote:
Tue Nov 26, 2019 2:23 pm

Code: Select all

    oSheet:Range("A1:"+nColumns+nRows):Select()
as i see you have select Range so you also can write

Code: Select all

   aValue := oSheet:Range("A1:"+nColumns+nRows):Select():Value
on other Sheet you can use

Code: Select all

   oSheet:Range("A1:"+nColumns+nRows):Select():Value := aValue
have fun
have fun
Jimmy

Tiger
Posts: 62
Joined: Mon Aug 31, 2015 11:28 am
Location: Taipei
Has thanked: 16 times
Been thanked: 13 times

Post by Tiger »

Thanks for your help but I got an error message said " Error BASE/1004 No Exported method: VALUE form following two lines:

aValue := oSheet:Range("A1:"+nColumns+nRows):Select():Value
oSheet:Range("A1:"+nColumns+nRows):Select():Value := aValue

But if I deleted the "select()" and no more error message...
But I got a blank sheet, it seems no data to paste it from the array aValue … I can sure there are some data on the array by confirm it with aValue[1,1]…
I don't know what's wrong ...

User avatar
AUGE_OHR
Posts: 860
Joined: Sun Aug 25, 2019 3:12 pm
DBs Used: DBF, PostgreSQL, MySQL, SQLite
Location: Hamburg, Germany
Has thanked: 129 times
Been thanked: 216 times

Post by AUGE_OHR »

Tiger wrote:
Thu Nov 28, 2019 8:47 am
Thanks for your help but I got an error message said " Error BASE/1004 No Exported method: VALUE form following two lines:

aValue := oSheet:Range("A1:"+nColumns+nRows):Select():Value
oSheet:Range("A1:"+nColumns+nRows):Select():Value := aValue

But if I deleted the "select()" and no more error message...
sorry , i have just copy/past your Code without run it
Tiger wrote:But I got a blank sheet, it seems no data to paste it from the array aValue … I can sure there are some data on the array by confirm it with aValue[1,1]…
I don't know what's wrong ...
are you using Windows 10 :?: there is something like Multi Clipboard ... :roll:

this is ClipBrd.EXE from Windows XP which still run under Windows 10 so you can "look" into Clipboard.
clipbrd.zip
(46.55 KiB) Downloaded 30 times
have fun
Jimmy

Tiger
Posts: 62
Joined: Mon Aug 31, 2015 11:28 am
Location: Taipei
Has thanked: 16 times
Been thanked: 13 times

Post by Tiger »

Hi Jimmy,

I have downloaded this tools, but it doesn't show anything from its monitor screen...

Anyway I try to check the data form excel file and array "aValue" and I found the oSheet:Cells(1,8):Value == aValue[8,1] , but it seems this code "oSheet:Range("A1:"+nColumns+nRows):Value := aValue "can't paste the data to the new sheet of another excel file. :( :P :P :P

User avatar
AUGE_OHR
Posts: 860
Joined: Sun Aug 25, 2019 3:12 pm
DBs Used: DBF, PostgreSQL, MySQL, SQLite
Location: Hamburg, Germany
Has thanked: 129 times
Been thanked: 216 times

Post by AUGE_OHR »

Tiger wrote:
Fri Nov 29, 2019 7:33 am
I have downloaded this tools, but it doesn't show anything from its monitor screen...
when start 1st time it is most minimized to lower left, so have a look again.
Tiger wrote:Anyway I try to check the data form excel file and array "aValue" and I found the oSheet:Cells(1,8):Value == aValue[8,1] , but it seems this code "oSheet:Range("A1:"+nColumns+nRows):Value := aValue "can't paste the data to the new sheet of another excel file. :( :P :P :P
hm ...
your RANGE select is wrong :!:
it must be something like A1:Z1 but not A1:123

Code: Select all

         // calculate A-Z
         cEnde := ZAHL2CHR( nLFcount + 1 )
         // and now hole assign Array   
         oSheet:range( "B2:" + cEnde + LTRIM( STR( nLFcount + 1 ) ) ) :value := aSammel

Code: Select all

FUNCTION ZAHL2CHR( nLFcount )
LOCAL nMal, cEnde
   IF nLFcount > 26
      nMal := INT( nLFcount / 26 )
      IF nMal = nLFcount / 26
         cEnde := CHR( nMal + 64 - 1 ) + CHR( 90 )
      ELSE
         cEnde := CHR( nMal + 64 ) + CHR( ( nLFcount - ( nMal * 26 ) ) + 64 )
      ENDIF
   ELSE
      cEnde := CHR( nLFcount + 64 )
   ENDIF
RETURN cEnde
have fun
Jimmy

Tiger
Posts: 62
Joined: Mon Aug 31, 2015 11:28 am
Location: Taipei
Has thanked: 16 times
Been thanked: 13 times

Post by Tiger »

It still can't work even I use following codes ...

oExcel:WorkBooks:OPEN( _TEMP_PATH + "\" + "old.XLSX" )
oSheet:=oExcel:ActiveSheet()
aValue := oSheet:Range("A1:K100"):Value
and then open another excel file
oExcel:WorkBooks:OPEN( _TEMP_PATH + "\" + "new.XLSX" )
oExcel:Sheets:Add()
oSheet:=oExcel:ActiveSheet()
oSheet:Range("A1:k100"):Value := aValue

maybe excel can use the array with VBA but not with harbour ???

User avatar
AUGE_OHR
Posts: 860
Joined: Sun Aug 25, 2019 3:12 pm
DBs Used: DBF, PostgreSQL, MySQL, SQLite
Location: Hamburg, Germany
Has thanked: 129 times
Been thanked: 216 times

Post by AUGE_OHR »

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
have fun
Jimmy

Post Reply