danovkos
08-04-2010, 05:02 AM
Hi all,
i want to convert existing data in txt files to existing sheet in existing wb. I found some codes and change it to my needs, but one thing i dont know, how to do it. This code does load data from txt files to new (added) sheet, but i want it load it to existing wb (prehlady2010) to sheet "test".
how can i do this?
thx a lot.
Sub readTXTwriteTOsheet2()
Dim a As Variant, b As Variant
Dim ws As Worksheet
Dim lngCounter As Long
Dim x As Long
Dim strTemp
Dim LastColumn As Long
Dim LastRow As Long
Set ws = Windows("Prehlady2010.xls").Worksheets("test") 'this is my try :(
With Windows("Prehlady2010.xls") 'this is my try :(
Sheets("test").Activate 'this is my try :(
Set ws = Workbooks.Add.Worksheets(1) 'this WORKS ok, but i want something other
Open "c:\a\kriz.txt" For Input As #1
ReDim b(1 To 20, 1 To 1)
Do While (EOF(1) = False)
lngCounter = lngCounter + 1
ReDim Preserve b(1 To 20, 1 To lngCounter)
Line Input #1, strTemp
a = Split(strTemp, vbTab)
For x = 0 To UBound(a)
b(x + 1, lngCounter) = a(x)
Next x
Loop
Close #1
ws.Cells(1, 1).Resize(UBound(b, 2), UBound(b, 1)).Value = WorksheetFunction.Transpose(b)
LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
LastColumn = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
Open "c:\a\krieš.txt" For Input As #1
lngCounter = 1
ReDim b(1 To 20, 1 To 1)
Do While (EOF(1) = False)
lngCounter = lngCounter + 1
ReDim Preserve b(1 To 20, 1 To lngCounter)
Line Input #1, strTemp
a = Split(strTemp, vbTab)
For x = 0 To UBound(a)
b(x + 1, lngCounter) = a(x)
Next x
Loop
Close #1
ws.Cells(1, 17).Resize(UBound(b, 2), UBound(b, 1)).Value = WorksheetFunction.Transpose(b)
End With
End Sub
i want to convert existing data in txt files to existing sheet in existing wb. I found some codes and change it to my needs, but one thing i dont know, how to do it. This code does load data from txt files to new (added) sheet, but i want it load it to existing wb (prehlady2010) to sheet "test".
how can i do this?
thx a lot.
Sub readTXTwriteTOsheet2()
Dim a As Variant, b As Variant
Dim ws As Worksheet
Dim lngCounter As Long
Dim x As Long
Dim strTemp
Dim LastColumn As Long
Dim LastRow As Long
Set ws = Windows("Prehlady2010.xls").Worksheets("test") 'this is my try :(
With Windows("Prehlady2010.xls") 'this is my try :(
Sheets("test").Activate 'this is my try :(
Set ws = Workbooks.Add.Worksheets(1) 'this WORKS ok, but i want something other
Open "c:\a\kriz.txt" For Input As #1
ReDim b(1 To 20, 1 To 1)
Do While (EOF(1) = False)
lngCounter = lngCounter + 1
ReDim Preserve b(1 To 20, 1 To lngCounter)
Line Input #1, strTemp
a = Split(strTemp, vbTab)
For x = 0 To UBound(a)
b(x + 1, lngCounter) = a(x)
Next x
Loop
Close #1
ws.Cells(1, 1).Resize(UBound(b, 2), UBound(b, 1)).Value = WorksheetFunction.Transpose(b)
LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
LastColumn = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
Open "c:\a\krieš.txt" For Input As #1
lngCounter = 1
ReDim b(1 To 20, 1 To 1)
Do While (EOF(1) = False)
lngCounter = lngCounter + 1
ReDim Preserve b(1 To 20, 1 To lngCounter)
Line Input #1, strTemp
a = Split(strTemp, vbTab)
For x = 0 To UBound(a)
b(x + 1, lngCounter) = a(x)
Next x
Loop
Close #1
ws.Cells(1, 17).Resize(UBound(b, 2), UBound(b, 1)).Value = WorksheetFunction.Transpose(b)
End With
End Sub