Results 1 to 5 of 5
  1. #1
    rafpac is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2022
    Posts
    2

    Need help on VBA command to open the Export to Excel Spreadsheet prompt


    Hi All, This is an excellent place and I have learned so many things here. Unfortunately this is the first issue I have not been able to figure out. I have an access front end which was created on Access 365. Using VBA I have disabled all ribbons. The users only have access to forms and a report. The users need to export this report to an excel file. I have found many VBA scripts that allow export to excel file but you have to script out the filename and location in the local computer in advance. However my users are many and I cannot set a common filepath across all computers. The next best solution would be to give them some control over the exporting function. On Access 365 there is an "Export to excel spreadsheet button" in the External Data Ribbon which opens up an export window. Is there a way I can just I can just add a button to the report and script a "On Click" event to execute the script and open this very dialog box for the users? Your help is much appreciated. Let me know if you need any further details. All my users are using a Front End connected to a DB on the network. Thanks. Raf

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,521
    Maybe you want:

    DoCmd.RunCommand acCmdExportExcel
    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.

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    3,473
    Using the FileDialog in conjunction with your many scripts would make it very easy to prompt each user for the file name and location, no need to have it preset as you state.
    https://stackoverflow.com/questions/...ing-access-vba
    https://software-solutions-online.co...edialogsaveas/

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    11,365
    Not that I disagree with using file or folder dialog as proposed by Vlad, but out of curiosity I researched and found a couple of interesting things. One is that invoking ribbon commands in code seems to be called repurposing (that seems like another M$ misnomer) but it involves xml and vba. Another is this this forum
    https://www.add-in-express.com/forum...FID=5&TID=2581

    In the 4th post there is a link which I thought might lead to an article, but it was a download for an add-in so do so at your own risk. The thread is interesting though. The point of it might be that while the file/folder dialogs may suffice here, they are of no help with other ribbon commands so the add in might be useful. I'm not going to try it simply because at this point I have no interest in exploring the subject further.
    Last edited by Micron; 11-11-2022 at 09:52 AM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    rafpac is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2022
    Posts
    2
    Thanks for all the response guys but June7 was on the dot.

    DoCmd.RunCommand acCmdExportExcel opens exactly what I need.

    Sweet. Thanks.

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

Similar Threads

  1. Dlookup in excel spreadsheet export.
    By JRodko in forum Import/Export Data
    Replies: 6
    Last Post: 01-21-2021, 03:19 PM
  2. Replies: 6
    Last Post: 07-10-2019, 07:34 PM
  3. Export Data to an Existing Excel Spreadsheet
    By wcrimi in forum Import/Export Data
    Replies: 16
    Last Post: 09-09-2018, 03:50 PM
  4. Export to spreadsheet with no Excel present
    By jabarlee in forum Import/Export Data
    Replies: 4
    Last Post: 08-14-2018, 02:37 PM
  5. Automate Export of a table to an Excel Spreadsheet
    By RayMilhon in forum Programming
    Replies: 3
    Last Post: 01-23-2018, 08:08 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