Auto Run a macro from PERSONAL.XLS

[Noogles]

Hey all, wondering if you can help with a small problem I am having. I am copying some data from a FoxPro table into an excel sheet. I have a macro in PERSONAL.XLS that will conditionally format the rows based on a specific value in one of the columns. It works fine if I select macros and run it manually. What I want to do is have the macro run automatically when the user opens the excel sheet. I tried putting the code in the Auto_Open() procedure of the sheet in question, the problem is when I copy the data from FoxPro to the Excel sheet, it overwrites the entire file and the macro is lost. Is there a way to automatically run the macro in PERSONAL when Excel opens? Tried Auto_Open() in PERSONAL but this does not work.....I think I read somewhere that it's because it is a hidden file. Or does anyone know how to simply append to the Excel doc from FoxPro instead of overwriting it....that would actually be better.

Cheers,

Noogles

[Simon Lloyd]

This link http://office.microsoft.com/en-us/ex...872961033.aspx explains very well how to Create, Add to and Use a personal.xls file

Regards,
SImon

[lucas]

Try putting this in the ThisWorkbook module of your personal.xls
[vba]Private Sub Workbook_Open()
MsgBox "test"
End Sub[/vba]

close excel and save changes to the personal.xls
open a new excel instance.

[Noogles]

Hey Lucas,

Thanks, yah that works....but I have a different problem now. What I want to do is when I open this specific excel doc, I want the macro in PERSONAL to take effect on the doc I have opened to highlight certain rows. Problem is I am getting an error message that says Method 'Cells' of object '_Global' failed. It stems from the line Cells.Select in the code below.

Private Sub Workbook_Open()


Cells.Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$W1=""U.S.A."""
Selection.FormatConditions(1).Font.ColorIndex = xlAutomatic
Selection.FormatConditions(1).Interior.ColorIndex = 6
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$W1=""Canada"""
Selection.FormatConditions(2).Interior.ColorIndex = 33
End Sub

Is this because it is referring to the wrong workbook or something? Thanks for the help.

Cheers,

Shawn

[lucas]

You have to tell it to act on the active workbook and the active sheet:
[vba]Option Explicit
Private Sub Workbook_Open()
With ActiveWorkbook
With ActiveSheet
Cells.Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$W1=""U.S.A."""
Selection.FormatConditions(1).Font.ColorIndex = xlAutomatic
Selection.FormatConditions(1).Interior.ColorIndex = 6
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$W1=""Canada"""
Selection.FormatConditions(2).Interior.ColorIndex = 33
End With
End With
End Sub[/vba]

ps If you select your code when posting and hit the vba button on the editor it will format it like it's in the vbe.

[Noogles]

Hey Lucas,

Thanks again. However, I tried this....pasted the code directly as you wrote it, and it still seems to be hanging on the same line.

Cheers

[lucas]

Your putting it in your personal.xls Thisworkbook module. Close and save and then just reopen a regular excel file? You have to specifically save changes to the personal..

[lucas]

Ok, It works on a new sheet but not if you open an existing. My question to you is do you really want this to run on every sheet you open and every sheet you create new? That is what will happen if you get it working the way your wanting it to.

[Noogles]

Yah I've done that and it still seems to hang on that line. Of note though.....if I open the debugger after receiving the error, I can step through the code and it works as it should.....it's just when it opens Excel that it doesn't seem to like that first line.

[lucas]

I would suggest that you put the code in a regular module as a public sub and add a button to an excel toolbar to run it when you need to.
Just right click on any toolbar
select customize at the bottom
select the commands tab
in the left window select macros
in the right window select either a menu item or a button and drag it to a toolbar....then assign your macro to the button by right clicking on the control before you close the cusomize dialog..etc.

[Noogles]

Erm....well.....I guess not.....I had considered that.....thought it wouldn't really be an issue....but it's probably better if I don't do it this way......I was just trying to automate the process a bit but in the long run how hard is it really to just run the macro manually....especially since it doen't like it when I open an existing sheet. Thanks alot for the help though.....I appreciate it.

[lucas]

Noogles,
This would be much easier if you could put it in the workbook that is being opened but you said that the file is being overwritten...any way around that?