CODEWITHSUNDEEP
excelvba
David Ch
David Ch

Reputation: 13

How to open a file from Excel according to the username

I am trying to open a Word file from Excel using VBA. Right now I have this code:

Sub OpenWordFile_Odrebekræftelse()

    Set wordapp = CreateObject("word.Application")

    Dim s As String
    On Error Resume Next
    s = Application.InputBox(Prompt:="Indtaste brugernavn (initialer)", Type:=2)

    wordapp.documents.Open "C:\Users\" & s & "\Standarddokumenter\C02 Aftale\C02.5 Kontrakt\Ordrebekræftelse_version 1.1.dotx", ReadOnly:=True

    wordapp.Visible = True

This allow me to open the Word file by entering the user's initials when you push the button. The file's path is different according to who's the user and that's my problem here...

This method Works fine but I would like to get rid of the "enter your initials" part and have a macro that gets the username directly into the file's path according to who's session is in use... if you see what I mean.

Can anyone help me with that? I looked at the Getusername Things but couldn't get it to Work properly...

Thanks!

Edit: I don't want to display the username in a cell, I want to "insert" it in the path's file in the vba code (where " & s & " stands right now) ...if that makes any sense! :)

Upvotes: 1

Views: 703

Answers (2)

Pᴇʜ
Pᴇʜ

Reputation: 57753

You should make use of the environment variable %USERPROFILE% which relsults in %SystemDrive%\Users\{username}

In your case

wordapp.documents.Open "%USERPROFILE%\Standarddokumenter\C02 Aftale\C02.5 Kontrakt\Ordrebekræftelse_version 1.1.dotx", ReadOnly:=True

or 

wordapp.documents.Open Environ("USERPROFILE") & "\Standarddokumenter\C02 Aftale\C02.5 Kontrakt\Ordrebekræftelse_version 1.1.dotx", ReadOnly:=True

should work.

For more info see: Wikipedia Environment Variables.

Upvotes: 1

Angelin Calu
Angelin Calu

Reputation: 1915

s = Application.UserName should do the trick.

s = Environ("USERPROFILE") might be even better.

Upvotes: 0

Related Questions