Converting Text File to Excel in VB2005

I have the following code,

Dim XLS As Excel.Application = New Excel.Application
            Dim WRK As Excel.Workbook = XLS.Workbooks.Add
            Dim Sheet As Object = WRK.Sheets.Add

            XLS.Cells(1, 1).Value = "ReportFile"
            XLS.Cells(2, 1).Value = "ReportJobNumber"
            Dim XlFormat As Excel.XlFileFormat = Excel.XlFileFormat.xlWorkbookNormal
            Try
                XLS.ActiveWorkbook.SaveAs("c:\report.xls", XlFormat, String.Empty, String.Empty, False, False, Excel.XlSaveAsAccessMode.xlNoChange)
            Catch e As Exception
                System.Windows.Forms.MessageBox.Show(e.Message, e.Source, MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
            WRK.Close()
            XLS.Quit()

Unfortunately I am getting some errors in vb2005.  It does not like the Excel.Application  Can someone tell me what to change that to, to get it to work.

Thanks
LVL 1
edfreelsAsked:
Who is Participating?
 
SanclerCommented:
It sounds like you're doing it right.  But the symptoms - the Designer not offering Excel - sound like the reference is missing.

In Solution Explorer, click the button at the top for "Show All Files".  Open up the References Node.  Is Excel listed there?

Roger
0
 
Bob LearnedCommented:
Did you add a COM reference to the Microsoft Excel type library to the project?

Bob
0
 
edfreelsAuthor Commented:
yes I added the excel 11.0 which is the latest, but still no go
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
ElrondCTCommented:
When you say "it does not like the Excel.Application", specifically what error message(s) are you getting? Is this in the designer (an error that prevents building the app), or is it when you run the app?
0
 
SanclerCommented:
Do you still have Microsoft Excel 10.0 Object Library on your machine.  I just tried your exact code with a reference to that and it worked fine for me.

Roger
0
 
edfreelsAuthor Commented:
ElrondCT, it is in the designer that it does not like, it offers a replacement that is just application, but that will not work

Roger, I do not have 10.0 on it.  Maybe I am doing it wrong, you should just go and add the reference right?  That is what I did and it made no difference, but if I am doing it wrong let me know.  

Thanks
0
 
edfreelsAuthor Commented:
Yea excel is there Roger,

I have this code now


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim XLS As Microsoft.Office.Interop.Excel.Application = New Excel.Application
        Dim WRK As Microsoft.Office.Interop.Excel.Workbook = XLS.Workbooks.Add
        Dim Sheet As Object = WRK.Sheets.Add

        XLS.Cells(1, 1).Value = "ReportFile"
        XLS.Cells(2, 1).Value = "ReportJobNumber"
        Dim XlFormat As Microsoft.Office.Interop.Excel.XlFileFormat = Excel.XlFileFormat.xlWorkbookNormal
        Try
            XLS.ActiveWorkbook.SaveAs("c:\report.xls", XlFormat, String.Empty, String.Empty, False, False, (Excel.XlSaveAsAccessMode.xlNoChange)
        Catch e As Exception
            System.Windows.Forms.MessageBox.Show(e.Message, e.Source, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
        WRK.Close()
        XLS.Quit()
    End Sub

It is ok with the other now, but still not liking Excel.Application

So not sure what to change that to
0
 
edfreelsAuthor Commented:
gives me another error too "variable e hides a variable in an enclosing block"
0
 
edfreelsAuthor Commented:
and when I try to run the program it tells me .exe is not found.  Never had this kind of trouble.
0
 
edfreelsAuthor Commented:
Ok I got it

Here is the final code.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim XLS As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application
        Dim WRK As Microsoft.Office.Interop.Excel.Workbook = XLS.Workbooks.Add
        Dim Sheet As Object = WRK.Sheets.Add

        XLS.Cells(1, 1).Value = "ReportFile"
        XLS.Cells(2, 1).Value = "ReportJobNumber"
        Dim XlFormat As Microsoft.Office.Interop.Excel.XlFileFormat = Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal
        Try
            XLS.ActiveWorkbook.SaveAs("c:\report.xls", XlFormat, String.Empty, String.Empty, False, False, (Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange))
        Catch d As Exception
            System.Windows.Forms.MessageBox.Show(d.Message, d.Source, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
        WRK.Close()
        XLS.Quit()
    End Sub

Thanks for the pointers
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.