Help with text file

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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
 
Back
Top