Excel macro works only for administrator

[Guest]

Greetings,

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 an administrator
runs it it stops on the first "sheets" command and give an error "invalid
subscript". I tested for a long time before I figured out the problem, but I
have set the 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?

thanks for looking,

Tod Brannen

 

[Guest]

Maybe the problem is not in Excel, but in the location of the csv file. It
is possible that it can only be accessed with administrator rights, even from
outside Excel.
Check also the path in the script, it may be refering to the "My documents"
folder of the logged user, so it will change for each user, leading to wrong
paths.

Hope this helps,
Miguel.

 

[Guest]

Miguel,

Thanks, but the 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 (myexcelfile.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

 

[Guest]

It looks like it is messing with the name of the worksheet somehow. If the
structure of the file is not changing, a possible workaround is using the
index of the sheet instead of the name, something like:
Sheets(1).Select
You have to figure out what is the index number of the sheet, it will be
usually the position of the tab, but it may not. In any case, it is not a
very safe practice (backup the file!), but it may do the trick.

Miguel.

 

[Guest]

Ok, I will try anything. But that would not explain why it does work for the
admistrator login and no one else.

Tod Brannen

 

[Guest]

Ok, I tried [ Sheets(1).select ] but it is the same problem. Works for me,
but not for anyone else. Also I said 'Administrator' but my login is 'todb'
on my local pc and it works. I am not on the network this office. On their
network, it works for 'Adminstrator' and I don't have a personal login on
their network.

thanks for looking,

Tod Brannen

 

[Guest]

Other thing that may happen is that the administrator has the workbook
"Broadspire.xls" open by default and the others not, although in that case
the error should be on line 8 *scratchs head*
Other thing you may try is adding Activeworkbook before the order in line 9,
something like:
ActiveWorkbook.Sheets("Quarterly BCO Detail").Select
That should select the worksheet in the active book, that should be the
Broadspire one.

Hope this works,
Miguel.

 

[Guest]

Hi Miguel,

I tried <ActiveWorkbook.Sheets("Quarterly BCO Detail").Select> as you
suggested. I get the exact same error. I attempted to create the macro while
logging in as another user. The macro still gives the error on the same line.
If I comment out the 'Sheets' command, the macro runs fine. It now seems that
it just does not want to run the 'sheets' commmand. Let me state again, that
this works perfectly if I am running it on my local pc, or if I run it on
their network as 'Administrator'. I fail to see why one command would not
work and the rest of the macro does work without error.

Thanks for looking,

Tod Brannen

Other thing that may happen is that the administrator has the workbook
"Broadspire.xls" open by default and the others not, although in that case
the error should be on line 8 *scratchs head*
Other thing you may try is adding Activeworkbook before the order in line 9,
something like:
ActiveWorkbook.Sheets("Quarterly BCO Detail").Select
That should select the worksheet in the active book, that should be the
Broadspire one.

Hope this works,
Miguel.

Ok, I tried [ Sheets(1).select ] but it is the same problem. Works for me,
but not for anyone else. Also I said 'Administrator' but my login is 'todb'
on my local pc and it works. I am not on the network this office. On their
network, it works for 'Adminstrator' and I don't have a personal login on
their network.

thanks for looking,

Tod Brannen

 

[Guest]

I am also puzzled about why it works with one user and not with others. The
last option I may think is defining the range as a variable before opening
the csv, and trying to use that variable instead of the selection.
Here is the code, if there is an error in line 2 here then I really don't
know what to try further.

Sub auto_open()
Dim rngDest as Range
Set rngDest = Activeworkbook.Sheets("Quarterly BCO Detail").Range("B16")

Workbooks.Open Filename:="h:\sme\data\Broadspire.csv" <<< Opens Fine.
Range("A3:H3").Select <<<Selects Fine.
Selection.Copy
Windows("Broadspire.xls").Activate
rngDest.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Hi Miguel,

I tried <ActiveWorkbook.Sheets("Quarterly BCO Detail").Select> as you
suggested. I get the exact same error. I attempted to create the macro while
logging in as another user. The macro still gives the error on the same line.
If I comment out the 'Sheets' command, the macro runs fine. It now seems that
it just does not want to run the 'sheets' commmand. Let me state again, that
this works perfectly if I am running it on my local pc, or if I run it on
their network as 'Administrator'. I fail to see why one command would not
work and the rest of the macro does work without error.

Thanks for looking,

Tod Brannen

Other thing that may happen is that the administrator has the workbook
"Broadspire.xls" open by default and the others not, although in that case
the error should be on line 8 *scratchs head*
Other thing you may try is adding Activeworkbook before the order in line 9,
something like:
ActiveWorkbook.Sheets("Quarterly BCO Detail").Select
That should select the worksheet in the active book, that should be the
Broadspire one.

Hope this works,
Miguel.

Ok, I tried [ Sheets(1).select ] but it is the same problem. Works for me,
but not for anyone else. Also I said 'Administrator' but my login is 'todb'
on my local pc and it works. I am not on the network this office. On their
network, it works for 'Adminstrator' and I don't have a personal login on
their network.

thanks for looking,

Tod Brannen

 

[Guest]

Miguel,

I have had someone helping me test this here. It seems that what is
happening is that the command Windows("broadspire.xls").Activate is not
working. So that when the macro does the Sheets command, I get the subscript
out of range error.

But it only does this on certain users.?????


Thanks for looking,

Tod Brannen

I am also puzzled about why it works with one user and not with others. The
last option I may think is defining the range as a variable before opening
the csv, and trying to use that variable instead of the selection.
Here is the code, if there is an error in line 2 here then I really don't
know what to try further.

Sub auto_open()
Dim rngDest as Range
Set rngDest = Activeworkbook.Sheets("Quarterly BCO Detail").Range("B16")

Workbooks.Open Filename:="h:\sme\data\Broadspire.csv" <<< Opens Fine.
Range("A3:H3").Select <<<Selects Fine.
Selection.Copy
Windows("Broadspire.xls").Activate
rngDest.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Hi Miguel,

I tried <ActiveWorkbook.Sheets("Quarterly BCO Detail").Select> as you
suggested. I get the exact same error. I attempted to create the macro while
logging in as another user. The macro still gives the error on the same line.
If I comment out the 'Sheets' command, the macro runs fine. It now seems that
it just does not want to run the 'sheets' commmand. Let me state again, that
this works perfectly if I am running it on my local pc, or if I run it on
their network as 'Administrator'. I fail to see why one command would not
work and the rest of the macro does work without error.

Thanks for looking,

Tod Brannen

Other thing that may happen is that the administrator has the workbook
"Broadspire.xls" open by default and the others not, although in that case
the error should be on line 8 *scratchs head*
Other thing you may try is adding Activeworkbook before the order in line 9,
something like:
ActiveWorkbook.Sheets("Quarterly BCO Detail").Select
That should select the worksheet in the active book, that should be the
Broadspire one.

Hope this works,
Miguel.

:

Ok, I tried [ Sheets(1).select ] but it is the same problem. Works for me,
but not for anyone else. Also I said 'Administrator' but my login is 'todb'
on my local pc and it works. I am not on the network this office. On their
network, it works for 'Adminstrator' and I don't have a personal login on
their network.

thanks for looking,

Tod Brannen

 

[Guest]

If that is the error, you may try to substitute the line with:
Application.Workbooks("broadspire.xls").Activate

Miguel.

Miguel,

I have had someone helping me test this here. It seems that what is
happening is that the command Windows("broadspire.xls").Activate is not
working. So that when the macro does the Sheets command, I get the subscript
out of range error.

But it only does this on certain users.?????


Thanks for looking,

Tod Brannen

I am also puzzled about why it works with one user and not with others. The
last option I may think is defining the range as a variable before opening
the csv, and trying to use that variable instead of the selection.
Here is the code, if there is an error in line 2 here then I really don't
know what to try further.

Sub auto_open()
Dim rngDest as Range
Set rngDest = Activeworkbook.Sheets("Quarterly BCO Detail").Range("B16")

Workbooks.Open Filename:="h:\sme\data\Broadspire.csv" <<< Opens Fine.
Range("A3:H3").Select <<<Selects Fine.
Selection.Copy
Windows("Broadspire.xls").Activate
rngDest.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Hi Miguel,

I tried <ActiveWorkbook.Sheets("Quarterly BCO Detail").Select> as you
suggested. I get the exact same error. I attempted to create the macro while
logging in as another user. The macro still gives the error on the same line.
If I comment out the 'Sheets' command, the macro runs fine. It now seems that
it just does not want to run the 'sheets' commmand. Let me state again, that
this works perfectly if I am running it on my local pc, or if I run it on
their network as 'Administrator'. I fail to see why one command would not
work and the rest of the macro does work without error.

Thanks for looking,

Tod Brannen

:

Other thing that may happen is that the administrator has the workbook
"Broadspire.xls" open by default and the others not, although in that case
the error should be on line 8 *scratchs head*
Other thing you may try is adding Activeworkbook before the order in line 9,
something like:
ActiveWorkbook.Sheets("Quarterly BCO Detail").Select
That should select the worksheet in the active book, that should be the
Broadspire one.

Hope this works,
Miguel.

:

Ok, I tried [ Sheets(1).select ] but it is the same problem. Works for me,
but not for anyone else. Also I said 'Administrator' but my login is 'todb'
on my local pc and it works. I am not on the network this office. On their
network, it works for 'Adminstrator' and I don't have a personal login on
their network.

thanks for looking,

Tod Brannen

 

[Guest]

I already tried that. 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

If that is the error, you may try to substitute the line with:
Application.Workbooks("broadspire.xls").Activate

Miguel.

Miguel,

I have had someone helping me test this here. It seems that what is
happening is that the command Windows("broadspire.xls").Activate is not
working. So that when the macro does the Sheets command, I get the subscript
out of range error.

But it only does this on certain users.?????


Thanks for looking,

Tod Brannen

I am also puzzled about why it works with one user and not with others. The
last option I may think is defining the range as a variable before opening
the csv, and trying to use that variable instead of the selection.
Here is the code, if there is an error in line 2 here then I really don't
know what to try further.

Sub auto_open()
Dim rngDest as Range
Set rngDest = Activeworkbook.Sheets("Quarterly BCO Detail").Range("B16")

Workbooks.Open Filename:="h:\sme\data\Broadspire.csv" <<< Opens Fine.
Range("A3:H3").Select <<<Selects Fine.
Selection.Copy
Windows("Broadspire.xls").Activate
rngDest.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


:

Hi Miguel,

I tried <ActiveWorkbook.Sheets("Quarterly BCO Detail").Select> as you
suggested. I get the exact same error. I attempted to create the macro while
logging in as another user. The macro still gives the error on the same line.
If I comment out the 'Sheets' command, the macro runs fine. It now seems that
it just does not want to run the 'sheets' commmand. Let me state again, that
this works perfectly if I am running it on my local pc, or if I run it on
their network as 'Administrator'. I fail to see why one command would not
work and the rest of the macro does work without error.

Thanks for looking,

Tod Brannen

:

Other thing that may happen is that the administrator has the workbook
"Broadspire.xls" open by default and the others not, although in that case
the error should be on line 8 *scratchs head*
Other thing you may try is adding Activeworkbook before the order in line 9,
something like:
ActiveWorkbook.Sheets("Quarterly BCO Detail").Select
That should select the worksheet in the active book, that should be the
Broadspire one.

Hope this works,
Miguel.

:

Ok, I tried [ Sheets(1).select ] but it is the same problem. Works for me,
but not for anyone else. Also I said 'Administrator' but my login is 'todb'
on my local pc and it works. I am not on the network this office. On their
network, it works for 'Adminstrator' and I don't have a personal login on
their network.

thanks for looking,

Tod Brannen

 

[Guest]

Good that you finally found the reason! I have to admit that I haven't
tought of that.

Best,
Miguel.

I already tried that. 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

If that is the error, you may try to substitute the line with:
Application.Workbooks("broadspire.xls").Activate

Miguel.

Miguel,

I have had someone helping me test this here. It seems that what is
happening is that the command Windows("broadspire.xls").Activate is not
working. So that when the macro does the Sheets command, I get the subscript
out of range error.

But it only does this on certain users.?????


Thanks for looking,

Tod Brannen

:

I am also puzzled about why it works with one user and not with others. The
last option I may think is defining the range as a variable before opening
the csv, and trying to use that variable instead of the selection.
Here is the code, if there is an error in line 2 here then I really don't
know what to try further.

Sub auto_open()
Dim rngDest as Range
Set rngDest = Activeworkbook.Sheets("Quarterly BCO Detail").Range("B16")

Workbooks.Open Filename:="h:\sme\data\Broadspire.csv" <<< Opens Fine.
Range("A3:H3").Select <<<Selects Fine.
Selection.Copy
Windows("Broadspire.xls").Activate
rngDest.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


:

Hi Miguel,

I tried <ActiveWorkbook.Sheets("Quarterly BCO Detail").Select> as you
suggested. I get the exact same error. I attempted to create the macro while
logging in as another user. The macro still gives the error on the same line.
If I comment out the 'Sheets' command, the macro runs fine. It now seems that
it just does not want to run the 'sheets' commmand. Let me state again, that
this works perfectly if I am running it on my local pc, or if I run it on
their network as 'Administrator'. I fail to see why one command would not
work and the rest of the macro does work without error.

Thanks for looking,

Tod Brannen

:

Other thing that may happen is that the administrator has the workbook
"Broadspire.xls" open by default and the others not, although in that case
the error should be on line 8 *scratchs head*
Other thing you may try is adding Activeworkbook before the order in line 9,
something like:
ActiveWorkbook.Sheets("Quarterly BCO Detail").Select
That should select the worksheet in the active book, that should be the
Broadspire one.

Hope this works,
Miguel.

:

Ok, I tried [ Sheets(1).select ] but it is the same problem. Works for me,
but not for anyone else. Also I said 'Administrator' but my login is 'todb'
on my local pc and it works. I am not on the network this office. On their
network, it works for 'Adminstrator' and I don't have a personal login on
their network.

thanks for looking,

Tod Brannen