Error in Workbook_Open
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? |
Error in Workbook_Open
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? |
Error in Workbook_Open
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". "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? |
Error in Workbook_Open
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? |
Error in Workbook_Open
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 |
Error in Workbook_Open
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 |
Error in Workbook_Open
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 |
Error in Workbook_Open
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 |
All times are GMT +1. The time now is 03:40 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com