How to get the formatting of an Excel file?

[adshocker]

Hi,

I'm trying to create an application that will accept two parameters.
- Source File. This will be a csv file.
- Template File. This will be an excel file with formatting inside but without any data.

So basically my goal is open the Template File, get all the formatting inside, close it then open the Source File, apply all of the formatting then convert and save it as an Excel file. This is using Excel Interop 2007 on Visual Studio 2008.

Is there a way to do this?

Thanks.

[westconn1]

without testing i would think the simplest solution would be
to open the csv file as an excel sheet
copy the formated sheet from the template
copy the data from csv sheet to formatted sheet

[adshocker]

Hi,

Thanks for the tip.

I did some research on Copying and came up with these:

        Dim xlApp As New Excel.Application
        Dim csvFile As String = "C:\temp\test.csv"
        Dim xlsfile As String = "C:\temp\test.xlsx"

        Console.WriteLine(xlsfile)

        If System.IO.File.Exists(xlsfile) Then
            System.IO.File.Delete(xlsfile)
        End If

        Dim xls As New Excel.Application

        Try
            With xlApp
                .Workbooks.Open(csvFile)
                xls.Workbooks.Open(xlsfile)
                xls.ActiveWorkbook.Worksheets(1).Copy(After:=.ActiveWorkbook.Worksheets(1))
                xls.ActiveWorkbook.Save()
                xls.ActiveWorkbook.Close()
                .Workbooks.Close()
                .Quit()
                xls.Quit()
            End With
        Catch ex As Exception
            Console.WriteLine(ex.Message)
            xlApp.Quit()
            xls.Quit()
        Finally
            xls.Quit()
            xlApp.Quit()
            Marshal.ReleaseComObject(xlApp)
        End Try

        Console.WriteLine("Finished")
        Console.ReadLine()

But I keep getting the error "Copy method of Workbook class failed".

I found an article about this error but somehow the sample doesn't give a sample of copying from one excel file to another.

http://support.microsoft.com/kb/210684

Can anyone help.

Thanks.

Edit: The test.xlsx file is my template and the csv is the source data. So basically I'm trying to copy the data from the source csv and paste it in the formatted template.

[westconn1]

activeworkbook is not valid in this scope, should be xls.activeworkbook, or better specify a workbook object to the specific workbook