Code needs a little Tweaking... Help PLEASE.

[Mister H]

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:

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

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

   Rows("70:70").Select 
   Selection.Cut  Rows("70:70").Select 
   Selection.Cut

Any assistance would be GREATLY Appreciated...

THANKS,
Mark

 

[Mister H]

Hi John:

Glad you understood my babbling... In regards to just clicking Open my system Automatically Opens the "Text Import Wizard - Step 1 of 3" Dialog Box

When this opened is when I started formatting.

I just tried again by pasting your code in a brand new workbook. Ran the code and when the Wizard popped up I click Finish but still nothing copied to the original sheet?

I then tried clicking Cancel for the Wizard but this produced an error 400?

If this code is working for you then I am sure I must be doing something wrong or my set up is messed up.

Any suggestions?

THANKS Again,
Mark

 

[Mister H]

Hi John:

Not sure IF this makes a difference but when I create my txt file it is through a program that puts the file in Internet Window

I then do a File/Save As and I need to select

File Name
Save as Type
Encoding

I give it a name and save as Text File (*.txt) and the Encoding (Whatever that is) I leave at Western European (ISO)

Like I said I don't know if this makes a difference but I thought I had better mention it... Better late then never.

Bye 4 Now,
Mark