How To Read From One Excel File And Write To Another

[acwoo1]

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

[anserkk]

Code: Select all  Expand view

#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     

[acwoo1]

Thanks

How do I specify only certain worksheets in the files ?

Regards
ACWoo
Using FWH1510

[anserkk]

You can make using the following functions to suit your requirements

Code: Select all  Expand view

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

[acwoo1]

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

[anserkk]

Code: Select all  Expand view

#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

[acwoo1]

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