Problem setting CustomDocumentProperty in Excel wrkBk from Access

[rhc]

Access and Excel 2003 on XP

original line of code in excel that works:
ThisWorkbook.CustomDocumentProperties.Add _
Name:="JournalType", Type:=msoPropertyTypeString, _
Value:=strJEType, LinkToContent:=False

Code when I try to do the same thing from access that doesnt work. ie I get
error Number 5 Invalid procedure call or argument.

Dim xlAPP As Object
Dim xlWB As Object
Dim xlWS As Excel.Worksheet
Dim RActive As Excel.Range

Set xlAPP = CreateObject("Excel.Application")
xlAPP.EnableEvents = False
Set xlWB = xlAPP.Workbooks.Open("c:\er\test\test.xls")
Set xlWS = xlWB.Worksheets(1)

xlWB.CustomDocumentProperties.Add Name:="JournalType",
Type:=msoPropertyTypeString, Value:="4", LinkToContent:=False

Help!

thanks in advance
Bob

 

[Stefan Hoffmann]

hi Bob,

Access and Excel 2003 on XP
xlWB.CustomDocumentProperties.Add Name:="JournalType",
Type:=msoPropertyTypeString, Value:="4", LinkToContent:=False

hmm, same setup. msoPropertyTypeString is not listed in the Excel library.


mfG
--> stefan <--

 

[rhc]

Hi Stefan
thanks for the reponse. msoPropertyTypeString is a legitamate type in
Excel. You will see it under the type section of the vba help for the Add
method for Document Properities. Here is that paragraph from the help page:
Type Optional Variant. The data type of the property. Can be one of the
following MsoDocProperties constants: msoPropertyTypeBoolean,
msoPropertyTypeDate, msoPropertyTypeFloat, msoPropertyTypeNumber, or
msoPropertyTypeString.

As I noted the equivalent code works fine in excel.

 

[Stefan Hoffmann]

hi Bob,

thanks for the reponse. msoPropertyTypeString is a legitamate type in
Excel. You will see it under the type section of the vba help for the Add
method for Document Properities. Here is that paragraph from the help page:
Type Optional Variant. The data type of the property. Can be one of the
following MsoDocProperties constants: msoPropertyTypeBoolean,
msoPropertyTypeDate, msoPropertyTypeFloat, msoPropertyTypeNumber, or
msoPropertyTypeString.

As I noted the equivalent code works fine in excel.

Yup, in Excel maybe, but not in Access as far as I can see.


mfG
--> stefan <--