Run-Time Error 1004 on Excel Startup

[J. Henke]

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 Bovey]

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]

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

 

[Rob Bovey]

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]

Rob,

Your example worked like a charm!

Thank you so much for all of your help.

J. Henke