CODEWITHSUNDEEP
excelvbalocationvariant
hakandeep
hakandeep

Reputation: 51

How to make location variable in vba?

Workbooks.Open ("C:\Users\**VARIANT**\Desktop\Excel Before Code Templates (BCT)\Data_BCT.xlsx")

I want to make the "variant" part a variable dependent on input. I want to do that because it needs to be revised for every other user.

Upvotes: 0

Views: 64

Answers (4)

user6432984
user6432984

Reputation:

Reference: Special Folders

FilePath = getDeskTopPath & "\Excel Before Code Templates (BCT)\Data_BCT.xlsx"

If Len(Dir(FilePath)) > 0 then

    Workbooks.Open (FilePath)

End If

Function getDeskTopPath()
    Dim objShell As Object
    Dim strPath As String

    Set objShell = CreateObject("Wscript.Shell")
    strPath = objShell.SpecialFolders("Desktop")
    getDeskTopPath = strPath

    Set objShell = Nothing
End Function

You can also have use the Application.FileDialog to choose a file or folder. See this artice for details: VBA FILEDIALOG – OPENING, SELECTING AND SAVING FILES AND FOLDERS

Upvotes: 2

Comintern
Comintern

Reputation: 22205

You can use Environ$ to get the current user:

Dim desktop As String
desktop = "C:\Users\" & Environ$("USERNAME") & "\Desktop\"
Debug.Print desktop

Upvotes: 3

Shai Rado
Shai Rado

Reputation: 33692

Just use the code below:

Sub InsertUserName()

Dim folder_var  As String

folder_var = InputBox("Insert your User Name")

Workbooks.Open ("C:\" & folder_var & " \Desktop\Excel Before Code Templates (BCT)\Data_BCT.xlsx")


End Sub

Upvotes: 0

gizlmo
gizlmo

Reputation: 1922

Dim variablePart as String

variablePart = "someFolderName"

Workbooks.Open("C:\Users\" & variablePart & "\Desktop\Excel Before Code Templates (BCT)\Data_BCT.xlsx")

'will lead to: C:\Users\someFolderName\Desktop\Excel Before Code Templates (BCT)\Data_BCT.xlsx")

Upvotes: 0

Related Questions