write Macro using Macro
Manage your personal finances in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: write Macro using Macro

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Singapore
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    XJ

  2. #2
    Guest

    Default

    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

  3. #3
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    didn't mean to post Anon ??

    ivan

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Singapore
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Singapore
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Singapore
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  8. #8
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com