write Macro using Macro

[Andrew XJ]

I need to write Macro to a series of files using a Macro. Is this possible? Please enlighten.
I appreciate any suggestions

XJ

 

[Guest]

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

 

[Ivan F Moala]

didn't mean to post Anon ??

ivan

 

[Andrew XJ]

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

 

[NateO]

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

 

[Andrew XJ]

[No message]

 

[Andrew XJ]

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)!!

 

[NateO]

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