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 |
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 |