Runtime error 424 object required on Set Statement
 

Good afternoon, Can someone please explain why I'm getting a runtime
errror on the following code:

' I declared the workbook and worksheet in the beginning of the
program:
Dim volume_WB as Workbook
Dim volume_sheet as Worksheet

' I open the workbook
Set volume_WB = Workbooks.Open("DASD.volumes.test.xls", UpdateLinks:=0)

' I call a routine to pass the workbook

Call dasdType(site, frowV2X, frowSHK, volume_WB, volume_sheet)

' site = a 2-character desgination. it will be either HQ or NE
' The program bombs on the first line:

Set volume_sheet = volume_WB.Sheets(site & "-V2X")

What am I doing wrong?

Runtime error 424 object required on Set Statement
 

Without seeing more code it is a little tough but here is something to try...

sub Whatever()
Dim volume_WB as Workbook
Dim volume_sheet as Worksheet

On error goto OpenError
Workbooks.Open("DASD.volumes.test.xls", UpdateLinks:=0)
on error goto 0
Set volume_WB = activeworkbook
if sheetexists(site & "-V2X", volume_WB) then
Set volume_sheet = volume_WB.Sheets(site & "-V2X")
Call dasdType(site, frowV2X, frowSHK, volume_WB, volume_sheet)
else
msgbox "Sorry no such sheet"
end if
exit sub
OpenError:
msgbox "Sorry Can't open the file"
end sub

Public Function SheetExists(SName As String, _
Optional ByVal WB As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If WB Is Nothing Then Set WB = ThisWorkbook
SheetExists = CBool(Len(WB.Sheets(SName).Name))
End Function

--
HTH...

Jim Thomlinson


"looloo" wrote:

Good afternoon, Can someone please explain why I'm getting a runtime
errror on the following code:

' I declared the workbook and worksheet in the beginning of the
program:
Dim volume_WB as Workbook
Dim volume_sheet as Worksheet

' I open the workbook
Set volume_WB = Workbooks.Open("DASD.volumes.test.xls", UpdateLinks:=0)

' I call a routine to pass the workbook

Call dasdType(site, frowV2X, frowSHK, volume_WB, volume_sheet)

' site = a 2-character desgination. it will be either HQ or NE
' The program bombs on the first line:

Set volume_sheet = volume_WB.Sheets(site & "-V2X")

What am I doing wrong?