2 Attachment(s)
[RESOLVED] HELP, Importing and exporting specific cells from Excel to Visual Studio
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:
Code:
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.
Attachment 106499
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:
Attachment 106497
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
Re: HELP, Importing and exporting specific cells from Excel to Visual Studio
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?
Re: HELP, Importing and exporting specific cells from Excel to Visual Studio
This is what I would use to write values to a spreadsheet (add a reference to the Excel Object Library first):
Code:
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.
1 Attachment(s)
Re: HELP, Importing and exporting specific cells from Excel to Visual Studio
Quote:
Originally Posted by
vbfbryce
This is what I would use to write values to a spreadsheet (add a reference to the Excel Object Library first):
Code:
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:
Attachment 106503
Re: HELP, Importing and exporting specific cells from Excel to Visual Studio
It seems that no matter what ""\Repair_Income.xls"" is set to it will always make it a xlsx file?
Re: HELP, Importing and exporting specific cells from Excel to Visual Studio
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.
Re: HELP, Importing and exporting specific cells from Excel to Visual Studio
Quote:
Originally Posted by
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.
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???
Re: HELP, Importing and exporting specific cells from Excel to Visual Studio
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?
1 Attachment(s)
Re: HELP, Importing and exporting specific cells from Excel to Visual Studio
Quote:
Originally Posted by
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?
Correct, now im getting this?
Attachment 106507
Re: HELP, Importing and exporting specific cells from Excel to Visual Studio
Okay, I think I fixed that bit :)
Re: HELP, Importing and exporting specific cells from Excel to Visual Studio
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.
Re: HELP, Importing and exporting specific cells from Excel to Visual Studio
Quote:
Originally Posted by
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.
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? :)