while copying from one excel sheet to other formatting of sheet get lost in VB.net

Question

                    Dim objworkbook As Excel.Workbook
                    objworkbook = objExcelAppDataSheet.Workbooks.Add(strTemplatePath)
                    Dim sheet_new As Excel.Worksheet
                    sheet_new = objworkbook1.Sheets.Add()
                    sheet_new.Name = objworksheet.Name
                    objworksheet.Cells.Range("A1", "EX150").Copy()
                    sheet_new.Paste()
                    Clipboard.Clear()
                    objworkbook.Close(SaveChanges:=False)

objExcelAppDataSheet is the excel application's object and strTemplatePath is the path of the excel file... This excel file is like a template that i am using in my application. In this file some cells are merged. After copying to the new sheet the formatting of the sheet does not remain same. Columns sizes are changing. Why this problems occurs?? Is this MS office versions issue??

Answer 1

Have you tried to copy the whole sheet instead of just some cells like in this example.

Answer 2

The below example assumes you are copying from your source (template) workbook to another workbook. Your question isn't clear on that point, so this is what I assumed.

  Dim strTemplatePath As String = "...your source workbook path and name..."
  Dim strDestinationPath As String = "...your destination workbook path and name..."

  Dim sourceWorkBook As Excel.Workbook
  Dim destWorkBook As Excel.Workbook

  sourceWorkBook = objExcelAppDataSheet.Workbooks.Add(strTemplatePath)
  destWorkBook = objExcelAppDataSheet.Workbooks.Add(strDestinationPath)

  Dim mySourceSheetName As String = "...your source worksheet name..."

  Dim sourceWorkSheet As Excel.Worksheet
  sourceWorkSheet = sourceWorkBook.Sheets(mySourceSheetName)

  //Copies the source worksheet to the destination workbook, places it after the last
  //sheet in the destination workbook.
  sourceWorkSheet.Copy(, destWorkBook.Sheets(destWorkBook.Sheets.Count))

  destWorkBook.SaveAs(strDestinationPath)

  destWorkBook.Close()
  sourceWorkBook.Close()

If you are just copying and pasting in the source workbook ( not using a different destination ) you can just remove the destination workbook portions of the code. Then your Copy statement would look like this:

sourceWorkSheet.Copy(, sourceWorkBook.Sheets(sourceWorkBook.Sheets.Count))

This will place the source sheet after the last sheet in your source workbook.

If you want to duplicate the formatting that includes column widths, row heights, cell merging, etc. in your new sheet, you need to copy the entire sheet.

If you don't want all of the data on the source sheet, but you still want the column widths, etc., you must first copy the entire sheet; then clear the newly copied sheet. This will remove the data, but not the formatting. After that, then you can copy just the range of cells that has the data you want.