Macro gives error 9 unless Administrator

[Guest]

I posted this in General, but I think it is more of a Programming issue.
As the subject says, I have a working macro (auto_open) that works fine when
i am testing. It simply opens a CSV file and copies cells to the different
sheets in the spreadsheet. However, if someone who is not 'Administrator'
runs it it stops on the first "sheets" command and give an error "subscript
out of range". I tested for a long time before I figured out the problem, but
I
have set Excel security levels at the lowest and changed the security on the
workbook and CSV files to allow "everyone" but it still won't work. What is
the problem? More important, what is the fix?
Tthe CSV file is opened correctly. In fact when the debug error
message is cancelled, I can see both files in Excel by changing windows. The
CSV file shows the data correctly and the first row is selected waiting to be
copied and pasted into the XLS. The error line shown in the debugger is
"Sheets (Broadspire.xls) .select" with error 9 'Subscript out of Range'.
Here is the beginning of the macro :

Sub auto_open()
'
' auto_open Macro
' Macro recorded 5/5/2006 by Tod Brannen
'
' Keyboard Shortcut: Ctrl+o
'
Workbooks.Open Filename:="h:\sme\data\Broadspire.csv" <<< Opens Fine.
Range("A3:H3").Select <<<Selects Fine.
Selection.Copy
Windows("Broadspire.xls").Activate
Sheets("Quarterly BCO Detail").Select <<<<<Error line
Range("B16").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


Thanks for looking,

Tod Brannen

 

[Tim Williams]

That error usually means you don't have a sheet named "Quarterly BCO Detail"
Probably not a security issue.

Try:
ActiveWorkbook.Sheets("Quarterly BCO Detail").Select

or:
Workbooks("Broadspire.xls").Sheets("Quarterly BCO Detail").Range("B16").PasteSpecial _
Paste:=xlValues, Operation:=xlNone, SkipBlanks:= False, Transpose:=False

Always better to be more explicit when selecting/activating and even better not to select/activate at all

Tim

 

[Guest]

Tim,

Thanks for the reply. I tried your suggestion, but it errored on the line
exactly the same as before. Works for the Administrator though. I of course,
do have the sheet named 'Quarterly BCO Deatail' because it works fine for me
on my pc.

Tod Brannen

 

[Guest]

Tim,
That actually turned out to be true - in a way.
But with some more testing, it looks like the error is caused by having the
..xls file and .csv file named the same. Seems to confuse Excel 2003. We
changed the name of the CSV file and now it seems to work. Just a wild guess
and we changed the Broadspire.Csv to test.csv and did not get the error.

Thanks,

Tod Brannen