Jump to content
Sign in to follow this  
singbass

Excel worksheet copy

Recommended Posts

singbass

I have two issues. I have multiple spreadsheets, each with one worksheet. I want to copy all of these into one spreadsheet with multiple worksheets.

I have been able to copy the contents from one worksheet to a new worksheet in a different spreadsheet by using the commands;

$aArray = _ExcelReadSheetToArray($iExcel) ;Using Default Parameters
_ExcelWriteSheetFromArray($oExcel, $aArray)

However, this does not preserve the formatting (lots of cells with different colored backgrounds as well as italics and bold). Is there a way to copy a worksheet from one spreadsheet to another and keep the formatting? In the forums I see _ExcelCopy() and thought that might work but I don't see that in my help file. Currently running V 3.3.4.0 dated January 15th, 2010. Didn't know if an update might get me what I need, but right now I am being blocked by the firewall and Websense.

Share this post


Link to post
Share on other sites
Macros

Since you haven't listed any of the sheet names, etc I will attempt to point you in the right direction...

Try creating a variation of the _ExcelSheetMove() function from the include excel.au3 file.

Instead of moving the sheet from inside the workbook, changing the function to use another workbook entirely.

hope that helps some! Perhaps someone else has actually done this and can point it out a more efficient way.

Share this post


Link to post
Share on other sites
enaiman

AFAIK the functions you use copy only the values, not the formatting.

You will need to write your own functions to be able to do that - no idea if anyone has done that yet.

It's not an easy task but you can do it if you have enough time and patience.


SNMP_UDF ... for SNMPv1 and v2c so far, GetBulk and a new example script

wannabe "Unbeatable" Tic-Tac-Toe

Paper-Scissor-Rock ... try to beat it anyway :)

Share this post


Link to post
Share on other sites
Juvigy

Small example from me:

$sFile2 = "11.xls"
$sFile1 = "22.xls"
$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 1
$oExcel.WorkBooks.Open("c:\11.xls")
$oExcel.WorkBooks.Open("c:\22.xls")
$oExcel.Application.Workbooks($sFile1).Sheets("Sheet1").Select ;or you acn use sheet number $oExcel.Application.Workbooks($sFile1).Sheets(1).Select
$oExcel.Application.ActiveWorkbook.Sheets("Sheet1").Copy(Default, $oExcel.Application.Workbooks($sFile2).Sheets("Sheet1"))

You can easily modify it.

  • Like 1

Share this post


Link to post
Share on other sites
PoojaKrishna

Hi,

Thank you for your sample Juvigy. It worked with some modifications

$oExcel.WorkBooks.Open($sInputFilePath) ;Open input file
  $oExcel.WorkBooks.Open($sResultFile) ;Open result file
  Local $o_workbooks = $oExcel.Application.Workbooks ;Get all active worksheets
  If Not IsObj($o_workbooks) Or $o_workbooks.Count = 0 Then ;if no worksheets
   SetError(7);Set error
   Return (0);Return
  EndIf
  $nSheetCount = $o_workbooks($sInputFilePath).Sheets.count ;get number of sheets of the input work book
  For $j = 1 to $nSheetCount ;For each sheet
   $o_workbooks($sInputFilePath).Sheets($j).Copy($o_workbooks($sResultFile).Sheets("Sheet1")) ;copy sheet and paste before sheet1 of resultant workbook
  Next
  $o_workbooks($sInputFilePath).close() ;Close input file
  $o_workbooks($sResultFile).close() ;Close result file

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×