how would i do this - put the name of document and its statics into an excel file

bbimis
bbimis used Ask the Experts™
on
how would i go about rather it is powershell, vbscript, autoit (i don't have a compiler for c++ or any language like that)

i'm wanting to parse the file>properties>advanced properties then characters with spaces count into a spreadsheet

so its something like this
title                                  count
jan23-2016                     5412

can it be done with powershell? if so how?
or any other scripting language?

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
With the Document open or closed?

I'd use VBA for this.

What is the trigger for adding to / updating the spreadsheet?

How many documents? How many people are involved in this?

Author

Commented:
Well I'm wanting to take all documents on desktop and read the filename,  then the statistics ad stated above into an excel file.  Basically to make an invoice for transcription. . So once they are parsed then the document is moved to a complete folder.  I just haven't a clue how to do it and what scripting language to use.
Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:
Sure, this is doable in a VBScript approach.

What columns do you want in the output file?  I would suggest creating it as a comma delimited file, which can then easily be opened in Excel.

I assume Word will be installed on the computer you need to do this on?

~bp
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:
And where would you like the output file written to?

~bp
IT / Software Engineering Consultant
Top Expert 2016
Commented:
Okay, here is a good starting scipt for you.  It's written in VBS, so runs standalone, but does require Word to be installed on the machine running the script.  Save this as a VBS file, and then run from a command line (or BAT file) as follows:

cscript EE28923283.vbs

Output currently goes to a file named "docstats.csv" in the current folder when the script is run, but you can adjust that in the code if desired.  It processes all files on the users Desktop that match the extensions specified in the script.  These are currently set to .DOC and .DOCX but you can adjust if needed.  They have to be WORD files though.

Give a test and let me know if you have questions or I misunderstood something.

' Define needed I/O constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2

' Define constans needed for Word data gathering
Const wdPropertyTitle = 1
Const wdStatisticWords = 0 'Count of words
Const wdStatisticLines = 1 'Count of lines
Const wdStatisticPages = 2 'Count of pages
Const wdStatisticCharacters = 3 'Count of characters
Const wdStatisticParagraphs = 4 'Count of paragraphs
Const wdStatisticCharactersWithSpaces = 5 'Count of characters including spaces
Const wdStatisticFarEastCharacters = 6 'Count of characters for Asian languages

' Create needed objects
Set objShell = WScript.CreateObject("WScript.Shell")
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Define folders and files to work with
strBaseDir = objShell.SpecialFolders("Desktop")
arrExts = Array(".doc", ".docx")
strOutFile = "docstats.csv"

' Invoke Word, but don't show it
Set objWord = CreateObject("Word.Application")
objWord.Visible = False

' Open output file, write header line
Set objOutFile = objFSO.OpenTextFile(strOutFile, ForWriting, True)
objOutFile.WriteLine Quote("File") & "," & Quote("Title") & "," & Quote("Count")
 
' Load Word
Set objWord = CreateObject("Word.Application")

' Access the folder of files to process
Set objFolder = objFSO.GetFolder(strBaseDir)

' Process each file in the folder
For Each objFile in objFolder.Files

   ' See if the extension matches the ones we want to scan
   blnMatch = False
   For Each strExt in arrExts
      If LCase(Right(objFile.Name, Len(strExt))) = LCase(strExt) Then
         blnMatch = True
         Exit For
      End If
   Next

   If blnMatch Then
      On Error Resume Next

      ' Open this file in Word, catch any errors in case we hit a bad file
      Set objDoc = objWord.Documents.Open(objFile.Path)
      If Err.Number = 0 Then
         ' Write file info to output file
         objOutFile.WriteLine Quote(objFile.Name) & "," & Quote(objDoc.BuiltInDocumentProperties(wdPropertyTitle)) & "," & objDoc.ComputeStatistics(wdStatisticCharactersWithSpaces)

         ' Close document
         objDoc.Close
      Else
         objOutFile.WriteLine Quote(objFile.Name) & "," & Quote("*** ERROR READING FILE ***") & ",0"
      End If

      Err.Clear
      On Error Goto 0
   End If
Next

' Close Word and output file
objWord.Quit
objOutFile.Close


' Add surrounding double quotes to a string
Function Quote(s)
   Quote = Chr(34) & s & Chr(34)
End Function

Open in new window

~bp

Author

Commented:
wow, this will do the trick. You happen to know any good books to learn vbscript  or online resources?
Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:
I do have a few links and will share them with you shortly, headed out just now...

~bp

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial