Results 1 to 5 of 5
  1. #1
    Nanuaraq is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    77

    Possible to open excel file from Access macro?

    Is it possible to run a macro in Access that opens a particular excel file?

    My problem is, I collect data via Excel from a system that is otherwise inaccessible to me. Then I import that data into Access. Strange but there you have it.


    Anyway, this means the user must remember to open the excel file and then close&save in order to have updated data for the Access queries. This is an inconvenience. Is it perhaps possible that a macro in Access may open the excel file so the user does not have to remember to do this?

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I don't understand. If you are getting your excel file from an external source why do you need to open the file, then close and save it?

    I don't really do things with macros, I do it with code but if you're trying to open an excel file with the same file name all the time it would be in it's most basic form:

    application.followhyperlink <path and file name here>

    you would just have to supply the correct path and file name as a string

    i.e. application.followhyperlink "c:\test\testexcel.xlsx"

  3. #3
    Nanuaraq is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    77
    Quote Originally Posted by rpeare View Post
    I don't understand. If you are getting your excel file from an external source why do you need to open the file, then close and save it?

    I don't really do things with macros, I do it with code but if you're trying to open an excel file with the same file name all the time it would be in it's most basic form:

    application.followhyperlink <path and file name here>

    you would just have to supply the correct path and file name as a string

    i.e. application.followhyperlink "c:\test\testexcel.xlsx"
    The need to open and close the excel file comes from the data being only accessible to me via excel. Excel has a link which updates the data upon opening the excel fil. This is because our organization operates on a file and document management system which I have been given acces to via a plugin to Excel. Therefore I need to open the excel file, so that data updates, and then close it so the data is saved. This way the data becomes readable to Access.

    Anyway I tried your solution. It works! I am very enthusiastic about this as this is the first time I've attempted any sort of code that was not a macro. Thank you.

    To test this, I made a form where the code is executed on close. So the excel file opens when the form is closed. But I would like to simply make the code execute by means of a macro. How would I go about that?

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    just put a button on a form and in the ON CLICK event of the button use the code to open the file, or better yet copy the 'live' version to a folder where only your application can see it, then you shouldn't have to open it at all.

  5. #5
    Nanuaraq is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    77
    Quote Originally Posted by rpeare View Post
    just put a button on a form and in the ON CLICK event of the button use the code to open the file, or better yet copy the 'live' version to a folder where only your application can see it, then you shouldn't have to open it at all.
    Good idea with the button, but I'm looking for something that works automatically, and a form and autoexec macro together can do just that. Anyway, I didn't understand your last comment?

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

Similar Threads

  1. Replies: 6
    Last Post: 09-14-2017, 07:31 AM
  2. Replies: 1
    Last Post: 08-18-2016, 11:44 AM
  3. Open Excel from Access using a macro?
    By Cecile Desphy in forum Macros
    Replies: 2
    Last Post: 07-11-2016, 06:20 PM
  4. Replies: 1
    Last Post: 11-05-2015, 12:13 PM
  5. Replies: 3
    Last Post: 10-08-2014, 11:05 AM

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