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:
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
This revised code does what I think you are after:
Code:
Sub OpenandConvertROCSBReports()
' Keyboard Shortcut: Ctrl+a
Dim MyPath As String

    MyPath = "E:\FSDB\Revenue Management\40 Elm Documentation\ROCSB Disk Files\July 2007\"
    ChDir (MyPath)
    Application.Dialogs(xlDialogOpen).Show

'Find text "Total For Batch" in the Worksheet and move to row 300
    Cells.Find(What:="Total For Batch", After:=ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False).EntireRow.Cut Range("A300")
End Sub
 

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
Hi John:

I just returned to the office and noticed that you replied to my query. THANKS. :biggrin:

I just tried you code and I am getting an error.

Run-time error '91':

Object variable or With block variable not set

This may very well be due to an error on my end. I am not VBA savvy so I may have messed things up.

The other thing I was wondering is can I still set the margins and Import on Line number 9 using whatever file the user selects? I ran your code by itself and then i also tried merging it with what I had:

Code:
Sub OpenandConvertROCSBReports()

Dim MyPath As String

MyPath = "E:\FSDB\Revenue Management\40 Elm Documentation\ROCSB Disk Files\July 2007\"
ChDir (MyPath)
Application.Dialogs(xlDialogOpen).Show

'
'
'
'

'THIS PART OF MY CODE IS RED (ERROR)
'HOW DO I IMPLEMENT THIS PART OF THE CODE SO THAT THE SLECTED FILE IS IMPORTED AS FOLLOWS:

'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 and move to row 300
 
Cells.Find(What:="Total For Batch", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).EntireRow.Cut Range("A300")



'
'
'
'I added this part.  Is this correct?

Rows("300:300").Select
ActiveSheet.Paste



End Sub

Any further assistance would be appreciated.

THANKS Again John,
Mark :biggrin:
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
Hi Mark,

I couldn't tell at which line of code you are getting your "Run-time error '91':"

I don't know how to incorporate your "Open File with" options and the Open Dialog box to allow the user to select a file.

As for the
'I added this part. Is this correct?

Rows("300:300").Select
ActiveSheet.Paste
That should not be needed because in my suggested code I included a destination in the FIND statement.
Code:
Cells.Find(What:="Total For Batch", After:=ActiveCell, LookIn:=xlFormulas _ 
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ 
MatchCase:=False).EntireRow.Cut Range("A300")
This code ends with "EntireRow.Cut" and then the destination "Range("A300")". So it removes the row that was found by the FIND statement and pastes it at row 300, all in one step.
 

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507

ADVERTISEMENT

Hi John:

I was playing around with your code a little more and I think the problem might be that the User is opening a TXT file and NOT an Excel file. I think this was causing the error? When I Cut and Paste Special/Values onto the spreadsheet and run your code it indeed does work.

I guess I did not really want to open the TXT file I guess I should have asked how to IMPORT a TXT file into Excel.

When I run your code it allows me to select a file but it opens in a seperate documnet form the one I have placed the code in. This leaves me with 2 books open. Is there a code that you know of that would:

Open a TXT file
Copy the Values of the TXT file
Paste them into the Original Excel

Also in your Open Files can that be set to ALL Files or TEXT Files? The user will always be looking for a txt file and currently these won't show until the user changes the File Type which seems to default to xls.

Anyway, I thogut I had better reply to you and now I am off to see if I can find answer to some of my questions.

THANKS AGAIN John,
Take Care,
Mark
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
This code will open an "Open File" dialog box. After your selection the code will copy all cells on that Source page to the worksheet that was open when you started. Then the Source file will be closed without saving.
Code:
Sub OpenandConvertROCSBReports3()
Dim MyPath As String
Dim Target, TargetS, Source

'Assign variables for Target
    Target = ActiveWorkbook.Name
    TargetS = ActiveSheet.Name

'Open Dialog box for Source file
    MyPath = "E:\FSDB\Revenue Management\40 Elm Documentation\ROCSB Disk Files\July 2007\"
    ChDir (MyPath)
    Application.Dialogs(xlDialogOpen).Show

'Assign variable of opened file
    Source = ActiveWorkbook.Name

'Copy all cells from Source to Target
    Cells.Copy Workbooks(Target).Sheets(TargetS).Range("A1")

'Close Source file without Save
    Workbooks(Source).Close SaveChanges:=False

'Put code for other steps here

End Sub
The Open File dialog box will open with whatever was last chosen. If you looked for txt files last, that will be the current choice.
 

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507

ADVERTISEMENT

Hi John... SORRY to be such a PAIN but I can't get the code to function properly?

It seems to do everything except for Pasting the Value into the original workbook?

When I step through the code (F8) I can not see where it is copying the text. It ask for the file I want to open. I select the appropriate Text file and then the text file closes and the original is back on screen but nothing has been copied.

I placed the code on the Worksheet. Did I do something wrong? :oops:

THANKS for sticking with me John, MUCH APPRECIATED
Mark :confused:
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
Does the Text file have any data on the single worksheet that opens?
Should be able to see it if you are stepping through the code with F8.
When your highlighted step is at "Source = ActiveWorkbook.Name" what do you see on the excel page? Should see the cells that will be copied at the next step.
 

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
Hi John:

As I step through your code when the Dialog box opens I find my file and then:

1) When I am opening the txt file I am opening it as Fixed Width
2) I then begin the Import on Row 9
3) I then click Next
4) I then adjust the margins
5) I then click Next
6) I then click Finish

The Text File opens and then the code is on:
Source = ActiveWorkbook.Name

The Text File is Open in Excel. I then F8 again and it then highlights:
Cells.Copy Workbooks(Target).Sheets(TargetS).Range("A1") (I do not see anything highlighted or any indication that something has been copied?

F8 again and it closes the Text file and goes back to the Original File but there is nothing pasted into this document.

I do not have data in A1 of the Text file but there is data in Row 1. This will always change so I need the whole sheet copied form the Text File and then Pasted into the Excel file.

I hope I am not confusing the situation wiht my explanation.

THANKS John,
Mark :p
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
Excellent explanation.
You are not going to see anything highlight when the copy takes place. The way the code is written it does not "select" anything, runs much faster this way.

Maybe all your "Format" adjustments before you open the file are not allowing the code to copy what is there. Are those really needed?
Try the code without any adjustments at all, just select the file and click "Open".
 

Watch MrExcel Video

Forum statistics

Threads
1,127,529
Messages
5,625,351
Members
416,096
Latest member
forevans

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