Hi All:
I posted and deleted something similar. I thought I would try again as I received no replies so I figure I must have confused the Board. ANYWAY...
I created the code below using the Macro Recorder. I am now trying to modify it for future use:
I have commented above what I THINK requires changing but I am not VBA savvy so I may very well be wrong.
I think I need the following portions of the code changed:
1) The Line of Code Below NEEDS CHANGING so that it only opens the folder and then let's the User Select thier own .TXT File
2) This code below needs modifying due to the fact that the text will ALWAYS be on a different Row... NOT Row 70
Any assistance would be GREATLY Appreciated...
THANKS,
Mark
I posted and deleted something similar. I thought I would try again as I received no replies so I figure I must have confused the Board. ANYWAY...
I created the code below using the Macro Recorder. I am now trying to modify it for future use:
Code:
Sub OpenandConvertROCSBReports()
'
'
' Keyboard Shortcut: Ctrl+a
'The Line of Code Below NEEDS CHANGING so that it only opens the folder and then let's the User Select thier own .TXT File
Workbooks.OpenText Filename:= _
"E:\FSDB\Revenue Management\40 Elm Documentation\ROCSB Disk Files\July 2007\IFIS Receipt Register_270707 BEFORE.txt" _
, Origin:=xlWindows, StartRow:=9, DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(22, 1), Array(57, 1), Array(77, 1), Array(92, 1), Array(106, 1), _
Array(121, 1), Array(136, 1))
'Find text "Total For Batch" in the Worksheet
Cells.Find(What:="Total For Batch", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
'This code below needs modifying due to the fact that the text will ALWAYS be on a different Row... NOT Row 70
Rows("70:70").Select
Selection.Cut
Rows("300:300").Select
ActiveSheet.Paste
End Sub
I have commented above what I THINK requires changing but I am not VBA savvy so I may very well be wrong.
I think I need the following portions of the code changed:
1) The Line of Code Below NEEDS CHANGING so that it only opens the folder and then let's the User Select thier own .TXT File
Code:
Workbooks.OpenText Filename:= _
"E:\FSDB\Revenue Management\40 Elm Documentation\ROCSB Disk Files\July 2007\IFIS Receipt Register_270707 BEFORE.txt" _
2) This code below needs modifying due to the fact that the text will ALWAYS be on a different Row... NOT Row 70
Code:
Rows("70:70").Select
Selection.Cut Rows("70:70").Select
Selection.Cut
Any assistance would be GREATLY Appreciated...
THANKS,
Mark