Some help for inexperinced VBA users

[Lo Lun To]

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.

[jkpd2000]

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.

[jkpd2000]

Hi ,

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

[Mark O'Brien]

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)

[kkknie]

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

[jkpd2000]

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

[Mark O'Brien]

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

[jkpd2000]

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 ?

[Mark O'Brien]

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 ]

[jkpd2000]

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.?