Results 1 to 2 of 2
  1. #1
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108

    Need to use a macro (not the wizard) to open and Excel file to import into access

    Hello, I am trying to find a way to make a button or some system that in the end will:
    1. open a pre defined table and append the data to and archive table then clear all data
    2. open an pre defined Excel file and import to the data into the cleared table


    I have been trying to research this, but I do not know sql so I don't know if the examples I am reading will work for what im looking for. I didn't know if I could use Access's macro builder to create what I need, or how I would need to go about it. I could not find any examples that said they were doing what I am looking for so I was hoping I could get some guidance.

    Im not sure what info would be needed, but let me know.



    Thanks,
    Will

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    A macro can be most, if not all, of what you need.
    - You can append data to a table using an Append Query. Just use the OpenQuery action in Access Macros to run any query (including Action Queries like Append Queries)
    - You can delete all the table from a Table by using the RunSQL Action in a Macro and use a line like: "DELETE * FROM TableName"
    - You can import an Excel file into Access using the TransferSpreadsheet Action in a Macro

    You can then run this Macro from a Command Button.

    If for any reason you need to make the process more dynamic, a good start is to create a Macro that works on an example, and then use the "Convert Macros to Visual Basic" functionality, to convert it to VBA code, where you can make it more dynamic (i.e. not static file names, etc). Then just have your Macro run that VBA code.

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

Similar Threads

  1. Open Import Excel from Macro
    By shepa006 in forum Macros
    Replies: 3
    Last Post: 04-17-2015, 12:58 PM
  2. Replies: 3
    Last Post: 10-08-2014, 11:05 AM
  3. Import Excel File using a Macro
    By smakkiee in forum Access
    Replies: 6
    Last Post: 04-28-2014, 08:45 AM
  4. Accessing Data Import wizard via a macro?
    By Madraykin in forum Import/Export Data
    Replies: 6
    Last Post: 03-27-2012, 12:34 PM
  5. Import Excel File Using Macro?
    By oregoncrete in forum Import/Export Data
    Replies: 0
    Last Post: 04-05-2011, 12: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