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

  • 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
    Friday, September 9, 2011 7:34 AM

Answers

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

    • Marked as answer by Siri Haglund Wednesday, September 14, 2011 11:12 AM
    Monday, September 12, 2011 6:51 AM
  • 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

    • Marked as answer by Siri Haglund Wednesday, September 14, 2011 11:12 AM
    Friday, September 9, 2011 10:27 AM
  • 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.

    • Marked as answer by Siri Haglund Wednesday, September 14, 2011 11:32 AM
    Wednesday, September 14, 2011 9:28 AM

All replies

  • 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

    Friday, September 9, 2011 9:19 AM
  • 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
    Friday, September 9, 2011 9:31 AM
  • 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

    • Marked as answer by Siri Haglund Wednesday, September 14, 2011 11:12 AM
    Friday, September 9, 2011 10:27 AM
  • 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.

    • Marked as answer by Siri Haglund Wednesday, September 14, 2011 11:12 AM
    Monday, September 12, 2011 6:51 AM
  • 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
    • Edited by Siri Haglund Wednesday, September 14, 2011 8:44 AM
    Tuesday, September 13, 2011 9:45 AM
  • 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.

    • Marked as answer by Siri Haglund Wednesday, September 14, 2011 11:32 AM
    Wednesday, September 14, 2011 9:28 AM
  • 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
    Wednesday, September 14, 2011 11:31 AM