I need some help on this one...

I am trying to code a macro that will bring the same text file up and use
the fixed width method of text to columns. I have recorded the marco and
tried to figure the code based on that. Below is the recorded macro first,
followed by the code that I am building. Can some help mefill in the missing
pieces to the how the flow should be from the Dim to the Array?

Sub WPCOM()
'
' WPCOM Macro
' Macro recorded 3/15/2006 by ewagner
'

'
ChDir "G:\shared-drive\XXXX\XXXX\XX Sheets"
Workbooks.Open Filename:= _
"G:\XXXXX\XXXXX\XXXXX\XXXXX\XXXXX Mar-2006.xls"
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("MTD").Select
ActiveWindow.LargeScroll ToRight:=-1
ChDir "G:\COMMAND-CENTER\IEX LA info"
Workbooks.OpenText Filename:= _
"G:\XXXXX\XXXXX\XXXXX.xls", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1),
Array( _
165, 1), Array(174, 1), Array(182, 1))
ActiveWindow.LargeScroll Down:=1
Range("B62:B107").Select
Selection.Copy
ActiveWindow.ActivateNext
Range("B5").Select
Selection.PasteSpecial Paste:=xlValues
End Sub
___________________________

Sub WPCOM()

Dim LA As Workbook
Dim MTD As Worksheet
Dim IEX As Workbook
Dim WPCOM As Worksheet

Set LA = Workbooks.Open(Filename:= _
"G:\XXXX\XXXX\XXXX\XXXX\XXXX " & Format(Date, "mmm-yyyy") & ".xls")
Set MTD = LAWkbk.Worksheets("MTD")
Set IEX =

End Sub

The portion where the code ends is where I would imagine that the array code
would go to open the text file with the fixed width.

Thanks ahead of time for any help in the matter!
--
EW - Analyst

No, opentext doesn't return a reference, so you can't do what you imagine:

Sub WPCOM()

Dim LA As Workbook
Dim MTD As Worksheet
Dim IEX As Workbook
Dim WPCOM As Worksheet

Set LA = Workbooks.Open(Filename:= _
"G:\XXXX\XXXX\XXXX\XXXX\XXXX " & Format(Date, "mmm-yyyy") & ".xls")
Set MTD = LAWkbk.Worksheets("MTD")
OpenText blah blah blah
Set IEX = Activeworkbook

End Sub

--
Regards,
Tom Ogilvy



"ewagz" wrote:

I need some help on this one...

I am trying to code a macro that will bring the same text file up and use
the fixed width method of text to columns. I have recorded the marco and
tried to figure the code based on that. Below is the recorded macro first,
followed by the code that I am building. Can some help mefill in the missing
pieces to the how the flow should be from the Dim to the Array?

Sub WPCOM()
'
' WPCOM Macro
' Macro recorded 3/15/2006 by ewagner
'

'
ChDir "G:\shared-drive\XXXX\XXXX\XX Sheets"
Workbooks.Open Filename:= _
"G:\XXXXX\XXXXX\XXXXX\XXXXX\XXXXX Mar-2006.xls"
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("MTD").Select
ActiveWindow.LargeScroll ToRight:=-1
ChDir "G:\COMMAND-CENTER\IEX LA info"
Workbooks.OpenText Filename:= _
"G:\XXXXX\XXXXX\XXXXX.xls", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1),
Array( _
165, 1), Array(174, 1), Array(182, 1))
ActiveWindow.LargeScroll Down:=1
Range("B62:B107").Select
Selection.Copy
ActiveWindow.ActivateNext
Range("B5").Select
Selection.PasteSpecial Paste:=xlValues
End Sub
___________________________

Sub WPCOM()

Dim LA As Workbook
Dim MTD As Worksheet
Dim IEX As Workbook
Dim WPCOM As Worksheet

Set LA = Workbooks.Open(Filename:= _
"G:\XXXX\XXXX\XXXX\XXXX\XXXX " & Format(Date, "mmm-yyyy") & ".xls")
Set MTD = LAWkbk.Worksheets("MTD")
Set IEX =

End Sub

The portion where the code ends is where I would imagine that the array code
would go to open the text file with the fixed width.

Thanks ahead of time for any help in the matter!
--
EW - Analyst

Does this open the array in the text files....sorry, I'm not understanding
--
EW - Analyst


"Tom Ogilvy" wrote:

No, opentext doesn't return a reference, so you can't do what you imagine:

Sub WPCOM()

Dim LA As Workbook
Dim MTD As Worksheet
Dim IEX As Workbook
Dim WPCOM As Worksheet

Set LA = Workbooks.Open(Filename:= _
"G:\XXXX\XXXX\XXXX\XXXX\XXXX " & Format(Date, "mmm-yyyy") & ".xls")
Set MTD = LAWkbk.Worksheets("MTD")
OpenText blah blah blah
Set IEX = Activeworkbook

End Sub

--
Regards,
Tom Ogilvy



"ewagz" wrote:

I need some help on this one...

I am trying to code a macro that will bring the same text file up and use
the fixed width method of text to columns. I have recorded the marco and
tried to figure the code based on that. Below is the recorded macro first,
followed by the code that I am building. Can some help mefill in the missing
pieces to the how the flow should be from the Dim to the Array?

Sub WPCOM()
'
' WPCOM Macro
' Macro recorded 3/15/2006 by ewagner
'

'
ChDir "G:\shared-drive\XXXX\XXXX\XX Sheets"
Workbooks.Open Filename:= _
"G:\XXXXX\XXXXX\XXXXX\XXXXX\XXXXX Mar-2006.xls"
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("MTD").Select
ActiveWindow.LargeScroll ToRight:=-1
ChDir "G:\COMMAND-CENTER\IEX LA info"
Workbooks.OpenText Filename:= _
"G:\XXXXX\XXXXX\XXXXX.xls", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1),
Array( _
165, 1), Array(174, 1), Array(182, 1))
ActiveWindow.LargeScroll Down:=1
Range("B62:B107").Select
Selection.Copy
ActiveWindow.ActivateNext
Range("B5").Select
Selection.PasteSpecial Paste:=xlValues
End Sub
___________________________

Sub WPCOM()

Dim LA As Workbook
Dim MTD As Worksheet
Dim IEX As Workbook
Dim WPCOM As Worksheet

Set LA = Workbooks.Open(Filename:= _
"G:\XXXX\XXXX\XXXX\XXXX\XXXX " & Format(Date, "mmm-yyyy") & ".xls")
Set MTD = LAWkbk.Worksheets("MTD")
Set IEX =

End Sub

The portion where the code ends is where I would imagine that the array code
would go to open the text file with the fixed width.

Thanks ahead of time for any help in the matter!
--
EW - Analyst