How to copy and paste excel sheet on office 2106

General Help regarding HMG, Compilation, Linking, Samples

Moderator: Rathinagiri

Tiger
Posts: 49
Joined: Mon Aug 31, 2015 11:28 am
Location: Taipei
Has thanked: 10 times
Been thanked: 10 times

How to copy and paste excel sheet on office 2106

Post by Tiger » 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 + "\" + "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: 1191
Joined: Fri Oct 26, 2012 7:58 pm
Location: Guadalajara Jalisco, MX
Has thanked: 37 times
Been thanked: 85 times
Contact:

Post by andyglezl » Tue Nov 26, 2019 4:46 pm

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: 49
Joined: Mon Aug 31, 2015 11:28 am
Location: Taipei
Has thanked: 10 times
Been thanked: 10 times

Post by Tiger » Wed Nov 27, 2019 1:52 am

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: 290
Joined: Sun Aug 25, 2019 3:12 pm
DBs Used: DBF, PostgreSQL, MySQL, SQLite
Location: Hamburg, Germany
Has thanked: 32 times
Been thanked: 39 times

Post by AUGE_OHR » Wed Nov 27, 2019 11:20 pm

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

Tiger
Posts: 49
Joined: Mon Aug 31, 2015 11:28 am
Location: Taipei
Has thanked: 10 times
Been thanked: 10 times

Post by Tiger » 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...
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: 290
Joined: Sun Aug 25, 2019 3:12 pm
DBs Used: DBF, PostgreSQL, MySQL, SQLite
Location: Hamburg, Germany
Has thanked: 32 times
Been thanked: 39 times

Post by AUGE_OHR » Thu Nov 28, 2019 8:57 am

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 17 times

Tiger
Posts: 49
Joined: Mon Aug 31, 2015 11:28 am
Location: Taipei
Has thanked: 10 times
Been thanked: 10 times

Post by Tiger » Fri Nov 29, 2019 7:33 am

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: 290
Joined: Sun Aug 25, 2019 3:12 pm
DBs Used: DBF, PostgreSQL, MySQL, SQLite
Location: Hamburg, Germany
Has thanked: 32 times
Been thanked: 39 times

Post by AUGE_OHR » Fri Nov 29, 2019 9:25 am

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

Tiger
Posts: 49
Joined: Mon Aug 31, 2015 11:28 am
Location: Taipei
Has thanked: 10 times
Been thanked: 10 times

Post by Tiger » Tue Dec 03, 2019 6:18 am

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: 290
Joined: Sun Aug 25, 2019 3:12 pm
DBs Used: DBF, PostgreSQL, MySQL, SQLite
Location: Hamburg, Germany
Has thanked: 32 times
Been thanked: 39 times

Post by AUGE_OHR » Tue Dec 03, 2019 6:27 pm

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

Post Reply