Using Const Variable to Reference a Cell in XL03 VBA

[fmcna]

I am creating a macro that will need to open another workbook on a server. I originally set the file path of the "server workbook" to a public constant variable to be used by all modules/procedures.

Once I complete this project, it will be used at different sites with their own servers, so the file path will change. The other sites are not VBA proficient, so my answer to this was to have a cell on Sheet1 that contains the file path and the public constant variable will refer to that cell. However, when I debug the project, I get a compile error stating "Constant Expression Required" and the ".Range" part of the code is highlighted. (If the whole file path is coded in, I do not get this error)

Is there a way to get this to work? Or am I going to have to explain to others how to change the path in the macro?

Problem Code:

Public Const lotdatalocation As String = ThisWorkbook.Worksheets("Raw Materials").Range("C30") & "\Lotdata.xls"

Working Code:

Public Const lotdatalocation As String = "C:\Documents and Settings\cterry\Desktop\MCDS 4.0\Lotdata.xls"

 

[p45cal]

Take out the Const, make it a variable - it shouldn't change, once set.

 

[fmcna]

Thank you, but that didn't work either. I get another compile error, "Expected: end of statement". The whole line turns red and the equal sign is highlighted.

 

[tweedle]

You can't declare a variable and assign it on the same line like you do a Const.
But with a Const, by definition, one can't make it vary.

So try this:

'In your module 
Public lotdatalocation As String 
 
'Then in Workbook_Open
Private Sub Workbook_Open()
lotdatalocation = ThisWorkbook.Worksheets("Raw Materials").Range("C30") & "\Lotdata.xls"
'Alternate Method
'lotdatalocation = Application.GetOpenFilename("Excel Files (*.xl*), *.xl*")
End Sub

 

[fmcna]

tweedle,

That did it! I appreciate the help.

FYI, I already had 'Sub Autpen' procedure and I put the code in that.