MS Excel 2010 VBA compile error


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
        MsgBox "The Workbook has updated Formats"
End With
cmdOK.Enabled = False
    MsgBox "Please select a Macro"
End If

End Sub
CFMIFinancial Systems AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

That constant (FULL_PATH) is not used anywhere in your code.

I think you can safely remove that whole line of code.

No, that kind of assignment is not possible.

You will need to define FULL_PATH as a (Public) variable...

Public FULL_PATH As String

Then elsewhere in the code (at the same point as the current statement that fails to execute, for example) set it to the desired value from the other open workbook.

This could be, for instance, from the first cell ([A1]) in the first workbook:

FULL_PATH = Workbooks("SSreportBuilder.xls").Worksheets(1).Cells(1)

Presumably the UserForm1 form will be open/being used in the "SSreportBuilder.xls" workbook concurrently when the above Visual Basic for Applications code is running.

I would suggest storing the value in the SSFileName textbox control within a cell in the workbook, or perhaps in a hidden named range (not visible within any worksheet in that workbook), & referring to that value instead of what you are trying to achieve.



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CFMIFinancial Systems AnalystAuthor Commented:
Excellent, the code compiles and runs well.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.