+ Reply to Thread
Results 1 to 3 of 3

How to check workbook is already opened before passing the workbook obj to a subroutine in Word

  1. #1
    Bon
    Guest

    How to check workbook is already opened before passing the workbook obj to a subroutine in Word

    Hello all

    I want to pass a workbook object or a filepath string from a module in
    MS Access 2000 to a subroutine in MS Word 2000. Before passing the
    object or filepath, I want to check the workbook is already opened or
    not. How can I do that?

    IF the workbook is already opened
    MS Word subroutine will extract excel data from the opened workbook
    ELSE
    Open the excel workbook
    MS Word subroutine extract excel data
    End IF

    If the workbook has been opened, the workbook is opened in the MS Word
    subroutine would be better? Or the workbook is opened in MS Access
    would be better?

    My whole workflow
    Firstly, MS Access calls a module with Workbook obj/filepath as
    parameter in MS Word
    Secondly, MS Word will extract data from MS Excel and then paste it
    under a bookmark in MS Word.

    Could anyone give me some advices? Thank you

    Cheers
    Bon


  2. #2
    Bob Phillips
    Guest

    Re: How to check workbook is already opened before passing the workbook obj to a subroutine in Word

    This should get you started

    Function IsFileOpen(FileName As String)
    Dim iFilenum As Long
    Dim iErr As Long

    On Error Resume Next
    iFilenum = FreeFile()
    Open FileName For Input Lock Read As #iFilenum
    Close iFilenum
    iErr = Err
    On Error GoTo 0

    Select Case iErr
    Case 0: IsFileOpen = False
    Case 70: IsFileOpen = True
    Case Else: Error iErr
    End Select

    End Function

    Sub test()
    If Not IsFileOpen("C:\MyTest\volker2.xls") Then
    Workbooks.Open "C:\MyTest\volker2.xls"
    End If
    End Sub


    --
    HTH

    Bob Phillips

    (remove nothere from email address if mailing direct)

    "Bon" <bonnie.tangyn@gmail.com> wrote in message
    news:1137599058.558130.97700@f14g2000cwb.googlegroups.com...
    > Hello all
    >
    > I want to pass a workbook object or a filepath string from a module in
    > MS Access 2000 to a subroutine in MS Word 2000. Before passing the
    > object or filepath, I want to check the workbook is already opened or
    > not. How can I do that?
    >
    > IF the workbook is already opened
    > MS Word subroutine will extract excel data from the opened workbook
    > ELSE
    > Open the excel workbook
    > MS Word subroutine extract excel data
    > End IF
    >
    > If the workbook has been opened, the workbook is opened in the MS Word
    > subroutine would be better? Or the workbook is opened in MS Access
    > would be better?
    >
    > My whole workflow
    > Firstly, MS Access calls a module with Workbook obj/filepath as
    > parameter in MS Word
    > Secondly, MS Word will extract data from MS Excel and then paste it
    > under a bookmark in MS Word.
    >
    > Could anyone give me some advices? Thank you
    >
    > Cheers
    > Bon
    >




  3. #3
    NickHK
    Guest

    Re: How to check workbook is already opened before passing the workbook obj to a subroutine in Word

    Bon,
    Presumably if you have a reference to a workbook (your workbook object), you
    must have opened it in order to obtain the reference. You can't have a
    reference to a file that is not open.
    Otherwise you would only have a file name/path.

    Why not pass a variant and check if its an object (Excel.Workbook) or a
    string and act accordingly

    NickHK

    "Bon" <bonnie.tangyn@gmail.com> wrote in message
    news:1137599058.558130.97700@f14g2000cwb.googlegroups.com...
    > Hello all
    >
    > I want to pass a workbook object or a filepath string from a module in
    > MS Access 2000 to a subroutine in MS Word 2000. Before passing the
    > object or filepath, I want to check the workbook is already opened or
    > not. How can I do that?
    >
    > IF the workbook is already opened
    > MS Word subroutine will extract excel data from the opened workbook
    > ELSE
    > Open the excel workbook
    > MS Word subroutine extract excel data
    > End IF
    >
    > If the workbook has been opened, the workbook is opened in the MS Word
    > subroutine would be better? Or the workbook is opened in MS Access
    > would be better?
    >
    > My whole workflow
    > Firstly, MS Access calls a module with Workbook obj/filepath as
    > parameter in MS Word
    > Secondly, MS Word will extract data from MS Excel and then paste it
    > under a bookmark in MS Word.
    >
    > Could anyone give me some advices? Thank you
    >
    > Cheers
    > Bon
    >




+ 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