Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Macro still wont run
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 75
Default Macro still wont run
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!

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Macro still wont run
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" wrote in message
...
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!



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 75
Default Macro still wont run
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" wrote in message
...
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!




Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 75
Default Macro still wont run
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" wrote in message
...
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!




Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Macro still wont run
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" wrote in message
...
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" wrote in message
...
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!






Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 75
Default Macro still wont run
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" wrote in message
...
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!




Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Keep getting error, wont run macro! HELP! Myles Excel Discussion (Misc queries) 2 March 30th 09 03:58 PM
Date format from excel to CVS file wont. Change in CVS wont stay. Fish''s Mermaid Excel Worksheet Functions 1 October 14th 06 12:28 AM
Macro wont run! Joe Excel Discussion (Misc queries) 9 January 17th 06 07:01 PM
Macro that wont go away - Help taxmom Excel Worksheet Functions 3 April 13th 05 06:39 PM
Help, Macro wont run from a particular worksheet Joel Mills Excel Programming 3 December 30th 04 05:47 PM


All times are GMT +1. The time now is 09:45 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement