Jump to content
Sign in to follow this  

Excel worksheet copy

Recommended Posts

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

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

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

Small example from me:

$sFile2 = "11.xls"
$sFile1 = "22.xls"
$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 1
$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.

Share this post

Link to post
Share on other sites


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
  $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
  $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  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Create New...