Error in Workbook_Open

[matt]

Hello,

I have an excel file that I get as an attachment everymonring with a table
in it. Every monring I save the attachment over the previous day's file.
If I open the document and then open the VB editor in the project explorer
on the left, there is two projects the first I believe Personal.xls and the
second is the attachment file that I was talking about.
I wanted to create a macro that runs on the open of the file using "Private
Sub Workbook_Open" but the problem is everytime I save the attachment, the
code for that file gets wiped out.
So I was trying to write a "Workbook_Open" under the
"Personal.xls"/"ThisWorbook". But the problem is when I open the file,
"personal.xls" project opens first then my files project opens. Now I think
It is trying to run this code before it open my files workbook, and dosen't
find it. I get an "Error 91" I click cancel, and then my file opens.
The code that I wrote works if I step through it in the VB Editor, but I get
the error on the open of the file.
Any thoughts?

 

[JLGWhiz]

Error 91 is when an Object variable is not Set. If your code had been posted
someone could probably spot the problem.

 

[matt]

I had some code to find a value in a cell delete the cell and move the row
up. But all I'm trying to do right now just to see when it runs is:

Sub Workbook_Open()

MsgBox ActiveWorkbook.name

End Sub()

It works if I run it in the editor, but if I save and close the file and
open it back up I get the error. It's wriiten under VBAProject (PERSONAL.XLS)
-->"this workbook".

 

[matt]

Here is the code that I eventually want to be able to run automatically on
the open of the file, through personal.xls... If this is even possible


Sub Workbook_Open()

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select

mycount = Selection.Count

For j = mycount To 1 Step -1
If Cells(j, 1).Value = "Sales" Then
Exit Sub
Else
Rows(j).Select
Selection.Delete Shift:=xlUp
End If
Next j

End Sub

 

[Dave Peterson]

Most people have the personal.xls workbook hidden. That means that there is no
activeworkbook.

But aren't you afraid of opening a file that should NOT have this macro run and
destroying some data--or even having the wrong worksheet active???

If I were you, I'd make this just a regular subroutine in a general module.
Then run it on demand.

====
If you don't like using alt-f8 to invoke your macros, you could do things to
make your life a bit easier...

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
or
http://www.rondebruin.nl/2007addin.htm

In xl2007, those toolbars and menu modifications will show up under the addins.

 

[matt]

Yea I did think of that, the deleting of data in other files. But i was
thinking if I could get it to run on open then I could have a conditonal
clause with something like:

If ActiveDocument.Name = "Report.xls" Then

CODE

End If
'Or something like that.

But what I dont understand is why it isnt working correctly when it is in
the personal.xls???Lets call my file "Report.xls". Is it because the
personal.xls opens FIRST tries to run the code, and Report.xls is not yet
running. So it does not recognize it and I get an error?

Like I was saying before I can run the code manually from the VBA editor
just fine, but just doesnt work when I open the doc, I get the runtime error
'91'.

As for putting it in a regular module in Report.xls. I get the file every
morning and save it over the one from the previous day, so everything gets
deleted.

Thanks for you reply Dave. I know I'm a little long winded but any other
thoughts???

 

[Dave Peterson]

You would use something like:

if activeworkbook.name = "report.xls" then
or
if lcase(activeworkbook.name) = lcase("report.xls") then

(activedocument is from MSWord???)

And that's exactly what I'm guessing. Personal.xls opens before the other
workbook. And since personal.xls is hidden, it ain't active--and neither is any
other workbook.

You could have your code wait a few seconds and then run another macro:

option explicit
sub Workbook_open()
'this will call a different routine in 3 seconds
application.ontime now + timeserial(0,0,3), "Continue_Open"
end sub

And this goes in a general module:

Sub Continue_Open()
if activeworkbook is nothing then
msgbox "Not enough of a delay!"
else
if lcase(activeworkbook.name) = lcase("report.xls") then
'do your stuff
end if
end if
end sub

But I find that just clicking a button is much simpler!

You may want to take a look at Chip Pearson's notes on .ontime:
http://www.cpearson.com/excel/OnTime.aspx

 

[matt]

Yea I'll give that a whirl. The whole thing with trying to use the
Workbook_Open is because I never really have to open the file because the
table I need from the file is linked in a Word document, so when the Word doc
opens it opens an instance of the file and would run any code in
Workbook_Open.
I tried to use a Wait command, but that just waited a couple of seconds and
didnt really change anything. It still didn't open the Report.xls until it
finished the Wait. I'm gona give your code a try though.
Thanks Dave!!!