Software

Ask something
Discussion Locked

Excel Macros: Starting up and Exiting

0 helpful
11 replies

by gcheels

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I need to build a macro that will start as soon as I open the Excel file, do some processing, and exit Excel. What I intend to do is, schedule the Excel file to run at night, retrieve data from an Access database, save the file and exit Excel. I need to know how to automatically start the macro when the spreadsheet file opens and after it finishes it's processing, save the file (with no prompt to overwrite the file) and close Excel. Thanks.

11 replies

by dklippert

You may be looking for:

Auto_Open
Auto_Close

But also look at:

www.cpearson.com/excel/ontime.htm

by ammartq

Start excel file in which you need to use the macro. Press Alt+F11 (both keys together).
Double-click "ThisWorkbook" from left, top box. Select "Workbook" from top center combo box. You will see following function in main text area:

Private Sub Workbook_Open()

End Sub

Whatever you write between these two lines will be run when your excel file starts.

by ammartq

I need to know why my above answer was rejected although it was correct. Please specify your reason to reject the answer.

by gcheels

Your answer may be correct, but it's like saying to write a program, you 1)turn on the computer, 2)write the program, 3)then turn the computer off. I also needed to know what went between the lines as well. Besides, I just tried it, and the directions were not correct. When I press Alt-F11, "This Workbook" is not in the upper left combo box, it's in the main window and you have to click on it before it appears in the combo box........

by ammartq

You said you "need to know how to automatically start the macro when the spreadsheet file opens and after it finishes it's processing, save the file (with no prompt to overwrite the file) and close Excel."

Thats what I told you. If you want me togive you the code to get the data from Access, you should have clearly specified that in the question. Rather you said that YOU intend to do... so I thought you will write the data collection yourself. :)

by firstpeter

Perhaps this will help:

1. Start Excel.
2. Press Alt-F11 to enter the Visual Basic Editor.
3. Select the "ThisWorkbook" branch under "VBAProject(Book1)" by double-clicking on it [Book1 is the name of your Excel workbook].
4. The code you'll want to type in the code pane is:

Private Sub Workbook_Open()
<< Your data retrieval code here >>
ActiveWorkbook.Close savechanges:=True
End Sub

Does that help?

This discussion is closed to new replies.