[RESOLVED] HELP, Importing and exporting specific cells from Excel to Visual Studio-VBForums
Results 1 to 12 of 12

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

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Oct 2013
    Location
    United Kingdom
    Posts
    116

    Resolved [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.

    Name:  fgh.png
Views: 1805
Size:  5.1 KB

    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:

    Name:  sdf.png
Views: 1836
Size:  6.4 KB

    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

  2. #2

    Thread Starter
    Lively Member
    Join Date
    Oct 2013
    Location
    United Kingdom
    Posts
    116

    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?

  3. #3
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,423

    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.

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Oct 2013
    Location
    United Kingdom
    Posts
    116

    Re: HELP, Importing and exporting specific cells from Excel to Visual Studio

    Quote Originally Posted by vbfbryce View Post
    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:
    Name:  ghj.png
Views: 1676
Size:  26.0 KB

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Oct 2013
    Location
    United Kingdom
    Posts
    116

    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?

  6. #6
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,423

    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.

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Oct 2013
    Location
    United Kingdom
    Posts
    116

    Re: HELP, Importing and exporting specific cells from Excel to Visual Studio

    Quote Originally Posted by vbfbryce View Post
    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???

  8. #8
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,423

    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?

  9. #9

    Thread Starter
    Lively Member
    Join Date
    Oct 2013
    Location
    United Kingdom
    Posts
    116

    Re: HELP, Importing and exporting specific cells from Excel to Visual Studio

    Quote Originally Posted by vbfbryce View Post
    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?
    Name:  jkl.png
Views: 1687
Size:  28.0 KB

  10. #10

    Thread Starter
    Lively Member
    Join Date
    Oct 2013
    Location
    United Kingdom
    Posts
    116

    Re: HELP, Importing and exporting specific cells from Excel to Visual Studio

    Okay, I think I fixed that bit

  11. #11
    PowerPoster kaliman79912's Avatar
    Join Date
    Jan 2009
    Location
    Ciudad Juarez, Chihuahua. Mexico
    Posts
    2,592

    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.
    More important than the will to succeed, is the will to prepare for success.

    Please rate the posts, your comments are the fuel to keep helping people

  12. #12

    Thread Starter
    Lively Member
    Join Date
    Oct 2013
    Location
    United Kingdom
    Posts
    116

    Re: HELP, Importing and exporting specific cells from Excel to Visual Studio

    Quote Originally Posted by kaliman79912 View Post
    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?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.