Linking using a variable in the path

[Guest]

I am working on a project where certian pieces of data are pulled from a
linked document.

The problem, not all users (at other sites) use the same mapped letter for
the drive. Therefore I can't link via a static path. I have decided to
place this linked document on each users local drive, which is defined as
"D:\USERS\(username)\documents. I have this defined in the macro as a
"DataPath".

The question, how do I use this defined path from the macro when linking
within the workbook (ie cell a1 ='DataPath\[workbook.xls]Sheet1'!$A$1). When
I enter this into the cell, it asks me to update values and define the path.
How do I link to the file using a variable path?

Code used to determine variable path:

Option Explicit
Global SharedDrive
Public Username
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias
"GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long



Function fOSUserName() As String
On Error GoTo fOSUserName_Err

Dim lngLen As Long, lngX As Long
Dim strUserName As String

strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)

If lngX <> 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = ""
End If

fOSUserName_Exit:
Exit Function

fOSUserName_Err:
MsgBox Error$
Resume fOSUserName_Exit

End Function

And in the ThisWorkbook object in the Worksheet_Open() event it would look
like this:

Private Sub Workbook_Open()
Username = fOSUserName
DataPath = "D:\Users\" & Username & "\Documents\"
End Sub


Thank you

 

[Guest]

I don't think what you are trying to achieve will work.

You can specify

\\server\path\ instead of e.g. D:\ a drive letter; however, it is very
unlikely that the servers at the different sites will have the same names.

 

[Guest]

That's the problem I ran into to begin with, the server names are different.
Is there another way to define the path in the link, depending on where the
user is located?

I don't think what you are trying to achieve will work.

You can specify

\\server\path\ instead of e.g. D:\ a drive letter; however, it is very
unlikely that the servers at the different sites will have the same names.

I am working on a project where certian pieces of data are pulled from a
linked document.

The problem, not all users (at other sites) use the same mapped letter for
the drive. Therefore I can't link via a static path. I have decided to
place this linked document on each users local drive, which is defined as
"D:\USERS\(username)\documents. I have this defined in the macro as a
"DataPath".

The question, how do I use this defined path from the macro when linking
within the workbook (ie cell a1 ='DataPath\[workbook.xls]Sheet1'!$A$1). When
I enter this into the cell, it asks me to update values and define the path.
How do I link to the file using a variable path?

Code used to determine variable path:

Option Explicit
Global SharedDrive
Public Username
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias
"GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long



Function fOSUserName() As String
On Error GoTo fOSUserName_Err

Dim lngLen As Long, lngX As Long
Dim strUserName As String

strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)

If lngX <> 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = ""
End If

fOSUserName_Exit:
Exit Function

fOSUserName_Err:
MsgBox Error$
Resume fOSUserName_Exit

End Function

And in the ThisWorkbook object in the Worksheet_Open() event it would look
like this:

Private Sub Workbook_Open()
Username = fOSUserName
DataPath = "D:\Users\" & Username & "\Documents\"
End Sub


Thank you

 

[Dave Peterson]

Maybe you can use this to get the location of the "My Documents" folder:

Option Explicit
Sub testme()
Dim myDocumentsPath As String

Dim wsh As Object

Set wsh = CreateObject("WScript.Shell")
myDocumentsPath = wsh.SpecialFolders.Item("mydocuments")

MsgBox myDocumentsPath

End Sub

That's the problem I ran into to begin with, the server names are different.
Is there another way to define the path in the link, depending on where the
user is located?

I don't think what you are trying to achieve will work.

You can specify

\\server\path\ instead of e.g. D:\ a drive letter; however, it is very
unlikely that the servers at the different sites will have the same names.

I am working on a project where certian pieces of data are pulled from a
linked document.

The problem, not all users (at other sites) use the same mapped letter for
the drive. Therefore I can't link via a static path. I have decided to
place this linked document on each users local drive, which is defined as
"D:\USERS\(username)\documents. I have this defined in the macro as a
"DataPath".

The question, how do I use this defined path from the macro when linking
within the workbook (ie cell a1 ='DataPath\[workbook.xls]Sheet1'!$A$1). When
I enter this into the cell, it asks me to update values and define the path.
How do I link to the file using a variable path?

Code used to determine variable path:

Option Explicit
Global SharedDrive
Public Username
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias
"GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long



Function fOSUserName() As String
On Error GoTo fOSUserName_Err

Dim lngLen As Long, lngX As Long
Dim strUserName As String

strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)

If lngX <> 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = ""
End If

fOSUserName_Exit:
Exit Function

fOSUserName_Err:
MsgBox Error$
Resume fOSUserName_Exit

End Function

And in the ThisWorkbook object in the Worksheet_Open() event it would look
like this:

Private Sub Workbook_Open()
Username = fOSUserName
DataPath = "D:\Users\" & Username & "\Documents\"
End Sub


Thank you

 

[WebMasterB]

Dave,

I'm very sorry I was looking back at some of my previous questions and
realized I acknoledged that the answer you provided worked in my situation,
but completely forgot to thank you personally.

Please accept my (extremely)belated gratitude.

THANK YOU THANK YOU THANK YOU

Maybe you can use this to get the location of the "My Documents" folder:

Option Explicit
Sub testme()
Dim myDocumentsPath As String

Dim wsh As Object

Set wsh = CreateObject("WScript.Shell")
myDocumentsPath = wsh.SpecialFolders.Item("mydocuments")

MsgBox myDocumentsPath

End Sub

That's the problem I ran into to begin with, the server names are different.
Is there another way to define the path in the link, depending on where the
user is located?

I don't think what you are trying to achieve will work.

You can specify

\\server\path\ instead of e.g. D:\ a drive letter; however, it is very
unlikely that the servers at the different sites will have the same names.

:

I am working on a project where certian pieces of data are pulled from a
linked document.

The problem, not all users (at other sites) use the same mapped letter for
the drive. Therefore I can't link via a static path. I have decided to
place this linked document on each users local drive, which is defined as
"D:\USERS\(username)\documents. I have this defined in the macro as a
"DataPath".

The question, how do I use this defined path from the macro when linking
within the workbook (ie cell a1 ='DataPath\[workbook.xls]Sheet1'!$A$1). When
I enter this into the cell, it asks me to update values and define the path.
How do I link to the file using a variable path?

Code used to determine variable path:

Option Explicit
Global SharedDrive
Public Username
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias
"GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long



Function fOSUserName() As String
On Error GoTo fOSUserName_Err

Dim lngLen As Long, lngX As Long
Dim strUserName As String

strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)

If lngX <> 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = ""
End If

fOSUserName_Exit:
Exit Function

fOSUserName_Err:
MsgBox Error$
Resume fOSUserName_Exit

End Function

And in the ThisWorkbook object in the Worksheet_Open() event it would look
like this:

Private Sub Workbook_Open()
Username = fOSUserName
DataPath = "D:\Users\" & Username & "\Documents\"
End Sub


Thank you

 

[Dave Peterson]

Glad it worked for you.

Dave,

I'm very sorry I was looking back at some of my previous questions and
realized I acknoledged that the answer you provided worked in my situation,
but completely forgot to thank you personally.

Please accept my (extremely)belated gratitude.

THANK YOU THANK YOU THANK YOU

Maybe you can use this to get the location of the "My Documents" folder:

Option Explicit
Sub testme()
Dim myDocumentsPath As String

Dim wsh As Object

Set wsh = CreateObject("WScript.Shell")
myDocumentsPath = wsh.SpecialFolders.Item("mydocuments")

MsgBox myDocumentsPath

End Sub

That's the problem I ran into to begin with, the server names are different.
Is there another way to define the path in the link, depending on where the
user is located?

:

I don't think what you are trying to achieve will work.

You can specify

\\server\path\ instead of e.g. D:\ a drive letter; however, it is very
unlikely that the servers at the different sites will have the same names.

:

I am working on a project where certian pieces of data are pulled from a
linked document.

The problem, not all users (at other sites) use the same mapped letter for
the drive. Therefore I can't link via a static path. I have decided to
place this linked document on each users local drive, which is defined as
"D:\USERS\(username)\documents. I have this defined in the macro as a
"DataPath".

The question, how do I use this defined path from the macro when linking
within the workbook (ie cell a1 ='DataPath\[workbook.xls]Sheet1'!$A$1). When
I enter this into the cell, it asks me to update values and define the path.
How do I link to the file using a variable path?

Code used to determine variable path:

Option Explicit
Global SharedDrive
Public Username
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias
"GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long



Function fOSUserName() As String
On Error GoTo fOSUserName_Err

Dim lngLen As Long, lngX As Long
Dim strUserName As String

strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)

If lngX <> 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = ""
End If

fOSUserName_Exit:
Exit Function

fOSUserName_Err:
MsgBox Error$
Resume fOSUserName_Exit

End Function

And in the ThisWorkbook object in the Worksheet_Open() event it would look
like this:

Private Sub Workbook_Open()
Username = fOSUserName
DataPath = "D:\Users\" & Username & "\Documents\"
End Sub


Thank you