Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Some help for inexperinced VBA users

  1. #1

    Join Date
    May 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    I see a great number of requests on this board for VBA code that can be obtained merely by running the macro recorder.

    Before posting requests for macro code, try the macro recorder.
    This may result in your question to this board being unnecessary.
    In addition, your VBA knowledge is likely to be more enhanced by having found the code yourself, rather than being supplied by others.

    Persons who are experienced with VBA frequently use the macro recorder to get code.
    Many of the answers supplied to the many simple VBA questions on this board are most likely obtained from the macro recorder.



  2. #2
    Board Regular
    Join Date
    May 2002
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    After i read your message i tried and it worked. But my question is can you tell us who are inexperienced a small vbscript code example , how to place the code , where to place and how to run it.

    For example. I have created a macro to open a text file and save it as excel.

    Now i want to run it automatically every day.
    What should i do ? Thanks in advance.

  3. #3
    Board Regular
    Join Date
    May 2002
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi ,

    I am recording a macro. and after i exit from excel the macro is getting deleted . how to retain that ? any thoughts?

  4. #4
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How would you like this to run automatically? When the workbook is opened or something like that? (Note: You're going to have to at least open the workbook to get code to run)

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Location
    Greenwood, SC
    Posts
    677
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    jkpd2000,

    If you exit without saving the workbook it will get deleted (since you are recording it into the workbook). If you want to have it available to all workbooks you open, you will need to save it into your personal macro workbook (by changing the drop-down list when you start recording the macro).

    K

  6. #6
    Board Regular
    Join Date
    May 2002
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,
    That worked. thanks.

    Now i need to run it automatically. i.e.
    i created a personal macro. i saved it.
    But every day i have to open a file, run that macro and save that file as a different file. it should do automatically.

    is there a way ?

    thanks in advance

  7. #7
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    (Note: You're going to have to at least open the workbook to get code to run)

  8. #8
    Board Regular
    Join Date
    May 2002
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    i think i can open the work book from a batch file. my question is how to run the macro i created to run automatically. is there any option i can specify in batchfile itself to run that macro as soon as it opens the workbook ?

  9. #9
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That's what I was asking you before.

    Ok:

    1. Open you workbook
    2. Hit ALT + F11 to open the VBE
    3. Double Click on "ThisWorkbook" in the project explorer pane
    4. On the combobox on the left hand side at the top of the code pane, select "WorkBook", this code should then appear:


    Private Sub Workbook_Open()

    End Sub


    5. Insert the name of your macro between these two lines of code. e.g. if it's called "Sub Macro123()" put this:


    Private Sub Workbook_Open()
    Macro123
    End Sub


    6. Save the workbook and close.

    Whenever the workbook is opened, this code will run. However, unless your security level is set lower than "Medium" or you have some kind of digital signature, your workbook will always throw up the "Enable Macros?" box when the worksheet is opened.

    _________________
    [b] Mark O'Brien

    [ This Message was edited by: Mark O'Brien on 2002-05-10 07:56 ]

  10. #10
    Board Regular
    Join Date
    May 2002
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    At last , I am getting some hope.

    Mark, I did the same way you told.

    didn't work.

    1. i did a macro with recorder called test and placed just as you said.

    Private Sub Workbook_Open()
    test
    End Sub

    it is giving error.

    sub or function not defined. do i need to paste the macro code somewhere?

    2. how to open this workbook from scheduler or batch file.?


Some videos you may like

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
  •