Runtime Error 9
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 |
Runtime Error 9
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") -- HTH... Jim Thomlinson "Noemi" wrote: 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 |
Runtime Error 9
Thanks Jim
"Jim Thomlinson" wrote: 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") -- HTH... Jim Thomlinson "Noemi" wrote: 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 |
All times are GMT +1. The time now is 12:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com