Code needs a little Tweaking... Help PLEASE.

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
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:
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. :p

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... :biggrin:

THANKS,
Mark :confused:
 

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
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 :oops:
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,126,963
Messages
5,621,854
Members
415,862
Latest member
nascaline

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top