+ Reply to Thread
Results 1 to 12 of 12

vba syntax error

Hybrid View

  1. #1
    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

  2. #2
    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.

  3. #3
    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.

  4. #4
    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

  5. #5
    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.

  6. #6
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: vba syntax error

    I think that the method should be Activate, not Select

  7. #7
    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

    Buran is correct (thank you), my apologies.

    Brain cramp ...

  8. #8
    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 & Buran,
    Changing from Select to Activate solved the problem.
    Thank you to both of you.
    Jaz

+ 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