Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 8
Default Error Executing a Macro on Opening Q

I'm hoping some one can hell with an error that has been happening on some code, that previously worked with no issue for several years.

I run a small Batch file that opens an Excel file, once the file is opened it executes a macro. As mentioned this has stopped working and now I get an error...

Runtime Error 429 "ActiveX Component Can't Create object"

My Excel Macro code is....

Option Explicit
Sub Auto_Open()

Application.ScreenUpdating = False

Dim OLKApp As Outlook.Application
Dim WeStartedIt As Boolean
Dim sh As Object
Dim Password As Object


If (Month(Now) = 12) And _
(Day(Now) = 26) Then
Exit Sub
End If

For Each sh In ActiveWorkbook.Worksheets
On Error Resume Next
sh.Unprotect Password = "1234"
sh.Unprotect
On Error GoTo 0
sh.Activate
sh.Range("A1").Select
Next sh
With ActiveWorkbook.Worksheets("Current Week")
.Activate

Application.GoTo Range("C6"), True
Range("C6").Activate


ActiveWindow.Zoom = 75
End With

On Error Resume Next
Set OLKApp = GetObject(, "Outlook.Application")
On Error GoTo 0
If OLKApp Is Nothing Then
Set OLKApp = CreateObject("Outlook.Application")
If OLKApp Is Nothing Then
' can't create app
' error mesage then exit
MsgBox "Can't Get Outlook"
Exit Sub
End If
WeStartedIt = True
Else
WeStartedIt = False
End If


Dim OkToCallMacro As Boolean 'If File is opened between 9:45am and 9:54am run the code
OkToCallMacro = False
Select Case Weekday(Date)
Case vbMonday To vbFriday
If Time = TimeSerial(9, 49, 0) _
And Time < TimeSerial(9, 54, 0) Then
OkToCallMacro = True
End If
Case Is = vbSaturday, vbSunday
If Time = TimeSerial(9, 49, 0) _
And Time < TimeSerial(9, 54, 0) Then
OkToCallMacro = True
End If
End Select

If OkToCallMacro Then
Application.WindowState = xlMinimized

Call RefreshSales
Call Copy_Paste

If Workbooks.Count = 1 Then
'only this workbook is open
ThisWorkbook.Save
'close the application
'(which will close thisworkbook)
Application.Quit
Else
ThisWorkbook.Close savechanges:=True
End If
End If

If WeStartedIt = True Then
OLKApp.Quit

End If

End Sub
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Error Executing a Macro on Opening Q

Try changing this line...

Dim OLKApp As Outlook.Application

TO
Dim OLKApp As Object

...because it "implies" you've made a reference (early binding) to the Outlook
Object Model, but your code uses late binding (preferred method!).

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 8
Default Error Executing a Macro on Opening Q

On Monday, May 8, 2017 at 7:58:35 PM UTC+1, GS wrote:
Try changing this line...

Dim OLKApp As Outlook.Application

TO
Dim OLKApp As Object

..because it "implies" you've made a reference (early binding) to the Outlook
Object Model, but your code uses late binding (preferred method!).

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


Unfortunately I tried that but same error
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Error Executing a Macro on Opening Q

On Monday, May 8, 2017 at 7:58:35 PM UTC+1, GS wrote:
Try changing this line...

Dim OLKApp As Outlook.Application

TO
Dim OLKApp As Object

..because it "implies" you've made a reference (early binding) to the
Outlook Object Model, but your code uses late binding (preferred method!).

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


Unfortunately I tried that but same error


Have a look at how you dim'd Password, then have a look at how you passed it
when unprotecting the sheet...

Make these changes:

Dim sPassword As String
sPassword = "1234"

-OR-

Const sPassword As String = "1234" '//my preference


In your loop:

For Each sh In ActiveWorkbook.WorkSheets
With sh
.Unprotect sPassword '//pass as default arg
'OR
'sh.Unprotect Password:= sPassword '//pass as named arg
Application.GoTo .Range("A1")
End With
Next 'sh

Then change the 3 lines to do this as follows:

Application.GoTo Sheets("Current Week").Range("C6")

After you make these changes step (F8) through the code to see *where* errors
occur so you can see the line that throws it!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
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
executing macros upon opening xls files dr chuck Excel Programming 5 July 4th 06 08:59 PM
Error 400 on executing macro first time in terminal server [email protected] Excel Programming 5 March 24th 06 04:47 PM
File Already Open Error When Executing A Macro Chuckles123[_26_] Excel Programming 0 October 7th 04 06:20 PM
File Already Open Error When Executing A Macro Chuckles123[_25_] Excel Programming 0 October 7th 04 05:48 PM
Executing code with opening target workbook Todd Huttenstine[_3_] Excel Programming 1 December 26th 03 03:00 AM


All times are GMT +1. The time now is 01:51 PM.

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

About Us

"It's about Microsoft Excel"