Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Test if file is open

    Hi All. I have an Access 97 database that I am exporting query data to an Excel spreadsheet using the DoCmd.TransferSpreadsheet... command. Just when you think you've trapped for all errors a user can create, there is always one more.

    I'm finding that when the user has the spreadsheet open from a previous run the TransferSpreadsheet action errors. Is there code that I can include that will test to see if this spreadsheet is currently open, then close it before running the Transfer action?

    Mike

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test if file is open

    Here is some code that should do want you want. Just change enterspreadsheetnamehere to the name of your spreadsheet.

    Dim MyXl As Object
    Dim intCntWS As Integer, intWS
    Dim strName As String
    Dim test
    intWS = 1
    strName = "enterspreadsheetnamehere"
    On Error GoTo NoExcel
    Set MyXl = GetObject(, "Excel.Application")
    On Error GoTo 0
    intCntWS = MyXl.Application.workbooks.Count + 1
    Do Until intWS = intCntWS
    test = MyXl.Application.workbooks(intWS).Name
    If MyXl.Application.workbooks(intWS).Name = strName & ".xls" Then
    MyXl.Application.workbooks(intWS).Close
    End If
    intWS = intWS + 1
    Loop
    NoExcel:

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test if file is open

    Thanks, I will load this into a function and give it a try

Posting Permissions

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