Hi Daniel
Try opening the workbook from Excel (rather than making the workbook open directly and bring excel along with it. I have a feeling the debug stuff does not always get operational while Excel is not fully open. It should work OK apart from the debugging.
Make sure the macro is in the "This workbook" module, not in a standard (added) module. All event modules need to be in the object they are triggered by.
Make sure you do not have security settings that will prevent macros from running on startup.
Select Case Choice
Should probably be
SstrName = Choice
It does not make sense to have A Select Case statement which is not immediately followed by a Case = statement. In fact I don't think it is allowed and may cause the sub to terminate.
I also think that the sub will always go into an unending loop but I have not checked this in detail.
Best Regards
David Grugeon
Excel VBA Group Moderator
-----Original Message-----
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On Behalf Of danielrose02
Sent: Tuesday, 5 June 2012 6:09 AM
To: ExcelVBA@yahoogroups.com
Subject: [ExcelVBA] Re: Need to automatically invoke an EXCEL MACRO
Unfortunately, this approach DID NOT WORK.
Here is my MACRO:
Private Sub Workbook_Open()
Call Test_For_OPEN
End Sub
and
Test_For_OPEN is:
Sub Test_For_OPEN()
Dim Choice As String
Dim SstrName As String, Sloop_control As String, Sselect_choice As String
Dim SOpt_1 As String, SOpt_2 As String, SOpt_3 As String, SOpt_4 As String, SOpt_5 As String
Dim SPath_Name As String, SWorkBook_Name As String
Dim SOpt_Select As String
Sloop_control = "N"
Choice = "5"
' Choice = "1"
Select Case Choice
Do
SOpt_1 = "Do you want to automatically start the weekly process?"
SOpt_4 = "Enter either:" & Chr(13) & Chr(10) & "Y to start" & Chr(13) & Chr(10) & "N to Stop"
SstrName = Application.InputBox(Prompt:=SOpt_1 _
& Chr(13) & Chr(10) & SOpt_4, _
Title:="Auto Start of Weekly KPI process", _
Default:="Y", Type:=2)
If SstrName = vbNullString Then
Exit Do
Else
Select Case SstrName
Case "Y", "y"
SOpt_Select = "Y"
Sloop_control = "Y"
Case "N", "n"
SOpt_Select = "N"
Sloop_control = "Y"
Case Else
SOpt_Select = vbNullString
End Select
' Something to loop
If SOpt_Select = vbNullString Then
Sloop_control = "N"
End If
End If
Loop Until (Sloop_control = "Y" Or Sloop_control = "y")
If (SOpt_Select = "Y" Or SOpt_Select = "y") Then
Call A_Construct_KPI_REPORT
Else
MsgBox "Automatic KPI Report will be bypassed"
End If
End Sub
I tried putting breakpoints in Test_For_OPEN and it seems that the VBA debugger does not even get control at this point.
--- In ExcelVBA@yahoogroups.com, "Hafizullah" <hafizullah@...> wrote:
>
> A better strategy will be to have the macro run as a Workbook_Open() event.
>
> So the following macro will be a property of the Workbook:
>
> Private Sub Workbook_Open()
> DoStuff
> End Sub
>
>
>
> You won’t be able to automate this through a batch file, and doing it via automation in VBS is a can of worms that I guarantee you don’t want to get into if you don’t already know a lot about VB/A and VBS.
>
>
>
> Hafizullah
> Hafizullah
> @}->-`,--
>
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
------------------------------------
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------Yahoo! Groups Links
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar