Page 1 of 2
How to copy and paste excel sheet on office 2106
Posted: Tue Nov 26, 2019 2:23 pm
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
Re: How to copy and paste excel sheet on office 2106
Posted: Tue Nov 26, 2019 4:46 pm
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 ...
Re: How to copy and paste excel sheet on office 2106
Posted: Wed Nov 27, 2019 1:52 am
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 …
Re: How to copy and paste excel sheet on office 2106
Posted: Wed Nov 27, 2019 11:20 pm
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
Re: How to copy and paste excel sheet on office 2106
Posted: Thu Nov 28, 2019 8:47 am
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 ...
Re: How to copy and paste excel sheet on office 2106
Posted: Thu Nov 28, 2019 8:57 am
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 ...
this is ClipBrd.EXE from Windows XP which still run under Windows 10 so you can "look" into Clipboard.
Re: How to copy and paste excel sheet on office 2106
Posted: Fri Nov 29, 2019 7:33 am
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.
Re: How to copy and paste excel sheet on office 2106
Posted: Fri Nov 29, 2019 9:25 am
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.
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
Re: How to copy and paste excel sheet on office 2106
Posted: Tue Dec 03, 2019 6:18 am
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 ???
Re: How to copy and paste excel sheet on office 2106
Posted: Tue Dec 03, 2019 6:27 pm
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