Good day Folks,
I have some code that exports data to an Excel spreadsheet, which I got working fine, I needed to create the same process for some other data so I created a similar process for that data. The problem I am experiencing is that whenever I run the code to export both sets of data to Excel the second created process always errors out with the error 91, having read what access pops up in the help menu, it states the following:
Object variable or With block variable not set (Error 91)
There are two steps to creating an object variable. First you must declare the object variable. Then you must assign a valid reference to the object variable using the Set statement. Similarly, a With...End With block must be initialized by executing the With statement entry point. This error has the following causes and solutions:
however I do have the correct reference to Excel in my module which is "Microsoft Excel 11.0 Object Library, otherwise the first created code would not work. it always happens on the second created code, which has the exact same references. Here's the code and the one that is always highlighted is in red
Any suggestions as to why this keeps happening, because I can't been able to work out from the help information what the problem is.
Thanks in advance
John
I have some code that exports data to an Excel spreadsheet, which I got working fine, I needed to create the same process for some other data so I created a similar process for that data. The problem I am experiencing is that whenever I run the code to export both sets of data to Excel the second created process always errors out with the error 91, having read what access pops up in the help menu, it states the following:
Object variable or With block variable not set (Error 91)
There are two steps to creating an object variable. First you must declare the object variable. Then you must assign a valid reference to the object variable using the Set statement. Similarly, a With...End With block must be initialized by executing the With statement entry point. This error has the following causes and solutions:
- <LI class=LB1>You attempted to use an object variable that isn't yet referencing a valid object.
Specify or respecify a reference for the object variable. For example, if the Set statement is omitted in the following code, an error would be generated on the reference to MyObject: Dim MyObject As Object ' Create object variable.Set MyObject = Sheets(1) ' Create valid object reference.MyCount = MyObject.Count ' Assign Count value to MyCount.<LI class=LB1>You attempted to use an object variable that has been set to Nothing. Set MyObject = Nothing ' Release the object.MyCount = MyObject.Count ' Make a reference to a released object.Respecify a reference for the object variable. For example, use a new Set statement to set a new reference to the object.
<LI class=LB1>The object is a valid object, but it wasn't set because the object library in which it is described hasn't been selected in the References dialog box. Select the object library in the Add References dialog box.
<LI class=LB1>The target of a GoTo statement is inside a With block. Don't jump into a With block. Make sure the block is initialized by executing the With statement entry point. - You specified a line inside a With block when you chose the Set Next Statement command. The With block must be initialized by executing the With statement.
however I do have the correct reference to Excel in my module which is "Microsoft Excel 11.0 Object Library, otherwise the first created code would not work. it always happens on the second created code, which has the exact same references. Here's the code and the one that is always highlighted is in red
Code:
[FONT=Times New Roman][COLOR=blue]With[/COLOR] Objsheet [/FONT]
[FONT=Times New Roman] .Rows("1:1").Font.Bold = [COLOR=blue]True[/COLOR][/FONT]
[FONT=Times New Roman] .Rows("1:1").Font.Underline = xlUnderlineStyleSingle [/FONT]
[FONT=Times New Roman] .Rows("1:1").Select [/FONT]
[FONT=Times New Roman] [COLOR=red]Selection.Insert Shift:=xlDown[/COLOR][/FONT]
[FONT=Times New Roman] Range("A1").Select[/FONT]
[FONT=Times New Roman] ActiveCell.FormulaR1C1 = "Camera Form Code 03_Q"[/FONT]
[FONT=Times New Roman] Range("B1").Select [/FONT]
[FONT=Times New Roman] ActiveCell.FormulaR1C1 = "As At Date :"[/FONT]
[FONT=Times New Roman] Range("D1").Select [/FONT]
[FONT=Times New Roman] Selection.NumberFormat = "@"[/FONT]
[FONT=Times New Roman] [COLOR=blue]With [/COLOR][COLOR=black]Selection[/COLOR][/FONT]
[FONT=Times New Roman] .HorizontalAlignment = xlLeft [/FONT]
[FONT=Times New Roman] .VerticalAlignment = xlBottom [/FONT]
[FONT=Times New Roman] .ReadingOrder = xlContext[/FONT]
[FONT=Times New Roman] [COLOR=blue]End With[/COLOR][/FONT]
[COLOR=blue][FONT=Times New Roman]End With[/FONT][/COLOR]Any suggestions as to why this keeps happening, because I can't been able to work out from the help information what the problem is.
Thanks in advance
John
