Error Number 91

[CujoFreak]

Hello,

I am trying to create a VB code that will help automate sales Orders, however I am receiving Error #91 when I am trying to compile the code. I was wondering if someone could provide me with assistance. Here is the portion of the code that is erroring out at the line 'Set rngStart = wks.Range("A4")':

Set appExcel = GetObject(, "Excel.Application")

'Set reference to worksheet and activate it
Set bks = appExcel.Workbooks

'Create and open new, blank workbook
bks.Add

'Make the worksheet visible
appExcel.Application.Visible = True

'Set reference to query
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("qryOrders", dbOpenDynaset)
rst.MoveLast
rst.MoveFirst
lngCount = rst.RecordCount
If lngCount = 0 Then
MsgBox "No contacts to export"
GoTo ErrorHandlerExit
Else
strPrompt = "Exporting " & lngCount & " contacts to Excel"
strTitle = "Exporting"
MsgBox strPrompt, vbInformation + vbOKOnly, strTitle
End If

'Go to first data cell
Set wks = wkb.Sheets(1)
wks.Activate
Set rngStart = wks.Range("A4")
rngStart.Activate

Thank you,
CujoFreak

 

[fumei]

Run-time error 91: Object variable or With block variable not set.

1. Are you using Option explicit? If not (and I suspect you are not), then start doing so.

2. Set wks = wkb.Sheets(1)

This I think is the real problem.

Set rngStart = wks.Range("A4") is making an object reference to wkb...but wkb is never declared or Set. At least not in your code.

The error message actually says it all - there is an object not Set.

3. Please use the TGML code tags when posting code. It makes thing easier to read. Thanks.

faq219-2884

Gerry
My paintings and sculpture

 

[CujoFreak]

Thank you for the quick reply. Answers follow:

1. Yes Option Explicit is on.
2. I initially tried running it without that line of code resulting in the same error. The wkb is delcared and set as follows in this post.
3. I have never posted on codes on a forum before, and I don't quite understand how I would apply it to make it easier to read but I have made an attempt on this post.

I have posted the full code verifying the use of Option explicit, the declaration of the variable, and the assignment statement for the path of the excel template.

I am not quite sure where it is erroring out. I was suspecting that is was erroring out on that line because when I set a break point it ran through the code fine up until that point.

Do you have any other recommendations as to why I am receiving the error? I have never created an export to excel, and I am only familiar with word automation.

Option Compare Database
[highlight]Option Explicit[/highlight]

Private Sub btnExport_Click()
On Error GoTo ErrorHandler

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strWorksheetPath As String
Dim appExcel As Excel.Application
[highlight]Dim strPath As String[/highlight]
Dim bks As Excel.Workbooks
Dim rng As Excel.Range
Dim rngStart As Excel.Range
Dim strFileName As String
[highlight]Dim wkb As Excel.Workbook[/highlight]
[highlight]Dim wks As Excel.Worksheet[/highlight]
Dim lngCount As Long
Dim strPrompt As String
Dim strTitle As String
Dim strTemplateFileAndPath As String
Dim prps As Object
Dim strSaveName As String
Dim strTestFile As String
Dim strDefault As String

'Set Path to save file
[highlight]strPath = "C:\Test\BlankOrders.xltx"[/highlight]

Set appExcel = GetObject(, "Excel.Application")

'Set reference to worksheet and activate it
Set bks = appExcel.Workbooks
[highlight]Set wkb = bks.Add(strPath)[/highlight]

'Create and open new, blank workbook
'bks.Add (strPath)

'Make the worksheet visible
appExcel.Application.Visible = True

'Set reference to query
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("qryOrders", dbOpenDynaset)
rst.MoveLast
rst.MoveFirst
lngCount = rst.RecordCount
If lngCount = 0 Then
MsgBox "No contacts to export"
GoTo ErrorHandlerExit
Else
strPrompt = "Exporting " & lngCount & " contacts to Excel"
strTitle = "Exporting"
MsgBox strPrompt, vbInformation + vbOKOnly, strTitle
End If

'Go to first data cell
[highlight]Set rngStart = wks.Range("A4")[/highlight]
rngStart.Activate

Thank you,
CujoFreak

 

[fumei]

Click "Process TGML" beyond the message area to see how to use code tags.

What application are you running this from?

"2. I initially tried running it without that line of code resulting in the same error. The wkb is delcared and set as follows in this post."

No...you are not. Yes, wkb is Set:

  Set wkb = bks.Add(strPath)

but you do NOT set wks. You use:

   Set rngStart = [highlight]wks[/highlight].Range("A4")

but have NOT set wks. Therefore...

Object variable or With block variable not set.

faq219-2884

Gerry
My paintings and sculpture