I have a program where I create a .csv File for Data and Open Excel using this file (call it Data.csv). Note: All ““Filenames”” are with full path, e.g., ““C:Data.csv”” I then want to Save the Excel file in the Excel File format .xlsx with the name Data.xlsx. It appears to Save the file Data.xlsx, but when I subsequently go to open the Excel.xlsx file manually in Excel, I get an error message saying that the ““file is corrupted”” and can’t be opened in Excel. Here are the pertinent VB.NET program statements that I use in the program. Maybe someone can see where my error is in trying to do this under VB.Net program control? Sub OpenOutputFile() '----- Subroutine to Open the Excel Compatible Data.csv File & Save As Data.xlsx File Dim xlsApp As Excel.ApplicationClass Dim xlsWB As Excel.WorkbookClass Dim dialogCsv As New OpenFileDialog() Dim txtFile As String Dim txtFileName As String Dim intIndexDot As Int32 Dim strFileNameXlsx As String If DialogResult.OK = dialogCsv.ShowDialog Then txtFile = dialogCsv.FileName End If xlsApp = New Excel.ApplicationClass xlsApp.Visible = True xlsWB = xlsApp.Workbooks.Open(txtFile) Try '----- Delete the “”.csv"" suffix and substitute the “”.xlsx"" suffix in the new filename intIndexDot = txtFile.IndexOf(“”.“”) txtFileName = txtFile.Remove(intIndexDot + 1, 3) strFileNameXlsx = txtFileName & ““xlsx”” If File.Exists(strFileNameXlsx) Then MessageBox.Show(““File “” & strFileNameXlsx & “” Already Exits and will be Deleted to Allow Update””) File.Delete(strFileNameXlsx) End If dialogCsv.AddExtension = True dialogCsv.DefaultExt = ““xlsx”” xlsWB.SaveAs(strFileNameXlsx) ’ ? Apparently this does NOT WORK to provide an Excel Compatible File Catch ex As Exception ’ ? No Error Messages are Displayed MessageBox.Show(""ERROR = “” & ex.Message) End Try End Sub
Michael,
Your provided information is always of value to me. The key working statements are as follows:
Imports Microsoft.Office.Interop.Excel.WorkbookClass
xlsApp = New Excel.ApplicationClass
xlsWB = xlsApp.Workbooks.Open(txtFile) '----- txtFile = Data.csv
intIndexDot = txtFile.IndexOf(“.”)
txtFileName = txtFile.Remove(intIndexDot + 1, 3)
strFileNameXlsx = txtFileName & “xlsx”
xlsWB.SaveAs(Filename:=strFileNameXlsx, FileFormat:=xlExcel7, CreateBackup:=False)
These statements resulted in the desired “Data.xlsx” file that can be opened into Excel 2010.
Thank you for your help, Russ
Russell,
I apologize if my post included information you already knew. I wanted to ensure that it was useful to you, and to other readers.
You’ve defined xlsWB as Excel.WorkbookClass - I believe the correct data type is Excel.Workbook. Try that and let us know if it changes the behavior.
Michael S. Meyers-Jouan
Michael,
Yes, I knew that the file structures were different for .csv and .xlsx formatted files. However, when I used the following statement in place of the one below [ xlsWB.SaveAs(strFileNameXlsx) ] for “SaveAs”
xlsWB.SaveAs(Filename:=strFileNameXlsx, FileFormat:= = xlOpenXMLWorkbook, CreateBackup:=False)
I got the dreaded “blue underline” in Visual Studio telling me that VB.NET did not recognize the keyword “FileFormat.”
It seems to me that there should be some way to perform the same operations through VB.NET that one can manually do to “SaveAs” a .csv file format opened in Excel into an Excel compatible .xlsx file format.
Currently, I direct the User (through a MessageBox message) to do this Manually once Excel has opened with the .csv file. I’d prefer to do this all under VB.NET program control.
Any further help along these lines would be appreciated.
Thanks, Russ
Russell,
You need to understand that different types of files have different internal structures.
A .CSV file has a very simple structure. It contains readable text. Each line of text is delimited by a line ending (typically a Carriage Return character followed by a Line Feed character). Each “field” on the line is delimited by a “separator” character. The most common separator character is a comma - hence Comma Separated Value (CSV).
An Excel file has a much more complicated structure. It represents a Workbook that can have multiple Worksheets, each of which has multiple Cells, where each cell can have a complex formula. It is nothing like a CSV file.
While there is a convention to use different “extensions” (the three of four characters after the last period in the file name) to identify different file types, it’s only an agreement among computer users. Simply changing the extension does not change the file type. You are effectively just renaming the file - and that’s what your code does.
Therefore, when you attempt to open the .XLSX file in Excel, the program can’t interpret the text file as a workbook, so it displays the error you are seeing.
To actually create an .XLSX file, you need to use the .SaveAs method with additional arguments. Otherwise, you are simply saving the current file format (CSV) with a different name.
Remember that The Help File Is Your Friend. Search for “Workbooks.SaveAs” to read the documentation on this method. You will find that you need (at least) a second argument to specify the FileFormat that you want to save.
Michael S. Meyers-Jouan
