fireguy1125
asked on
Macro Always Available for Excel 2007 Workbook
I have an excel workbook file that automatically gets replaced (saved over) on a daily basis from a scheduled task that copies it from another location.
I need to then run a macro in this xls file that performs a conversion.
I find that I need to enter the macro each time the file gets replaced.
Is there a way that I can have my macro saved to excel, so when i have the excel file set as a scheduled task to run, it will automatically add the macro and execute it?
I need to then run a macro in this xls file that performs a conversion.
I find that I need to enter the macro each time the file gets replaced.
Is there a way that I can have my macro saved to excel, so when i have the excel file set as a scheduled task to run, it will automatically add the macro and execute it?
No. You would be able to create a template containing the macro, but that would only help when having that template available on creation time of the workbook.
The way to go is to use VBScript or PowerShell, or a template XLS only containing the macro, but opening and accessing the other workbook, and run those instead.
The way to go is to use VBScript or PowerShell, or a template XLS only containing the macro, but opening and accessing the other workbook, and run those instead.
ASKER
How can I move the macro to a personal workbook?
The xls file is executed using windows task scheduler.
So I guess my next question is if I'm creating a new workbook which will remain static and contain my macro, how would I modify my macro to run and to call my other workbook which changes daily?
This is my current macro:
The xls file is executed using windows task scheduler.
So I guess my next question is if I'm creating a new workbook which will remain static and contain my macro, how would I modify my macro to run and to call my other workbook which changes daily?
This is my current macro:
Option Compare Text
Private Sub Workbook_Open()
If ActiveSheet.Cells(1, 11) = "Custom Attribute 3" Then
'nothing to do as already done
ActiveWorkbook.Save
Else
'fill extra columns
FillColumnKLM
'now save and close
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs "C:\Scripts\Reports\Rename\Active-transform.xls"
Application.DisplayAlerts = True
End If
Application.Quit
End Sub
Sub FillColumnKLM()
Dim WS As Worksheet, I As Long, RwCnt As Long
Set WS = ActiveSheet
RwCnt = WS.Cells(Rows.Count, 1).End(xlUp).Row
WS.Cells(1, 11) = "Custom Attribute 3"
WS.Cells(1, 11).Font.Bold = True
For I = 2 To RwCnt
On Error Resume Next
If InStr(1, LCase(WS.Cells(I, 1)), "Apple Street") Then
WS.Cells(I, 11) = "Fruit"
Else
End If
If InStr(1, LCase(WS.Cells(I, 1)), "Clear Water") Then
WS.Cells(I, 11) = "Ocean"
Else
End If
If InStr(1, LCase(WS.Cells(I, 1)), "Blue Sand") Then
WS.Cells(I, 11) = "Sun"
Else
End If
Next
Columns.AutoFit
End Sub
ASKER
I was able to create the Personal workbook, and have it saved in the PERSONAL.XLSB and the Macro in Module1. How do I link the personal.xlsb and my workbook called active.xls now?
You would write something like:
set wb = Workbooks.Open("PathAndFile")
and can then refer to the workbook wb instead of ActiveWorkbook, and so on.
>How can I move the macro to a personal workbook?
If you've previously recorded macros, you may already have access to the Personal Macro workbook. Open the VBA area and look in the Project window on the left (menu View | Project Explorer, if needed.)
Look through the projects for VBAProject (PERSONAL.XLS*)
If not there, then you should be able to expose it by going back to excel and preparing a new macro that will use it:
(Using Excel 2007/2010):
View | Macro | Record Macro
in "Store macro in:" select "Personal Macro Workbook" and click OK.
Stop the recorder and you should now have this project in VBA with a blank macro (in a Module, which you can remove).
Add all of your macro code into this workbook and it should be accessible to all workbooks when you open them (although only on your machine.)
If you've previously recorded macros, you may already have access to the Personal Macro workbook. Open the VBA area and look in the Project window on the left (menu View | Project Explorer, if needed.)
Look through the projects for VBAProject (PERSONAL.XLS*)
If not there, then you should be able to expose it by going back to excel and preparing a new macro that will use it:
(Using Excel 2007/2010):
View | Macro | Record Macro
in "Store macro in:" select "Personal Macro Workbook" and click OK.
Stop the recorder and you should now have this project in VBA with a blank macro (in a Module, which you can remove).
Add all of your macro code into this workbook and it should be accessible to all workbooks when you open them (although only on your machine.)
ASKER
when i put in the line as you provide and then run the macro, it gives me the error:
Compile error: Invalid outside procedure with the Set command highlighted.
Compile error: Invalid outside procedure with the Set command highlighted.
> how would I modify my macro to run and to call my other workbook which changes daily?
Go into the VBA Personal workbook.
Expand the Microsoft Excel Objects folder.
Double-click ThisWorkbook.
Add code like this:
Every time you open Excel, you will see this message.
Next you'll want to change this so that, for example, you only run this code when a specific workbook opens. then change the code to run the specific macro you want. You may need to update certain references to point to that workbook,
Go into the VBA Personal workbook.
Expand the Microsoft Excel Objects folder.
Double-click ThisWorkbook.
Add code like this:
Private Sub Workbook_Open()
MsgBox "gonna run a macro"
End Sub
Close your workbook to save the above code.Every time you open Excel, you will see this message.
Next you'll want to change this so that, for example, you only run this code when a specific workbook opens. then change the code to run the specific macro you want. You may need to update certain references to point to that workbook,
Private Sub Workbook_Open()
If ActiveWorkbook.Name = "active.xls" Then' case sensitive comparison
MsgBox "hey"
End If
End Sub
For example, you can do something like this:
Note that this will work on new Excel sessions but not if you open a workbook inside an existing Excel session, since the personal workbook will not re-open. When you open a new session, it will tell you that the personal workbook "is locked for editing" and you can open it read-only, which can then run your macro if set up correctly.
Private Sub Workbook_Open()
If ActiveWorkbook.Name = "Book1" Then
AddData
End If
End Sub
Sub AddData()
Dim irow As Integer
For irow = 1 To 20
ActiveWorkbook.Sheets(1).Cells(irow, 1).Formula = "=row()"
Next
End Sub
Note that this will work on new Excel sessions but not if you open a workbook inside an existing Excel session, since the personal workbook will not re-open. When you open a new session, it will tell you that the personal workbook "is locked for editing" and you can open it read-only, which can then run your macro if set up correctly.
ASKER
I can't have a pop-up come up, because this will be set to a scheduled task that will automatically open the workbook, run the macro, save it as a different file name, then close Excel.
When I put in the
I would need to know how to integrate based on the code provided, I'm novice when it comes to scripting, and although I appreciate the examples, I would need the exact instructions how to move forward with creating the script.
So far, I have 2 VBAProject modules.
One is is VPNProject(active.xls), which contains the following in ThisWorkbook:
The other is VBAProject (PERSONAL.XLSB), in the Modules folder, and the Module1, contains the code:
When I put in the
Private Sub Workbook_Open()
End Sub
in the This workbook code, nothing happens, and the script does not run.I would need to know how to integrate based on the code provided, I'm novice when it comes to scripting, and although I appreciate the examples, I would need the exact instructions how to move forward with creating the script.
So far, I have 2 VBAProject modules.
One is is VPNProject(active.xls), which contains the following in ThisWorkbook:
Private Sub Workbook_Open()
End Sub
The other is VBAProject (PERSONAL.XLSB), in the Modules folder, and the Module1, contains the code:
Option Compare Text
Private Sub Workbook_Open()
If ActiveSheet.Cells(1, 11) = "Custom Attribute 3" Then
'nothing to do as already done
ActiveWorkbook.Save
Else
'fill extra columns
FillColumnKLM
'now save and close
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs "C:\Scripts\Reports\Rename\Active-transform.xls"
Application.DisplayAlerts = True
End If
Application.Quit
End Sub
Sub FillColumnKLM()
Dim WS As Worksheet, I As Long, RwCnt As Long
Set WS = ActiveSheet
RwCnt = WS.Cells(Rows.Count, 1).End(xlUp).Row
WS.Cells(1, 11) = "Custom Attribute 3"
WS.Cells(1, 11).Font.Bold = True
For I = 2 To RwCnt
On Error Resume Next
If InStr(1, LCase(WS.Cells(I, 1)), "Apple Street") Then
WS.Cells(I, 11) = "Fruit"
Else
End If
If InStr(1, LCase(WS.Cells(I, 1)), "Clear Water") Then
WS.Cells(I, 11) = "Ocean"
Else
End If
If InStr(1, LCase(WS.Cells(I, 1)), "Blue Sand") Then
WS.Cells(I, 11) = "Sun"
Else
End If
Next
Columns.AutoFit
Re-read this post (which I think you missed): https://www.experts-exchange.com/questions/28327589/Macro-Always-Available-for-Excel-2007-Workbook.html?anchorAnswerId=39746638#a39746638
Your code to start and end a sub will, of course, do nothing, because there is no code there to run.
Instead you'll need to try something like this, IN THE PERSONAL WORKBOOK's Thisworkbook:
Then change from:
Private Sub Workbook_Open()
...
to
Sub ProcessWorkbook()
...
From there, you may still need to make some changes to the code to ensure that it references the ActiveWorkbook, but try this first, since you reference ActiveSheet etc so it may be fine.
Your code to start and end a sub will, of course, do nothing, because there is no code there to run.
Instead you'll need to try something like this, IN THE PERSONAL WORKBOOK's Thisworkbook:
If ActiveWorkbook.Name = "active.xls" Then' case sensitive comparison
ProcessWorkbook
End If
Then change from:
Private Sub Workbook_Open()
...
to
Sub ProcessWorkbook()
...
From there, you may still need to make some changes to the code to ensure that it references the ActiveWorkbook, but try this first, since you reference ActiveSheet etc so it may be fine.
ASKER
I've performed the change, however the macro still does not run automatically when I open the xls file. Additionally, when I go to run the Macro manually, I'm given the option to choose from Macros In: VBAProject (PERSONAL.XLSB) or VBAProject (active.xls).
When I choose the Run the FillColumnKLM or the ProcessWorkbook Macro in the VBAProject (PERSONAL.XLSB), I get the Compile error: Invalid outside procedure and it highlights the
When I choose the Macros in VBAProject (active.xls) there are no available macros to run.
When I choose the Run the FillColumnKLM or the ProcessWorkbook Macro in the VBAProject (PERSONAL.XLSB), I get the Compile error: Invalid outside procedure and it highlights the
Sub ProcessWorkbook()
line in yellow.When I choose the Macros in VBAProject (active.xls) there are no available macros to run.
It works for me just fine so it appears that something is not quite right.
First, make sure that the active.xls has NO Workbook_Open(). If it's there, remove it. Only the personal workbook should have the code. (Basically, your active.xls should have no code at all, since it will be overwritten each day, right?)
Next, make sure that the code in the personal workbook open looks like this:
Next, make sure that ProcessWorkbook is accessible. It should be located in a module inside your personal workbook (such as just underneath the above sub, and should not start with the word Private if you place it in a different module.
Next, make sure that when you open your active.xls workbook, that there are no Excel sessions open. You should probably locate the file and double-click it to launch it with your Personal workbook.
Beyond that, something you can try to see if the code is running at all is to set a "breakpoint". The problem is that when Excel is closed, it removes all breakpoints. Instead, we can force VB to break by inserting this code temporarily on a new line after the workbook_open:
Debug.Assert False
Now, when you first open Excel, it should throw you into the debugger, where you can then trace through by pressing the F8 key to step in and see where the code goes or you can use other debugging techniques to review what's happening.
First, make sure that the active.xls has NO Workbook_Open(). If it's there, remove it. Only the personal workbook should have the code. (Basically, your active.xls should have no code at all, since it will be overwritten each day, right?)
Next, make sure that the code in the personal workbook open looks like this:
Private Sub Workbook_Open()
If ActiveWorkbook.Name = "active.xls" Then' case sensitive comparison
ProcessWorkbook
End If
End Sub
Next, make sure that ProcessWorkbook is accessible. It should be located in a module inside your personal workbook (such as just underneath the above sub, and should not start with the word Private if you place it in a different module.
Next, make sure that when you open your active.xls workbook, that there are no Excel sessions open. You should probably locate the file and double-click it to launch it with your Personal workbook.
Beyond that, something you can try to see if the code is running at all is to set a "breakpoint". The problem is that when Excel is closed, it removes all breakpoints. Instead, we can force VB to break by inserting this code temporarily on a new line after the workbook_open:
Debug.Assert False
Now, when you first open Excel, it should throw you into the debugger, where you can then trace through by pressing the F8 key to step in and see where the code goes or you can use other debugging techniques to review what's happening.
ASKER
Thanks for clarifying several points. I've put in the Debug.Assert False and ensured I don't have any active excel processes or workbooks open. When I go to open Excel after running through your instructions, i'm present with the following:
Compile Error: Expected variable or procedure, not module
with the ProcessWorkbook highlighted in blue in the PERSONAL.XLSB Workbook
Also the Private Sub Workbook_Open() is highlighted in yellow.
The code i'm using in the PERSONAL.XLSB ThisWorkbook is:
Compile Error: Expected variable or procedure, not module
with the ProcessWorkbook highlighted in blue in the PERSONAL.XLSB Workbook
Also the Private Sub Workbook_Open() is highlighted in yellow.
The code i'm using in the PERSONAL.XLSB ThisWorkbook is:
Private Sub Workbook_Open()
If ActiveWorkbook.Name = "active.xls" Then ' case sensitive comparison
Debug.Assert False
ProcessWorkbook
End If
End Sub
The errors indicate":
Yellow: where the code was trying to run when it found a problem.
Blue: where the problem is located.
It appears that the sub named "ProcessWorkbook" cannot be located. Did you move that code into the same module as the Workbook Open? That's the easiest solution. Alternately make sure it does NOT have the word Private in front of it if it's in another module.
Yellow: where the code was trying to run when it found a problem.
Blue: where the problem is located.
It appears that the sub named "ProcessWorkbook" cannot be located. Did you move that code into the same module as the Workbook Open? That's the easiest solution. Alternately make sure it does NOT have the word Private in front of it if it's in another module.
Just spotted the error!
You (accidentally?) named the module the same as the name of the sub. Either rename the module or rename the sub and call that new name.
Also, at this point you should remove the Assert line (or comment it out) so that it doesn't interrupt the normal flow of the program.
You (accidentally?) named the module the same as the name of the sub. Either rename the module or rename the sub and call that new name.
Also, at this point you should remove the Assert line (or comment it out) so that it doesn't interrupt the normal flow of the program.
ASKER
Thanks, i renamed the module from ProcessWorkbook to Module1. Just to confirm, when I did this, I didn't have to change any of the ProcessWorkbook commands in the code? I commented out the Assert line, and re-opened the active.xls workbook, but i'm getting a run-time error '91': Object variable or With block variable not set
Now the 2nd line in my PERSONALXLSB code is highlighted in yellow:
If ActiveWorkbook.Name = "active.xls" Then ' case sensitive comparison
Now the 2nd line in my PERSONALXLSB code is highlighted in yellow:
If ActiveWorkbook.Name = "active.xls" Then ' case sensitive comparison
ASKER
I press Debug, and then press the green continue button, it actually performs the macro function, then closes out and generates the new excel file, so it works, but not hands free and that run-time error pops up. The same error comes up when i launch the destination file name, which it shouldn't running the macro with the new file name.
It looks like there might be a reference problem in the personal workbook. The only thing that would cause that error on the line is ActiveWorkbook.
Next time you're on that line, hover over and see if it shows anything in a tooltip. Or select that word and use Shift-F9 to display the current value.
We could take out that line (and the corresponding End If) but then it would apply to every workbook. However, you may want to try that first just to see if it works smoothly, then we can figure out how to add it back.
Next time you're on that line, hover over and see if it shows anything in a tooltip. Or select that word and use Shift-F9 to display the current value.
We could take out that line (and the corresponding End If) but then it would apply to every workbook. However, you may want to try that first just to see if it works smoothly, then we can figure out how to add it back.
ASKER
Hmmm...so it's not able to find the current workbook. I'm not sure exactly how you're running this, but maybe it needs a different context.
You could try checking to see what workbooks you have. Something like this in a test sub:
After running it, look in the Intermediate window (Ctrl+G to open if not showing) and you should see a list of all workbooks. It seems that maybe you have none open, according to Excel. There may be a "runaway" Excel session hanging around so check your windows task box after you close all Excels and make sure there are none left.
However, if you see the desired workbook in the window, then we should be able to do something simple like Workbooks(0).Activate before the IF.
You could try checking to see what workbooks you have. Something like this in a test sub:
Sub showWBs()
Dim wb As Workbook
For Each wb In Workbooks
Debug.Print wb.Name
Next
End Sub
After running it, look in the Intermediate window (Ctrl+G to open if not showing) and you should see a list of all workbooks. It seems that maybe you have none open, according to Excel. There may be a "runaway" Excel session hanging around so check your windows task box after you close all Excels and make sure there are none left.
However, if you see the desired workbook in the window, then we should be able to do something simple like Workbooks(0).Activate before the IF.
ASKER
I'm running it by basically double-clicking on the workbook that I want this macro to run on, and immediately upon opening it i get the following pop-up message:
Run-time error '91': Object variable or With block variable not set
I then press End, manually go into the Developer tab, select Visual Basic, then press F5 to Run Sub/UserForm
After which the process completes successfully.
I'm not exactly sure where to enter your code as a test sub into my VBA project - do I create a new module in my VBAProject\PERSONAL.XLSB\M odules folder and put in your code?
Run-time error '91': Object variable or With block variable not set
I then press End, manually go into the Developer tab, select Visual Basic, then press F5 to Run Sub/UserForm
After which the process completes successfully.
I'm not exactly sure where to enter your code as a test sub into my VBA project - do I create a new module in my VBAProject\PERSONAL.XLSB\M
Just put it in as a test Sub anywhere in the VBA windows for starters. Then put the cursor anywhere inside the Sub and Press the F5 key. Check the Immediate window (Ctrl+G) to see the output, unless you get an error.
At some point, you'll want to move it into the Personal macro area and check it there.
I'm wondering if either the Personal Workbook is hidden, or otherwise inaccessible, or that maybe you have to open Excel first, then open the workbook (rather than double-click, in which case we may need to find another solution.)
At some point, you'll want to move it into the Personal macro area and check it there.
I'm wondering if either the Personal Workbook is hidden, or otherwise inaccessible, or that maybe you have to open Excel first, then open the workbook (rather than double-click, in which case we may need to find another solution.)
ASKER
It seems I lost the PERSONAL.XLSB association on the Excel workbook, I don't know how this happened, but I am able to run it manually from the location and it works
The personal workbook likes to remain hidden on new installations of Office/Excel. To find it, you can simply start recording a macro that uses it (in the initial dialog window when specifying the macro name.) If it got "lost" after-the-fact, I'm not sure if the above will bring it back or if there's another way to retrieve it.
Let me know what else you need to proceed.
Let me know what else you need to proceed.
ASKER
If I open the active.xls workbook and keep it open, then i open the PERSONAL.XLSB spreadsheet, it runs the macro and closes automatically and works. How can I have it setup so I don't have to run both workbooks, as the end goal is to run a scheduled task to perform this process.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks for your help with this.
First, if the macros are being added to the workbook over and over, you should move the macro into a shared workbook (such as the Personal workbook.) This wold allow you to simply run the macro without having to import it each time.
The next part is determining how to get the scheduling to run the desired macro(s). How is The scheduling happening? Is it a Windows task or some other tool?
I've never tried automating a saved macro to apply to a different workbook, but it certainly seems do-able. The approach I would take is to create a "bootstrap" object (maybe workbook) that handles all of the work for you.
For example, create new workbook that has the desired macro in it and have your automation call this workbook along with a start-up macro that launches the daily workbook and runs the macro against that workbook. (There are better ways, but that might just be the simplest with tools that you know how to use.)