none
how can i make VB read information from a closed workbook and show them in textboxes RRS feed

  • Question

  • Hello everyone

    I have this "search engine" where the user would enter a value and the program will go through workbook and extract the data in it and put them in textboxes.

    what i want is for the code to go through a closed excel sheet, search for the details under the  PO number that was entered.

    here is what i have so far

    Private Sub CommandButton1_Click()
    
    row_number = 0
    
    Do
    
    DoEvents
    
    row_number = row_number + 1
    
    item_in_review = Sheets("PO").Range("A" & row_number)       'Look for the entered data in Colomn A
    
    If item_in_review = TextBox1.Text Then                      'if statment starts
    
        TextBox2.Text = Sheets("PO").Range("B" & row_number)    'Reading information out of the second(B)colomn of the same row
        TextBox3.Text = Sheets("PO").Range("C" & row_number)    'Reading information out of the Third (C)colomn of the same row
        TextBox4.Text = Sheets("PO").Range("D" & row_number)    'Reading information out of the Fourth(D)colomn of the same roW
        
        Exit Do
        
        Else
                TextBox2.Text = "Invalid Entry"
                TextBox3.Text = "Invalid Entry"
                TextBox4.Text = "Invalid Entry"
        
        
        End If
            
                
        Loop Until item_in_review = ""                          'no space in the quotation marks
    
    
    End Sub
    it is working when i make a Userform within Excel, but i want is for it to be done on an actual VB app and to do what i have mentioned above. I looked online to find something that would solve this for me but all i found was different answers to different problems.

    Tuesday, April 4, 2017 12:31 PM

Answers

  • Hi Batada,

    You could open a closed workbook via Workbooks.Open Method.

    Here is the example run in a VB winform application.

    'Remember add referenceMicrosoft.Office.Interop.Excel

    Imports Excel = Microsoft.Office.Interop.Excel
    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim row_number As Integer = 0
            Dim xlApp As Excel.Application
            Dim xlWb As Excel.Workbook
            Dim xlWs As Excel.Worksheet
            Dim item_in_review As Excel.Range
            xlApp = New Excel.Application
            xlWb = xlApp.Workbooks.Open("C:\Users\Administrator\Desktop\Test.xlsx")
            xlWs = xlWb.Sheets("PO")
            Do
                row_number = row_number + 1
                item_in_review = xlWs.Range("A" & row_number)       'Look for the entered data in Colomn A
                If item_in_review.Text = TextBox1.Text Then                      'if statment starts
                    TextBox2.Text = xlWs.Range("B" & row_number).Text     'Reading information out of the second(B)colomn of the same row
                    TextBox3.Text = xlWs.Range("C" & row_number).Text    'Reading information out of the Third (C)colomn of the same row
                    TextBox4.Text = xlWs.Range("D" & row_number).Text    'Reading information out of the Fourth(D)colomn of the same roW
                    Exit Do
                Else
                    TextBox2.Text = "Invalid Entry"
                    TextBox3.Text = "Invalid Entry"
                    TextBox4.Text = "Invalid Entry"
                End If
            Loop Until item_in_review.Text = ""                          'no space in the quotation marks
        End Sub
    End Class

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Chenchen LiModerator Monday, April 10, 2017 2:21 AM
    • Marked as answer by Batada Thursday, April 13, 2017 8:49 AM
    Wednesday, April 5, 2017 2:23 AM
    Moderator

All replies

  • Hi Batada,

    You could open a closed workbook via Workbooks.Open Method.

    Here is the example run in a VB winform application.

    'Remember add referenceMicrosoft.Office.Interop.Excel

    Imports Excel = Microsoft.Office.Interop.Excel
    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim row_number As Integer = 0
            Dim xlApp As Excel.Application
            Dim xlWb As Excel.Workbook
            Dim xlWs As Excel.Worksheet
            Dim item_in_review As Excel.Range
            xlApp = New Excel.Application
            xlWb = xlApp.Workbooks.Open("C:\Users\Administrator\Desktop\Test.xlsx")
            xlWs = xlWb.Sheets("PO")
            Do
                row_number = row_number + 1
                item_in_review = xlWs.Range("A" & row_number)       'Look for the entered data in Colomn A
                If item_in_review.Text = TextBox1.Text Then                      'if statment starts
                    TextBox2.Text = xlWs.Range("B" & row_number).Text     'Reading information out of the second(B)colomn of the same row
                    TextBox3.Text = xlWs.Range("C" & row_number).Text    'Reading information out of the Third (C)colomn of the same row
                    TextBox4.Text = xlWs.Range("D" & row_number).Text    'Reading information out of the Fourth(D)colomn of the same roW
                    Exit Do
                Else
                    TextBox2.Text = "Invalid Entry"
                    TextBox3.Text = "Invalid Entry"
                    TextBox4.Text = "Invalid Entry"
                End If
            Loop Until item_in_review.Text = ""                          'no space in the quotation marks
        End Sub
    End Class

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Chenchen LiModerator Monday, April 10, 2017 2:21 AM
    • Marked as answer by Batada Thursday, April 13, 2017 8:49 AM
    Wednesday, April 5, 2017 2:23 AM
    Moderator
  • Thank you for responding. I copied your code and added it to mine after adding the reference you told me to and i keep getting three errors under the same code which is BC31424... any sugesstion on how to fix this??
    Sunday, April 9, 2017 6:39 AM
  • Never mind. it is working now. I just added the reference  System.Xaml and now it has worked!

    Thank you so much! I was able to add few adjustment. excel used to be opening every time a search is inducted and stays even after closing the app. now it closes after the app closes. so next step would be just letting close excel after each search is conducted. I also manage to put it on a share drive for other users to use.

    Thank you again :)
    Sunday, April 9, 2017 11:49 AM
  • Hi,

    I suggest you mark helpful post or your solution as answer to close the thread. Thanks for your understanding.

    If you have any new issues, please feel free to post threads. We are willing to help you.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Batada Thursday, April 13, 2017 8:49 AM
    • Unmarked as answer by Batada Thursday, April 13, 2017 8:49 AM
    Monday, April 10, 2017 2:25 AM
    Moderator
  • here is the final code

    Imports Excel = Microsoft.Office.Interop.Excel                                  'Must add the references Microsoft.Office.Interop.Excel AND System.Xaml
    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim row_number As Integer = 0
            Dim xlApp As Excel.Application
            Dim xlWb As Excel.Workbook
            Dim xlWs As Excel.Worksheet
            Dim item_in_review As Excel.Range
            Dim Fname As String = "\\C:\Po.xlsx"
            xlApp = New Excel.Application
            xlWb = xlApp.Workbooks.Open(Fname)
            xlWs = xlWb.Sheets("PO")

            Do
                row_number = row_number + 1
                item_in_review = xlWs.Range("A" & row_number)                       'Look for the entered data in Colomn A
                If item_in_review.Text = TextBox1.Text Then                         'if statment starts
                    TextBox2.Text = xlWs.Range("B" & row_number).Text               'Reading information out of the second(B)colomn of the same row
                    TextBox3.Text = xlWs.Range("C" & row_number).Text               'Reading information out of the Third (C)colomn of the same row
                    TextBox4.Text = xlWs.Range("D" & row_number).Text               'Reading information out of the Fourth(D)colomn of the same roW
                    Exit Do
                Else
                    TextBox2.Text = "Invalid Entry"
                    TextBox3.Text = "Invalid Entry"
                    TextBox4.Text = "Invalid Entry"
                End If
            Loop Until item_in_review.Text = ""                                     'no space in the quotation marks


            'xlApp.ActiveWorkbook.Saved = True
            xlWb.Saved = True                                                       'Close Excel instance
            xlWb.Close()
            xlApp.Quit()
        End Sub

    End Class

    Thursday, April 13, 2017 8:50 AM