Questions

How to Start a Excel Marco Automatically

+
0 Votes
Locked

How to Start a Excel Marco Automatically

tallman_roy
Hi,

I would like to know how to program or set a Excel file to run a marco upon opening the file?

Normally we need to click an object that link to a macro or run it by selecting Alt + F8. I want a specific marco in the excel file to start when any user open this particular Excel file. How can I do it? Please help.

Roy
  • +
    0 Votes
    Cozy_Rocks

    Put your macro code in the Workbook_Open() sub of the VB editor. In the VB Editor projects window, Double Click "This Workbook" and from the drop down at the top of the code window select "workbook" and "open". You would need to set you macro security down to zero for it to run without prompt. This is not genarally accepted as good practice as it would allow malicious code to run without stopping.

    +
    0 Votes
    tallman_roy

    Hi Acameron,

    I need to run the below macro automatically when the file is first open. I have tried the recommendation, but seems doesn't work. Might be due to the setting which I don't understand. Please guide me step-by-step if possible with screen-shot. You may contact me at tallman_roy@yahoo.com.sg

    ==============================
    Sub test()
    DES_SHT = "Sheet1"
    DES_VAL = Worksheets(DES_SHT).Cells(1, 4).Value

    DES_VAL = DES_VAL + 1
    Worksheets(DES_SHT).Cells(1, 4).Value = DES_VAL

    End Sub
    ==================================

    Regards.


    Roy

    +
    0 Votes
    Cozy_Rocks

    Roy

    I have sent an instruction document direct which I hope makes things a bit easier to understand and gets you the results you are after.

    Regards

    +
    0 Votes
    CharlieSpencer

    I've been able to get a macro to start automatically in Excel 2003 by naming the macro 'auto_open'. Naming it 'auto_close' will cause it to run when the spreadsheet is closed.

    Regarding the security, you can go through the process of generating your own signature. The single time I did that it took almost 20 seconds for the file to open. I could leave the macro security set to "Prompt" and click the "Open" button in less than five, so I stopped creating the sigs.

    +
    0 Votes
    Cozy_Rocks

    You can have too much in the opening process which slows down the work. better to have controls to activate queries or updates etc.
    I use the worksheet_open element to ensure all colums are set to autofit in a large spreadsheet with various sizes of numbers and different data types. Also to create an index sheet.

    +
    0 Votes
    tallman_roy

    Hi Andy,

    What do you means by;
    "use the worksheet_open element to ensure all colums are set to autofit in a large spreadsheet with various sizes of numbers and different data types. Also to create an index sheet. "

    Please explain. Thanks.


    Roy

    +
    0 Votes
    Cozy_Rocks

    If you have different lengths of numbers e.g. 1,000,000 and 1000 and maybe dates in your cels excel may not set the width of the colum to show all the data so it might display ##### instead. The macro below selects the first sheet and colums a to Z and auto fits the colums to their contents so every time the sheet is opened the data is dislayed correctly.

    Private Sub Workbook_Open()
    Sheets(1).Activate
    Range("A:Z").Columns.AutoFit
    End Sub
    Check this tech rep article for how to add an index sheet to your spreadsheet.
    http://articles.techrepublic.com.com/5100-10877_11-5629983.html

    +
    0 Votes
    tallman_roy

    Hi Andy,

    Thanks. I wrote you an email, did you got it?

    I am still stuck on the reading and writing without open the excal data file using VB. Any solution?

    Regards.


    Roy

    +
    0 Votes
    Jesus_C

    Press Alt+F11, or choose Macros from the Tools menu and then choose Visual Basic Editor from the resulting submenu. Excel displays the Visual Basic Editor.
    Near the upper-left side of the editor is the Project Explorer. This contains a hierarchical tree that shows the different modules in your workbook. If the Project Explorer is not visible on your screen, press Ctrl+R to display it.
    Within the Project Explorer should be a folder called Modules. If it is not already open, double-click on the Modules folder to display its contents.
    Right-click on a module in the folder. A Context menu is displayed.
    Choose the Remove option from the Context menu. You are asked if you want to export the module before removing it.

    +
    0 Votes
    tallman_roy

    Hi J, thanks. I got it.
    I have another 2 questions;
    1) How to read a cell without opening the excel data file using VB?

    2) How to write a data into a cell without opening the excel data file using VB?

    Please advise.
    Regards.


    Roy

  • +
    0 Votes
    Cozy_Rocks

    Put your macro code in the Workbook_Open() sub of the VB editor. In the VB Editor projects window, Double Click "This Workbook" and from the drop down at the top of the code window select "workbook" and "open". You would need to set you macro security down to zero for it to run without prompt. This is not genarally accepted as good practice as it would allow malicious code to run without stopping.

    +
    0 Votes
    tallman_roy

    Hi Acameron,

    I need to run the below macro automatically when the file is first open. I have tried the recommendation, but seems doesn't work. Might be due to the setting which I don't understand. Please guide me step-by-step if possible with screen-shot. You may contact me at tallman_roy@yahoo.com.sg

    ==============================
    Sub test()
    DES_SHT = "Sheet1"
    DES_VAL = Worksheets(DES_SHT).Cells(1, 4).Value

    DES_VAL = DES_VAL + 1
    Worksheets(DES_SHT).Cells(1, 4).Value = DES_VAL

    End Sub
    ==================================

    Regards.


    Roy

    +
    0 Votes
    Cozy_Rocks

    Roy

    I have sent an instruction document direct which I hope makes things a bit easier to understand and gets you the results you are after.

    Regards

    +
    0 Votes
    CharlieSpencer

    I've been able to get a macro to start automatically in Excel 2003 by naming the macro 'auto_open'. Naming it 'auto_close' will cause it to run when the spreadsheet is closed.

    Regarding the security, you can go through the process of generating your own signature. The single time I did that it took almost 20 seconds for the file to open. I could leave the macro security set to "Prompt" and click the "Open" button in less than five, so I stopped creating the sigs.

    +
    0 Votes
    Cozy_Rocks

    You can have too much in the opening process which slows down the work. better to have controls to activate queries or updates etc.
    I use the worksheet_open element to ensure all colums are set to autofit in a large spreadsheet with various sizes of numbers and different data types. Also to create an index sheet.

    +
    0 Votes
    tallman_roy

    Hi Andy,

    What do you means by;
    "use the worksheet_open element to ensure all colums are set to autofit in a large spreadsheet with various sizes of numbers and different data types. Also to create an index sheet. "

    Please explain. Thanks.


    Roy

    +
    0 Votes
    Cozy_Rocks

    If you have different lengths of numbers e.g. 1,000,000 and 1000 and maybe dates in your cels excel may not set the width of the colum to show all the data so it might display ##### instead. The macro below selects the first sheet and colums a to Z and auto fits the colums to their contents so every time the sheet is opened the data is dislayed correctly.

    Private Sub Workbook_Open()
    Sheets(1).Activate
    Range("A:Z").Columns.AutoFit
    End Sub
    Check this tech rep article for how to add an index sheet to your spreadsheet.
    http://articles.techrepublic.com.com/5100-10877_11-5629983.html

    +
    0 Votes
    tallman_roy

    Hi Andy,

    Thanks. I wrote you an email, did you got it?

    I am still stuck on the reading and writing without open the excal data file using VB. Any solution?

    Regards.


    Roy

    +
    0 Votes
    Jesus_C

    Press Alt+F11, or choose Macros from the Tools menu and then choose Visual Basic Editor from the resulting submenu. Excel displays the Visual Basic Editor.
    Near the upper-left side of the editor is the Project Explorer. This contains a hierarchical tree that shows the different modules in your workbook. If the Project Explorer is not visible on your screen, press Ctrl+R to display it.
    Within the Project Explorer should be a folder called Modules. If it is not already open, double-click on the Modules folder to display its contents.
    Right-click on a module in the folder. A Context menu is displayed.
    Choose the Remove option from the Context menu. You are asked if you want to export the module before removing it.

    +
    0 Votes
    tallman_roy

    Hi J, thanks. I got it.
    I have another 2 questions;
    1) How to read a cell without opening the excel data file using VB?

    2) How to write a data into a cell without opening the excel data file using VB?

    Please advise.
    Regards.


    Roy