write Macro using Macro

Andrew XJ

Board Regular
Joined
Feb 21, 2002
Messages
77
I need to write Macro to a series of files using a Macro. Is this possible? Please enlighten.
I appreciate any suggestions

XJ
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
On 2002-02-27 23:57, Andrew XJ wrote:
I need to write Macro to a series of files using a Macro. Is this possible? Please enlighten.
I appreciate any suggestions

XJ

Yes this is possible.....you can do a number
of things;
What are you trying to do ?
You can add to routines, copy modules, copy useforms etc, change Application events etc......

Would suggest that you have your routine in
a seperate module then through code export this out, open the files you want this code in and import the module in.
Probably best to keep / transfer the files to change in a seperate Dir (AND BACK them up JIC).


Let me know.


Ivan
 
Upvote 0
In fact i need to Modify a series of files and add some codes in the Auto_open() subroutine of each file. And another task is to Modify the codes according to the file name and path.
Could you provide some simple samples?
This message was edited by Andrew XJ on 2002-02-28 06:42
 
Upvote 0
Here's one I did a while back:

I called the following from my code (application.run("CopyOneModule"). In this case I'm taking module 3 and putting it in the new files. From what I understand, you have to export the module to text and import the module in your new workbook.

Private Sub CopyOneModule()

Dim FName2 As String
Dim FName3 As String
With Workbooks("Tool.xls")'name your workbook
FName2 = .path & "code.txt"
If FName2<> "" Then
On Error Resume Next
Kill FName2
End If
.VBProject.VBComponents("Module3").Export FName2
End With
FName3 = "Target Workbook Name.xls"
Workbooks(FName3).VBProject.VBComponents.Import FName2
Kill FName2
End Sub


And in module 3, I put the following procedure. All contents of module 3 should be in your target workbook.


Option Explicit

Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Private Sub auto_open()
'Macro hand-crafted by Nate Oliver on 1/29/2002
Dim sUser As String
Dim lpBuff As String * 1024
GetUserName lpBuff, Len(lpBuff)
sUser = Left$(lpBuff, (InStr(1, lpBuff, vbNullChar)) - 1)
lpBuff = ""
On Error Resume Next
If ActiveWorkbook.Name = "Tool.xls" Then
Else:
If sUser<> "" Then
MsgBox prompt:="Welcome " & WorksheetFunction.Proper(sUser) & " to the new tool.", _
Title:="Welcome!"
Else: MsgBox prompt:="Welcome to the new tool", _
Title:="Welcome!"
End If
End If

End Sub

Modifying code gets tricky. But, check out the following, Chip does a nice job.

http://www.cpearson.com/excel/vbe.htm

Hope this helps. Cheers,

Nate
This message was edited by NateO on 2002-02-28 08:26
 
Upvote 0
Thank you! Nate, i am sorry that i just step into Macro for one month. I am digesting second part of you codes. From my understanding. Else: is just a label named "Else". Is "Else:" just a typing error?
Your codes and the suggested website is very nice :).
May i didn't make my questions clear. I need to change parts of Macro of files. In one subroutine, maybe just some codes need to be modified. I feel it is almost impossible. It is using Macro to edit codes(text,macro)!!
 
Upvote 0
Andrew, the Else: is part of a vba "IF" statement.

If &*&*&*& then
'your code
else: 'your code
end if

If you look at Chip's site, you can see the parts regarding adding or deleting procedures. Personally, I think it's messy to delete certain lines, etc...I'd prefer to delete the replace the whole thing with code that works. Just my opinion. Hope this helps.

Cheers, Nate
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

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
Back
Top