none
VB.NET Excel SaveAs XlFileFormat? RRS feed

  • Question

  • I'm using Microsoft Excel 14 Object Library (Excel 2010) and would like to save in older 2003 format (.xls not .xlsx).  When doing a normal .SaveAs("filename.xls")  it works but really it's a xlsx file format named xls.  So when user opens file they get an error.   "The file formatt and extention of 'filename.xls' don't match.  the file could be corrupted or unsafe. unless you trust it's source, don't open it. Do you want to open it anyway?" 

    So if I try what is documented .SaveAs("filename.xls", Excel.XlFileFormat.xlExcel5) nothing is saved.  While debugging the code it resulting with an error message as Public member 'XLFileFormat' on type'ApplicationClassnot found.

    What is the proper syntax to .SaveAs  an older version of excel?  any help is greatly appreciated.  

    Test Code: 

    Public Class Form1
    
        Dim LoadDir As String
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            LoadDir = Environment.CurrentDirectory() & "\"
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            Try
    
                Dim Excel As Object = CreateObject("Excel.Application")
    
                With Excel
                    .SheetsInNewWorkbook = 1
                    .Workbooks.Add()
                    .Worksheets(1).Select()
                    .WorkSheets(1).Name = "Sheet1"
    
                    .Application.DisplayAlerts = False
    
                    '.ActiveCell.Worksheet.SaveAs(LoadDir & "filename.xls")
                    .ActiveCell.Worksheet.SaveAs(LoadDir & "filename.xls", Excel.XlFileFormat.xlExcel8)
                    .Workbooks.Close()
                    .Excel.Quit()
                End With
    
            Catch ex As System.Exception
                'MsgBox(ex.ToString)
            End Try
    
        End Sub
    
    End Class


    Work Smarter Not Harder



    Tuesday, April 10, 2018 3:21 PM

Answers

  • Hello jimbrown.ws,

    XlFileFormat is an enumeration of Microsoft.Office.Interop.Excel namespace, not a member of an Excel application.

    I would suggest you re-name the excel application object for better code reading.

    Here is the simply code.

    Imports Excel = Microsoft.Office.Interop.Excel
    
    
    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
            Try
    
                Dim objExcelApplication As Object = CreateObject("Excel.Application")
    
                With objExcelApplication
                    .SheetsInNewWorkbook = 1
                    .Workbooks.Add()
                    .Worksheets(1).Select()
                    .WorkSheets(1).Name = "Sheet1"
    
                    .Application.DisplayAlerts = False
    
                    '.ActiveCell.Worksheet.SaveAs(LoadDir & "filename.xls")
                    .ActiveCell.Worksheet.SaveAs(LoadDir & "filename.xls", Excel.XlFileFormat.xlExcel8)
                    .Workbooks.Close()
                    .Quit()
                End With
    
            Catch ex As System.Exception
                'MsgBox(ex.ToString)
            End Try
    
        End Sub

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by jimbrown.net Thursday, April 12, 2018 12:29 PM
    Thursday, April 12, 2018 6:02 AM

All replies

  • Hello jimbrown.ws,

    >>"The file formatt and extention of 'filename.xls' don't match.  the file could be corrupted or unsafe. unless you trust it's source, don't open it. Do you want to open it anyway?" 

    If you confirm to open the file, could the file be opened correctly?

    >>Excel.XlFileFormat.xlExcel5

    For 2003, if you try to use xlExcel8, will it work for you?

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 11, 2018 10:10 AM
  • No,  does not even create the file. debugging the code it resulting with an error message as Public member 'XLFileFormat' on type'ApplicationClassnot found.

    Work Smarter Not Harder


    Wednesday, April 11, 2018 12:28 PM
  • Hello jimbrown.ws,

    XlFileFormat is an enumeration of Microsoft.Office.Interop.Excel namespace, not a member of an Excel application.

    I would suggest you re-name the excel application object for better code reading.

    Here is the simply code.

    Imports Excel = Microsoft.Office.Interop.Excel
    
    
    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
            Try
    
                Dim objExcelApplication As Object = CreateObject("Excel.Application")
    
                With objExcelApplication
                    .SheetsInNewWorkbook = 1
                    .Workbooks.Add()
                    .Worksheets(1).Select()
                    .WorkSheets(1).Name = "Sheet1"
    
                    .Application.DisplayAlerts = False
    
                    '.ActiveCell.Worksheet.SaveAs(LoadDir & "filename.xls")
                    .ActiveCell.Worksheet.SaveAs(LoadDir & "filename.xls", Excel.XlFileFormat.xlExcel8)
                    .Workbooks.Close()
                    .Quit()
                End With
    
            Catch ex As System.Exception
                'MsgBox(ex.ToString)
            End Try
    
        End Sub

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by jimbrown.net Thursday, April 12, 2018 12:29 PM
    Thursday, April 12, 2018 6:02 AM
  • Thank you,  Terry

    Work Smarter Not Harder

    Thursday, April 12, 2018 12:30 PM