How to copy excel 2010 .xltm file to .xlsm in vb.net?

Question

Hi,

I am trying to create an excel 2010 .xlsm file from an excel 2010 .xltm file in vb.net.

I do not get any errors in vb.net for the following code

System.IO.File.Copy("C:\temp\template.xltm", "C:\temp\newfile.xlsm", False)

and the file newfile.xlsm is created, but when I try to open newfile.xlsm file I get the error message

"Excel cannot open the file 'newfile.xlsm' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file."

For excel 2003 files it is possible to copy a .xlt file to a .xls file in a similiar manner, how to do this for macro enabled files in excel 2010??

BR

/Siri

 

---

Siri Haglund

Answer 1

The xml's are not compatible, eg [Content_Types].xml in the xltm file would include

ContentType="application/vnd.ms-excel.template.macroEnabled.main+xml"
but in the xlsm file
ContentType="application/vnd.ms-excel.sheet.macroEnabled.main+xml"

there may be other differences.

Easiest would be to open the template in Excel (could be a non-visible automated instance) as a NEW file and saveas an xlsm

Peter Thornton

Answer 2

Thank you for your reply.

But how do you mean, how to create a "non-visible automated instance" and automatically (from vb.net) save as .xlsm?

This is very strange, is the solution to stop using template files? I suppose my problem is solved if I do not use the .xltm format and instead use the .xltx for my "template file".

BR /Siri 

---

Siri Haglund

Answer 3

But how do you mean, how to create a "non-visible automated instance" and automatically (from vb.net) save as .xlsm?

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

For your purposes no need to add all the references just for this, not tested

Sub abc()
Dim sTemplate As String
Dim sFile As String
Dim objXL As Object
Dim objWB As Object

sTemplate = "C:\<path1>\myTemplate.xltm"
sFile = "C:\<path2>\myFile.xlsm"

' VBA
'Set objXL = CreateObject("excel.application")
'Set objWB = objXL.Workbooks.Add(sTemplate)

objXL = CreateObject("excel.application")
objWB = objXL.Workbooks.Add(sTemplate)

objWB.SaveAs sFile, 52 'xlOpenXMLWorkbookMacroEnabled
objWB.Close False
objXL.Quit

End Sub
 > This is very strange, is the solution to stop using template files? I

suppose my problem is solved if I do not use the .xltm format and instead use the .xltx for my "template file".

I suspect the confusion here is your objective for a "template" is not the same as Excel's (a new template opens unsaved without an extension). For some purposes it is indeed better to use an xls as a template, which you could keep with any extension you want to avoid opening it accidentally in Excel.

Peter Thornton

Answer 4

Thanks Peter for your explanation.

Hi Siri,

You need to use Workbook.SaveAs method to create a .xlsm file from a template workbook.

To do this, you need to add reference to Office Object first:

Add reference -> .NET tab -> Microsoft.Office.Interop.Excel 14.0 version

Use the snippet code below to create a .xlsm file based on the template workbook

        Dim fileName As String = "C:\Template.xltm"
        Dim DesName As String = "C:\Template2.xlsm"
        Dim oExcel As Excel.Application = New Excel.Application
        'open the template workbook
        Dim wb As Excel.Workbook = oExcel.Workbooks.Open(fileName)
        'save the template workbook as .xlsm format
        wb.SaveAs(DesName, Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled)

        wb.Close()
        oExcel.Quit()
        wb = Nothing
        oExcel = Nothing

Good day,

---

Best Regards, Calvin Gao [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 5

Thank you for helping me, Peter Thornton and Calvin Gay.

I am sorry, maybe I did not use the correct terminology for "templates". I simply defined a "template" as an excel file with the the .xlt/.xltx/.xltm extension. 

I have now tried to do as you suggest, to use the Excel.Application object and Workbook.SaveAs method and this works fine. A new .xlsm file is created from my .xltm file. 

My problem now is that I am not able to fill the .xlsm file with information from my database via OLEDB.

I have plenty of named ranges defined in my workbook which I fill with information from database using INSERT statements (like INSERT INTO [DATA_RANGE1] VALUES ('var1','var2','var3') where DATA_RANGE1 is my defined name range). I do not get any exceptions running the insert statements, everything seems to work fine, but file is empty when opening it.

But if I create a new empty .xltm file and define a named range DATA_RANGE1 and save this file as a new .xlsm file, the OLEDB insertion is working, so I suppose it is something problematic with my file. Do you have any suggestion for this case?

Here is my code, with methods for copying template file, printing to file with excel application object and printing to file via oledb provider:

Imports Microsoft.Office.Interop

Imports Microsoft.Office.Core



Public Class Automation2



    'DATA_RANGE1 is a defined range name in mytemplate.xltm, definition like =Sheet1!$A$1:$C$1

    Private tempFile As String = "C:\temp\myTemplate.xltm"

    Private newFile As String = "C:\temp\myFile.xlsm"



    Private Sub PrintToFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PrintToFile.Click



        Dim objWB As Object



        Try

            Dim oApp As New Excel.Application()

            oApp.Visible = True

            oApp.UserControl = True

            Dim oldCI As System.Globalization.CultureInfo = _

                System.Threading.Thread.CurrentThread.CurrentCulture

            System.Threading.Thread.CurrentThread.CurrentCulture = _

                New System.Globalization.CultureInfo("en-US")

            objWB = oApp.Workbooks.Add(newFile)



            Dim oSheet As Excel.Worksheet = objWB.ActiveSheet



            Dim cmdArr(,) As String = {{"var1", _

                                       "var2", _

                                       "var3"}, _

                                       {"var1_", _

                                       "var2_", _

                                       "var3_"}}



            oSheet.Range("DATA_RANGE1").Resize(2, 3).Value2 = cmdArr



            objWB.SaveAs(newFile, 52) 'xlOpenXMLWorkbookMacroEnabled



            objWB.Close(False)

            System.Threading.Thread.CurrentThread.CurrentCulture = oldCI

            oApp.Quit()

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

    End Sub



    Private Sub btnCopyFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCopyFile.Click

        Try



            Dim oExcel As Excel.Application = New Excel.Application



            Dim oldCI As System.Globalization.CultureInfo = _

                       System.Threading.Thread.CurrentThread.CurrentCulture

            System.Threading.Thread.CurrentThread.CurrentCulture = _

                New System.Globalization.CultureInfo("en-US")



            'open the template workbook

            Dim wb As Excel.Workbook = oExcel.Workbooks.Open(tempFile)

            'save the template workbook .xltm in .xlsm format

            wb.SaveAs(newFile, Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled)



            wb.Close()

            oExcel.Quit()

            wb = Nothing

            oExcel = Nothing

            System.Threading.Thread.CurrentThread.CurrentCulture = oldCI

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try



    End Sub



    Private Sub btnOLEDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOLEDB.Click

        Dim myConnection As System.Data.OleDb.OleDbConnection

        Dim myCommand As New System.Data.OleDb.OleDbCommand

        Try



            myConnection = New System.Data.OleDb.OleDbConnection _

               ("provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + _

               "'" + newFile + "';Extended Properties=Excel 12.0 Macro;")

            myConnection.Open()

            myCommand.Connection = myConnection



            Dim commandText As System.Text.StringBuilder = New System.Text.StringBuilder()

            commandText.Append(String.Format("INSERT INTO [DATA_RANGE{0}] VALUES ('{1}','{2}','{3}')", _

                                             1, _

                                             "var1", _

                                             "var2", _

                                             "var3"))



            myCommand.CommandText = commandText.ToString()



            myCommand.ExecuteNonQuery()

            myConnection.Close()



            If Not IsNothing(myCommand) Then

                myCommand.Dispose()

            End If

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

    End Sub



End Class

What method is best/fastest, using OLEDB to fill excel file from database, or using the Excel.Application object?

I have not found out if it is possible to execute multiple SQL statements with the OLEDB provider? If I want to fill a defined range with a matrix of values, is it possible to do this with the OLEDB provider to an excel file? Or do I need to use the Excel.Application object for this? Or what method is best to use? It is time consuming doing a lot of INSERT call statement after each other if you have many rows to insert.

Sorry if too many questions, I tried to separate them in forum, but did not manage.

BR /Siri

---

Siri Haglund

Answer 6

Hi Siri,

Would you like to open a new thread for your new question in this forum as the original question has been solved? In addition, could you close the thread by making appropriate replies as answers?

After posting a new thread, you can post a link here, and I'll follow up with you in the new thread.

Thanks for your understanding and support.

Good day,

---

Best Regards, Calvin Gao [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 7

Hi,

ok, sorry! I have now started a new thread instead ("How to fill named range in Office 2010 excel file via OLEDB"), and of course marked your answers as Answers too since those really helped me, thank you again! 

BR /Siri

 

 

---

Siri Haglund