Error in Workbook_Open
in [Programming]
|
From: matt on 17 Jul 2008 15:51 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?
From: JLGWhiz on 17 Jul 2008 16:01 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?
From: matt on 17 Jul 2008 16:23 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?
From: matt on 17 Jul 2008 17:01 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?
From: Dave Peterson on 17 Jul 2008 18:50
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 |