Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2007
    Location
    Austin Texas
    Posts
    7

    Unanswered: Setting Excel Built in Doc Properties

    I am updating excel file properties from Access VBA function using the following code.....

    ---------------------------------
    Function SetExcelCategory(filepath As String, Text As String) As String

    Dim appExcel As Object
    Dim Result As String

    'On Error GoTo error_proc
    10 Set appExcel = GetObject(filepath)
    20 appExcel.BuiltinDocumentProperties("Category").Val ue = Text
    30 appExcel.Save
    40 appExcel.Close

    exit_proc:

    100 Set appExcel = Nothing
    110 SetExcelCategory = Result

    Exit Function

    error_proc:
    blablabla

    End Function
    ---------------------------------

    After running this function and the workbook is hidden within the file. So when user goes to open they have to use the menu option Window / Unhide.

    Any help would be greatly appreciated
    DK: eek:

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Poots has a post in the code bank that's got a lot of Excel Automation tips in it. I think it's something like exlapp.visible or exlworksheet.visible = true or something (can't think off the top of my head).
    Me.Geek = True

  3. #3
    Join Date
    Nov 2007
    Location
    Austin Texas
    Posts
    7
    Thank you for the quick reply... not sure why I would have to make visible when I did not hide it to start with.

    I tried to add
    appExcel.Visible = True
    but got an error "Object does not support this property....

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    Have you tried something like:

    Set exlBook = appExcel.Workbooks.Open(strFilePath)
    Me.Geek = True

  5. #5
    Join Date
    Nov 2007
    Location
    Austin Texas
    Posts
    7
    Just tried and get same error "error "Object does not support this property...."

  6. #6
    Join Date
    May 2005
    Posts
    1,191
    Well, for one, in line 20 of the code you have a space in the middle of the word "value", but I'm not sure if this was a copy paste error or if it's actually in the code.

    Else, first off can you try explicitly declaring the type of application, like:
    Code:
        Dim exlApp As Excel.Application, _
            exlBook As Excel.Workbook
    
        Set exlApp = New Excel.Application
        Set exlBook = exlApp.Workbooks.Open(strPersonalFile)
    Secondly, doing a help search on the BuiltinDocumentProperties, I think you need to specify which property in the documentproperties collection you want to change.

    Thirdly, Access help says it's a read-only, so I'm not sure you can write to it the Text value in the first place.

    Try looking into that and see where it gets you.
    Me.Geek = True

  7. #7
    Join Date
    Nov 2007
    Location
    Austin Texas
    Posts
    7
    Thanks for your input Nick but still no luck..... I am not having problems with writing to a BuiltinDocumentProperties nor am I having a problem accessing worksheets.

    To put it as simple as I can.... The issue is when using the "appExcel.Save" it hides the worksheets.


    In the most simplest form ....

    zsub()
    Dim FilePath As String

    FilePath = "C:\Test\Test.xls"
    Dim appExcel As Object

    Set appExcel = GetObject(FilePath)
    appExcel.Save
    appExcel.Close
    End Sub

    I am all for an educated guess but if you do not know then don't respond.


    I will post this issue in other forums as it appears that I have stumped this one.

    Thanks again for your time and efforts.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •