JohnT JohnT - 11 days ago 5
SQL Question

Retrieve value from Access table in Excel

I have an Excel file that exports data into Word. It includes a cover page and grabs the user name ("First.Last") and changes it to "First Last" but I also need to include the user's professional title. This information is housed in an Access table. It has a field called Name and a field called Title. The Name field match exactly to User with no duplicates.

I have tried about eight different methods I've found online to grab this value from the table. The table will never happen to be open so I can't use "CurrentDB()".

I just need to be able to reach into the table in a database, grab the "title" value given that the value of the field "Name" is equal to the value of User (user name from the environment - the person using the excel file).

If it helps, I can provide examples of the different chunks of code I've used so far.

User is the username from the environment
tblPickName is the table I am trying to open
Path is the directory and file where the table is located

tblPickName has 2 fields, Name and Title
I need to grab the Title from this table and set it to my variable "Title" as long as Name equals User. Then I can export the username and title to Word along with the rest of the data.

Dim Path As String
Dim User As String
Dim Title As String

Dim db As DAO.Database
Dim rs As DAO.Recordset

User = Environ("UserName")
User = Replace(User, ".", " ")
User = StrConv(User, vbProperCase)

Path = "Directory\Name.mdb"

Set db = DBEngine.OpenDatabase(Path)
Set rs = db.OpenRecordset("SELECT tblPickAnalyst.Title FROM tblPickAnalyst WHERE [Analyst]='" & User & "'")

Title = rs!Title

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

docWord.Bookmarks("AnalystName").Range.Text = User
docWord.Bookmarks("AnalystTitle").Range.Text = Title

Answer

Here is the final solution. Thank you to everyone who helped!

Dim Path As String
Dim User As String
Dim Title As String

Dim db As DAO.Database
Dim rs As DAO.Recordset

User = Environ("UserName")
User = Replace(User, ".", " ")
User = StrConv(User, vbProperCase)

Path = "Directory\FileName"

Set db = DBEngine.OpenDatabase(Path)
Set rs = db.OpenRecordset("SELECT tblPickAnalyst.Title FROM tblPickAnalyst WHERE [Analyst]='" & User & "'")

Title = rs!Title

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

docWord.Bookmarks("AnalystName").Range.Text = User
docWord.Bookmarks("AnalystTitle").Range.Text = Title
Comments