Share via

Export Excel Data via Macro Button on Excel - Import to Access

Anonymous
2023-02-10T17:37:07+00:00

Currently I have the following set up in a Master Excel document: (doc name: Quoting Master)

This spreadsheet is used to calculate Quotes. Each Quote has its own Quote # and the Excel document is renamed for each Quote generated (doc name: Quoting Master 8202) and saved in its Quote Folder (folder name: Quotes/"Customer Name"/"year"/"Quote #")

I'd like to create the following:

  1. A button on the Quoting Master template so that when the document is completed, the button can be pressed, and the data is exported/imported into Microsoft Access.

Quoting Master document is kept in file folder Quotes/01-Template/Quoting Master.xlsm

Access database is kept in a different folder database/Kam Wire ERP Database.accdb

The data will be put into a table: tblQuoteData. This way the information can be pulled based on the Quote Form.

The Quote Data button on the Quote Logs Form will then pull a query pertaining to this spreadsheet data.

Is there an easy way to do this?

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

8 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-02-13T17:40:07+00:00

    Where would this fall in the code from the site you advised me of?

    Also, would this go in the VBA on the Excel spreadsheet/workbook or in Access?

    0 comments
  2. Anonymous
    2023-02-13T17:52:13+00:00

    You can run VBA in access to open excel file.

    Access VBA - Open Excel File - Automate Excel

    0 comments
  3. Anonymous
    2023-02-13T18:04:22+00:00

    Maybe I'm not being descriptive enough...

    I use an Excel Template (Quoting Master XXXX) within the File Folder (01-Template). It is then copied and saved in the proper year (2023) and then renamed based on the Quote # associated to it (Quoting Master 8212). This changes for each new quote generated by Access.

    I want to create a Macro Button on the Excel Template that would pull the data array on the Access Tab within the Workbook and import that data into the Access Database table (tblQuoteData).

    Then Access would pull the necessary information from tblQuoteData to run Queries and Reports.

    Not sure if this rather large and confusing CODE you sent me will work for what I want it to do.

    0 comments