VAB - Replace \Username\ in filepath with cell range value does not work

[ONIWE]

Hello Good People,

When I replace the username in a file path as below, the code does not run unless i use the actual username.
Someone please tell me what I am doing wrong.

"C:\Users\Jackdniels\OneDrive - This is the actual path and it works fine

"C:\Users\" & fromPath & "\OneDrive - This is the replacement that does not work.
fromPath = Sheets("Summary Template").Range("b6")

 

[6StringJazzer]

What does "does not work" mean? What happens that is different than what you expect? What is the value of fromPath (B6) at the point this executes? How is this path used in your code? Please show all the code, not just the one line where you think there is a problem.

 

[ONIWE]

it means I'm getting constant expression required.

Sub ExtractData()
Application.ScreenUpdating = False
Dim wkbDest As Workbook
Dim wkbSource As Workbook
Dim fPath As String

fPath = Sheets("Summary").range("b6").Value

Set wkbDest = ThisWorkbo

With Application
.ScreenUpdating = False
.AskToUpdateLinks = False
.DisplayAlerts = False
End With


'Const strPath As String = "C:\Users\jackdaniels\OneDrive - headoffice\Desktop\MACRO 2\"
Const strPath As String = "C:\Users\" & fPath & "\OneDrive - headoffice\Desktop\MACRO 2\"
ChDir strPath

strExtension = Dir("*Evaluation_*")
Do While strExtension <> ""
Set wkbSource = Workbooks.Open(strPath & strExtension)


Something to do with a dynamic range from what I have read so far.

 

[Kyle123]

You need to dim it as a string, not a const. you can’t use variables in a const

 

[6StringJazzer]

Const strPath As String = "C:\Users\" & fPath & "\OneDrive - headoffice\Desktop\MACRO 2\"

The value of a Const must be a constant that is determined at compile time. You are using the "&" operator which is evaluated at runtime and so cannot be used in an assignment of a Const value.
Do this instead

Dim strPath As String
strPath = "C:\Users\" & fPath & "\OneDrive - headoffice\Desktop\MACRO 2\"