Run-Time Error 1004 on Excel Startup

Hello, all.

When I launch Excel 2000 with an Auto_Open macro in my PERSONAL.XLS
workbook, I receive a "Run-time error '1004': Method 'Range' of object
'_Global' failed."

It's particularly confusing because when I debug, I do not see any
problems with the code (it breaks on a line with the code:
"Range("AE1").Select").

If I am to run the macro manually, it executes properly. However,
this Auto_Open procedure is giving me (and apparently Excel) fits.

Any suggestions would be wonderful.

Thank you,

J. Henke

P.S. Below is the macro's code:

Sub Auto_Open()

'Select the first "signature" cell
Range("AE1").Select

'Verify whether or not this is a spreadsheet to convert
If ActiveCell.Value = "Commission %" Then

'Select column A
Columns("A:A").Select

'Insert a new column, and shift the other columns to the right
Selection.Insert Shift:=xlToRight

'Select the first row in the spreadsheet
Rows("1:1").Select

'Delete the header row and shift up
Selection.Delete Shift:=xlUp

'Select cell A1
Range("A1").Select

'Declare a variable to store the number of rows in the active
worksheet
Dim numRowsInSheet As Integer

'Count the number of rows in the active worksheet and store
them in a variable
numRowsInSheet = ActiveSheet.UsedRange.Rows.Count

'Declare a new string
Dim myCellVal As String

'Store the new range (with the variable number of rows) in the
string
myCellVal = "A1:A" & numRowsInSheet

'Insert 1 in cell A1 for the first RecordID
ActiveCell.FormulaR1C1 = "1"
Range("A1").Select

'Use Excel's AutoFill function to fill the whole range of
cells with 1's for the RecordID
Selection.AutoFill Destination:=Range(myCellVal),
Type:=xlFillDefault
'Range("A1:A6").Select

'Initialize a general property in columns 1 through 42 to
ensure that 42 columns get
'some sort of tab delimiter
Range("A1:AP1").Select
Selection.Font.Italic = True
Selection.Font.Italic = False

'Change to the directory in which to save the project
ChDir "C:\My Documents"

ActiveWorkbook.SaveAs Filename:="C:\My
Documents\myFlatFile.txt" _
, FileFormat:=xlText, CreateBackup:=False
Else
MsgBox "Sorry chum, this isn't an acceptable spreadsheet!",
vbInformation, "Sorry!"
End If
'
End Sub

The code in your Auto_Open procedure assumes that there will be an
ActiveWorkbook to operate on. Personal.xls is a hidden workbook which opens
prior to any other workbooks being created. Therefore, when the Personal.xls
Auto_Open procedure runs, there are no other workbooks open and any attempt
to reference range objects will fail.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"J. Henke" <kelevance@yahoo.com> wrote in message
news:828d4265.0307070553.4956a61c@posting.google.com...
> Hello, all.
>
> When I launch Excel 2000 with an Auto_Open macro in my PERSONAL.XLS
> workbook, I receive a "Run-time error '1004': Method 'Range' of object
> '_Global' failed."
>
> It's particularly confusing because when I debug, I do not see any
> problems with the code (it breaks on a line with the code:
> "Range("AE1").Select").
>
> If I am to run the macro manually, it executes properly. However,
> this Auto_Open procedure is giving me (and apparently Excel) fits.
>
> Any suggestions would be wonderful.
>
> Thank you,
>
> J. Henke
>
> P.S. Below is the macro's code:
>
> Sub Auto_Open()
>
> 'Select the first "signature" cell
> Range("AE1").Select
>
> 'Verify whether or not this is a spreadsheet to convert
> If ActiveCell.Value = "Commission %" Then
>
> 'Select column A
> Columns("A:A").Select
>
> 'Insert a new column, and shift the other columns to the right
> Selection.Insert Shift:=xlToRight
>
> 'Select the first row in the spreadsheet
> Rows("1:1").Select
>
> 'Delete the header row and shift up
> Selection.Delete Shift:=xlUp
>
> 'Select cell A1
> Range("A1").Select
>
> 'Declare a variable to store the number of rows in the active
> worksheet
> Dim numRowsInSheet As Integer
>
> 'Count the number of rows in the active worksheet and store
> them in a variable
> numRowsInSheet = ActiveSheet.UsedRange.Rows.Count
>
> 'Declare a new string
> Dim myCellVal As String
>
> 'Store the new range (with the variable number of rows) in the
> string
> myCellVal = "A1:A" & numRowsInSheet
>
> 'Insert 1 in cell A1 for the first RecordID
> ActiveCell.FormulaR1C1 = "1"
> Range("A1").Select
>
> 'Use Excel's AutoFill function to fill the whole range of
> cells with 1's for the RecordID
> Selection.AutoFill Destination:=Range(myCellVal),
> Type:=xlFillDefault
> 'Range("A1:A6").Select
>
> 'Initialize a general property in columns 1 through 42 to
> ensure that 42 columns get
> 'some sort of tab delimiter
> Range("A1:AP1").Select
> Selection.Font.Italic = True
> Selection.Font.Italic = False
>
> 'Change to the directory in which to save the project
> ChDir "C:\My Documents"
>
> ActiveWorkbook.SaveAs Filename:="C:\My
> Documents\myFlatFile.txt" _
> , FileFormat:=xlText, CreateBackup:=False
> Else
> MsgBox "Sorry chum, this isn't an acceptable spreadsheet!",
> vbInformation, "Sorry!"
> End If
> '
> End Sub

Interesting...

That is what I thought maybe was happening, but do you
know if there is anyway to activate the workbook I am
trying to open (e.g. by double-clicking on a .xls file)
before running the code?

Or is what I am attempting to do simply not plausible with
Excel?

Thanks!

J. Henke

>-----Original Message-----
>
> The code in your Auto_Open procedure assumes that
there will be an
>ActiveWorkbook to operate on. Personal.xls is a hidden
workbook which opens
>prior to any other workbooks being created. Therefore,
when the Personal.xls
>Auto_Open procedure runs, there are no other workbooks
open and any attempt
>to reference range objects will fail.
>
>--
>Rob Bovey, MCSE, MCSD, Excel MVP
>Application Professionals
>http://www.appspro.com/
>
>* Please post all replies to this newsgroup *
>* I delete all unsolicited e-mail responses *
>
>
>"J. Henke" <kelevance@yahoo.com> wrote in message
>news:828d4265.0307070553.4956a61c@posting.google.com...
>> Hello, all.
>>
>> When I launch Excel 2000 with an Auto_Open macro in my
PERSONAL.XLS
>> workbook, I receive a "Run-time error '1004':
Method 'Range' of object
>> '_Global' failed."
>>
>> It's particularly confusing because when I debug, I do
not see any
>> problems with the code (it breaks on a line with the
code:
>> "Range("AE1").Select").
>>
>> If I am to run the macro manually, it executes
properly. However,
>> this Auto_Open procedure is giving me (and apparently
Excel) fits.
>>
>> Any suggestions would be wonderful.
>>
>> Thank you,
>>
>> J. Henke
>>
>> P.S. Below is the macro's code:
>>
>> Sub Auto_Open()
>>
>> 'Select the first "signature" cell
>> Range("AE1").Select
>>
>> 'Verify whether or not this is a spreadsheet to
convert
>> If ActiveCell.Value = "Commission %" Then
>>
>> 'Select column A
>> Columns("A:A").Select
>>
>> 'Insert a new column, and shift the other
columns to the right
>> Selection.Insert Shift:=xlToRight
>>
>> 'Select the first row in the spreadsheet
>> Rows("1:1").Select
>>
>> 'Delete the header row and shift up
>> Selection.Delete Shift:=xlUp
>>
>> 'Select cell A1
>> Range("A1").Select
>>
>> 'Declare a variable to store the number of rows
in the active
>> worksheet
>> Dim numRowsInSheet As Integer
>>
>> 'Count the number of rows in the active
worksheet and store
>> them in a variable
>> numRowsInSheet =
ActiveSheet.UsedRange.Rows.Count
>>
>> 'Declare a new string
>> Dim myCellVal As String
>>
>> 'Store the new range (with the variable number
of rows) in the
>> string
>> myCellVal = "A1:A" & numRowsInSheet
>>
>> 'Insert 1 in cell A1 for the first RecordID
>> ActiveCell.FormulaR1C1 = "1"
>> Range("A1").Select
>>
>> 'Use Excel's AutoFill function to fill the
whole range of
>> cells with 1's for the RecordID
>> Selection.AutoFill Destination:=Range
(myCellVal),
>> Type:=xlFillDefault
>> 'Range("A1:A6").Select
>>
>> 'Initialize a general property in columns 1
through 42 to
>> ensure that 42 columns get
>> 'some sort of tab delimiter
>> Range("A1:AP1").Select
>> Selection.Font.Italic = True
>> Selection.Font.Italic = False
>>
>> 'Change to the directory in which to save the
project
>> ChDir "C:\My Documents"
>>
>> ActiveWorkbook.SaveAs Filename:="C:\My
>> Documents\myFlatFile.txt" _
>> , FileFormat:=xlText, CreateBackup:=False
>> Else
>> MsgBox "Sorry chum, this isn't an acceptable
spreadsheet!",
>> vbInformation, "Sorry!"
>> End If
>> '
>> End Sub
>
>
>.
>

What you're doing is not possible to do in the Auto_Open procedure of
Personal.xls. If you're trying to watch for workbooks the user opens in
Excel and take action when they do, this is possible using a more advanced
technique that involves setting up a WithEvents class module in
Personal.xls. This class will be instantiated when Personal.xls loads and
will notify you any time a workbook is opened via an Application-level
WorkbookOpen event. Here's a basic example.

----------
In Class1
----------
Private WithEvents mxlApp As Excel.Application

Private Sub Class_Initialize()
Set mxlApp = Excel.Application
End Sub

Private Sub Class_Terminate()
Set mxlApp = Nothing
End Sub

Private Sub mxlApp_WorkbookOpen(ByVal Wb As Excel.Workbook)
''' Ignore this workbook and any add-ins.
If Wb.Name <> ThisWorkbook.Name And Not Wb.IsAddin Then
''' Put the code that used to be in your
''' Auto_Open procedure here.
MsgBox Wb.Name & " was just opened."
End If
End Sub

------------
In Module1
------------
Public gclsEventHandler As Class1

Sub Auto_Open()
Set gclsEventHandler = New Class1
End Sub

Both of these modules go in Personal.xls. Once Personal.xls has opened
and run its Auto_Open procedure, the mxlApp_WorkbookOpen event procedure in
Class1 will fire any time the user opens a workbook, including if they have
started Excel by double-clicking on a workbook from Explorer.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"J. Henke" <kelevance@yahoo.com> wrote in message
news:798901c344ad$e1b0d7c0$a401280a@phx.gbl...
> Interesting...
>
> That is what I thought maybe was happening, but do you
> know if there is anyway to activate the workbook I am
> trying to open (e.g. by double-clicking on a .xls file)
> before running the code?
>
> Or is what I am attempting to do simply not plausible with
> Excel?
>
> Thanks!
>
> J. Henke
>
> >-----Original Message-----
> >
> > The code in your Auto_Open procedure assumes that
> there will be an
> >ActiveWorkbook to operate on. Personal.xls is a hidden
> workbook which opens
> >prior to any other workbooks being created. Therefore,
> when the Personal.xls
> >Auto_Open procedure runs, there are no other workbooks
> open and any attempt
> >to reference range objects will fail.
> >
> >--
> >Rob Bovey, MCSE, MCSD, Excel MVP
> >Application Professionals
> >http://www.appspro.com/
> >
> >* Please post all replies to this newsgroup *
> >* I delete all unsolicited e-mail responses *
> >
> >
> >"J. Henke" <kelevance@yahoo.com> wrote in message
> >news:828d4265.0307070553.4956a61c@posting.google.com...
> >> Hello, all.
> >>
> >> When I launch Excel 2000 with an Auto_Open macro in my
> PERSONAL.XLS
> >> workbook, I receive a "Run-time error '1004':
> Method 'Range' of object
> >> '_Global' failed."
> >>
> >> It's particularly confusing because when I debug, I do
> not see any
> >> problems with the code (it breaks on a line with the
> code:
> >> "Range("AE1").Select").
> >>
> >> If I am to run the macro manually, it executes
> properly. However,
> >> this Auto_Open procedure is giving me (and apparently
> Excel) fits.
> >>
> >> Any suggestions would be wonderful.
> >>
> >> Thank you,
> >>
> >> J. Henke
> >>
> >> P.S. Below is the macro's code:
> >>
> >> Sub Auto_Open()
> >>
> >> 'Select the first "signature" cell
> >> Range("AE1").Select
> >>
> >> 'Verify whether or not this is a spreadsheet to
> convert
> >> If ActiveCell.Value = "Commission %" Then
> >>
> >> 'Select column A
> >> Columns("A:A").Select
> >>
> >> 'Insert a new column, and shift the other
> columns to the right
> >> Selection.Insert Shift:=xlToRight
> >>
> >> 'Select the first row in the spreadsheet
> >> Rows("1:1").Select
> >>
> >> 'Delete the header row and shift up
> >> Selection.Delete Shift:=xlUp
> >>
> >> 'Select cell A1
> >> Range("A1").Select
> >>
> >> 'Declare a variable to store the number of rows
> in the active
> >> worksheet
> >> Dim numRowsInSheet As Integer
> >>
> >> 'Count the number of rows in the active
> worksheet and store
> >> them in a variable
> >> numRowsInSheet =
> ActiveSheet.UsedRange.Rows.Count
> >>
> >> 'Declare a new string
> >> Dim myCellVal As String
> >>
> >> 'Store the new range (with the variable number
> of rows) in the
> >> string
> >> myCellVal = "A1:A" & numRowsInSheet
> >>
> >> 'Insert 1 in cell A1 for the first RecordID
> >> ActiveCell.FormulaR1C1 = "1"
> >> Range("A1").Select
> >>
> >> 'Use Excel's AutoFill function to fill the
> whole range of
> >> cells with 1's for the RecordID
> >> Selection.AutoFill Destination:=Range
> (myCellVal),
> >> Type:=xlFillDefault
> >> 'Range("A1:A6").Select
> >>
> >> 'Initialize a general property in columns 1
> through 42 to
> >> ensure that 42 columns get
> >> 'some sort of tab delimiter
> >> Range("A1:AP1").Select
> >> Selection.Font.Italic = True
> >> Selection.Font.Italic = False
> >>
> >> 'Change to the directory in which to save the
> project
> >> ChDir "C:\My Documents"
> >>
> >> ActiveWorkbook.SaveAs Filename:="C:\My
> >> Documents\myFlatFile.txt" _
> >> , FileFormat:=xlText, CreateBackup:=False
> >> Else
> >> MsgBox "Sorry chum, this isn't an acceptable
> spreadsheet!",
> >> vbInformation, "Sorry!"
> >> End If
> >> '
> >> End Sub
> >
> >
> >.
> >

Rob,

Your example worked like a charm!

Thank you so much for all of your help.

J. Henke


>-----Original Message-----
>
> What you're doing is not possible to do in the
Auto_Open procedure of
>Personal.xls. If you're trying to watch for workbooks the
user opens in
>Excel and take action when they do, this is possible
using a more advanced
>technique that involves setting up a WithEvents class
module in
>Personal.xls. This class will be instantiated when
Personal.xls loads and
>will notify you any time a workbook is opened via an
Application-level
>WorkbookOpen event. Here's a basic example.
>
>----------
>In Class1
>----------
>Private WithEvents mxlApp As Excel.Application
>
>Private Sub Class_Initialize()
> Set mxlApp = Excel.Application
>End Sub
>
>Private Sub Class_Terminate()
> Set mxlApp = Nothing
>End Sub
>
>Private Sub mxlApp_WorkbookOpen(ByVal Wb As
Excel.Workbook)
> ''' Ignore this workbook and any add-ins.
> If Wb.Name <> ThisWorkbook.Name And Not Wb.IsAddin
Then
> ''' Put the code that used to be in your
> ''' Auto_Open procedure here.
> MsgBox Wb.Name & " was just opened."
> End If
>End Sub
>
>------------
>In Module1
>------------
>Public gclsEventHandler As Class1
>
>Sub Auto_Open()
> Set gclsEventHandler = New Class1
>End Sub
>
> Both of these modules go in Personal.xls. Once
Personal.xls has opened
>and run its Auto_Open procedure, the mxlApp_WorkbookOpen
event procedure in
>Class1 will fire any time the user opens a workbook,
including if they have
>started Excel by double-clicking on a workbook from
Explorer.
>
>--
>Rob Bovey, MCSE, MCSD, Excel MVP
>Application Professionals
>http://www.appspro.com/
>
>* Please post all replies to this newsgroup *
>* I delete all unsolicited e-mail responses *
>
>
>"J. Henke" <kelevance@yahoo.com> wrote in message
>news:798901c344ad$e1b0d7c0$a401280a@phx.gbl...
>> Interesting...
>>
>> That is what I thought maybe was happening, but do you
>> know if there is anyway to activate the workbook I am
>> trying to open (e.g. by double-clicking on a .xls file)
>> before running the code?
>>
>> Or is what I am attempting to do simply not plausible
with
>> Excel?
>>
>> Thanks!
>>
>> J. Henke
>>
>> >-----Original Message-----
>> >
>> > The code in your Auto_Open procedure assumes that
>> there will be an
>> >ActiveWorkbook to operate on. Personal.xls is a hidden
>> workbook which opens
>> >prior to any other workbooks being created. Therefore,
>> when the Personal.xls
>> >Auto_Open procedure runs, there are no other workbooks
>> open and any attempt
>> >to reference range objects will fail.
>> >
>> >--
>> >Rob Bovey, MCSE, MCSD, Excel MVP
>> >Application Professionals
>> >http://www.appspro.com/
>> >
>> >* Please post all replies to this newsgroup *
>> >* I delete all unsolicited e-mail responses *
>> >
>> >
>> >"J. Henke" <kelevance@yahoo.com> wrote in message
>> >news:828d4265.0307070553.4956a61c@posting.google.com...
>> >> Hello, all.
>> >>
>> >> When I launch Excel 2000 with an Auto_Open macro in
my
>> PERSONAL.XLS
>> >> workbook, I receive a "Run-time error '1004':
>> Method 'Range' of object
>> >> '_Global' failed."
>> >>
>> >> It's particularly confusing because when I debug, I
do
>> not see any
>> >> problems with the code (it breaks on a line with the
>> code:
>> >> "Range("AE1").Select").
>> >>
>> >> If I am to run the macro manually, it executes
>> properly. However,
>> >> this Auto_Open procedure is giving me (and apparently
>> Excel) fits.
>> >>
>> >> Any suggestions would be wonderful.
>> >>
>> >> Thank you,
>> >>
>> >> J. Henke
>> >>
>> >> P.S. Below is the macro's code:
>> >>
>> >> Sub Auto_Open()
>> >>
>> >> 'Select the first "signature" cell
>> >> Range("AE1").Select
>> >>
>> >> 'Verify whether or not this is a spreadsheet to
>> convert
>> >> If ActiveCell.Value = "Commission %" Then
>> >>
>> >> 'Select column A
>> >> Columns("A:A").Select
>> >>
>> >> 'Insert a new column, and shift the other
>> columns to the right
>> >> Selection.Insert Shift:=xlToRight
>> >>
>> >> 'Select the first row in the spreadsheet
>> >> Rows("1:1").Select
>> >>
>> >> 'Delete the header row and shift up
>> >> Selection.Delete Shift:=xlUp
>> >>
>> >> 'Select cell A1
>> >> Range("A1").Select
>> >>
>> >> 'Declare a variable to store the number of
rows
>> in the active
>> >> worksheet
>> >> Dim numRowsInSheet As Integer
>> >>
>> >> 'Count the number of rows in the active
>> worksheet and store
>> >> them in a variable
>> >> numRowsInSheet =
>> ActiveSheet.UsedRange.Rows.Count
>> >>
>> >> 'Declare a new string
>> >> Dim myCellVal As String
>> >>
>> >> 'Store the new range (with the variable
number
>> of rows) in the
>> >> string
>> >> myCellVal = "A1:A" & numRowsInSheet
>> >>
>> >> 'Insert 1 in cell A1 for the first RecordID
>> >> ActiveCell.FormulaR1C1 = "1"
>> >> Range("A1").Select
>> >>
>> >> 'Use Excel's AutoFill function to fill the
>> whole range of
>> >> cells with 1's for the RecordID
>> >> Selection.AutoFill Destination:=Range
>> (myCellVal),
>> >> Type:=xlFillDefault
>> >> 'Range("A1:A6").Select
>> >>
>> >> 'Initialize a general property in columns 1
>> through 42 to
>> >> ensure that 42 columns get
>> >> 'some sort of tab delimiter
>> >> Range("A1:AP1").Select
>> >> Selection.Font.Italic = True
>> >> Selection.Font.Italic = False
>> >>
>> >> 'Change to the directory in which to save the
>> project
>> >> ChDir "C:\My Documents"
>> >>
>> >> ActiveWorkbook.SaveAs Filename:="C:\My
>> >> Documents\myFlatFile.txt" _
>> >> , FileFormat:=xlText, CreateBackup:=False
>> >> Else
>> >> MsgBox "Sorry chum, this isn't an acceptable
>> spreadsheet!",
>> >> vbInformation, "Sorry!"
>> >> End If
>> >> '
>> >> End Sub
>> >
>> >
>> >.
>> >
>
>
>.
>