I did a search on the forums to no avail...
Can you programmatically change the default Author setting for Excel? I see you can return it, but I can't see how to set it!
I am running an Access database which allows users to export data to Excel. When they do this, however, I want their username to be the Author attribute of the documents that they create. How can I do this? I can set the attribute for an individual workbook, but I want to change it in their Excel options.
Thanks!!
Tom Stock
Office version: MSO 2002 SP3
OS: Windows XP Pro
Hardware: IBM Thinkpad T41
Good evening Tom
Try going to Tools > Options, General tab, Username.
This is the name that Excel uses as the author in the file properties.
HTH
DominicB
I appreciate your post, however, I am looking to do this programatically. I know how to do this manually.
Hi Tom
I answered the question that you asked using the information you supplied. In the light of what you've just posted, this might help.
Sub Test()
Application.UserName = "DominicB"
End Sub
HTH
DominicB
I guess you missed the part where I statedOriginally Posted by dominicb
I felt it was pretty clear, but I could be mistaken.Can you programmatically change the default Author setting for Excel? I see you can return it, but I can't see how to set it!
BTW, your code does what I was looking for it to do.
Thanks for your help!
You're welcome.
DominicB
Ultimately, the purpose of this exercise was for an Access database. In some cases I instantiate XL spreadsheets on the fly - I wanted to set the author to be the database and username. This works fine if the spreadsheet is created in code using the XL object.
However, if I use an Access function like this:
the author attribute is NOT set (as a matter of fact, it is blank!). This is my current problem.
Please Login or Register to view this content.
More Info: What I am currently doing is this: when the user logs into the database, I grab the current value of Excel.Application.Username. Then, anytime I create a spreadsheet, I change the E.A.Username to "MyDatabase(user)", then I create and save the spreadsheet, then I change it back to the orginal value. The only time this doesn't work is if I use the Access function described above.
Any thoughts?
Hi Tom
Unfortunately not. According to Excel's help files the only way to access the author name is to use theBuiltInDocumentProperties structure thus:
Sub Test()
a = ActiveWorkbook.BuiltinDocumentProperties("Author").Value
MsgBox a
End Sub
which is a read-only setting and hence no good to you. Although using legitimate instructions, XL doesn't seem to like communicating all file properties to Access. Stumped!
Sorry I cannot be of any further help.
DominicB
> which is a read-only setting and hence no good to you.
It is not a read-only setting. You can set the 'Author' property
with code:
ThisWorkbook.BuiltinDocumentProperties("Author").Value = _
"New Author Name"
MsgBox ThisWorkbook.BuiltinDocumentProperties("Author").Value
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
"dominicb" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi Tom
>
> Unfortunately not. According to Excel's help files the only
> way to
> access the author name is to use theBuiltInDocumentProperties
> structure
> thus:
>
> Sub Test()
> a = ActiveWorkbook.BuiltinDocumentProperties("Author").Value
> MsgBox a
> End Sub
>
> which is a read-only setting and hence no good to you.
> Although using
> legitimate instructions, XL doesn't seem to like communicating
> all file
> properties to Access. Stumped!
>
> Sorry I cannot be of any further help.
>
> DominicB
>
>
> --
> dominicb
> ------------------------------------------------------------------------
> dominicb's Profile:
> http://www.excelforum.com/member.php...o&userid=18932
> View this thread:
> http://www.excelforum.com/showthread...hreadid=379129
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks