[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1172
  • Last Modified:

COMException - Error creating Excel Workbook

I'm getting a COMException error when trying to create a workbook in VB.net 2005.

Any thoughts?

Error:
COMException was unhandled
Microsoft Office Excel cannot access the file 'C:\A750D100'. There are several possible reasons:

• The file name or path does not exist.
• The file is being used by another program.
• The workbook you are trying to save has the same name as a currently open workbook.

Code:
Error occurs at line: xlApp.ActiveWorkbook.SaveAs("C:\test.xlsx")

Private Sub Create_Workbook()

        Dim xlApp As New EXCEL.Application
        Dim xlWorkBook As EXCEL.Workbook = xlApp.Workbooks.Add
        Dim xlWorkSheet As EXCEL.Worksheet = CType(xlWorkBook.Worksheets.Add, EXCEL.Worksheet)
        Dim strFileName As String = ""

        Dim misValue As Object = System.Reflection.Missing.Value

        xlWorkSheet.Name = Me.txt_Slip_Number.Text
        xlWorkSheet.Cells(1, 1) = Me.DatePick_Slip_Date.Value.ToString
        xlWorkSheet.Cells(1, 2) = Me.txt_Location.Text
        xlWorkSheet.Cells(1, 3) = "Job No:"
        xlWorkSheet.Cells(1, 4) = Me.txt_Job_Number.Text
        xlWorkSheet.Cells(1, 5) = "Engineer: "
        xlWorkSheet.Cells(1, 6) = Me.txt_Engineer.Text
        xlWorkSheet.Cells(2, 1) = "Contractor: "
        xlWorkSheet.Cells(2, 2) = Me.txt_contractor.Text
        xlWorkSheet.Cells(2, 3) = "SWO#: "
        xlWorkSheet.Cells(2, 4) = Me.txt_SWO_ID.Text

        xlApp.ActiveWorkbook.SaveAs("C:\test.xlsx")

        xlApp.ActiveWorkbook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

        MsgBox("Excel file created , you can find the file c:\")
    End Sub

Open in new window

0
tobin46
Asked:
tobin46
  • 2
  • 2
1 Solution
 
tobin46Author Commented:
Got it....found MSDN article I did not see the first time around.  I like this approach better.


Private Sub Create_Excel_WB()
        Dim oXL As EXCEL.Application
        Dim oWB As EXCEL.Workbook
        Dim oSheet As EXCEL.Worksheet
        Dim oRng As EXCEL.Range

        ' Start Excel and get Application object.
        oXL = CreateObject("Excel.Application")
        oXL.Visible = True

        ' Get a new workbook.
        oWB = oXL.Workbooks.Add
        oSheet = oWB.ActiveSheet

        ' Add table headers going cell by cell.
        oSheet.Cells(1, 1).Value = Me.DatePick_Slip_Date.Value.ToString
        oSheet.Cells(1, 2).Value = Me.txt_Location.Text
        oSheet.Cells(1, 3).Value = "Job No: "
        oSheet.Cells(1, 4).Value = Me.txt_Job_Number.Text
        oSheet.Cells(1, 5).value = "Engineer: "
        oSheet.Cells(1, 6).value = Me.txt_Engineer.Text
        oSheet.Cells(2, 1).value = "Contractor: "
        oSheet.Cells(2, 2).value = Me.txt_contractor.Text
        oSheet.Cells(2, 3).value = "SWO#: "
        oSheet.Cells(2, 4).value = Me.txt_SWO_ID.Text

        ' Format A1:D1 as bold, vertical alignment = center.
        With oSheet.Range("A1", "D1")
            .Font.Bold = True
            .VerticalAlignment = EXCEL.XlVAlign.xlVAlignCenter
        End With

        oSheet.Name = Me.txt_Slip_Number.Text

        ' AutoFit columns A:D.
        oRng = oSheet.Range("A1", "D1")
        oRng.EntireColumn.AutoFit()

        ' Make sure Excel is visible and give the user control
        ' of Excel's lifetime.
        oXL.Visible = True
        oXL.UserControl = True

        oXL.ActiveWorkbook.SaveAs("test.xlsx")

        ' Make sure that you release object references.
        oRng = Nothing
        oSheet = Nothing
        oWB = Nothing
        oXL.Quit()
        oXL = Nothing

        Exit Sub
Err_Handler:
        MsgBox(Err.Description, vbCritical, "Error: " & Err.Number)
    End Sub
End Class

Open in new window

0
 
omegaomegaDeveloperCommented:
Hello, tobin46,

Your code worked fine for me (although I'm using the ancient version of XL2K).  

I'm assuming that there is nothing obviously "funny" that is preventing you from accessing the root of the C: drive.

As a suggestion, you might try adding the line:

            xlApp.Visible = True

just before your "SaveAs" statement.  The set a breakpoint on the SaveAs statement and when the program stops there, execute the SaveAs command from Excel's file menu instead of from the program.  Perhaps you will get a more informative error message that way.

Cheers,
Randy
0
 
tobin46Author Commented:
@Randy:  I'm sorry, looks like our comments crossed streams...I found a good article on MSDN.  

http://support.microsoft.com/kb/301982

I think the xlApp.Visible = True may have made the difference though...

Thanks for your time.
0
 
omegaomegaDeveloperCommented:
Hi, tobin46,

Glad to know that you're on your way.

Cheers,
Randy

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now