Results 1 to 2 of 2

Macro to close open excel sheet

This is a discussion on Macro to close open excel sheet within the Microsoft Access forums, part of the Question Forums category; Hi all, I am trying to use macros in Access to close both an open excel worksheet based on tables ...

  1. #1
    New Member
    Join Date
    May 2010
    Posts
    17

    Default Macro to close open excel sheet

    Hi all,

    I am trying to use macros in Access to close both an open excel worksheet based on tables in Access and the access database prior to updating all my tables from a 3rd party software,

    Part of the 3rd party software functionality is the use of .bat files, Therefore to open the access database or excel workbook is relatively easy.

    My plan was to use the .bat files

    @echo off
    SET datafile=DB reports
    SET macro=Open

    REM ---------------- Do not change below this line ----------------
    ECHO Interface running for %datafile%.
    "C:\Program Files\Microsoft Office\Office12\MSACCESS" "c:\FAS interfaces\%datafile%.mdb" /x %macro%

    The above code will allow me to open my database DB reports, and run the macro called Open in the access database,

    Now the hard bit,

    I need to create a Close Marco, which will see if there is a file call Reports.xls open and if so, close it, without saving, and then close the database DB reports,

    The latter bit is a simple close command, does anyone have any ideas on how to kill the excel workbook prior to closing the database.

    Many thanks in advance.

    DM

  2. #2
    Board Regular Trevor G's Avatar
    Join Date
    Jul 2008
    Location
    Tamworth, Staffordshire
    Posts
    5,782

    Default Re: Macro to close open excel sheet

    Quote Originally Posted by dreammaker View Post
    Hi all,

    I am trying to use macros in Access to close both an open excel worksheet based on tables in Access and the access database prior to updating all my tables from a 3rd party software,

    Part of the 3rd party software functionality is the use of .bat files, Therefore to open the access database or excel workbook is relatively easy.

    My plan was to use the .bat files

    @echo off
    SET datafile=DB reports
    SET macro=Open

    REM ---------------- Do not change below this line ----------------
    ECHO Interface running for %datafile%.
    "C:\Program Files\Microsoft Office\Office12\MSACCESS" "c:\FAS interfaces\%datafile%.mdb" /x %macro%

    The above code will allow me to open my database DB reports, and run the macro called Open in the access database,

    Now the hard bit,

    I need to create a Close Marco, which will see if there is a file call Reports.xls open and if so, close it, without saving, and then close the database DB reports,

    The latter bit is a simple close command, does anyone have any ideas on how to kill the excel workbook prior to closing the database.

    Many thanks in advance.

    DM
    Here is a line of code to kill a workbook if it exists.

    If Dir(CurrentProject.Path & "\Peer Review Country Analysis Report.xls") <> "" Then Kill CurrentProject.Path & "\Peer Review Country Analysis Report.xls"
    Trevor
    I am on a learning curve of life, I know a little but like to share what I have learnt with others.
    I am using Microsoft Office 2003 to 2013
    Please remember everyone here is a volunteer, so if you have had a reply to your thread be courteous and acknowledge this.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com