Results 1 to 5 of 5

Command Button to run import data

  1. #1
    mphaneuf79 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    2

    Command Button to run import data


    Hello all, I have a specific request that is a but above my abilities with Access. A little background, I am in the Navy and I run data analysis and trend analysis. I pull data from another program via an ADHOC and save it as an excel file. I then have to manually copy the data from that saved excel file to the master excel file. The master excel file is linked to my access database to give me the print out of the report and has programmed in various and/or statements to further break down the data. However, at the beginning of each month it takes me a substantial amount of time to save all the different ADHOCs then copy and paste them to their appropriate master excel file. I have no issue with this but I am trying to streamline the process to be able to share the database with other commands in the Navy for use and want to make it more user friendly. Now, what I would like to do is create a command button in Access that when clicked will extract the data from the monthly excel file and copy it to the master file. Is this possible to write VBA for this? Or am I thinking to robust for the project? Thank you for any help you can provide!

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,529
    save the xl file to the same file everytime, like: c:\temp\Data2Import.xlsx (and another archive version)
    link this file as an external linked table,
    build an append query to add the xl data to the main table,
    put this query in macro: mImportXLdata
    put the macro on the button click event.

    then the steps are:
    1. save new XL file to c:\temp\Data2import.xlsx
    2. click button
    done.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,149

    substantial amount of time to save all the different ADHOCs
    How many ADHOC queries do you have to run?


    then copy and paste them to their appropriate master excel file
    How many Master Excel files are there?
    How do yo know which ADHOC queries go with which Master Excel file?

    The Master Excel files are linked to an Access dB? Why isn't the monthly data imported to Access tables?
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  4. #4
    mphaneuf79 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    2
    Right now every month I run 8 ADHOC queries. Each one is saved with different data so it cannot all go into one excel file. Since I am working on process improvement, I am sure in the future there is going to be request for more data and different reports. Which in turn will require more ADHOCs to be created.

    I have the Master Excel files linked to access already. Those master files is what I use to run the reports. I use Access to give me the ability to search the data by date, name, keyword etc... for trend analysis. So the process is just clunky: run the ADHOC on the main program, save as an xls (the main program will not allow me to save as a xlsx file), open the file and convert to xlsx, Excel then crashes, have to reopen file once converted, copy the info and then paste it over to my master xlsm file. Then I can use the data in my database.

    Quote Originally Posted by ssanfu View Post


    How many ADHOC queries do you have to run?



    How many Master Excel files are there?
    How do yo know which ADHOC queries go with which Master Excel file?

    The Master Excel files are linked to an Access dB? Why isn't the monthly data imported to Access tables?

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,149
    So 8 ADHOC queries means 8 Master Excel files?

    Do you delete the data in the Master Excel file(s) or do you keep appending data?


    Is the data triple S (super secret sh** .... err stuff), or could you post an ADHOC query, a Master Excel file and the access dB?
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

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

Similar Threads

  1. Replies: 7
    Last Post: 01-28-2019, 03:33 AM
  2. Replies: 7
    Last Post: 06-29-2014, 11:11 PM
  3. Error Import Command Button
    By kowen091010 in forum Access
    Replies: 4
    Last Post: 12-15-2011, 06:56 AM
  4. Command button to input data in a table
    By wilsgaard in forum Forms
    Replies: 1
    Last Post: 08-07-2011, 06:59 PM
  5. Replies: 1
    Last Post: 07-27-2010, 01:27 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
  •  
Tech Forums: Microsoft Office Forums