Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Can't open/find Excel file after export (97)

    Dear members of this board, can anyone help me with this one (it must be quite stupid/simple...)?
    How do I open the file that results from exporting a query to excel using TransferSpreadSheet, like Excell tends to do when you use the 'publish with Excel' menu command...?
    Even after 10" the Dir() function I use to assure that the file is created returns a "" but when I check afterwards in my Explorer, it's there for sure.

    This is the code I actually use:

    <font face="Script MT Bold"> Dim strInput As String
    Dim Start As Date
    DoCmd.TransferSpreadsheet acExport, , qryName, strInput
    Start = Now
    Do While Dir(strInput) = ""
    If DateDiff("s", Start, Now()) > 10 Then
    MsgBox "Time-out: file " & Dir(strInput) & " can't be opened.", vbInformation, "Export details => Excel"
    Exit Sub
    End If
    Loop
    Shell strInput, vbNormalFocus</font face=script>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can't open/find Excel file after export (97)

    In the code as posted, strInput is not assigned a value. Make sure that you assign it a complete path and file name.

    You can also use DoCmd.OutputTo; this has an argument AutoStart - if you set it to True, the exported file will be opened automatically.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Can't open/find Excel file after export (97)

    Thanks, Hans,
    it works perfectly!

    When time multiplies, I'll maybe check out what went wrong with the other method. The path was something like "c:name". So I probably forgot to add the .xls to strInput (and/or Dir()) because when I corrected this, I got another error <img src=/S/flatcat.gif border=0 alt=flatcat width=61 height=21>.

Posting Permissions

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