Jump to content

Set author in excel


Recommended Posts

4 hours ago, Rskm said:

How do i set the author name for this excel file.

The BuiltinDocumentProperties collection itself is read only.  However, its properties can be modified and saved for any given workbook.  Meaning, if/when you open a new workbook, the values of the BuiltInDocumentProperties collection will be as they originally were.

Here's an example of how to modify the Author property for a workbook:

#include <Excel.au3>


Func example()
    Local $oExcel, $oWorkbook

    $oExcel    = _Excel_Open()
    $oWorkbook = _Excel_BookNew($oExcel)

    With $oWorkbook
        ;Display current author, modify it, display it after being modified
        ConsoleWrite("Author (Before) = " & .BuiltInDocumentProperties.Item("Author").Value & @CRLF)
        .BuiltinDocumentProperties.Item("Author").Value = "New Author"
        ConsoleWrite("Author (After)  = " & .BuiltInDocumentProperties.Item("Author").Value & @CRLF)

;~  _Excel_Close($oExcel)


Author (Before) = TheXman
Author (After)  = New Author


Edited by TheXman
Link to comment
Share on other sites

Another example:

#include <Excel.au3>
#include <WinAPIFiles.au3>

Opt("MustDeclareVars", 1)
Opt("TrayIconDebug", 1)

Global $sFilePath = @ScriptDir & "\" & "test.xlsx"

Global $oMyError = ObjEvent("AutoIt.Error", "ErrFunc") ;Install a custom error handler
Global $iEventError ; to be checked to know if com error occurs. Must be reset after handling.

Global $oAppl = _Excel_Open()
;$oAppl.EnableEvents = False
;$oAppl.DisplayAlerts = False

Global $oWorkbook = _Excel_BookNew($oAppl)

$oWorkbook.Author = @YEAR & @MON & @MDAY

_Excel_BookSaveAs($oWorkbook, $sFilePath, $xlOpenXMLWorkbook, True) ;$xlOpenXMLWorkbook  51  ;$xlExcel8  56
_Excel_BookClose($oWorkbook, False)

;$oAppl.EnableEvents = True
;$oAppl.DisplayAlerts = True

While _WinAPI_FileInUse($sFilePath)

_Excel_Close($oAppl, False, True)

;This is a custom error handler
Func ErrFunc()
   Local $HexNumber = Hex($oMyError.number, 8)
;~    MsgBox(0, "", "We intercepted a COM Error !" & @CRLF & _
;~       "Number is: " & $HexNumber & @CRLF & _
;~       "WinDescription is: " & $oMyError.windescription)
   ConsoleWrite("->    We intercepted a COM Error !" & @CRLF & _
   "->    err.number is: " & @TAB & $HexNumber & @CRLF & _
   "->    err.source: " & @TAB & $oMyError.source & @CRLF & _
   "->    err.windescription: " & @TAB & $oMyError.windescription & _
   "->    err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF)

   $iEventError = 1 ; Use to check when a COM Error occurs
EndFunc   ;==>ErrFunc


Link to comment
Share on other sites



For some reason, Microsoft doesn't list the Workbook.Author property, but it does use it in an example on the same page.  That's odd.  In any case, thanks for the example.  :thumbsup:

Link to comment
Share on other sites

30 minutes ago, robertocm said:

The key seems to be the saving of the file

I'm not sure what you are talking about.  I didn't have any problem saving the file with the modified author.  The only reason I didn't include it in my example is that it wasn't necessary in order to show that the author property had been successfully modified.  Furthermore, the original post doesn't mention having any problem saving the file.  It only asks "how do I to set the author name".  With that said, what do you mean by "the key seems to be the saving of the file, not the property"?  How or why is that the "key"?  That not only doesn't seem to be "key", but it doesn't seem to be an issue at all.

Edited by TheXman
Link to comment
Share on other sites

Sorry, my confusion came from the "read only" in your first sentence, and then i misunderstood this: "when you open [...]  will be as they originally were" (i connected this ideas...)

I clearly need to improve my English and also the level of attention when reading

Thanks for all your work in the forum!

Link to comment
Share on other sites

An example for closed excel files using 7-Zip

(excel password protected files can't be changed)

#include <File.au3>
#include <String.au3>
#include <WinAPIFiles.au3>

;Required 7-zip
Global $Path7z = "C:\Program Files\7-Zip"
;Global $Path7z = @ProgramFilesDir & "\7-Zip"
If Not FileExists($Path7z & "\7z.exe") Then
    MsgBox(16, "", "7z.exe not found in path " & $Path7z)

Global $bFileOpen
Global $sFileRead, $sOldAuthor, $sNewAuthor = "NewAuthor"
Global $sFolder = FileSelectFolder("Directory to change excel author", "")
Global $sTempDir = @ScriptDir & "\TempDir"
Global $sFileCoreXml = $sTempDir & "\docProps\core.xml"
If FileExists($sTempDir) Then DirRemove($sTempDir, $DIR_REMOVE)
Global $sFolderLen = StringLen($sFolder)
If StringRight($sFolder, 1) = "\" Then $sFolderLen += 1

;Look for excel files in selected directory and subdirectories
Global $aFileList = _FileListToArrayRec($sFolder, "*.xls?", $FLTAR_FILES, $FLTAR_RECUR, $FLTAR_NOSORT, $FLTAR_FULLPATH)
If Not @error Then
    For $i = 1 To $aFileList[0]
        ;Discard some kind of temp files (locked files from antivirus?)
        If StringInStr($aFileList[$i], "~", 0, 1, $sFolderLen) > 0 Then
            Consolewrite($aFileList[$i] & @CRLF)

        $bFileOpen = _WinAPI_FileInUse($aFileList[$i])
        If $bFileOpen = 0 Then
            ;use x command to keep the folder stucture, -aoa Overwrite All existing files without prompt, use -r to unzip the subfolders from the zip file
            RunWait('"' & $Path7z & '\7z.exe" x -aoa -r -y "' & $aFileList[$i] & '" -o"' & $sTempDir & '"', $Path7z, @SW_HIDE)

            If FileExists($sFileCoreXml) Then
                $sFileRead = FileRead($sFileCoreXml)
                $sOldAuthor = _StringBetween($sFileRead, "<dc:creator>", "</dc:creator>")[0]
                _ReplaceStringInFile($sFileCoreXml, "<dc:creator>" & $sOldAuthor & "</dc:creator>", "<dc:creator>" & $sNewAuthor & "</dc:creator>")

                ;Help File 7-zip.chm 'Switch can be used in any place in command line'
                RunWait('"' & $Path7z & '\7z.exe" a -r -tzip "' & $aFileList[$i] & '" "' & $sTempDir & '\*"', $Path7z, @SW_HIDE)
                If FileExists($sTempDir & "\EncryptedPackage") Then Consolewrite("Error password protected file: " & $aFileList[$i] & @CRLF)
            DirRemove($sTempDir, $DIR_REMOVE)
            Consolewrite("Error Locked file: " & $aFileList[$i] & @CRLF)
    MsgBox(16, "Error", "No excel files were found in the folder")


Edited by robertocm
Code correction: DirRemove must go inside the loop
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...