how to add a recorded excel macro with existing code

[stever1725]

To anyone who can assist me.. i have this code that opens an existing .xls file and renames it and saves with a password. I recorded an excel macro to format the file and would like to add this right before it saves with a password. thanks steve

--opens and saves w / pwd
Public Function PROpwd()
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.DisplayAlerts = False
Set objWorkbook = objExcel.workbooks.Open("\\Steve\pfs it\CCP\PRONERVE.xls")
objWorkbook.Saveas "\\Steve\pfs it\CCP\PROCESSED\PRONERVE_CCPs " & Format(Date, "mmddyy") & ".xls", , "procardon"
objExcel.Quit
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
fso.Deletefile ("\\Steve\pfs it\CCP\PRONERVE.xls")

End Function


--- recorded from excel

Sub MacroCGH()
'
' MacroCGH Macro
' Macro recorded 5/15/2009 by steve
'

'
Range("A1:Q18").Select
Range("A18").Activate
Selection.Copy
Range("A20").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
With Selection
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Rows("1:19").Select
Range("A19").Activate
Selection.Delete Shift:=xlUp
Cells.Select
Cells.EntireColumn.AutoFit
Columns("B").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("A1").Select
End Sub

 

[lameid]

A good rule of themb is that anything you are automating in another program can be run off the application object in another.

Range("A20").Select

Becomes

objExcel.Range("A20").Select

Or

With objExcel
     .Range("A20").Select
End With

Usually I use the latter with recorded macros... I don't think you can nest with statments so you might be better with the first.

 

[lameid]

I looked and I was wrong... you can nest with box. Of course, only the context of the innermost with statment applies inside it.

 

[stever1725]

lameid.. so do i fix the verbage of the recorded macro and then paste the code after ***Set objWorkbook = objExcel.workbooks.Open("\\Steve\pfs it\CCP\PRONERVE.xls")***

if so do paste in the **Sub MacroCGH()** part as well?

 

[stever1725]

lameid, got it. thanks for the Great Advice!!