Set author in excel

[Rskm]

Hi, My autoit program generates excel output file.  How do i set the author name for this excel file. thanks

[Nine]

Look at this :

https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.builtindocumentproperties

[TheXman]

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>

example()

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)
    EndWith

;~  _Excel_Close($oExcel)
EndFunc

Console:

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

Edited March 25, 2021 by TheXman

[robertocm]

Another example:

expandcollapse popup

#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)
   Sleep(1000)
Wend

_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

 

[TheXman]

@robertocm

https://docs.microsoft.com/en-us/office/vba/api/excel.workbook#properties

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. 

[robertocm]

@TheXman

The key seems to be the saving of the file, not the property

Try adding this at the end of your example:

_Excel_BookSaveAs($oWorkbook, @ScriptDir & "\" & "test.xlsx", $xlOpenXMLWorkbook, True)

An interesting reference:
http://www.snb-vba.eu/VBA_Bestanden_en.html

[TheXman]

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 March 27, 2021 by TheXman

[robertocm]

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!

[robertocm]

An example for closed excel files using 7-Zip

(excel password protected files can't be changed)

expandcollapse popup

#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)
    Exit
EndIf

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)
            ContinueLoop
        EndIf

        $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)
            Else
                If FileExists($sTempDir & "\EncryptedPackage") Then Consolewrite("Error password protected file: " & $aFileList[$i] & @CRLF)
            EndIf
            DirRemove($sTempDir, $DIR_REMOVE)
        Else
            Consolewrite("Error Locked file: " & $aFileList[$i] & @CRLF)
        EndIf
    Next
Else
    MsgBox(16, "Error", "No excel files were found in the folder")
EndIf

 

Edited June 23, 2021 by robertocm
Code correction: DirRemove must go inside the loop