INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

reteriving text form a text box in vb

reteriving text form a text box in vb

(OP)
Im trying to write a progam that takes text out of a text box named data5 that is in a excel document that which I have defined as workbooks(fileName).worksheet(1).I want to take that string and save is a lastTextbox and put it in a another excel document that is called Clients1 at cell(i,3). no matter what I try I cant get the vb progam to see and read that data in textbox data5.


btw everything is in different workbooks

I have tried everything I can think of weather it is the .range method or
focus(). but there is always a problem getting the actual text form the text box in a different document. can someone plz help me

Do While fileName <> ""
i = i + 1
j = 2
jj = j + 1
Cells(i, 1) = fileName
Cells(i, 2) = lastTextbox
Application.ScreenUpdating = False
Workbooks.Open (directory & fileName)
' Workbooks(directory & fileName).Worksheets(1).data5.Focus()
firstChr = data5.selectionStart = 0
lastTextbox = data5.SelectionLength = data5.Text.Length

Workbooks.Open (directory & "clients1.xlsx")
For Each sheet In Workbooks(fileName).Worksheets

RE: reteriving text form a text box in vb

Hi,

Are you coding in Excel VBA?

If not, in what editor or application?

RE: reteriving text form a text box in vb

(OP)
yes

RE: reteriving text form a text box in vb

CODE

lastTextbox.Text = data5.Text 
This assumes that the controls are on the same sheet.

RE: reteriving text form a text box in vb

(OP)
no they are not data5 in in a work book "filename"
and I want to put the in a new work book "clients1"
the vb code in being done inside clients1 if that makes any difference

RE: reteriving text form a text box in vb

Just curious, why are you putting text in a Textbox, that is designed for a USER to enter data in?

RE: reteriving text form a text box in vb

(OP)
I am trying to read text out of a text box in our invoices and make a master sheet that has all of out customers info on it
its going from a text box to just getting placed into a basic excel spredsheet

RE: reteriving text form a text box in vb

But the text that a user enters in a Textbox, is used somewhere. Just does not just go into a TextBox. You eventually put that in a cell on a sheet, right? Else it's virtually useless, right?

RE: reteriving text form a text box in vb

Debug.Print Data5.Text

This is what is in your Textbox.

So you want to get all the data from your invoice to some other workbook/sheet, I assume into a table in a single row and not into another TextBox.

Another question is why are you opening a bunch (I assune) of different workbooks, from values in row 1?

RE: reteriving text form a text box in vb

(OP)
all of the invoices are in different workbooks. that the only way I can find to get the data out of them, is have the vb program open the workbook and hopefully pull that data out.


the final print out will have all of their data in a row and the then next client under that.

I don't understand what you want me to do with Debug.Print Data5.Text
I tried putting in a vb progam and I get a run time error on it

RE: reteriving text form a text box in vb

Wow! What a mess!

Okay your code should be in the master, where you store the invoice data in a table.

I assume that each invoice workbook/sheet is identical.

I don't understand that you're opening TWO workbooks in your Do...Loop (which BTW, there is no Loop or Next in your posted code???

What's in the sheets in the For...Next loop?

RE: reteriving text form a text box in vb

(OP)
directory & fileName is where the text boxes are coming from
directory & "clients1.xlsx" is where all the data is going to go
Do While fileName <> ""
i = i + 1
j = 2
jj = j + 1
Cells(i, 1) = fileName
Cells(i, 2) = lastTextbox
Application.ScreenUpdating = False
Workbooks.Open (directory & fileName)
Workbooks.Open (directory & "clients1.xlsx")
For Each sheet In Workbooks(fileName).Worksheets
Workbooks(clients).Worksheets(1).Cells(i, j).Value = sheet.Name
j = j + 1
jj = jj + 1
Next sheet
Workbooks(fileName).Close
fileName = Dir()
Loop
Application.ScreenUpdating = True

End Sub

RE: reteriving text form a text box in vb

Please the two questions in the previous post.

RE: reteriving text form a text box in vb

(OP)
the code is in the file clients1
each invoice sheet is identical
Workbooks.Open (directory & fileName) this is where the text box is located
Workbooks.Open (directory & "clients1.xlsx") this is where the data is going to end up (should I have this outside the do while loop)
inside the for loop is where im going to get the data out of the text boxes.. right now the only thing I can get it to do is print out the sheet name
I didn't put the whole code up the first time now there is a next

RE: reteriving text form a text box in vb

You're referring to "where the text box is located."

Is that not the various Invoice workbooks? Are you ONLY interested in what's in the textbox in each sheet in each workbook? Don't you want ALL the data from each invoice?

I am still too confused to provide any cogent help!

RE: reteriving text form a text box in vb

(OP)
Workbooks.Open (directory & fileName) this is going to open each of the work books and im going to grab the text out of a couple of textboxes.. but each of the textboxes info needs to go into a differnet spot in the new excel sheet . and some of the data is going to get searched through and only key words are going to get used

RE: reteriving text form a text box in vb

(OP)
and yes it is the various invoice workbooks

RE: reteriving text form a text box in vb

This assumes that the code is running from

CODE

'
    Dim Sh As Worksheet
    
    Do While Filename <> ""
        i = i + 1
        j = 2
        jj = j + 1
        Application.ScreenUpdating = False
        With Workbooks.Open(directory & Filename)
            
            For Each Sh In .Worksheets
                ThisWorkbook.Sheets(1).Cells(i, j).Value = Sh.Name
                ThisWorkbook.Sheets(1).Cells(i, 1).Value = Filename
                ThisWorkbook.Sheets(1).Cells(i, 2).Value = data5.Text
                j = j + 1
                jj = jj + 1
            Next Sheet
            .Close      'close without saving
        End With
        Filename = Dir()
    Loop 

RE: reteriving text form a text box in vb

(OP)
still having trouble with pulling the text out....run time error

ThisWorkbook.Sheets(1).Cells(i, 2).Value = data5.Text

and if I negate that line it doesn't cycle through the all of the workbooks

RE: reteriving text form a text box in vb

sorry, the textbox is on each sheet, right?

CODE

With Workbooks.Open(directory & Filename)
            
            For Each Sh In .Worksheets
                ThisWorkbook.Sheets(1).Cells(i, j).Value = Sh.Name
                ThisWorkbook.Sheets(1).Cells(i, 1).Value = Filename
                ThisWorkbook.Sheets(1).Cells(i, 2).Value = Sh.data5.Text
                j = j + 1
                jj = jj + 1
            Next Sheet
            .Close      'close without saving
        End With 

RE: reteriving text form a text box in vb

(OP)
same problemish.... it says method or data member not found and if focuses on data5

RE: reteriving text form a text box in vb

So if you Debug at that point, can you see if you have a Textbox named data4 ON THAT SHEET?

RE: reteriving text form a text box in vb

(OP)
Debug.Print Data5.Text
Debug.Print sheet.Data5.Text
I cant get these to work... method or data member not found

RE: reteriving text form a text box in vb

(OP)
of I get object required if I take out the sheet.

RE: reteriving text form a text box in vb

Do you know which sheet the TextBox is on?

Debug.Print Sh.Name

If you actually (manually) select that sheet, 1) is there a Textbox and 2) is the Textbox.name data5?

RE: reteriving text form a text box in vb


That is SELECT the Textbox and look at the PROPERTIES a window for that Textbox for the name.

RE: reteriving text form a text box in vb

(OP)
the text box name is data5
if I click define name is gives me a refres to =Invoice!$E$12

RE: reteriving text form a text box in vb

Is this an ActiveX control or a Forms control?

RE: reteriving text form a text box in vb

(OP)
I dont know....how do i tell....sorry this is so complicated ...only worked with vb a little bit

RE: reteriving text form a text box in vb

(OP)
I went into about ... forms3: 14.0.7140.500 version 1628

RE: reteriving text form a text box in vb

I'm having trouble with my Forms controls, so this will be from memory.

First let me direct you to this FAQ. FAQ707-4594: How to use the Watch Window as a Power Programming Tool

I have used this technique to DISCOVER all kinds of things regarding variables and OBJECTS in my code. This can help you discover what's going on with data5.

I believe that this is the code for assigning a tect value from this Forms textbox according to my recollection.

CODE

MsgBox Sh.data5.OLEFormat.Object.Value 
If that fails, then run your code, perhaps using the Step feature, or BREAK at the point of assigning the textbox. Use the Watch Window to discover the Object path to the value.

Let me know what happens.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close