+ Reply to Thread
Results 1 to 12 of 12

vba syntax error

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    12-08-2005
    Location
    Silicon Valley, CA USA
    MS-Off Ver
    2013 - Using 64 bit system
    Posts
    275

    vba syntax error

    HI,
    I am trying to insert this code in macro to open a workbook. If already opened, activate it. But for some reason it does not work.

    HTML Code: 
    Please help me on this.
    Thank you
    Jaz
    Last edited by jazbath; 09-17-2009 at 02:08 PM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: vba syntax error

    A reference to an open workbook doesn't include the path.

        Const sPath As String = "C:\Test\"
        Const sWkb As String = "mybook.xls"
        
        If WorkbookIsOpen(sWkb) Then
            Workbooks(sWkb).Select
        Else
            Workbooks.Open sPath & sWkb
        End If
    
    ...
    
    Function WorkbookIsOpen(sWkb As String) As Boolean
        On Error Resume Next
        WorkbookIsOpen = Not Workbooks(sWkb) Is Nothing
        Err.Clear
    End Function
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Contributor
    Join Date
    12-08-2005
    Location
    Silicon Valley, CA USA
    MS-Off Ver
    2013 - Using 64 bit system
    Posts
    275

    Re: vba syntax error

    Hi Shg,
    Thanks for helping me on this. Where do I ned to put the function?
    I inserted in the same workbook as function, it says "Object does not support this porperty or method.
    Jaz
    Last edited by jazbath; 09-16-2009 at 08:07 PM.

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: vba syntax error

    Put the stand-alone function in a code module.

    The declarations and code fragment above it replace the code you posted in whatever macro it was in.

  5. #5
    Forum Contributor
    Join Date
    12-08-2005
    Location
    Silicon Valley, CA USA
    MS-Off Ver
    2013 - Using 64 bit system
    Posts
    275

    Re: vba syntax error

    Hi Shg,
    I did as you mentioned. But still it gives me a run time error 438
    in the following line:

    Workbooks(sWkb).Select --- Object does not support this property or method.

    If the workbook is closed it works well, but if it is open then the above error occurs.

    Jaz

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: vba syntax error

    Post your code.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1