VBA and Excel

U

uwe

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
 
J

Joel

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?
 
U

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?









- Show quoted text -
Click to expand...

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.
 
J

Joel

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
 
U

uwe

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 theFolder
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





- Show quoted text -
Click to expand...

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.
 
J

Joel

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?
 
U

uwe

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?







- Show quoted text -
Click to expand...

It was the SheetName.
As i found out the SheetName had to be Text and not a number. I was
able to fix that. Thanks again.
Now to my next problem. I would like to do the following.

If .Range("H1") = 6095 Then
Rows("1:1").Select
End If
If .Range("H1") = 6090 Then
Rows("2:2").Select
End If
If .Range("H1") = 6085 Then
Rows("3:3").Select
End If
If .Range("H1") = 6080 Then
Rows("4:4").Select
End If

This goes down all the way to the value 915 in H1, in increments of
5, thats means i would have to repeat that 817 times.
Do you know an easier way of doing this?
Uwe
 
U

uwe

SelectRow = Int((6100 - Range("H1"))/5)
Rows(SelectRow & ":" & SelectRow).Select








- Show quoted text -
Click to expand...

Joel, I really appreciate your time on answering to my questions.
Thanks for the codes, I'm getting there!
 
Top