Link to home
Start Free TrialLog in
Avatar of fireguy1125
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?
Avatar of rspahitz
rspahitz
Flag of United States of America image

It seems there may be several parts to this solution.
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.)
Avatar of Qlemo
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.
Avatar of fireguy1125
fireguy1125

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:

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

Open in new window

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")

Open in new window

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.)
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.
> 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:
Private Sub Workbook_Open()
    MsgBox "gonna run a macro"
End Sub

Open in new window

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

Open in new window

For example, you can do something like this:

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

Open in new window


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.
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
Private Sub Workbook_Open()
End Sub

Open in new window

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

Open in new window


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

Open in new window

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:
    If ActiveWorkbook.Name = "active.xls" Then' case sensitive comparison
        ProcessWorkbook
    End If

Open in new window


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.
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
Sub ProcessWorkbook()

Open in new window

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:
Private Sub Workbook_Open()
    If ActiveWorkbook.Name = "active.xls" Then' case sensitive comparison
        ProcessWorkbook
    End If
End Sub

Open in new window


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

Private Sub Workbook_Open()
    If ActiveWorkbook.Name = "active.xls" Then ' case sensitive comparison
    Debug.Assert False
        ProcessWorkbook
    End If
End Sub

Open in new window

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.
I believe I have.  Attached is a screenshot of what they look like.

User generated image
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.
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
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.
I'm not sure what it is I should do, the Shift-F9 on ActiveWorkbook highlight is in screenshot below:

User generated image
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:

Sub showWBs()
    Dim wb As Workbook
    For Each wb In Workbooks
        Debug.Print wb.Name
    Next
End Sub

Open in new window


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.
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\Modules folder and put in your code?
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.)
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.
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
Avatar of rspahitz
rspahitz
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks for your help with this.