Hi

I am looking for a VBA code, which is organizing Text strings to open
up a specific Excel sheet in a workbook.
(The workbook is seperate into 5 sheets)

String example
1014-08, WD1 , 642220, 4, T+G, 1, 6060x150x610, 6x8.0

WD = Name of workbook (on 2nd field of string)
6 = Name of sheet (on 8th field of string)
6060= value in colum A to indicate row which need to be selected (on
7th field of string)

I hope this makes any sense. Let me kow if you have any questions.

Thanks,

Uwe

Where are the text strings coming from and how many do you have?
Is 6060 the row number?
What data is entered into the cell or do you just want the cell selected?


"uwe" wrote:

Hi

I am looking for a VBA code, which is organizing Text strings to open
up a specific Excel sheet in a workbook.
(The workbook is seperate into 5 sheets)

String example
1014-08, WD1 , 642220, 4, T+G, 1, 6060x150x610, 6x8.0

WD = Name of workbook (on 2nd field of string)
6 = Name of sheet (on 8th field of string)
6060= value in colum A to indicate row which need to be selected (on
7th field of string)

I hope this makes any sense. Let me kow if you have any questions.

Thanks,

Uwe

On Sep 3, 1:00*am, Joel wrote:
Where are the text strings coming from and how many do you have?
Is 6060 the row number?
What data is entered into the cell or do you just want the cell selected?



"uwe" wrote:
Hi

I am looking for a VBA code, which is organizing Text strings to open
up a specific Excel sheet in a workbook.
(The workbook is seperate into 5 sheets)

String example
1014-08, WD1 * , 642220, 4, T+G, 1, 6060x150x610, 6x8.0

WD = Name of workbook * (on 2nd field of string)
6 = Name of sheet *(on 8th field of string)
6060= value in colum A to indicate row which need to be selected (on
7th field of string)

I hope this makes any sense. Let me kow if you have any questions.

Thanks,

Uwe- Hide quoted text -

- Show quoted text -

Joel,

The string is sent via a text file into a specific project folder
C:projects. The folder could have between 1 and 30 text files, each
text file could contain between 1 and 60 strings.
6060 is one of the values on column A. The 7th field of the string
should match one of the values in column A. Row with the matching
value need to be selected.
I hope it is a little bit more clear this time.

I've broken the problem into two parts. GetText() reads all the text files
(*.txt") in a directoy (Folder) and put the data into sheet1. then performs
text to columns method which seperates the data using the commas putting each
comma seperate text into a different column.

The 2nd part of the code GetData() intpretes the data in sheet one. It
reads each line and gets the BookName, Sheet Name , and RowNumber. Then it
opens the book and copies the data in the row to sheet2 of the workbook where
the macro is located.

There may be a couple of problems with getting the code working. Make sure
the sheet names ("Sheet1, and "Sheet2") matches the sheets in your workbook.
Second, the Filename WD1 in your eample didn't have a folder or an Extension.
I added the ".xls" to the workbook name and a folder. Make sure the Folder
in both macros match the directory where the files are located.

Sub GetText()

Const ForReading = 1, ForWriting = 2, _
ForAppending = 3
Set fs = CreateObject("Scripting.FileSystemObject")

Folder = "C:\Temp\"
FName = Dir(Folder & "*.txt")

With Sheets("Sheet1")
RowCount = 1
Do While FName < ""
Set fin = fs.OpenTextFile(Folder & FName, _
ForReading, TristateFalse)
Do While fin.AtEndOfStream < True
.Range("A" & RowCount) = fin.readline
RowCount = RowCount + 1
Loop
fin.Close
FName = Dir()
Loop
.Columns("A:A").TextToColumns _
Destination:=.Range("A1"), _
DataType:=xlDelimited, _
Comma:=True
.Columns.AutoFit
End With
End Sub


Sub GetData()

Folder = "C:\Temp\"

With Sheets("Sheet1")
OldRowCount = 1
NewRowCount = 1
Do While .Range("A" & OldRowCount) < ""
BookName = .Range("B" & OldRowCount)
ShtName = Val(.Range("H" & OldRowCount))
RowNumber = Val(.Range("G" & OldRowCount))

Set DataBK = Workbooks.Open(Filename:=Folder & BookName & ".xls")
With DataBK.Sheets(ShtName)
DataBK.Rows(RowNumber).Copy _
Destination:=Sheets("Sheet2").Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End With
DataBK.Close

OldRowCount = OldRowCount + 1
Loop
End With
End Sub


"uwe" wrote:

On Sep 3, 1:00 am, Joel wrote:
Where are the text strings coming from and how many do you have?
Is 6060 the row number?
What data is entered into the cell or do you just want the cell selected?



"uwe" wrote:
Hi

I am looking for a VBA code, which is organizing Text strings to open
up a specific Excel sheet in a workbook.
(The workbook is seperate into 5 sheets)

String example
1014-08, WD1 , 642220, 4, T+G, 1, 6060x150x610, 6x8.0

WD = Name of workbook (on 2nd field of string)
6 = Name of sheet (on 8th field of string)
6060= value in colum A to indicate row which need to be selected (on
7th field of string)

I hope this makes any sense. Let me kow if you have any questions.

Thanks,

Uwe- Hide quoted text -

- Show quoted text -

Joel,

The string is sent via a text file into a specific project folder
C:projects. The folder could have between 1 and 30 text files, each
text file could contain between 1 and 60 strings.
6060 is one of the values on column A. The 7th field of the string
should match one of the values in column A. Row with the matching
value need to be selected.
I hope it is a little bit more clear this time.

On Sep 3, 4:13*pm, Joel wrote:
I've broken the problem into two parts. *GetText() reads all the text files
(*.txt") in a directoy (Folder) and put the data into sheet1. *then performs
text to columns method which seperates the data using the commas putting each
comma seperate text into a different column.

The 2nd part of the code GetData() *intpretes the data in sheet one. *It
reads each line and gets the BookName, Sheet Name , and RowNumber. *Then it
opens the book and copies the data in the row to sheet2 of the workbook where
the macro is located.

There may be a couple of problems with getting the code working. *Make sure
the sheet names ("Sheet1, and "Sheet2") matches the sheets in your workbook. *
Second, the Filename WD1 in your eample didn't have a folder or an Extension.
*I added the ".xls" to the workbook name and a folder. *Make sure the Folder
in both macros match the directory where the files are located.

Sub GetText()

Const ForReading = 1, ForWriting = 2, _
* *ForAppending = 3
Set fs = CreateObject("Scripting.FileSystemObject")

Folder = "C:\Temp\"
FName = Dir(Folder & "*.txt")

With Sheets("Sheet1")
* *RowCount = 1
* *Do While FName < ""
* * * Set fin = fs.OpenTextFile(Folder & FName, _
* * * * *ForReading, TristateFalse)
* * * Do While fin.AtEndOfStream < True
* * * * *.Range("A" & RowCount) = fin.readline
* * * * *RowCount = RowCount + 1
* * * Loop
* * * fin.Close
* * * FName = Dir()
* *Loop
* *.Columns("A:A").TextToColumns _
* * * Destination:=.Range("A1"), _
* * * DataType:=xlDelimited, _
* * * Comma:=True
* *.Columns.AutoFit
End With
End Sub

Sub GetData()

Folder = "C:\Temp\"

With Sheets("Sheet1")
* *OldRowCount = 1
* *NewRowCount = 1
* *Do While .Range("A" & OldRowCount) < ""
* * * BookName = .Range("B" & OldRowCount)
* * * ShtName = Val(.Range("H" & OldRowCount))
* * * RowNumber = Val(.Range("G" & OldRowCount))

* * * Set DataBK = Workbooks.Open(Filename:=Folder & BookName & ".xls")
* * * With DataBK.Sheets(ShtName)
* * * * *DataBK.Rows(RowNumber).Copy _
* * * * * * Destination:=Sheets("Sheet2").Rows(NewRowCount)
* * * * *NewRowCount = NewRowCount + 1
* * * End With
* * * DataBK.Close

* * * OldRowCount = OldRowCount + 1
* *Loop
End With
End Sub



"uwe" wrote:
On Sep 3, 1:00 am, Joel wrote:
Where are the text strings coming from and how many do you have?
Is 6060 the row number?
What data is entered into the cell or do you just want the cell selected?

"uwe" wrote:
Hi

I am looking for a VBA code, which is organizing Text strings to open
up a specific Excel sheet in a workbook.
(The workbook is seperate into 5 sheets)

String example
1014-08, WD1 * , 642220, 4, T+G, 1, 6060x150x610, 6x8.0

WD = Name of workbook * (on 2nd field of string)
6 = Name of sheet *(on 8th field of string)
6060= value in colum A to indicate row which need to be selected (on
7th field of string)

I hope this makes any sense. Let me kow if you have any questions.

Thanks,

Uwe- Hide quoted text -

- Show quoted text -

Joel,

The string is sent via a text file into a specific project folder
C:projects. The folder could have between 1 and 30 text files, each
text file could contain between 1 and 60 strings.
6060 is one of the values on column A. The 7th field of the string
should match one of the values in column A. Row with the matching
value need to be selected.
I hope it is a little bit more clear this time.- Hide quoted text -

- Show quoted text -

Hi Joel,

Thanks so much for your help. The sub GetText works very well. I still
have some trouble with the sub GetData. If I run the macro it opens
the right Workbook, but not the right sheet. I get an run time error
"9" (Subscript out of range) If I debug it shows that there is a
problem with the code "With DataBK.Sheets(ShtName)
Do you know what to do to fix my problem? Thanks again for your time
and help.

The sheet name doesn't match a sheet in the workbook that was opened.
Is 6 the SheetName?
Is 6 the index number?
Is 6 suppose to be Sheet6?


"uwe" wrote:

On Sep 3, 4:13 pm, Joel wrote:
I've broken the problem into two parts. GetText() reads all the text files
(*.txt") in a directoy (Folder) and put the data into sheet1. then performs
text to columns method which seperates the data using the commas putting each
comma seperate text into a different column.

The 2nd part of the code GetData() intpretes the data in sheet one. It
reads each line and gets the BookName, Sheet Name , and RowNumber. Then it
opens the book and copies the data in the row to sheet2 of the workbook where
the macro is located.

There may be a couple of problems with getting the code working. Make sure
the sheet names ("Sheet1, and "Sheet2") matches the sheets in your workbook.
Second, the Filename WD1 in your eample didn't have a folder or an Extension.
I added the ".xls" to the workbook name and a folder. Make sure the Folder
in both macros match the directory where the files are located.

Sub GetText()

Const ForReading = 1, ForWriting = 2, _
ForAppending = 3
Set fs = CreateObject("Scripting.FileSystemObject")

Folder = "C:\Temp\"
FName = Dir(Folder & "*.txt")

With Sheets("Sheet1")
RowCount = 1
Do While FName < ""
Set fin = fs.OpenTextFile(Folder & FName, _
ForReading, TristateFalse)
Do While fin.AtEndOfStream < True
.Range("A" & RowCount) = fin.readline
RowCount = RowCount + 1
Loop
fin.Close
FName = Dir()
Loop
.Columns("A:A").TextToColumns _
Destination:=.Range("A1"), _
DataType:=xlDelimited, _
Comma:=True
.Columns.AutoFit
End With
End Sub

Sub GetData()

Folder = "C:\Temp\"

With Sheets("Sheet1")
OldRowCount = 1
NewRowCount = 1
Do While .Range("A" & OldRowCount) < ""
BookName = .Range("B" & OldRowCount)
ShtName = Val(.Range("H" & OldRowCount))
RowNumber = Val(.Range("G" & OldRowCount))

Set DataBK = Workbooks.Open(Filename:=Folder & BookName & ".xls")
With DataBK.Sheets(ShtName)
DataBK.Rows(RowNumber).Copy _
Destination:=Sheets("Sheet2").Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End With
DataBK.Close

OldRowCount = OldRowCount + 1
Loop
End With
End Sub



"uwe" wrote:
On Sep 3, 1:00 am, Joel wrote:
Where are the text strings coming from and how many do you have?
Is 6060 the row number?
What data is entered into the cell or do you just want the cell selected?

"uwe" wrote:
Hi

I am looking for a VBA code, which is organizing Text strings to open
up a specific Excel sheet in a workbook.
(The workbook is seperate into 5 sheets)

String example
1014-08, WD1 , 642220, 4, T+G, 1, 6060x150x610, 6x8.0

WD = Name of workbook (on 2nd field of string)
6 = Name of sheet (on 8th field of string)
6060= value in colum A to indicate row which need to be selected (on
7th field of string)

I hope this makes any sense. Let me kow if you have any questions.

Thanks,

Uwe- Hide quoted text -

- Show quoted text -

Joel,

The string is sent via a text file into a specific project folder
C:projects. The folder could have between 1 and 30 text files, each
text file could contain between 1 and 60 strings.
6060 is one of the values on column A. The 7th field of the string
should match one of the values in column A. Row with the matching
value need to be selected.
I hope it is a little bit more clear this time.- Hide quoted text -

- Show quoted text -

Hi Joel,

Thanks so much for your help. The sub GetText works very well. I still
have some trouble with the sub GetData. If I run the macro it opens
the right Workbook, but not the right sheet. I get an run time error
"9" (Subscript out of range) If I debug it shows that there is a
problem with the code "With DataBK.Sheets(ShtName)
Do you know what to do to fix my problem? Thanks again for your time
and help.