FiveTech Support Forums

FiveWin / Harbour / xBase community
Board indexAll discussionsLogin (GitHub)
Board index FiveWin for Harbour/xHarbour How To Read From One Excel File And Write To Another
acwoo1
Posts: 175
Joined: Tue Nov 10, 2009 10:56 AM
How To Read From One Excel File And Write To Another
Posted: Tue Jan 01, 2019 02: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

anserkk
Posts: 1335
Joined: Fri Jun 13, 2008 11:04 AM
Re: How To Read From One Excel File And Write To Another
Posted: Tue Jan 01, 2019 05:30 AM
Code (fw): Select all Collapse
#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
Posts: 175
Joined: Tue Nov 10, 2009 10:56 AM
Re: How To Read From One Excel File And Write To Another
Posted: Tue Jan 01, 2019 06:14 AM

Thanks

How do I specify only certain worksheets in the files ?

Regards
ACWoo
Using FWH1510

anserkk
Posts: 1335
Joined: Fri Jun 13, 2008 11:04 AM
Re: How To Read From One Excel File And Write To Another
Posted: Tue Jan 01, 2019 06:58 AM
You can make using the following functions to suit your requirements
Code (fw): Select all Collapse
oExcel:Sheets(cSheetName):Select()    //Select sheet
oSheet := oExcel:Sheets(“oSheet1”)  
nSheets := oExcel:Sheets:Count()  //count number of sheets in workbook
acwoo1
Posts: 175
Joined: Tue Nov 10, 2009 10:56 AM
Re: How To Read From One Excel File And Write To Another
Posted: Tue Jan 01, 2019 08: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

anserkk
Posts: 1335
Joined: Fri Jun 13, 2008 11:04 AM
Re: How To Read From One Excel File And Write To Another
Posted: Tue Jan 01, 2019 09:55 AM
Code (fw): Select all Collapse
#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
Posts: 175
Joined: Tue Nov 10, 2009 10:56 AM
Re: How To Read From One Excel File And Write To Another
Posted: Mon Jan 07, 2019 07: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

Continue the discussion

Static archive · New replies & topics via GitHub Discussions