FiveTech Software tech support forums

Posted: **Tue Jan 01, 2019 2:39 am**

Hi

How to read from one Worksheet in one excel file and write to another worksheet in another excel file.
I am not able to write to worksheet S2.

function test

#define xlDouble -4119

LOCAL oExcel := CREATEOBJECT( "Excel.Application" )

LOCAL oSheet
LOCAL oSheet2

subdirc3 = "WK2.xlsx"
oExcel:WorkBooks:Open( "&subdirc3" )
cNamex = "S1"
oExcel:Sheets( cNamex ):Select()
oSheet2 = oExcel:ActiveSheet

subdirc2 = "WK1.xlsx"
oExcel:WorkBooks:Open( "&subdirc2" )
cName = "S1"
oExcel:Sheets( cName ):Select()
oSheet = oExcel:ActiveSheet
excel1 = oSheet2
excel2 = oSheet
excel1:Cells( 30, 5 ):Value=excel2:Cells( 30, 5 ):Value
excel1:Cells( 30, 6 ):Value=excel2:Cells( 30, 6 ):Value
excel1:Cells( 35, 5 ):Value=excel2:Cells( 35, 5 ):Value
excel1:Cells( 35, 6 ):Value=excel2:Cells( 35, 6 ):Value

subdirc3 = "WK2.xlsx"
oExcel:WorkBooks:Open( "&subdirc3" )
cNamex = "S2"
oExcel:Sheets( cNamex ):Select()
oSheet2 = oExcel:ActiveSheet

subdirc2 = "WK1.xlsx"
oExcel:WorkBooks:Open( "&subdirc2" )
cName = "S2"
oExcel:Sheets( cName ):Select()
oSheet = oExcel:ActiveSheet
excel1 = oSheet2
excel2 = oSheet

excel1:Cells( 30, 5 ):Value=excel2:Cells( 30, 5 ):Value
excel1:Cells( 30, 6 ):Value=excel2:Cells( 30, 6 ):Value

Thanks

ACWoo
Using BCC582 + FHW1510

Posted: **Tue Jan 01, 2019 5:30 am**

Code: Select all

#include "Fivewin.ch"
//----------------------------------//
Function Main()
    
    Local oExcel,oBook,oSheet,cSrcFileName:="D:\FullPath\OfYour\SourceFile.xlsx"
    
    oExcel := ExcelObj()
    oBook  := oExcel:WorkBooks:Add()
    oSheet := oExcel:ActiveSheet
    oExcel:Visible := .T.
    
    ? "About to open the Source Excel file"
    
    oExcel:WorkBooks:Open(cSrcFileName)
    oSrcSheet := oExcel:Get("ActiveSheet")   
    
    ? "About to copy/read from the source sheet and write to the new excel sheet"
    
    oSheet:Cells( 1, 1 ):Value=oSrcSheet:Cells( 1, 1 ):Value 
    oSheet:Cells( 2, 1 ):Value=oSrcSheet:Cells( 2, 1 ):Value 

    ? "Finished writing. now you can view both the excel workbooks"
    
    CursorArrow()
Return NIL

Posted: **Tue Jan 01, 2019 6:14 am**

Thanks

How do I specify only certain worksheets in the files ?

Regards
ACWoo
Using FWH1510

Posted: **Tue Jan 01, 2019 6:58 am**

You can make using the following functions to suit your requirements

Code: Select all

oExcel:Sheets(cSheetName):Select()    //Select sheet
oSheet := oExcel:Sheets(“oSheet1”)  
nSheets := oExcel:Sheets:Count()  //count number of sheets in workbook

Posted: **Tue Jan 01, 2019 8:04 am**

Hi

How do I select worksheet "S1" of Excel File "WK1" and worksheet "S2" of Excel File "WK2" so that I can write certain values from S1 to S2 ?

Thanks

ACWoo
Using FWH1510

Posted: **Tue Jan 01, 2019 9:55 am**

Code: Select all

#include "Fivewin.ch"
//----------------------------------//
Function Main()
    
    Local oExcel,oBook,oSheetS1,oSheetS2
    
    oExcel := ExcelObj()
//  Instead of the next line 
//  oBook  := oExcel:WorkBooks:Add()
    // Use the following line 
    oBook  := oExcel:WorkBooks:Open("Your_WK1_FileName")  // ie "S1" of Excel File "WK1" 
// oSheetS1 := oExcel:ActiveSheet
    oSheetS1 := oExcel:Sheets(“S1”)
    oExcel:Visible := .T.
    
    ? "About to open the Source Excel file"
    
    oExcel:WorkBooks:Open(Your_WK2_FileName)  // ie "S2" of Excel File "WK2"
// oSheetS2 := oExcel:Get("ActiveSheet")   
    oSheetS2 := oExcel:Sheets(“S2”)
    
    ? "About to copy/read from the source sheet and write to the new excel sheet"
    
    oSheetS1:Cells( 1, 1 ):Value=oSheetS2:Cells( 1, 1 ):Value 
    oSheetS1:Cells( 2, 1 ):Value=oSheetS2:Cells( 2, 1 ):Value 

    ? "Finished writing. now you can view both the excel workbooks"
    
    CursorArrow()
Return NIL

Haven't tested the code, please try

Posted: **Mon Jan 07, 2019 7:49 am**

Thanks

Is there something like
File1:oSheetS1:Cells( 1, 1 ):Value=File2:oSheetS2:Cells( 1, 1 ):Value
File1:oSheetS3:Cells( 2, 1 ):Value=File2:oSheetS4:Cells( 2, 1 ):Value

?

Regards

ACWoo
Using FWH1510

FiveTech Software tech support forums

How To Read From One Excel File And Write To Another

How To Read From One Excel File And Write To Another

Re: How To Read From One Excel File And Write To Another

Re: How To Read From One Excel File And Write To Another

Re: How To Read From One Excel File And Write To Another

Re: How To Read From One Excel File And Write To Another

Re: How To Read From One Excel File And Write To Another

Re: How To Read From One Excel File And Write To Another