Macro still wont run

Bobby · 02-08-2006, 09:20 AM

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 · 02-08-2006, 10:20 AM

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")

--
Regards,
Tom Ogilvy

"Bobby" <[email protected]> wrote in message
news:[email protected]...
> 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!
>

Bobby · 02-08-2006, 01:45 PM

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!

"Tom Ogilvy" wrote:

> 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")
>
>
>
> --
> Regards,
> Tom Ogilvy
>
> "Bobby" <[email protected]> wrote in message
> news:[email protected]...
> > 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!
> >
>
>
>

Bobby · 02-08-2006, 04:45 PM

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

"Bobby" wrote:

> 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!
>
>
>
>
> "Tom Ogilvy" wrote:
>
> > 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")
> >
> >
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Bobby" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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 · 02-08-2006, 05:10 PM

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)

--
Regards,
Tom Ogilvy

"Bobby" <[email protected]> wrote in message
news:[email protected]...
> 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!
>
>
>
>
> "Tom Ogilvy" wrote:
>
> > 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")
> >
> >
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Bobby" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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!
> > >
> >
> >
> >

Bobby · 02-08-2006, 05:10 PM

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

"Tom Ogilvy" wrote:

> 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")
>
>
>
> --
> Regards,
> Tom Ogilvy
>
> "Bobby" <[email protected]> wrote in message
> news:[email protected]...
> > 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!
> >
>
>
>