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 quot;sheetsquot; command and give an error quot;invalid
subscriptquot;. 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 quot;everyonequot; but it still won't work. What is
the problem? More important, what is the fix?
thanks for looking,
Tod BrannenMaybe 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 quot;My documentsquot;
folder of the logged user, so it will change for each user, leading to wrong
paths.
Hope this helps,
Miguel.
quot;Free Agent99quot; wrote:
gt; Greetings,
gt;
gt; As the subject says, I have a working macro (auto_open) that works fine when
gt; i am testing. It simply opens a CSV file and copies cells to the different
gt; sheets in the spreadsheet. However, if someone who is not an administrator
gt; runs it it stops on the first quot;sheetsquot; command and give an error quot;invalid
gt; subscriptquot;. I tested for a long time before I figured out the problem, but I
gt; have set the security levels at the lowest and changed the security on the
gt; workbook and CSV files to allow quot;everyonequot; but it still won't work. What is
gt; the problem? More important, what is the fix?
gt;
gt; thanks for looking,
gt;
gt; Tod Brannen
gt;
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
quot;Sheets (myexcelfile.xls) .selectquot; 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:=quot;h:\sme\data\Broadspire.csvquot; lt;lt;lt; Opens Fine.
Range(quot;A3:H3quot;).Select lt;lt;lt;Selects Fine.
Selection.Copy
Windows(quot;Broadspire.xlsquot;).Activate
Sheets(quot;Quarterly BCO Detailquot;).Select lt;lt;lt;lt;lt;Error line
Range(quot;B16quot;).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=FalseThanks for looking,
Tod Brannen
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.
quot;Free Agent99quot; wrote:
gt; Miguel,
gt;
gt; Thanks, but the CSV file is opened correctly. In fact when the debug error
gt; message is cancelled, I can see both files in Excel by changing windows. The
gt; CSV file shows the data correctly and the first row is selected waiting to be
gt; copied and pasted into the XLS. The error line shown in the debugger is
gt; quot;Sheets (myexcelfile.xls) .selectquot; with error 9 'Subscript out of Range'.
gt; Here is the beginning of the macro :
gt;
gt; Sub auto_open()
gt; '
gt; ' auto_open Macro
gt; ' Macro recorded 5/5/2006 by Tod Brannen
gt; '
gt; ' Keyboard Shortcut: Ctrl o
gt; '
gt; Workbooks.Open Filename:=quot;h:\sme\data\Broadspire.csvquot; lt;lt;lt; Opens Fine.
gt; Range(quot;A3:H3quot;).Select lt;lt;lt;Selects Fine.
gt; Selection.Copy
gt; Windows(quot;Broadspire.xlsquot;).Activate
gt; Sheets(quot;Quarterly BCO Detailquot;).Select lt;lt;lt;lt;lt;Error line
gt; Range(quot;B16quot;).Select
gt; Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
gt; False, Transpose:=False
gt;
gt;
gt; Thanks for looking,
gt;
gt; Tod Brannen
Ok, I will try anything. But that would not explain why it does work for the
admistrator login and no one else.
Tod Brannen
quot;Miguel Zapicoquot; wrote:
gt; It looks like it is messing with the name of the worksheet somehow. If the
gt; structure of the file is not changing, a possible workaround is using the
gt; index of the sheet instead of the name, something like:
gt; Sheets(1).Select
gt; You have to figure out what is the index number of the sheet, it will be
gt; usually the position of the tab, but it may not. In any case, it is not a
gt; very safe practice (backup the file!), but it may do the trick.
gt;
gt; Miguel.
gt;
gt; quot;Free Agent99quot; wrote:
gt;
gt; gt; Miguel,
gt; gt;
gt; gt; Thanks, but the CSV file is opened correctly. In fact when the debug error
gt; gt; message is cancelled, I can see both files in Excel by changing windows. The
gt; gt; CSV file shows the data correctly and the first row is selected waiting to be
gt; gt; copied and pasted into the XLS. The error line shown in the debugger is
gt; gt; quot;Sheets (myexcelfile.xls) .selectquot; with error 9 'Subscript out of Range'.
gt; gt; Here is the beginning of the macro :
gt; gt;
gt; gt; Sub auto_open()
gt; gt; '
gt; gt; ' auto_open Macro
gt; gt; ' Macro recorded 5/5/2006 by Tod Brannen
gt; gt; '
gt; gt; ' Keyboard Shortcut: Ctrl o
gt; gt; '
gt; gt; Workbooks.Open Filename:=quot;h:\sme\data\Broadspire.csvquot; lt;lt;lt; Opens Fine.
gt; gt; Range(quot;A3:H3quot;).Select lt;lt;lt;Selects Fine.
gt; gt; Selection.Copy
gt; gt; Windows(quot;Broadspire.xlsquot;).Activate
gt; gt; Sheets(quot;Quarterly BCO Detailquot;).Select lt;lt;lt;lt;lt;Error line
gt; gt; Range(quot;B16quot;).Select
gt; gt; Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
gt; gt; False, Transpose:=False
gt; gt;
gt; gt;
gt; gt; Thanks for looking,
gt; gt;
gt; gt; Tod Brannen
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 BrannenOther thing that may happen is that the administrator has the workbook
quot;Broadspire.xlsquot; 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(quot;Quarterly BCO Detailquot;).Select
That should select the worksheet in the active book, that should be the
Broadspire one.
Hope this works,
Miguel.
quot;Free Agent99quot; wrote:
gt; Ok, I tried [ Sheets(1).select ] but it is the same problem. Works for me,
gt; but not for anyone else. Also I said 'Administrator' but my login is 'todb'
gt; on my local pc and it works. I am not on the network this office. On their
gt; network, it works for 'Adminstrator' and I don't have a personal login on
gt; their network.
gt;
gt; thanks for looking,
gt;
gt; Tod Brannen
gt;
Hi Miguel,
I tried lt;ActiveWorkbook.Sheets(quot;Quarterly BCO Detailquot;).Selectgt; 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
quot;Miguel Zapicoquot; wrote:
gt; Other thing that may happen is that the administrator has the workbook
gt; quot;Broadspire.xlsquot; open by default and the others not, although in that case
gt; the error should be on line 8 *scratchs head*
gt; Other thing you may try is adding Activeworkbook before the order in line 9,
gt; something like:
gt; ActiveWorkbook.Sheets(quot;Quarterly BCO Detailquot;).Select
gt; That should select the worksheet in the active book, that should be the
gt; Broadspire one.
gt;
gt; Hope this works,
gt; Miguel.
gt;
gt; quot;Free Agent99quot; wrote:
gt;
gt; gt; Ok, I tried [ Sheets(1).select ] but it is the same problem. Works for me,
gt; gt; but not for anyone else. Also I said 'Administrator' but my login is 'todb'
gt; gt; on my local pc and it works. I am not on the network this office. On their
gt; gt; network, it works for 'Adminstrator' and I don't have a personal login on
gt; gt; their network.
gt; gt;
gt; gt; thanks for looking,
gt; gt;
gt; gt; Tod Brannen
gt; gt;
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(quot;Quarterly BCO Detailquot;).Range(quot;B16quot;)
Workbooks.Open Filename:=quot;h:\sme\data\Broadspire.csvquot; lt;lt;lt; Opens Fine.
Range(quot;A3:H3quot;).Select lt;lt;lt;Selects Fine.
Selection.Copy
Windows(quot;Broadspire.xlsquot;).Activate
rngDest.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=Falsequot;Free Agent99quot; wrote:
gt; Hi Miguel,
gt;
gt; I tried lt;ActiveWorkbook.Sheets(quot;Quarterly BCO Detailquot;).Selectgt; as you
gt; suggested. I get the exact same error. I attempted to create the macro while
gt; logging in as another user. The macro still gives the error on the same line.
gt; If I comment out the 'Sheets' command, the macro runs fine. It now seems that
gt; it just does not want to run the 'sheets' commmand. Let me state again, that
gt; this works perfectly if I am running it on my local pc, or if I run it on
gt; their network as 'Administrator'. I fail to see why one command would not
gt; work and the rest of the macro does work without error.
gt;
gt; Thanks for looking,
gt;
gt; Tod Brannen
gt;
gt; quot;Miguel Zapicoquot; wrote:
gt;
gt; gt; Other thing that may happen is that the administrator has the workbook
gt; gt; quot;Broadspire.xlsquot; open by default and the others not, although in that case
gt; gt; the error should be on line 8 *scratchs head*
gt; gt; Other thing you may try is adding Activeworkbook before the order in line 9,
gt; gt; something like:
gt; gt; ActiveWorkbook.Sheets(quot;Quarterly BCO Detailquot;).Select
gt; gt; That should select the worksheet in the active book, that should be the
gt; gt; Broadspire one.
gt; gt;
gt; gt; Hope this works,
gt; gt; Miguel.
gt; gt;
gt; gt; quot;Free Agent99quot; wrote:
gt; gt;
gt; gt; gt; Ok, I tried [ Sheets(1).select ] but it is the same problem. Works for me,
gt; gt; gt; but not for anyone else. Also I said 'Administrator' but my login is 'todb'
gt; gt; gt; on my local pc and it works. I am not on the network this office. On their
gt; gt; gt; network, it works for 'Adminstrator' and I don't have a personal login on
gt; gt; gt; their network.
gt; gt; gt;
gt; gt; gt; thanks for looking,
gt; gt; gt;
gt; gt; gt; Tod Brannen
gt; gt; gt;
Miguel,
I have had someone helping me test this here. It seems that what is
happening is that the command Windows(quot;broadspire.xlsquot;).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
quot;Miguel Zapicoquot; wrote:
gt; I am also puzzled about why it works with one user and not with others. The
gt; last option I may think is defining the range as a variable before opening
gt; the csv, and trying to use that variable instead of the selection.
gt; Here is the code, if there is an error in line 2 here then I really don't
gt; know what to try further.
gt;
gt; Sub auto_open()
gt; Dim rngDest as Range
gt; Set rngDest = Activeworkbook.Sheets(quot;Quarterly BCO Detailquot;).Range(quot;B16quot;)
gt;
gt; Workbooks.Open Filename:=quot;h:\sme\data\Broadspire.csvquot; lt;lt;lt; Opens Fine.
gt; Range(quot;A3:H3quot;).Select lt;lt;lt;Selects Fine.
gt; Selection.Copy
gt; Windows(quot;Broadspire.xlsquot;).Activate
gt; rngDest.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
gt; False, Transpose:=False
gt;
gt;
gt; quot;Free Agent99quot; wrote:
gt;
gt; gt; Hi Miguel,
gt; gt;
gt; gt; I tried lt;ActiveWorkbook.Sheets(quot;Quarterly BCO Detailquot;).Selectgt; as you
gt; gt; suggested. I get the exact same error. I attempted to create the macro while
gt; gt; logging in as another user. The macro still gives the error on the same line.
gt; gt; If I comment out the 'Sheets' command, the macro runs fine. It now seems that
gt; gt; it just does not want to run the 'sheets' commmand. Let me state again, that
gt; gt; this works perfectly if I am running it on my local pc, or if I run it on
gt; gt; their network as 'Administrator'. I fail to see why one command would not
gt; gt; work and the rest of the macro does work without error.
gt; gt;
gt; gt; Thanks for looking,
gt; gt;
gt; gt; Tod Brannen
gt; gt;
gt; gt; quot;Miguel Zapicoquot; wrote:
gt; gt;
gt; gt; gt; Other thing that may happen is that the administrator has the workbook
gt; gt; gt; quot;Broadspire.xlsquot; open by default and the others not, although in that case
gt; gt; gt; the error should be on line 8 *scratchs head*
gt; gt; gt; Other thing you may try is adding Activeworkbook before the order in line 9,
gt; gt; gt; something like:
gt; gt; gt; ActiveWorkbook.Sheets(quot;Quarterly BCO Detailquot;).Select
gt; gt; gt; That should select the worksheet in the active book, that should be the
gt; gt; gt; Broadspire one.
gt; gt; gt;
gt; gt; gt; Hope this works,
gt; gt; gt; Miguel.
gt; gt; gt;
gt; gt; gt; quot;Free Agent99quot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Ok, I tried [ Sheets(1).select ] but it is the same problem. Works for me,
gt; gt; gt; gt; but not for anyone else. Also I said 'Administrator' but my login is 'todb'
gt; gt; gt; gt; on my local pc and it works. I am not on the network this office. On their
gt; gt; gt; gt; network, it works for 'Adminstrator' and I don't have a personal login on
gt; gt; gt; gt; their network.
gt; gt; gt; gt;
gt; gt; gt; gt; thanks for looking,
gt; gt; gt; gt;
gt; gt; gt; gt; Tod Brannen
gt; gt; gt; gt;
留言列表
