Error in Workbook_Open
in [Programming]
|
From: matt on 17 Jul 2008 22:44 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" wrote: > 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 wrote: > > > > 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 > > > > "JLGWhiz" wrote: > > > > > Error 91 is when an Object variable is not Set. If your code had been posted > > > someone could probably spot the problem. > > > > > > "matt" wrote: > > > > > > > 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? > > -- > > Dave Peterson >
From: Dave Peterson on 17 Jul 2008 23:57 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 wrote: > > 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" wrote: > > > 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 wrote: > > > > > > 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 > > > > > > "JLGWhiz" wrote: > > > > > > > Error 91 is when an Object variable is not Set. If your code had been posted > > > > someone could probably spot the problem. > > > > > > > > "matt" wrote: > > > > > > > > > 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? > > > > -- > > > > Dave Peterson > > -- Dave Peterson
From: matt on 18 Jul 2008 09:16
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!!! "Dave Peterson" wrote: > 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 wrote: > > > > 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" wrote: > > > > > 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 wrote: > > > > > > > > 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 > > > > > > > > "JLGWhiz" wrote: > > > > > > > > > Error 91 is when an Object variable is not Set. If your code had been posted > > > > > someone could probably spot the problem. > > > > > > > > > > "matt" wrote: > > > > > > > > > > > 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? > > > > > > -- > > > > > > Dave Peterson > > > > > -- > > Dave Peterson > |