• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 624
  • Last Modified:

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
0
edfreels
Asked:
edfreels
1 Solution
 
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
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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
 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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