Link to home
Start Free TrialLog in
Avatar of CFMI
CFMIFlag for United States of America

asked on

MS Excel 2010 VBA compile error

Experts,

I receive a compile error in the below VBA on the statement, “Const FULL_PATH As String = SSreportBuilder.xls.UserForm1.SSFileName”.  The code is an object in Excel trying to run an enclosed macro with a selected workbook. The Names include:
      Spreadsheet: SSReportBuilder.xls
      Form: UserForm1
                Text Box named – SSFileName

Please provide your assistance and thank you in advance.


Private Sub cmdOK_Click()
cmdOK.Enabled = False
Dim app                          As Application
Dim wbTarget                 As Workbook
Dim strReturnedValue   As String
Dim SSFileName             As String
Dim strArg                      As String


If Risk = -1 Then

'================================================
   'Supply/Change fullname
'================================================
Const FULL_PATH As String = SSreportBuilder.xls.UserForm1.SSFileName

'================================================
   'Set a reference to the new instance
'================================================
Set app = New Application

With app
.Visible = True
.WindowState = xlMinimized

'================================================
   'Open Workbook
'================================================
Set wbTarget = app.Workbooks.Open(SSFileName)

'================================================
    'Run selected macro
'================================================
    Application.Run "SSreportBuilder.xls!McrRisk"
       
'================================================
    'Close Workbook and Kill the instance
'================================================
    Workbooks(SSFileName).Close savechanges:=False
        wbTarget.Close False
        .Quit
        MsgBox "The Workbook has updated Formats"
End With
cmdOK.Enabled = False
Else
    MsgBox "Please select a Macro"
End If

End Sub
SOLUTION
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of CFMI

ASKER

Excellent, the code compiles and runs well.