Results 1 to 9 of 9
  1. #1
    CobraKai is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2019
    Posts
    5

    How to keep Excel file open until it refreshes?

    Hello,

    I am looking for help to keep my Excel file open for a couple of seconds till it refreshes. VBA works correctly if I run step by step but it shut down Excel too quickly, it has no chance to refresh table.

    Can you please help me how to do it?

    I tried with wait but it didn't work, disabling background refreshes check box didn't help.

    Code:
    Function OpenExcelfromAccess()
        Dim MyXL As Object
        
        Set MyXL = CreateObject("Excel.Application")
        With MyXL
            .Application.Visible = True
            .Workbooks.Open "Path"
            .ActiveWorkbook.refreshall
           ????????wait for 5 seconds???
            .ActiveWorkbook.Close SaveChanges:=True
        End With
        MyXL.Quit
    
    End Function


  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    if you're going to close the workbook, why refresh it if nobody is gonna see it?

  3. #3
    CobraKai is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2019
    Posts
    5
    Quote Originally Posted by ranman256 View Post
    if you're going to close the workbook, why refresh it if nobody is gonna see it?
    Access will see Excel's content. I want to work with latest information that is why refresh needed.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not sure if this would interest anyone, but when I made many calls to that function during a series of vba executed sql statements I found I could shorten the total delay somewhat by passing fractional values to it if the interval was declared as a variant. That way it will handle anything short of null without error.
    Not a big deal I guess but if you can get by with say, 30 half-second calls, it saves a wee bit of time. Being a variant, you don't have to test for > 0 or any such thing.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    CobraKai is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2019
    Posts
    5
    Hello,

    First link works correctly. I don't understand why it works but I am happy . There is not too much difference in new macro, looks like autosave with close is not working (I am not sure).

    Code:
    Function OpenExcelfromAccess()
        Dim MyXL As Object
        
        Set MyXL = CreateObject("Excel.Application")
        With MyXL
            .Application.Visible = True
            .Workbooks.Open "Path"
            .ActiveWorkbook.refreshall
            .ActiveWorkbook.Save
            .ActiveWindow.Close
        End With
        MyXL.Quit
    End Function
    Second doesn't works because Access macro can't handle wait as far I read.

    Thank you very much for help.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    That's not a macro (at least not when it comes to Access). It's a function, which is a type of procedure. I'm not splitting hairs here. If you say in a post that your "macro" isn't working without posting code, you'll get comments like "I don't use macros..."
    It looks like your code is a mish-mash of Excel and Access code. From Access, you can't simply refer to .ActiveWorkbook when it isn't.
    Here's where you should get your code for working with Excel from Access (or vice versa)
    http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

  8. #8
    CobraKai is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2019
    Posts
    5
    Thank you for help.

    I scan through your link quickly but I need a liitle more help.

    Could you please help me where to find a solution for my problem? I looking for a possibility to update an existing table in Access. Data of table are coming from an Excel file and its content changes on daily base. I want to update my table only with new data, refresh must be performed by a command button.

    My macro works fine now but I would like to have a professional solution if available.

    Thank you for help in advance.

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by CobraKai View Post
    Thank you for help.
    I scan through your link quickly but I need a liitle more help.

    Could you please help me where to find a solution for my problem? I looking for a possibility to update an existing table in Access. Data of table are coming from an Excel file and its content changes on daily base. I want to update my table only with new data, refresh must be performed by a command button.
    You are changing what the stated problem is. Advice has been given based on your original post, which was attempting to use what's known as Automation. Now you are expressing what you have and what you need, which is usually the best way to start off.

    There is more than one possible approach to a solution. Which you prefer depends on your situation and comfort with vba code (not macros).

    1) You can link to a sheet as a data source and use it as you would a table in Access. Some prefer to use append queries to copy this data into a properly formatted table and then work from the table. This seems to alleviate issues related to how Excel can mess up data types when linked or imported into Access. A linked sheet always reflects the up to date data. If you use a secondary table, you set up 1 or more fields to not allow dupes so that you don't duplicate records and just run an append query to copy records from the workbook to the table. Regardless, you can work from the linked spreadsheet or the table in a normal fashion as you normally would. Little or no code is required regardless.

    2) you can use Automation to manipulate and/or move data between Access and Excel. The link I provided is for this approach and is in tune with your first post. However, it would require a lot more than a "quick" look on your part and uses of a lot of code.

    You probably should pursue the first option, which would allow you to use button(s) that can call your macros. Start by looking up how to link a spreadsheet if you don't know how to do that.

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

Similar Threads

  1. Replies: 1
    Last Post: 02-03-2019, 09:16 AM
  2. Replies: 6
    Last Post: 09-14-2017, 07:31 AM
  3. Excel file can't open again ????
    By ksor in forum Programming
    Replies: 3
    Last Post: 06-08-2017, 08:25 AM
  4. Add logic to see if excel file is already open
    By allenjasonbrown@gmail.com in forum Programming
    Replies: 5
    Last Post: 10-18-2012, 12:42 PM
  5. How to open a word/excel/other file in vba
    By pkstormy in forum Code Repository
    Replies: 0
    Last Post: 08-28-2010, 10:36 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