How to copy and paste excel sheet on office 2106
Moderator: Rathinagiri
How to copy and paste excel sheet on office 2106
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
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
Quizás así...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
*--------------------------------
Maybe so ...
Andrés González López
Desde Guadalajara, Jalisco. México.
Desde Guadalajara, Jalisco. México.
Re: How to copy and paste excel sheet on office 2106
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 …
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 …
- AUGE_OHR
- Posts: 2064
- Joined: Sun Aug 25, 2019 3:12 pm
- DBs Used: DBF, PostgreSQL, MySQL, SQLite
- Location: Hamburg, Germany
Re: How to copy and paste excel sheet on office 2106
hi,
when "internal copy" of Excel does not work like you want what about "external" use Array instead of Clipboard
on other Sheet you can use
have fun
when "internal copy" of Excel does not work like you want what about "external" use Array instead of Clipboard
as i see you have select Range so you also can write
Code: Select all
aValue := oSheet:Range("A1:"+nColumns+nRows):Select():Value
Code: Select all
oSheet:Range("A1:"+nColumns+nRows):Select():Value := aValue
have fun
Jimmy
Jimmy
Re: How to copy and paste excel sheet on office 2106
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 ...
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 ...
- AUGE_OHR
- Posts: 2064
- Joined: Sun Aug 25, 2019 3:12 pm
- DBs Used: DBF, PostgreSQL, MySQL, SQLite
- Location: Hamburg, Germany
Re: How to copy and paste excel sheet on office 2106
sorry , i have just copy/past your Code without run itTiger 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...
are you using Windows 10 there is something like Multi Clipboard ...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 ...
this is ClipBrd.EXE from Windows XP which still run under Windows 10 so you can "look" into Clipboard.
have fun
Jimmy
Jimmy
Re: How to copy and paste excel sheet on office 2106
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.
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.
- AUGE_OHR
- Posts: 2064
- Joined: Sun Aug 25, 2019 3:12 pm
- DBs Used: DBF, PostgreSQL, MySQL, SQLite
- Location: Hamburg, Germany
Re: How to copy and paste excel sheet on office 2106
when start 1st time it is most minimized to lower left, so have a look again.
hm ...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.
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
Jimmy
Re: How to copy and paste excel sheet on office 2106
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 ???
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 ???
- AUGE_OHR
- Posts: 2064
- Joined: Sun Aug 25, 2019 3:12 pm
- DBs Used: DBF, PostgreSQL, MySQL, SQLite
- Location: Hamburg, Germany
Re: How to copy and paste excel sheet on office 2106
hi,
i "think" you still not "activate" Cell of Sheet
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
Jimmy