Results 1 to 6 of 6
  1. #1
    scoe is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2013
    Posts
    91

    Save and close Excel Workbook

    Thank you for the help I have had in the past, however I have another problem trying to write a module. I have a macro that runs four queries that open in separate excel workbooks, one after the other. What I have been trying to write is a simple module that will then save and close the spreadsheets in templates to be used at a later time. At present you have to click save and close manually and I don't really want that to be the case. The code I am presently trying, and isn't working is

    [Public Function SaveChanges()

    appExcel.Workbooks("G:\Databases\Working Copies\SC\ASC\ASC Template.xls").Close SaveChanges:=TrueEnd Function]

    I have tried different ways posted on the net but no-one seems to be trying to do exactly what I want and I dont seem to get the right combination of coding.

    Any hlp would be greatly appreciated.

  2. #2
    Dal Jeanis is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    To help you do this, we have to see the part of the code that actually created one of the workbooks. How did you get hold of the excel app, create the file, and so on.

    The syntax for closing is dependent on what it is that you're closing.

  3. #3
    scoe is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2013
    Posts
    91
    Hi

    Thank you for getting back to me, I've converted the macro to VBA and the code is as follows:

    [Function ALF()
    On Error GoTo ALF_Err
    DoCmd.SetWarnings False
    DoCmd.OutputTo acOutputQuery, "ALF Student AOS missing ASC", "ExcelWorkbook(*.xlsx)", "G:\Databases\Working Copies\SC\Outstanding LA Reports\ASC\ASC Template.xlsx", True, "", , acExportQualityPrint

    DoCmd.SetWarnings False
    DoCmd.OutputTo acOutputQuery, "ALF Student AOS missing LA CPS", "ExcelWorkbook(*.xlsx)", "G:\Databases\Working Copies\SC\Outstanding LA Reports\CPS\CPS Template.xlsx", True, "", , acExportQualityPrint
    DoCmd.SetWarnings False
    DoCmd.OutputTo acOutputQuery, "ALF Student AOS missing LA SCI", "ExcelWorkbook(*.xlsx)", "G:\Databases\Working Copies\SC\Outstanding LA Reports\SCI\SCI Template.xlsx", True, "", , acExportQualityPrint
    DoCmd.SetWarnings False
    DoCmd.OutputTo acOutputQuery, "ALF Student AOS missing LA TEC", "ExcelWorkbook(*.xlsx)", "G:\Databases\Working Copies\SC\Outstanding LA Reports\TEC\TEC Template.xlsx", True, "", , acExportQualityPrint

    ALF_Exit:
    Exit Function
    ALF_Err:
    MsgBox Error$
    Resume ALF_Exit
    End Function]
    Each query runs and opens the workbook template and remains open, what I want it to do is stay open for a few seconds and then save and close. Thank you again for your time.

  4. #4
    Dal Jeanis is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Try switching the fifth parameter from "True" to "False" and see what happens.

    That parameter should be "AutoStart application". If you don't start a new copy of the app, then the app shouldn't stay open after the export, if I understand the parameter correctly.

  5. #5
    scoe is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2013
    Posts
    91
    Thank you, I have done that and it doesn't open but saves the file to the location, however I would have liked it to open for a few seconds before closing but don't seem to be able to get t to do that. As it stands though it will work thatnk you again.

  6. #6
    Dal Jeanis is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    In order to have that happen you would need to open Excel a different way using VBA. It's nontrivial, but not terribly difficult either. I wouldn't do it for a cosmetic effect, though, I'd just create a message form that opened for a few seconds and then shut itself on a timer.

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

Similar Threads

  1. Runtime error 1004 - Save method of workbook failed
    By captdkl02 in forum Programming
    Replies: 2
    Last Post: 01-03-2013, 05:53 AM
  2. Replies: 4
    Last Post: 12-17-2012, 01:21 PM
  3. Close and Save Excel file already opened in Access
    By DB2010MN26 in forum Programming
    Replies: 7
    Last Post: 01-19-2012, 06:50 PM
  4. Link to Excel Workbook
    By Shelly9633 in forum Access
    Replies: 2
    Last Post: 01-06-2012, 10:43 AM
  5. Save and close
    By brobb56 in forum Forms
    Replies: 4
    Last Post: 10-10-2011, 03:07 PM

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