Saving work documnet property in excel

[12jan87]

Here is the code that I have so far to show word document property in a message box

Sub DocumentProperties()
Dim Output As String
Dim Prop As DocumentProperty

For Each Prop In ActiveDocument.BuiltInDocumentProperties
Output = Output + Prop.Name + " = " + Str(Prop.Value) + vbCrLf
Next

MsgBox (Output)
End Sub

I want to rewrite this code as a word macro so that it saves property
names and its values in a new excel spreadsheet. The property name
should go in column A and and property values should go in column B

 

[SkipVought]

Hi,

Check out CreateObject in VB Help.

You must also have a reference set for the MS Excel Object Library in Tools > References.

Once you create a reference to the Excel Application Object, you will ADD a workbook.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[12jan87]

ok i got this far now

Sub DocumentProperties()
Dim output As String

Dim Prop As DocumentProperty

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim i As Integer
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Add ' create a new workbook

On Error Resume Next
For Each Prop In ActiveDocument.BuiltInDocumentProperties
output = output + Prop.Name + " = " + Str(Prop.Value) + vbCrLf
Next
End Sub


but its giving me error

 

[SkipVought]

An error on WHAT statement?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[12jan87]

when i comment this line it doesn'tgive me any error

output = output + Prop.Name + " = " + Str(Prop.Value) + vbCrLf

so there is proba something worng i am doing here

 

[SkipVought]

"The property name should go in column A and and property values should go in column B"

Sub DocumentProperties()
    Dim output As String
    
    Dim Prop As DocumentProperty, lRow As Long
    
    Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Dim i As Integer
    
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    Set xlWB = xlApp.Workbooks.Add ' create a new workbook
    
    lRow = 2
    For Each Prop In ActiveDocument.BuiltInDocumentProperties
        xlWB.Sheets(1).Cells(lRow, "A").Value = Prop.Name
        xlWB.Sheets(1).Cells(lRow, "B").Value = Prop.Value
        lRow = lRow + 1
    Next
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Your error was Str(Prop.Value)

You probably wanted CStr, CONVERT to string. Also you ought to use & rather than + to concatenate strings.

In this case, unless your original requirement was in error, there is no reason to concatenate anything.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}