Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Question Unanswered: Test for Existance of File Before Exporting from Access

    My Access application in development exports to Excel using "OutputTo" and the user selects a sales officer from a form, then executes the export process and views the Excel file I'll call SALES_RESULTS.xls, which has been formatted from VBA in another Excel workbook. The problem occurs if Excel is not closed and the Excel file remains open, then the export is done again from the Access form. With the Excel file already open, Access can't do the export and produces the error message from "OutputTo", which happens to be in a macro.

    I am looking for a solution in my Access code to test if the Excel file is open, then I would advise the user to close the file first before exporting a new file.
    Using DIR does not find the open Excel file in this function:
    Code:
    ...
        filePath = "c:\Temp\"
        fileName = "SALES_RESULTS.xls"
        fileToDelete = filePath & fileName
        If fileExists(fileToDelete, fileName) Then Kill fileToDelete
        ...
    Function fileExists(pathFile As String, nameToCompare As String)
        Dim fileIn As String
    
        fileIn = UCase(Dir(pathFile))
        If fileIn = UCase(nameToCompare) Then
            fileExists = True
        Else
            fileExists = False
        End If
    
    End Function
    Or maybe there is some other solution besides testing for an open Excel file.
    Jerry

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Jerry,

    Can you trap the message by message number? If so, you can code

    Code:
    On Error GoTo Next
    where "next" would be something like

    Code:
    If Err.Number = WhateverItIs Then
        MsgBox "Please close Excel so I can continue ......" (you finish the sentence)
        Err.Clear
        return to the OutputTo line
    End If
    Hope this helps,

    Sam

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If you google you will find several API solutions for this.
    As an alternative, I usually time stamp my exports & check that the file does not exist on the system before saving. If it does then I uniquify the name. Not what you asked for but in case it is of use then you can see this in the Excel export code in the code-bank.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    It seems that DoCmd.OutputTo acOutputQuery, ... does not obey the On Error statement when the file name to be exported to is currently open.

    A solution that I found to work was to put code in the click event of the Report/Export button that attempts to write a blank line to the specific Excel file name, and when that fails, the user is told to close the Excel file first before creating a new one.

    Thanks for your interest in this. It appears that I have resolved the open file problem.
    Jerry

Posting Permissions

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