PDA

View Full Version : Solved: Open File on shared network drive and copy information to sheet



slang
04-01-2008, 06:53 AM
I have a small module that opens a file on a shared network drive and copys information back to the sheet. This works with some users but some get a runtime error#9, subscript out of range when the "Windows("TADS 1.2.xls").Activate line is ran in the code below.
I know I am missing something but I am new to this.Thanks in advance on any help you could provide.

I am using Excel 2003 sp2 and the workbook with the code is TADS 1.2.xls
Thanks....


Workbooks.Open filename:= _
"I:\BDMSFA\BDMdatafiles\tombstonecrm.xls"
Cells.Select
Selection.AutoFilter
Selection.AutoFilter Field:=16, Criteria1:=terrnum
rows("2:4000").Select
Selection.copy
Windows("TADS 1.2.xls").Activate
Range("A2").Select

Jan Karel Pieterse
04-01-2008, 07:00 AM
Are you sure that file is open for those users?

slang
04-01-2008, 07:25 AM
Yes because that is where the code exists and is called from. only certain users get it and some it goes away by itself. ???
Am I refering to the object correctly or should I be using the workbook object instead??
It has been 10 years since I have coded anything and I am very rusty. Thank god for macro recorder. LOL
Thanks....

Jan Karel Pieterse
04-01-2008, 07:58 AM
I suspect this is caused by a windows setting: hide extension of known filetypes. ANyway, you can prevent it by replacing your code with:


Workbooks.Open filename:= _
"I:\BDMSFA\BDMdatafiles\tombstonecrm.xls"
Cells.AutoFilter Field:=16, Criteria1:=terrnum
rows("2:4000").copy
ThisWorkbook.Activate
Range("A2").Select

slang
04-04-2008, 06:47 AM
Thanks Jan, it works fine now. :friends: