Answered by:
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 HaglundFriday, 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 ObjectsTemplate = "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.QuitEnd Sub
> This is very strange, is the solution to stop using template files? Isuppose 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 HaglundFriday, 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 ObjectsTemplate = "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.QuitEnd Sub
> This is very strange, is the solution to stop using template files? Isuppose 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 HaglundWednesday, September 14, 2011 11:31 AM