none
CREATE & OPEN EXCEL FILE RRS feed

  • Question

  • HI,

    i'm trying to create ane excel file.

    when i run the script the first time it creates the file on my desktop but not opening..

    second time i get an error on the line "xl.Workbook.SaveAs(filename)" -

    System.Runtime.InteropServices.COMException:

    'Exception from HRESULT: 0x800A03EC

    what am i doing wrong?

    the script:

       Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            '
            Dim filename As String = "Environment.GetFolderPath(Environment.SpecialFolder.Desktop)" & "\" & "Test3.xlsx"
            If File.Exists(filename) Then
                File.Delete(filename)
    
            End If
    
            Dim xlApp As New Excel.Application
            Dim xlWorkbook As Excel.Workbook = xlApp.Workbooks.Add()
            Dim xlWorksheet As Excel.Worksheet = CType(xlWorkbook.Sheets(1), Excel.Worksheet)
            If xlApp Is Nothing Then
                MsgBox("NO EXCEL")
            End If
            xlWorkbook.Application.DisplayAlerts = False
    
            xlWorksheet.Cells(1, 1) = "data in first cell"
            xlWorkbook.SaveAs(filename)
    
            xlWorkbook.Close()
    
            xlApp.Quit()
    
             xlApp.Workbooks.Open(filename)
    
            xlApp = Nothing
            xlWorkbook = Nothing
            xlWorksheet = Nothing
    
    
        End Sub
    

    thanks.

    Sunday, April 8, 2018 8:21 AM

Answers

  • Hello,

    I would highly suggest not using Excel automation but instead use OpenXML or a wrapper library as Excel automation is prone to failure from version issues e.g. one computer to another with a different version or you or the customer upgrades to a different version or Excel is not installed. Using OpenXML or a wrapper library this is not the case. Also if something goes wrong with a runtime exception using automation usually Excel remains in memory until you kill it, search Google for killing Excel, programmers who don't understand the Excel object model fall prey to this.

    Here is a working example using a free wrapper library called SpreadSheetLight with no restrictions. I separated creation and read for demo purposes only.

        Public sub CreateFileOnDesktop()
            dim fileName as string = Path.Combine(
                Environment.GetFolderPath(Environment.SpecialFolder.Desktop),"Test3.xlsx")
    
            Using doc As New SLDocument()
                doc.SetCellValue("A1","Data in first cell")
                doc.SaveAs(fileName)
            End Using
            OpenDesktopFile(fileName)
        End sub
        Public sub OpenDesktopFile(byval pFileName As string)
            Using doc As New SLDocument(pFileName)
                Dim value as String = doc.GetCellValueAsString("A1")
                Console.WriteLine($"A1 is {value}")
            End Using
        End sub

    See my MSDN code samples for using SpreadsheetLight.

    Basics of using Excel automation in VB.NET with emphasis on creating and destroy 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Sunday, April 8, 2018 9:26 AM
    Moderator

All replies

  • May This Help You

    Corrected Code

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            '
            Dim filename As String = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) & "\" & "Test3.xlsx"
            
            If File.Exists(filename) Then
                File.Delete(filename)
    
            End If
    
            Dim xlApp As New Excel.Application
            Dim xlWorkbook As Excel.Workbook = xlApp.Workbooks.Add()
            Dim xlWorksheet As Excel.Worksheet = CType(xlWorkbook.Sheets(1), Excel.Worksheet)
            If xlApp Is Nothing Then
                MsgBox("NO EXCEL")
            End If
            xlWorkbook.Application.DisplayAlerts = False
    
            xlWorksheet.Cells(1, 1) = "data in first cell"
            xlWorkbook.SaveAs(filename)
    
            xlWorkbook.Close()
    
            xlApp.Quit()
    
            xlApp.Workbooks.Open(filename)
    
            xlApp.Visible = True
    
    
        End Sub



    Sunday, April 8, 2018 8:46 AM
  • thanks!

    i get now an error in line "file.delete(filename)"-

    System.UnauthorizedAccessException:

    'Access to the path 'C:\Users\halif_n\Desktop\Test3.xlsx' is denied.'

    Sunday, April 8, 2018 9:02 AM
  • Hello,

    I would highly suggest not using Excel automation but instead use OpenXML or a wrapper library as Excel automation is prone to failure from version issues e.g. one computer to another with a different version or you or the customer upgrades to a different version or Excel is not installed. Using OpenXML or a wrapper library this is not the case. Also if something goes wrong with a runtime exception using automation usually Excel remains in memory until you kill it, search Google for killing Excel, programmers who don't understand the Excel object model fall prey to this.

    Here is a working example using a free wrapper library called SpreadSheetLight with no restrictions. I separated creation and read for demo purposes only.

        Public sub CreateFileOnDesktop()
            dim fileName as string = Path.Combine(
                Environment.GetFolderPath(Environment.SpecialFolder.Desktop),"Test3.xlsx")
    
            Using doc As New SLDocument()
                doc.SetCellValue("A1","Data in first cell")
                doc.SaveAs(fileName)
            End Using
            OpenDesktopFile(fileName)
        End sub
        Public sub OpenDesktopFile(byval pFileName As string)
            Using doc As New SLDocument(pFileName)
                Dim value as String = doc.GetCellValueAsString("A1")
                Console.WriteLine($"A1 is {value}")
            End Using
        End sub

    See my MSDN code samples for using SpreadsheetLight.

    Basics of using Excel automation in VB.NET with emphasis on creating and destroy 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Sunday, April 8, 2018 9:26 AM
    Moderator
  • thanks!

    i get now an error in line "file.delete(filename)"-

    System.UnauthorizedAccessException:

    'Access to the path 'C:\Users\halif_n\Desktop\Test3.xlsx' is denied.'

    Yet another possible issue with Excel automation if all objects are not released this can cause a UnauthorizedAccessException. Adding to my last example assertion to remove the file if it exists.

    Public sub CreateFileOnDesktop()
        dim fileName as string = Path.Combine(
            Environment.GetFolderPath(Environment.SpecialFolder.Desktop),"Test3.xlsx")
    
        if File.Exists(fileName)
            File.Delete(fileName)
        End If
    
        Using doc As New SLDocument()
            doc.SetCellValue("A1","Data in first cell")
            doc.SaveAs(fileName)
        End Using
    
        OpenDesktopFile(fileName)
    
    End sub


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, April 8, 2018 9:30 AM
    Moderator
  • For the record here is how (if you really want to use Excel automation) to create a new Excel file and release memory.

    Imports 

    Imports Excel = Microsoft.Office.Interop.Excel
    Imports System.Runtime.InteropServices

    Code

    Public Sub CreateFile(ByVal pFileName As String)
    
        If Not IO.File.Exists(pFileName) Then
            Dim xlApp As Excel.Application = Nothing
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
    
    
            xlApp = New Excel.Application
            xlApp.DisplayAlerts = False
    
            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Add()
            Dim worksheets As Excel.Sheets = xlWorkBook.Worksheets
            Dim workSheet As Excel.Worksheet = CType(worksheets(1), Excel.Worksheet)
    
            Dim xlNewSheet = DirectCast(worksheets.Add(workSheet), Excel.Worksheet)
            xlNewSheet.Name = "Karen"
    
            xlWorkBook.SaveAs(pFileName)
    
            xlWorkBook.Close()
            xlApp.UserControl = True
            xlApp.Quit()
    
            If Not xlNewSheet Is Nothing Then
                Marshal.FinalReleaseComObject(xlNewSheet)
                xlNewSheet = Nothing
            End If
    
            If Not workSheet Is Nothing Then
                Marshal.FinalReleaseComObject(workSheet)
                workSheet = Nothing
            End If
    
            If Not worksheets Is Nothing Then
                Marshal.FinalReleaseComObject(worksheets)
                worksheets = Nothing
            End If
    
    
            If Not xlWorkBook Is Nothing Then
                Marshal.FinalReleaseComObject(xlWorkBook)
                xlWorkBook = Nothing
            End If
    
            If Not xlWorkBooks Is Nothing Then
                Marshal.FinalReleaseComObject(xlWorkBooks)
                xlWorkBooks = Nothing
            End If
    
            If Not xlApp Is Nothing Then
                Marshal.FinalReleaseComObject(xlApp)
                xlApp = Nothing
            End If
    
        End If
    End Sub

    Read a single cell and release all objects.

        Public Function ReadSingleCell(ByVal pFileName As String,ByVal pSheetName As String,ByVal pCellAddress As String) As String
    
            Dim Result As String = ""
    
            If IO.File.Exists(pFileName) Then
    
                Dim Proceed As Boolean = False
    
                Dim xlApp As Excel.Application = Nothing
                Dim xlWorkBooks As Excel.Workbooks = Nothing
                Dim xlWorkBook As Excel.Workbook = Nothing
                Dim xlWorkSheet As Excel.Worksheet = Nothing
                Dim xlWorkSheets As Excel.Sheets = Nothing
                Dim xlCells As Excel.Range = Nothing
    
                xlApp = New Excel.Application
                xlApp.DisplayAlerts = False
                xlWorkBooks = xlApp.Workbooks
                xlWorkBook = xlWorkBooks.Open(pFileName)
    
                xlApp.Visible = False
    
                xlWorkSheets = xlWorkBook.Sheets
    
                For x As Integer = 1 To xlWorkSheets.Count
                    xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
    
                    If xlWorkSheet.Name = pSheetName Then
                        Proceed = True
                        Exit For
                    End If
    
                    Marshal.FinalReleaseComObject(xlWorkSheet)
                    xlWorkSheet = Nothing
    
                Next
    
                If Proceed Then
    
                    xlCells = xlWorkSheet.Range(pCellAddress)
                    If xlCells.Value IsNot Nothing Then
                        Result = xlCells.Value.ToString
                    Else
                        Result = "_Empty_"
                    End If
    
                Else
                    MessageBox.Show(pSheetName & " not found.")
                End If
    
                xlWorkBook.Close()
                xlApp.UserControl = True
                xlApp.Quit()
    
                ReleaseComObject(xlCells)
                ReleaseComObject(xlWorkSheets)
                ReleaseComObject(xlWorkSheet)
                ReleaseComObject(xlWorkBook)
                ReleaseComObject(xlWorkBooks)
                ReleaseComObject(xlApp)
            Else
                MessageBox.Show("'" & pFileName & "' not located. Try one of the write examples first.")
            End If
    
            Return Result
    
        End Function

    Release method

    Private Sub ReleaseComObject(ByVal obj As Object)
        Try
            If obj IsNot Nothing Then
                Marshal.ReleaseComObject(obj)
            End If
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        End Try
    End Sub


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, April 8, 2018 9:41 AM
    Moderator