Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking using a variable in the path
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking using a variable in the path
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. "WebMasterB" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking using a variable in the path
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? "AA2e72E" wrote: 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. "WebMasterB" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking using a variable in the path
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 WebMasterB wrote: 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? "AA2e72E" wrote: 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. "WebMasterB" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking using a variable in the path
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 "Dave Peterson" wrote: 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 WebMasterB wrote: 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? "AA2e72E" wrote: 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. "WebMasterB" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking using a variable in the path
Glad it worked for you.
WebMasterB wrote: 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 "Dave Peterson" wrote: 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 WebMasterB wrote: 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? "AA2e72E" wrote: 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. "WebMasterB" wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
variable path | Links and Linking in Excel | |||
Linking files with variable directory path | Excel Programming | |||
Re-Linking with New Path | Links and Linking in Excel | |||
Path to a dll as a variable | Excel Programming | |||
put path in as variable | Excel Programming |