7 Replies - 6927 Views - Last Post: 12 July 2012 - 08:17 AM Rate Topic: -----

#1 cire03  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 10-July 12

Autofill vb6 textbox from excel cells

Posted 10 July 2012 - 11:49 PM

I am trying to call cell data in excel and place it to respective textboxes in vb6. From the code i have done below, i was only able to place fixed data from cells B2 to F2. How can i fill respective textboxes based on the transaction number i encoded in trans_no.text. Transaction numbers are in Column A of the excel file.

Private Sub Import_Click()


    Dim r As Integer
    Dim xl As Excel.Application
    Dim var As Variant
        
    Set xl = CreateObject("excel.Application")
    
    xl.Workbooks.Open ("C:\Documents and Settings\BFAD\My Documents\vb6\OP Report.xls")
    xl.Sheets("sheet1").Select
    xl.Visible = False
   
   var = Trans_no.Text

     ' ActiveSheet.Range("B2", "F2").Select
    ActiveSheet.Range("B2", "F2").Select
    
    Client.Text = ActiveSheet.Range("B2")
    Particulars.Text = ActiveSheet.Range("C2")
    Fees.Text = ActiveSheet.Range("D2")
    LRF.Text = ActiveSheet.Range("E2")
    Total.Text = ActiveSheet.Range("F2")
    
    xl.ActiveWorkbook.Close
    xl.Application.Quit

End Sub



Is This A Good Question/Topic? 0
  • +

Replies To: Autofill vb6 textbox from excel cells

#2 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Autofill vb6 textbox from excel cells

Posted 11 July 2012 - 11:23 AM

No idea, since you haven't explained what sort of value a "transaction number" is. Nor have you explained how you want to relate a text box's identity to a transaction number. If the transaction number is a random number, for example, it would be tough to infer a text box from it.

Need more information.
Was This Post Helpful? 0
  • +
  • -

#3 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Autofill vb6 textbox from excel cells

Posted 11 July 2012 - 11:36 AM

Ok, I'll take a wild guess here. You want to put a transaction number in a text box, and have the row in your excel sheet that has that transaction number in column a appear in your text boxes.

To do this, you'll need to:

1. Add a MATCH function to a cell your spreadsheet.
2. Add a cell to hold the value of trans_no.text.
3. Reference the cell in 2 from the function in 1.
4. Your cell in 1 will contain the row number offset (row number -1) in your spreadsheet that contains the transaction number in column A.
5. Pull columns B-F in the row in the cell in 4, preferably into an array.
6. Plug your values into the text boxes.

Try to minimize traffic between vb and excel. That's why it's better to use an array.

This post has been edited by BobRodes: 11 July 2012 - 11:37 AM

Was This Post Helpful? 0
  • +
  • -

#4 cire03  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 10-July 12

Re: Autofill vb6 textbox from excel cells

Posted 11 July 2012 - 07:53 PM

View PostBobRodes, on 11 July 2012 - 11:36 AM, said:

Ok, I'll take a wild guess here. You want to put a transaction number in a text box, and have the row in your excel sheet that has that transaction number in column a appear in your text boxes.

To do this, you'll need to:

1. Add a MATCH function to a cell your spreadsheet.
2. Add a cell to hold the value of trans_no.text.
3. Reference the cell in 2 from the function in 1.
4. Your cell in 1 will contain the row number offset (row number -1) in your spreadsheet that contains the transaction number in column A.
5. Pull columns B-F in the row in the cell in 4, preferably into an array.
6. Plug your values into the text boxes.

Try to minimize traffic between vb and excel. That's why it's better to use an array.

Was This Post Helpful? 0
  • +
  • -

#5 cire03  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 10-July 12

Re: Autofill vb6 textbox from excel cells

Posted 11 July 2012 - 08:11 PM

Thank you very much for your response. Im sorry if my question is not that clear. I want to import values of cell and put it into the correspoding textboxes. in excel file, col A is the Transaction No, Col B is the Client's Name, Col C is the Particular transaction, Col D is the Application fee, col E is the Legal Research Fee and col F is the Total amount payable. I want to know the code, that when i encoded the transaction in VB6 Textbox form, the system will look for the particular row of that transaction number, then capture the cell values of columns B-F within the the same row to the corresponding textboxes.
Below if the revised source code i have made. But still, same values were captured in the textboxes. Please assist me to make this code works. thank you very much for your guidance.
Private Sub Import_Click()


    Dim r As Integer
    Dim xl As Excel.Application
    Dim var As Variant
        
    Set xl = CreateObject("excel.Application")
    
    xl.Workbooks.Open ("C:\Documents and Settings\BFAD\My Documents\vb6\OP Report.xls")
    xl.Sheets("sheet1").Select
    xl.Visible = False

var = Trans_no.Text

     ' ActiveSheet.Range("B2", "F2").Select
    ActiveSheet.Range("B2", "F2").Select
    
    Client.Text = ActiveSheet.Range("B2")
    Particulars.Text = ActiveSheet.Range("C2")
    Fees.Text = ActiveSheet.Range("D2")
    LRF.Text = ActiveSheet.Range("E2")
    Total.Text = ActiveSheet.Range("F2")
    
    'Detect the next available BLANK row.
    'assume that .cell(ROW,  COLUMN)
    r = 2
    While (xl.Worksheets("sheet1").Cells(r, 1).Value <> "Trans_no.Text")
        r = r + 1
    Wend

    xl.ActiveWorkbook.Close
    xl.Application.Quit

End Sub


Was This Post Helpful? 0
  • +
  • -

#6 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Autofill vb6 textbox from excel cells

Posted 11 July 2012 - 08:31 PM

Looks like my wild guess was accurate, then. Look at my steps 1-4. Those are changes you need to make to the spreadsheet itself. You can work out how to do that loop, but it will be PIG slow. If you insist on doing it that way, then you need to put the cell assignments after you run your loop and get the value for r, then you have to incorporate that value into your range values.

This post has been edited by BobRodes: 11 July 2012 - 08:35 PM

Was This Post Helpful? 0
  • +
  • -

#7 cire03  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 10-July 12

Re: Autofill vb6 textbox from excel cells

Posted 11 July 2012 - 08:42 PM

View PostBobRodes, on 11 July 2012 - 08:31 PM, said:

Looks like my wild guess was accurate, then. Look at my steps 1-4. Those are changes you need to make to the spreadsheet itself. You can work out how to do that loop, but it will be PIG slow. If you insist on doing it that way, then you need to put the cell assignments after you run your loop and get the value for r, then you have to incorporate that value into your range values.


Thank you very much sir. I'll try your instructions. Hope i can make it work this time. You're really a big help. God Bless
Was This Post Helpful? 0
  • +
  • -

#8 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Autofill vb6 textbox from excel cells

Posted 12 July 2012 - 08:17 AM

Same to you, and good luck. If you get stuck, post back.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1