Hi, I'm fairly green at VBA and am trying to update my workbook upon open. The scrpt runs without error, but nothing happens so I have no idea what's wrong. When I open my workbook now I receive two questions. 1) do I want to enable macros (yes) 2) do I want to update (no) What I would like to have happen is when the workbook is opened the update button is automatically activated and the entire spreadsheet updates and saves. What may be wrong with this VBA?
Unable to open embedded Excel spreadsheet in Word 2010. Hello my fellow Experts. One of my networked end users, Beatriz, is having an interesting issue. Open the source Excel document in Excel 2010, check for any macros (Alt+F11 to get VB Editor and look for Modules, userforms & classes in the top left panel), bad links (Data > Edit links.
Katlyn wrote: 2. What I would actually like to have happen (hope is always good) is to have the file update itself at a certain time of day, without actaully having to open the file. So far as I know, Excel VBA can't do that directly (although it might be able to so long as Excel is running and the macro is in an open workbook - maybe your Personal macro workbook). However, you can run Scheduled Tasks on a computer.
You could set a Scheduled Task to open the spreadsheet. Note that you may need to use an administrator account to schedule a task to run when you're not logged on, and on a 'locked down' corporate PC you may not be able to do it at all. See the following for more info on how to do this: If you're not using Windows XP, please tell us what operating system you are on and I'll post a more appropriate link. Katlyn wrote: 1.
What I'm trying to do it automatically update the workbook upon opening. This would include automaticallly answering all the questions that pop up when the file opens. That should be possible.
I'm just not sure what you mean by 'automatically update', especially when in your previous posts you've said that you respond 'No' to to the update links dialog box when you open the file. It looks like you have a custom 'refresh' button, but I need to know at least a little bit about what that is doing. I'm also confused as to what files you're referring to as getting updated.
To help me get my head around this, can you please tell me: 1. What is the name of the spreadsheet that contains the macro? What is the name of the spreadsheet that you open manually to start this whole process? (If same as above, say so.) 3. What is the name of the spreadsheet you want to 'automatically update'? (If same as 1 or 2 above, say so.) 4. What are you updating it with?
For example: a) Does it contains links to other spreadsheets and all you want is for those links to be refreshed? B) Does it contain a Database Query that you want to refresh?
C) Does it just contain formulae based on the current date, and you want those formulae to recalculate? The message about enabling macros is - as suggested by gbrandy - a result of your macro security settings, and if it keeps reverting to medium after you set it to low, then it's probably part of your user account (domain) setup. There are some things you can do to make a particular macro acceptable under Medium security (which will stop the prompt), but I'm pretty sure in a tighly locked down network environment most if not all of them may be denied to you anyway. Macros installed in 'trusted locations' should be allowed by all security settings. However, depending upon your network/account set-up you may not be able to add trusted locations and you may not have write permission to the existing trusted locations. To go this path you'll probably need to either add the network path that the macro spreadsheet is in to the trusted locations, or save the macro spreadsheet in an existing trusted location, or create an Add-In from the macro (that's not hard to do) and install the add-in to a trusted location. 'Signed' macros are allowed by Medium and High security (not by Very High), but getting a macro 'signed' isn't that easy.
The message about updating may be because you have links in the spreadsheet (the message will refer to updating links). If the links are not intentional, then finding and removing the links in the spreadsheet will stop that message from appearing. If the links are intentional, then you probably do want them to update - and this message should be able to be bypassed by the way in which you open the workbook. If the message is about updating external data sources (there was mention of Oracle in the macro, so I guess you have an external data source), then changing the properties of the data block might stop the message. Let me know exactly what the update message is saying and I'll explain further. I need more info to be able to help. Where is this macro stored?
What workbook, and what VBA module? Note that a WorkbookOpen event will only trigger if it is in the 'ThisWorkbook' object, it won't trigger if it is in 'Module1', etc. Which workbook do you want it to update? At the moment, your code opens a workbook called 'DailyShipmentsProto.xls' and does a 'Refresh' on that workbook, not on the workbook that you opened manually (the one with the code in it). Then it saves 'DailyShipmentsProto.xls' as 'DailyShipmentsProto1.xls'.
There's nothing in the macro which will modify the workbook that the macro is in unless the code for the 'Refresh' button includes a reference to it specifically. Hi, First thank you for your help! The macro is stored in ThisWorkbook, under Workbook Open The spreadsheet has nine sheets in it.
I changed the Sheet name to the actual name rather than the number and I changed the file name to 'DailyShipmentsProto1.xls'. I think it wants to work now, but on opening it asks if I want to Disable or Enable Macros. When I select Enable Macros it then asks if I want to Update or Don't Update. When I select Don't Update I get the message to debug. The macro de-bug lands on the line: Workbooks.Open Filename:='W: fnetproj public ShipmentTool DailyShipmentsProto1.xls', UpdateLinks:=0, ReadOnly:=0 Since the name is correct, I'm not certain why it errors. Hello again, It just dawned on me to let you know what I'm trying to do.
And then to ask you if there is a way to do what I would like it to do. What I'm trying to do it automatically update the workbook upon opening. This would include automaticallly answering all the questions that pop up when the file opens. What I would actually like to have happen (hope is always good) is to have the file update itself at a certain time of day, without actaully having to open the file. Can either be accomplished? Katlyn wrote: 2.
What I would actually like to have happen (hope is always good) is to have the file update itself at a certain time of day, without actaully having to open the file. So far as I know, Excel VBA can't do that directly (although it might be able to so long as Excel is running and the macro is in an open workbook - maybe your Personal macro workbook). However, you can run Scheduled Tasks on a computer. You could set a Scheduled Task to open the spreadsheet. Note that you may need to use an administrator account to schedule a task to run when you're not logged on, and on a 'locked down' corporate PC you may not be able to do it at all. See the following for more info on how to do this: If you're not using Windows XP, please tell us what operating system you are on and I'll post a more appropriate link. Katlyn wrote: 1.
What I'm trying to do it automatically update the workbook upon opening. This would include automaticallly answering all the questions that pop up when the file opens. That should be possible. I'm just not sure what you mean by 'automatically update', especially when in your previous posts you've said that you respond 'No' to to the update links dialog box when you open the file. It looks like you have a custom 'refresh' button, but I need to know at least a little bit about what that is doing. I'm also confused as to what files you're referring to as getting updated. To help me get my head around this, can you please tell me: 1.
What is the name of the spreadsheet that contains the macro? What is the name of the spreadsheet that you open manually to start this whole process? (If same as above, say so.) 3. What is the name of the spreadsheet you want to 'automatically update'? (If same as 1 or 2 above, say so.) 4. What are you updating it with? For example: a) Does it contains links to other spreadsheets and all you want is for those links to be refreshed?
B) Does it contain a Database Query that you want to refresh? C) Does it just contain formulae based on the current date, and you want those formulae to recalculate? The message about enabling macros is - as suggested by gbrandy - a result of your macro security settings, and if it keeps reverting to medium after you set it to low, then it's probably part of your user account (domain) setup.
![when Opening Excel 2010 For Mac Why Do I Get \ when Opening Excel 2010 For Mac Why Do I Get \](/uploads/1/2/5/4/125484936/564956998.png)
There are some things you can do to make a particular macro acceptable under Medium security (which will stop the prompt), but I'm pretty sure in a tighly locked down network environment most if not all of them may be denied to you anyway. Macros installed in 'trusted locations' should be allowed by all security settings. However, depending upon your network/account set-up you may not be able to add trusted locations and you may not have write permission to the existing trusted locations. To go this path you'll probably need to either add the network path that the macro spreadsheet is in to the trusted locations, or save the macro spreadsheet in an existing trusted location, or create an Add-In from the macro (that's not hard to do) and install the add-in to a trusted location.
'Signed' macros are allowed by Medium and High security (not by Very High), but getting a macro 'signed' isn't that easy. The message about updating may be because you have links in the spreadsheet (the message will refer to updating links). If the links are not intentional, then finding and removing the links in the spreadsheet will stop that message from appearing. If the links are intentional, then you probably do want them to update - and this message should be able to be bypassed by the way in which you open the workbook. If the message is about updating external data sources (there was mention of Oracle in the macro, so I guess you have an external data source), then changing the properties of the data block might stop the message. Let me know exactly what the update message is saying and I'll explain further.