reteriving text form a text box in vb

[Dose87]

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

 

[SkipVought]

Hi,

Are you coding in Excel VBA?

If not, in what editor or application?

 

[Dose87]

yes

 

[SkipVought]

lastTextbox.Text = data5.Text

This assumes that the controls are on the same sheet.

 

[Dose87]

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

 

[SkipVought]

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

 

[Dose87]

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

 

[SkipVought]

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?

 

[Dose87]

here is a copy of my invoice next to name there is a text box named data5 I am trying to get that string out of that box ... if it goes somewhere, how do I find out where it has gone

 

[SkipVought]

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?

 

[Dose87]

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

 

[SkipVought]

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?

 

[Dose87]

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

 

[SkipVought]

Please the two questions in the previous post.

 

[Dose87]

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

 

[SkipVought]

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!

 

[Dose87]

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

 

[Dose87]

and yes it is the various invoice workbooks

 

[SkipVought]

This assumes that the code is running from

'
    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

 

[Dose87]

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