Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Miguel Zapico
 
Posts: n/a
Default Excel macro works only for administrator

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.

"Free Agent99" wrote:

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

  #2   Report Post  
Posted to microsoft.public.excel.misc
Free Agent99
 
Posts: n/a
Default Excel macro works only for administrator

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

Tod Brannen

"Miguel Zapico" wrote:

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.

"Free Agent99" wrote:

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

  #3   Report Post  
Posted to microsoft.public.excel.misc
Free Agent99
 
Posts: n/a
Default Excel macro works only for administrator

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

  #4   Report Post  
Posted to microsoft.public.excel.misc
Miguel Zapico
 
Posts: n/a
Default Excel macro works only for administrator

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.

"Free Agent99" wrote:

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

  #5   Report Post  
Posted to microsoft.public.excel.misc
Free Agent99
 
Posts: n/a
Default Excel macro works only for administrator

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

"Miguel Zapico" wrote:

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.

"Free Agent99" wrote:

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



  #6   Report Post  
Posted to microsoft.public.excel.misc
Miguel Zapico
 
Posts: n/a
Default Excel macro works only for administrator

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


"Free Agent99" wrote:

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

"Miguel Zapico" wrote:

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.

"Free Agent99" wrote:

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

  #7   Report Post  
Posted to microsoft.public.excel.misc
Free Agent99
 
Posts: n/a
Default Excel macro works only for administrator

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

"Miguel Zapico" wrote:

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


"Free Agent99" wrote:

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

"Miguel Zapico" wrote:

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.

"Free Agent99" wrote:

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

  #8   Report Post  
Posted to microsoft.public.excel.misc
Miguel Zapico
 
Posts: n/a
Default Excel macro works only for administrator

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

Miguel.

"Free Agent99" wrote:

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

"Miguel Zapico" wrote:

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


"Free Agent99" wrote:

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

"Miguel Zapico" wrote:

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.

"Free Agent99" wrote:

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
XLSSTART\ does not works when Excel is launched from other application Vliegenmepper Excel Discussion (Misc queries) 1 September 15th 05 12:15 PM
excel 4.0 macro removal tool Sachin Shah Excel Discussion (Misc queries) 0 August 25th 05 04:17 AM
excel 4.0 macro remover tool Sachin Shah Excel Discussion (Misc queries) 0 August 25th 05 04:14 AM
How do I convert works file to excel without works software? CatMB Excel Discussion (Misc queries) 1 June 21st 05 04:12 PM
Macro in Excel 2002 to save a workbook to a FTP location Lloyd Excel Discussion (Misc queries) 0 December 21st 04 02:49 PM


All times are GMT +1. The time now is 04:29 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"