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

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

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

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