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

Question

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?

Answer 1

Hi Rose,

Greetings! Thank you for posting to Microsoft Community.

You can reference below article to import excel to access.

Export Excel data to Access with VBA (Images and Code) (askeygeek.com)

To get more help for vba code, stack overflow is specifically about programming.

Newest 'vba' Questions - Stack Overflow

Best Regards,

Snow Lu

Answer 2

The Export Excel Data to Access with VBA is great but the link to my spreadsheet is always changing depending on the Quote # associated with it.

I have a form Quote Logs where the database gives an autonumbered record for each new quote generated. There is a link to the file folder containing the Excel Workbook is held in this folder under the appropriate name (e.g., file:///\kamsrv\Quotes\krew%20industrial\2023\8212; and the Excel workbook is called Quoting Master 8212).

How do I get the code to recognize the link in the Folder Link field box and to open the Quoting Master document within it?

Or how do I put a button on the Excel spreadsheet that will grab the necessary array to upload into the Access Database tblQuoteData when pressed by my Sales Manager upon completing the spreadsheet?

Answer 3

For the first question, you can use workbooks.open to get the 8212 file.

Workbooks.Open ("filepath\8212.xlsx") 

For the second question, you can add a column in excel, then set a rule to grab the necessary array.

If rule then

Range(cells(i,j),cells(k,l)).select

'Selection.upload

end if

Answer 4

Will that change work for each instance when the quote number changes? (e.g., 8212 or 8118)

The array would never change and the spreadsheet name within the workbook would never change just the file name itself.

Answer 5

Filename=quote number

Workbooks.Open ("filepath\"&filename&".xlsx")