rloucks
asked on
Run-time error when calling ActiveWorkbook object in Personal.xls
I have the following sub in the ThisWorkbook module of the Personal.xls
Private Sub Workbook_Open()
'Only run code if file is tab delimited
If Application.ActiveWorkbook
AddButtons
FormatFile
FormatDataFile
Else
Exit Sub
End If
End Sub
Whenever I try to open a tab delimited file I get the following error:
Run-time error '91':
Object variable or With block variable not set
If I debug and then click F5 to continue, it works fine. I tried adding a DoEvents which didn't work, then added a condition if Not IsObject(Application.Activ
Private Sub Workbook_Open()
'Only run code if file is tab delimited
If Application.ActiveWorkbook
AddButtons
FormatFile
FormatDataFile
Else
Exit Sub
End If
End Sub
Whenever I try to open a tab delimited file I get the following error:
Run-time error '91':
Object variable or With block variable not set
If I debug and then click F5 to continue, it works fine. I tried adding a DoEvents which didn't work, then added a condition if Not IsObject(Application.Activ
Try moving the code to a routine in a normal module and use Application.Ontime in the Workbook_Open event:
Private Sub Workbook_Open()
application.ontime now(), "my_macro_name"
End Sub
this should allow Excel to finish loading before it tries to reference active workbooks.
HTH
Rory
Private Sub Workbook_Open()
application.ontime now(), "my_macro_name"
End Sub
this should allow Excel to finish loading before it tries to reference active workbooks.
HTH
Rory
ASKER
Unfortunately putting On Error Resume Next didn't work. It ignores the code all together and the Application.OnTime Now() errors out the same.
Hmm weird...ohk..try changing this line...
If Application.ActiveWorkbook
To this...
If Application.thisWorkbook.F
and lets see if it works or not...
If Application.ActiveWorkbook
To this...
If Application.thisWorkbook.F
and lets see if it works or not...
ASKER
Wouldn't that take the Personal.xls ThisWorkbook?
Didn't execute the module. Any more suggestions would be much appreciated. :)
Didn't execute the module. Any more suggestions would be much appreciated. :)
Yups...didnt thought from that prospective...neways try this...
ActiveWorkbook.Name = y
Workbooks("y").Activate
ActiveWorkbook.Name = y
Workbooks("y").Activate
Oops...it would be without quotes sign...which is...
ActiveWorkbook.Name = y
Workbooks(y).Activate
ActiveWorkbook.Name = y
Workbooks(y).Activate
Saurabh,
I think you meant:
y = ActiveWorkbook.Name
Workbooks(y).Activate
but I'm not sure that would work - if it's already the activeworkbook, activating it won't do anything.
rloucks,
What was the full code you tried with the OnTime method?
I think you meant:
y = ActiveWorkbook.Name
Workbooks(y).Activate
but I'm not sure that would work - if it's already the activeworkbook, activating it won't do anything.
rloucks,
What was the full code you tried with the OnTime method?
Rory...im just trying it...because i dont know what his code is..and as per what i can see that it gives him a debug option and when he hit a f5 it works...so it must because his workbook is not selected properly..so just trying to select the workbook....
If the workbook is not active, then referring to the Activeworkbook won't work. If it is active, then activating it won't do anything.
rloucks, can you just confirm which line is highlighted when you debug the error?
rloucks, can you just confirm which line is highlighted when you debug the error?
ASKER
If Application.ActiveWorkbook
ASKER
It's the Application.ActiveWorkbook
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the help. It worked perfectly.
On Error Resume Next
Saurabh...