Runtime Error 9

[Guest]

Hi
I have a workbook that I have set workbook names but for some reason I keep
getting a runtime error 9.

I have attached the code which I have, could someone let me know what I have
done or missed out.

Dim wbkCurrent, wbkNew As Workbook

Set wbkCurrent = Workbooks("Stock Adjustment Workbook.xls")

The error keeps occuring at the set line.

Thanks
Noemi

 

[Guest]

A couple of things to note. You have declared your object variables a little
bit wrong. It should be...

Dim wbkCurrent as workbook
dim wbkNew As Workbook

otherwise you are actually declaring wbkCurrent as type variant (check out
this web site http://www.cpearson.com/excel/variables.htm)

The other issue with your code is that if the workbook is not open then the
code will crash (this is most likely the problem causing your error). Give
this a try...

Dim wbkCurrent as workbook
dim wbkNew As Workbook

On Error Resume Next
Set wbkCurrent = Workbooks("Stock Adjustment Workbook.xls")
on Error goto 0

if wbkcurrent is nothing then _
Set wbkCurrent = Workbooks.Open("C:\Stock Adjustment Workbook.xls")

 

[Guest]

Thanks Jim

A couple of things to note. You have declared your object variables a little
bit wrong. It should be...

Dim wbkCurrent as workbook
dim wbkNew As Workbook

otherwise you are actually declaring wbkCurrent as type variant (check out
this web site http://www.cpearson.com/excel/variables.htm)

The other issue with your code is that if the workbook is not open then the
code will crash (this is most likely the problem causing your error). Give
this a try...

Dim wbkCurrent as workbook
dim wbkNew As Workbook

On Error Resume Next
Set wbkCurrent = Workbooks("Stock Adjustment Workbook.xls")
on Error goto 0

if wbkcurrent is nothing then _
Set wbkCurrent = Workbooks.Open("C:\Stock Adjustment Workbook.xls")