Results 1 to 9 of 9
  1. #1
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108

    Open multiple Excel files

    I have an Access app which draws data from linked Excel files.
    These Excel files are queries from an ODBC db connection.

    For whatever reasons, Access is unable to see some of the db tables that Excel is able to see from my ODBC connection so the only way I have to get to the data is linking Access to these Excel files (my IT department was unable to help with that strange ODBC issue).

    The problem I have, is that these Excel files do not refresh in the background when they are not open so when I need to draw data from them I also need to open them first to refresh the data they contain.

    I am trying unsuccessfully to find a code that works...

    I've tried the following code to open one file and get a "user-defined type not defined" error

    Code:
    Dim AppExcel As Excel.ApplicationDim MyWorkbook As Excel.Workbook
    Set AppExcel = CreateObject("Excel.Application")
    Set MyWorkbook = AppExcel.Workbooks.Open("C:\Users\xxxx\OneDrive\Documents\document_name.xlsx")
    AppExcel.Visible = True
    Set AppExcel = Nothing
    Set MyWorkbook = Nothing
    I need to open 3 Excel files "on clic" before running a macro and opening a report (the macro and report work).

    These Excel queries are set to refresh on open though I am not 100% convinced that this actually works, ideally if at all possible, the script would activate the "refresh data" button in Excel, then save the files and close them but that's the cherry on the cake, if they just open "on click" that will already help.



    Thank you

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    this is not true. you do not have to open them.
    If the xl file is linked, and you overwrite it with a new file, then the new data will show w/o opening.
    I do this all the time. Now you can use a refresh...

    currentdb.tabledefs("xlTable").RefreshLink

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Try:
    Code:
    Dim appExcel As Excel.Application
    Dim myWorkbook As Excel.Workbook
    
    
    Set appExcel = CreateObject("Excel.Application")
    appExcel.Visible = False
      
    Set myWorkbook= appExcel.Workbooks.Open("C:\Users\xxxx\OneDrive\Documents\document_name.xlsx")
    myWorkbook.UpdateLink
    myWorkbook.Save 
    myWorkbook.Close
    
    'next file
    Set myWorkbook= appExcel.Workbooks.Open("C:\Users\xxxx\OneDrive\Documents\document_name2.xlsx")
    myWorkbook.UpdateLink
    myWorkbook.Save 
    myWorkbook.Close
    'third file
    Set myWorkbook= appExcel.Workbooks.Open("C:\Users\xxxx\OneDrive\Documents\document_name3.xlsx")
    myWorkbook.UpdateLink
    myWorkbook.Save 
    myWorkbook.Close
    
    
    appExcel.Quit
    Set myWorkbook = Nothing
    Set appExcel = Nothing
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  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
    You also might try using a query as shown in Ken Snell's site:

    Read Data from EXCEL File via Query (SQL Statement)

  5. #5
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    Thanks for the responses, I have to take a couple weeks break on that project, I'll test mid August and revert then

  6. #6
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    Quote Originally Posted by ranman256 View Post
    this is not true. you do not have to open them.
    If the xl file is linked, and you overwrite it with a new file, then the new data will show w/o opening.
    I do this all the time. Now you can use a refresh...

    currentdb.tabledefs("xlTable").RefreshLink

    The key here is "you overwrite it with a new file".

    I agree, I do this as well. What I'm saying is that they don't update by themselves in the background. I want to try avoiding updating the Excel files manually before running the Access db for ease of use.

    What I was thinking about is either coding or a macro that will open and save the Excel file before running the commands that do the work in Access. I would also need to delay running these commands to give Excel time to update and save.

  7. #7
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    Thanks Vlad.

    That gives me a "user-defined type" error on the first line. I think I remember reading somewhere that opening Excel from Access needs to be defined within Access options somewhere but I'm not finding it back, could be the reason of this error.

  8. #8
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    Quote Originally Posted by Gicu View Post
    Try:
    Code:
    Dim appExcel As Excel.Application
    Dim myWorkbook As Excel.Workbook
    
    
    Set appExcel = CreateObject("Excel.Application")
    appExcel.Visible = False
      
    Set myWorkbook= appExcel.Workbooks.Open("C:\Users\xxxx\OneDrive\Documents\document_name.xlsx")
    myWorkbook.UpdateLink
    myWorkbook.Save 
    myWorkbook.Close
    
    'next file
    Set myWorkbook= appExcel.Workbooks.Open("C:\Users\xxxx\OneDrive\Documents\document_name2.xlsx")
    myWorkbook.UpdateLink
    myWorkbook.Save 
    myWorkbook.Close
    'third file
    Set myWorkbook= appExcel.Workbooks.Open("C:\Users\xxxx\OneDrive\Documents\document_name3.xlsx")
    myWorkbook.UpdateLink
    myWorkbook.Save 
    myWorkbook.Close
    
    
    appExcel.Quit
    Set myWorkbook = Nothing
    Set appExcel = Nothing
    Replaced

    Code:
    Dim appExcel As Excel.Application
    Dim myWorkbook As Excel.Workbook
    With
    Code:
    Dim appExcel As Object
    Dim myWorkbook As Object
    this gets rid of the type error.

    Code:
    myWorkbook.UpdateLink
    does not do what I want but
    Code:
    myWorkbook.RefreshAll
    works.

    However, the next sequence
    Code:
    myWorkbook.Save
    creates a problem because it does not allow for the data refresh to complete and create a warning message in Excel which prevents the code from continuing.

    Is there a way to either tell Excel to complete the previous command before running the next ones, Save, then Close?
    Or, is there a way to put a timer between each command to allow them to complete before going to the next?

    I tried
    Code:
    Application.OnTime Now() + TimeValue("0:00:10"), "myWorkbook.Save"
    but I get a "method or data member not found" error, seems to be for Excel only.

  9. #9
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    I fixed the timing issue of giving Excel time to refresh the data before saving and closing each file by adding a timed using the following script.
    I would have preferred a code that would detect when Excel is done refreshing but this works great too, I just had to time how log it typically takes to refresh, add a couple seconds for safety and voila.

    Thanks for the help.

    https://www.fmsinc.com/MicrosoftAcce...idDoEvents.asp

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

Similar Threads

  1. Replies: 1
    Last Post: 04-10-2019, 11:39 AM
  2. Multiple Excel Files
    By DukeRollo in forum Import/Export Data
    Replies: 5
    Last Post: 11-30-2017, 06:41 PM
  3. Replies: 2
    Last Post: 08-27-2013, 06:29 AM
  4. Linking multiple Excel files - PLEASE help!
    By studor63 in forum Import/Export Data
    Replies: 4
    Last Post: 09-11-2012, 01:24 PM
  5. Replies: 4
    Last Post: 06-14-2011, 07:19 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