[RESOLVED] HELP, Importing and exporting specific cells from Excel to Visual Studio

[callumwk]

Hello!

Firstly, I need a text box and a button. On the click of the button it will send what ever is in the text box into a cell in an excel document. I have this:

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click


        Dim NumericTextBox1 As RichTextBox = New RichTextBox()
        NumericTextBox1.Parent = Me

        Dim sFoo As String = TextBox1.Text


2:
        Dim mySW As New IO.StreamWriter(My.Computer.FileSystem.SpecialDirectories.Desktop + "\Repair_Income.xlsx", True)

5:
        mySW.WriteLine(sFoo)
6:
        mySW.Close()

        Close()


    End Sub

It works well apart from when you go to open the Excel file, it says it is saved in the wrong format. I need this excel document to contain formulae so it is important it can be read.



Secondly, I have these text boxes to show a said formulae. So in the Excel document, values are added and the answer is produced in this window:



Using Excel as an external information handler may be the wrong way to go about this, so any alternatives are welcome however I cannot get an SQL server to work.

Thanks

[callumwk]

And for any real smart arses, is it possible to use the system clock and zero everything when there is a new year, but save the data from last year?

[vbfbryce]

This is what I would use to write values to a spreadsheet (add a reference to the Excel Object Library first):

Imports Microsoft.Office.Interop.Excel

Public Class Form1

    Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
        Dim oExcel As Object
        Dim oBook As Object
        Dim oSheet As Object
        Dim writeRow As Long

        oExcel = CreateObject("Excel.Application")
        oBook = oExcel.Workbooks.Open(My.Computer.FileSystem.SpecialDirectories.Desktop + "\Repair_Income.xlsx")
        oSheet = oBook.worksheets(1)
        writeRow = oSheet.cells(oSheet.rows.count, 1).end(XlDirection.xlUp).row + 1

        oSheet.range("a" & writeRow).value = txtInput.Text

        oBook.close(True)
        oSheet = Nothing
        oBook = Nothing
        oExcel = Nothing
    End Sub
End Class

This will write to the next blank cell in column A, in Sheet 1, so change as required.

[callumwk]

This is what I would use to write values to a spreadsheet (add a reference to the Excel Object Library first):

Imports Microsoft.Office.Interop.Excel

Public Class Form1

    Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
        Dim oExcel As Object
        Dim oBook As Object
        Dim oSheet As Object
        Dim writeRow As Long

        oExcel = CreateObject("Excel.Application")
        oBook = oExcel.Workbooks.Open(My.Computer.FileSystem.SpecialDirectories.Desktop + "\Repair_Income.xlsx")
        oSheet = oBook.worksheets(1)
        writeRow = oSheet.cells(oSheet.rows.count, 1).end(XlDirection.xlUp).row + 1

        oSheet.range("a" & writeRow).value = txtInput.Text

        oBook.close(True)
        oSheet = Nothing
        oBook = Nothing
        oExcel = Nothing
    End Sub
End Class

This will write to the next blank cell in column A, in Sheet 1, so change as required.

Thats works nicely thank you, however I still have this problem:

[callumwk]

It seems that no matter what ""\Repair_Income.xls"" is set to it will always make it a xlsx file?

[vbfbryce]

When I tried your code, it created a file that I couldn't open (corrupted somehow). When I started over and changed to Repair_Income2.xlsx, it worked for me, then I could open it.

I created the file first, with only a heading in cell A1, then I ran the code. See if that change works or no.

[callumwk]

When I tried your code, it created a file that I couldn't open (corrupted somehow). When I started over and changed to Repair_Income2.xlsx, it worked for me, then I could open it.

I created the file first, with only a heading in cell A1, then I ran the code. See if that change works or no.

Thats interesting, but for some reason, and I might just be being a noob, but when I try that the it still saves as Repair_income and does not change???

[vbfbryce]

you changed this:

oBook = oExcel.Workbooks.Open(My.Computer.FileSystem.SpecialDirectories.Desktop + "\Repair_Income.xlsx")

to this:

oBook = oExcel.Workbooks.Open(My.Computer.FileSystem.SpecialDirectories.Desktop + "\Repair_Income2.xlsx")

right?

[callumwk]

you changed this:

oBook = oExcel.Workbooks.Open(My.Computer.FileSystem.SpecialDirectories.Desktop + "\Repair_Income.xlsx")

to this:

oBook = oExcel.Workbooks.Open(My.Computer.FileSystem.SpecialDirectories.Desktop + "\Repair_Income2.xlsx")

right?

Correct, now im getting this?

[callumwk]

Okay, I think I fixed that bit

[kaliman79912]

With that code you are not creating a new excel file but writtin to an existing one. That is why previously when using Repair_Income, at trying to open it with excel you still get same error, and now it is telling you that it does not exists.

[callumwk]

With that code you are not creating a new excel file but writtin to an existing one. That is why previously when using Repair_Income, at trying to open it with excel you still get same error, and now it is telling you that it does not exists.

Yes thank you, I'm just a noob. That problem is all sorted now thank you, so now we need to import data from a cell into a text box?