Results 1 to 8 of 8
  1. #1
    DB2010MN26 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    37

    Close and Save Excel file already opened in Access

    I've searched all around and can't seem to find exactly the answer I'm looking for regarding what I'm trying to do.

    I have a macro I've setup within access that I want to essentially open an excel file every time I run it, pause for 10 seconds, then save and close the excel file. Now I'm guessing this isnt very hard to do via VBA code but I'm not well versed yet in it. Heres how I have it setup so far... I can't seem to get the save and close part down.

    Action: RunApp - I set the arguement to open excel and my file, this seems to work fine.

    Action: RunCode - I have created a module with the following public function I found via post on this forum that lets me set the pause time:



    Code:
    Public Function Pause(NumberOfSeconds As Variant)
    On Error GoTo Err_Pause
        
        Dim PauseTime As Variant, Start As Variant
        
        PauseTime = NumberOfSeconds
        Start = Timer
        Do While Timer < Start + PauseTime
        DoEvents
        Loop
        
    Exit_Pause:
        Exit Function
        
    Err_Pause:
        MsgBox Err.Number & " - " & Err.Description
        Resume Exit_Pause
        
    End Function
    I then enter the arguement Pause(seconds)

    Now the next part is where I get hung up, I can't seem to find a way to get the excel file (and application) to save and close. Can this be done through the simple macro commands in access or wiill I need to create additional code?

    Thanks in advance

  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
    You don't show any code to open, save, close Excel. Why would you want to open then close in 10 seconds? Do you want to save by another name?

    Check these for starters:
    http://www.actuarialoutpost.com/actu...d.php?t=229928
    http://stackoverflow.com/questions/1...-all-workbooks
    http://forums.aspfree.com/microsoft-...el-414974.html
    Last edited by June7; 01-19-2012 at 06:49 PM.
    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
    DB2010MN26 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    37
    I used the macro action RunApp to open my excel file, so it was outside of VB. The reason it needs to wait 10 seconds is that I have queries that pull info within the excel file that need around 5-10 seconds to retrieve the data. I would like the file to simply save under its existing name. Reason being is that I have the excel file linked as a table within the access database.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You have data in Access, Excel pulls from that data, you link to Excel. Going in circles?

    I suggest use VBA instead of macro. I don't use macros.
    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.

  5. #5
    DB2010MN26 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    37
    I have data in excel that is pulled in through queries form a data source I don't have access to via Access. So I'm forced to use excel to initially pull updates to the data..... So I'm trying to create a recurring process whereby The excel file opens each day and refreshes the data, then access pulls it in to tables..

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Okay, Excel pulling from some other db, Access links to Excel. Still suggest put all code in VBA.

    Excel has to actually import the data, can't just link to it? If Excel can see the data source, why can't Access?
    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.

  7. #7
    DB2010MN26 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    37
    Excel is pulling from an online data sorce using custom API, its unfortuately not available through access hence why I have to use excel to pull the data.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So have you reviewed the referenced links and attempted some code? Post for analysis if you still have issue.
    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. Replies: 13
    Last Post: 10-12-2011, 12:48 PM
  2. Can not close pop up form after second pop was opened
    By snoopy2003 in forum Programming
    Replies: 2
    Last Post: 03-09-2011, 02:56 AM
  3. Access Form- Save/Close Button error
    By Ashe in forum Forms
    Replies: 3
    Last Post: 02-10-2011, 01:01 PM
  4. Replies: 1
    Last Post: 09-27-2010, 10:10 AM
  5. Open / Save as Excel File and specifiy format
    By jaykappy in forum Access
    Replies: 8
    Last Post: 03-24-2009, 03:26 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