Macro still wont run

[Guest]

Thanks Jim Thomlinson for supplying this code for me.
However, now I get "Expected Function or Variable" on the OpenText part

Dim bk As Workbook, rng As Range
ChDir "C:\Temp"
Set bk = Workbooks.OpenText(Filename:="C:\Temp\myfile.txt", _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), _
Array(8, 1), Array(12, 1), Array(17, 1), _
Array(21, 1), Array(29, 1), Array(38, 1), _
Array(46, 1), Array(50, 1), Array(58, 1), _
Array(67, 1))) 'Missing last bracket
Set rng = ActiveSheet.Range("A1").CurrentRegion
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)
Windows("daily.xls").Activate
Sheets("table").Select
Worksheets("table").Unprotect
rng.Copy Destination:=Range("T2")

What am I missing now?
Thanks!

 

[Tom Ogilvy]

OpenText doesn't return a reference, so you can't use
Set bk =

you can do it this way:

Dim bk As Workbook, rng As Range
ChDir "C:\Temp"
Workbooks.OpenText Filename:="C:\Temp\myfile.txt", _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), _
Array(8, 1), Array(12, 1), Array(17, 1), _
Array(21, 1), Array(29, 1), Array(38, 1), _
Array(46, 1), Array(50, 1), Array(58, 1), _
Array(67, 1))
set bk = ActiveWorkbook
Set rng = ActiveSheet.Range("A1").CurrentRegion
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)
Windows("daily.xls").Activate
Sheets("table").Select
Worksheets("table").Unprotect
rng.Copy Destination:=Range("T2")

 

[Guest]

Thanks Tom
The next line of my code is
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Now I get "PasteSpecial method of Range class failed"

What am I missing?
Thanks!

 

[Guest]

My original post may be somewhat unclear. My ultimate goal is to paste the
contents of myfile.txt to '[Daily.xls]table!T2
Thanks

 

[Tom Ogilvy]

Dim bk As Workbook, rng As Range
ChDir "C:\Temp"
Workbooks.OpenText Filename:="C:\Temp\myfile.txt", _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), _
Array(8, 1), Array(12, 1), Array(17, 1), _
Array(21, 1), Array(29, 1), Array(38, 1), _
Array(46, 1), Array(50, 1), Array(58, 1), _
Array(67, 1))
set bk = ActiveWorkbook
Set rng = ActiveSheet.Range("A1").CurrentRegion
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)
rng.copy
With Workbooks("daily.xls").Sheets("table")
.Unprotect
rng.copy
.Range("T2").PasteSpecial xlValues
End With
' get rid of the text file "workbook"
Bk.close Savechanges:=False


Without seeing your code, it would be hard to tell - there is not
pastespecial in the code you showed. But I suspect what I provided should
work. And my guess on your problem is that between copying your data and
selecting the cell to pastespecial to, you are clearing the clipboard with
one of your actions - this is very easy to do with code - so there is
nothing to paste. I always like to get the copy and paste right next to
each other to avoid this. (as shown above)

 

[Guest]

OK I have found that if I step through the macro it works but if I try to run
it, it crashes. It appears as though it is not picking up the selection to
paste. I have modified some to include

Dim bk As Workbook, rng As Range
ChDir "C:\Temp"
Workbooks.OpenText Filename:="C:\Temp\myfile.txt", _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), _
Array(8, 1), Array(12, 1), Array(17, 1), _
Array(21, 1), Array(29, 1), Array(38, 1), _
Array(46, 1), Array(50, 1), Array(58, 1), _
Array(67, 1))
set bk = ActiveWorkbook
Set rng = ActiveSheet.Range("A1").CurrentRegion
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)
rng.select
selection.copy
Windows('daily.xls").Activate
Sheets("Table").Select
Worksheets("table").Unprotect
Range("t2").Select
ActiveSheet.Paste


Thanks