Results 1 to 2 of 2
  1. #1
    accessnewbie is offline Novice
    Windows XP Access 2010 (version 14.0)
    Join Date
    Oct 2009
    Posts
    20

    Save As dialog box exporting Excel file

    Happy Friday, all. I'm one of those people who only knows enough VBA to be dangerous. I have a query in my Access database and I would like to allow the users to save it using a Save As dialog box. Below is the code I am using. I like it and it works, but I have one problem I'm hoping I can easily fix.

    When I open the Excel spreadsheet after having saved it, the cells that were null in the Access query act like spaces. For example, when I have my cursor in one of the column header cells and hit Ctrl+down arrow, I go to the very bottom of the list. This is what happens even if there are cells in the column that are empty. I would like it to instead go to the next value in the column instead of all the way to the end. At least that is how I like to use it. Perhaps it's better the way it's actually working? I'm open to trying to undertsand the benefits of both, but was wondering if there was a way to make it act like it would if I manually exported the query to Excel. Not to mention, I like the formatting better this way as well, but asking my users to manually export the file isn't an option. Perhaps OutputTo isn't the best idea either. Thanks for any and all assistance!!
    -----
    Dim fd As FileDialog
    Dim Title As String
    Dim vrtSelectedItem As Variant


    Set fd = Application.FileDialog(msoFileDialogSaveAs)
    With fd
    .AllowMultiSelect = False
    .Title = "Save File"
    .initialFilename = ".xls"
    If .Show = True Then
    DoCmd.OutputTo acOutputQuery, "Report", acFormatXLS, .SelectedItems(1)
    Else
    MsgBox "File Not Saved", vbOKOnly, "File Not Saved"
    End If
    End With

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I tested this code and confirmed the export issue. I then went to each 'blank' cell and hit DELETE. Now the ctrl+up/down works as you want.

    Might not be anything can be done by use of OutputTo method, other than to 'scrub' the worksheet after export. Since I don't know what is really in the cells, no idea how to code for removing it.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Exporting a Access Report to an Excel File
    By Coffee in forum Import/Export Data
    Replies: 2
    Last Post: 07-28-2014, 11:32 AM
  2. Need help exporting large Access file into Excel
    By phidelt in forum Import/Export Data
    Replies: 2
    Last Post: 02-27-2013, 06:14 PM
  3. Export Queries to Excel tabs with Save as Dialog box
    By deepanadhi in forum Import/Export Data
    Replies: 1
    Last Post: 05-16-2012, 10:36 AM
  4. Exporting to formatted Excel file
    By Xerin in forum Access
    Replies: 4
    Last Post: 10-21-2011, 03:33 PM
  5. Exporting to Excel Date Stamped File
    By BED in forum Import/Export Data
    Replies: 1
    Last Post: 08-07-2010, 05:53 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums