Excel worksheet copy

[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.

[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.

[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.

[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.

[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