Err - Object Variable Or With Block Variable Not Set

[fredalina]

Sorry to need more help, but i'm running into snags. The bold line in the code below creates the subject error. Can you help?

Public Sub PastDue()

Dim Message
Dim objXL As Object
Dim strWkbkName As String
Dim objActiveWkb As Object

‘ This bit works fine
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Past Due – Name1", "C:\Sample"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Past Due – Name2", "C:\Sample"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Past Due – Name3", "C:\Sample"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Past Due – Name4", "C:\Sample"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Past Due – Name5", "C:\Sample"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Past Due – Name6", "C:\Sample"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Past Due – Name7", "C:\Sample"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Past Due – Name8", "C:\Sample"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Past Due – Name9", "C:\Sample"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Past Due – Name10", "C:\Sample"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Past Due – Name11", "C:\Sample"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Past Due – Name12", "C:\Sample"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Past Due – Name13", "C:\Sample"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Past Due – Name14", "C:\Sample"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Past Due – Name15", "C:\Sample"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Past Due – Name16", "C:\Sample"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Past Due – Name17", "C:\Sample"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Past Due – Name18", "C:\Sample"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Past Due – Name19", "C:\Sample"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Past Due – Name20", "C:\Sample"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Past Due – Name21", "C:\Sample"

' Bit where the error occurs
strWkbkName = CurrentDb().Name
MsgBox "strWkbkName is " & strWkbkName
strWkbkName = Left$(strWkbkName, Len(strWkbkName) - Len(Dir$(strWkbkName))) & _
"C:\Sample.XLS"
MsgBox "strWkbkName is " & strWkbkName

'If Len(Dir(strWkbkName)) = 0 Then
'MsgBox strWkbkName & " not found."
' Else
CreateObject ("Excel.Application")
objXL.Application.Workbooks.Open strWkbkName
With objXL.Application _
.Workbooks("C:\Sample.XLS") _
.Worksheets("Past Due – Name1")
.Rows("1:1").Font.Bold = True
.Range(.Columns(1), .Columns(1).End(-4161)) _
.Columns.Autofit
End With
'End If

objXL.Application.Workbooks( _
"C:\Sample.XLS").Close _
SaveChanges:=True
objXL.Application.Quit
Set objXL = Nothing
' End of sample code

Set Message = CreateObject("CDO.Message")
Message.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
Message.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "US-EX-MB-807.server"
Message.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
Message.Configuration.Fields.Update
With Message
.from = "anemail@here.com"
.To = "anotheremail@here.com"
.Subject = "Past Due"
.Addattachment "C:\Sample.XLS"
.Send
End With

End Sub

 

[DJkarl]

You aren't setting your variable to anything before you try to use it

CreateObject ("Excel.Application")
objXL.Application.Workbooks.Open strWkbkName

you need a line like

Set objXL = CreateObject ("Excel.Application")

before you try to utilize the application object

 

[Banana]

The error means exactly what it says:

You need to set an object to something:

Set objXL = CreateObject("Excel.Application")

Also, why not use more specific object instead of generic Object Type?

Dim oXls As New Excel.Workbook

Read up a little on "Early Binding" and see how it can help you.

 

[fredalina]

Banana, i'm sure that's a good idea, but the answer is because i'm blindly copying code from the internet in hopes it works.

My new error is Runtime error 9 - subscript out of range on the same line as before.

 

[boblarson]

As mentioned, you need to use

Set objXL = CreateObject("Excel.Application")

But you also are setting the file name weird. Just use:

strWkbkName = CurrentProject.Path & "\Sample.XLS"

If you want it in the same folder. Otherwise your path should be:

strWkbkName = "C:\Sample.XLS"

because

strWkbkName = Left$(strWkbkName, Len(strWkbkName) - Len(Dir$(strWkbkName))) & _
"C:\Sample.XLS"

is going to end up with something like:

C:\MyApplication.mdb\C:\Sample.XLS

 

[boblarson]

Read up a little on "Early Binding" and see how it can help you.

Early binding is good for development, but I would use late binding for production as it can save you a ton of reference error problems (which I've had enough of and try to always use the late binding method now to avoid them).

 

[Banana]

Interesting.

Is that even true for binding to Microsoft's library? I'd totally understand how it could be problem if we were using custom written libraries, but am sure that referencing any standard Office library shouldn't be problem, provided that computers where the application will be distributed has the same installation, no?

 

[boblarson]

Interesting.

Is that even true for binding to Microsoft's library? I'd totally understand how it could be problem if we were using custom written libraries, but am sure that referencing any standard Office library shouldn't be problem, provided that computers where the application will be distributed has the same installation, no?

Reference problems can occur with versions. For example, if I have Excel 2007 and use early binding and then someone with Excel 97, 2000, 2002, or 2003 tries to open it, it will suffer a reference error. Using late-binding solves that problem.

 

[Banana]

So it's not a matter of locating the library but knowing which the version of library we want to reference then? Does this still hold even if another had Excel 2007 but preferred to use 2003?

I haven't had to think about that as my company uses a volume license for Office and therefore are uniform all across and it's small enough that there's not any computer that is running older versions, but this may be the exception rather than the norm, I suspect.

 

[boblarson]

If you open an item in a version higher than the current one - no problem. But if you open a later version in a previous version then an error will result. So, as using late binding will generate no errors either way, I use that for production. However, for ease of use and for coding using intellisense, I will use early binding while developing. However, that is starting to be the exception for me as well as I am getting to know the coding syntax fairly well and don't need the intellisense as often now for the Excel object model.

 

[Banana]

Thanks for that information; that will definitely be handy when need arises.

Wish I could say the same for Word's object model.

 

[fredalina]

Can someone help me with the "Subscript Out of Range" error at the line:
With objXL.Application _
.Workbooks("C:\Sample.XLS") _
.Worksheets("Past Due - Name1")

All those lines are highlighted with the arrow at the .Worksheets line.

Thanks!

 

[Banana]

In this case, error is that you're trying to access a member of collection that doesn't exist. Workbooks is a collection, and you wanted a member "C:\Sample.XLS", which may resolve to a index (e.g. Workbooks(1)), but in this case, there was none found and thus index was out of range.

Normally, this is because you didn't open a workbook, or add it to the collection, or something like that. But you did first time I looked at your code, which confused me because I'd think it'd work. But I think BobLarson gave you an explanation:

As mentioned, you need to use

Set objXL = CreateObject("Excel.Application")

But you also are setting the file name weird. Just use:

strWkbkName = CurrentProject.Path & "\Sample.XLS"

If you want it in the same folder. Otherwise your path should be:

strWkbkName = "C:\Sample.XLS"

because

strWkbkName = Left$(strWkbkName, Len(strWkbkName) - Len(Dir$(strWkbkName))) & _
"C:\Sample.XLS"

is going to end up with something like:

C:\MyApplication.mdb\C:\Sample.XLS

To know how to debug the variables so you're sure it's what you expected, click the link in my siggy which has some explanations on how to debug step by step. HTH.

 

[boblarson]

Can someone help me with the "Subscript Out of Range" error at the line:
With objXL.Application _
.Workbooks("C:\Sample.XLS") _
.Worksheets("Past Due - Name1")

All those lines are highlighted with the arrow at the .Worksheets line.

Thanks!

It means that it can't find a worksheet by that name. Perhaps you don't have the name exactly correct. Try copying it directly from the worksheet itself and paste it into the code.

 

[fredalina]

I created a dummy Excel spreadsheet called Y:\Test.xls with a worksheet called It. I assigned each of them to a variable strWkBk and strWkSt. The code still creates the same error and follows:

strWkbkName = "Y:\Test.xls"
strWkSt = "It"

Set objXL = CreateObject("Excel.Application")
objXL.Application.Workbooks.Open (strWkbkName)
With objXL.Application _
.Workbooks(strWkbkName) _
.Worksheets(strWkSt)
.Rows("1:1").Font.Bold = True
.Columns.Autofit
End With

What am I doing wrong?

Thanks!

 

[boblarson]

Get rid of the .Application part

objXL.Workbooks.Open(strWkbkName)


With objXL
.Workbooks...etc.

 

[fredalina]

It's a slight improvement. The highlighted error has moved down from the With objXL line and the next 2 lines to

.Workbooks(strWkbkName) _
.Worksheets(strWkSt) _
.Rows("1:1").Font.Bold = True

but I'm still getting a "Subscript Out of Range" error.

Thanks!

 

[boblarson]

Maybe try this instead:

.Range("1:1").Font.Bold = True

 

[KenHigg]

Row(1).Font.Bold = True

??

 

[fredalina]

i've tried at least a dozen permutations and computations and nothing seems to work. i was happy for a moment when it seemed to work, but it was just my computer freezing up.