0

Currently I have:

  1. Excel Workbook that contains a spreadsheet called "Access" which will contain data required to populate information in my Access database based on quotes generated. Excel Workbook

  2. Access Database that provides the following: 2a. Quote Logs - autonumber generated based on the next quote created by Quote Manager 2b. Folder Link (text field) - contains the folder link on the server that holds the Excel spreadsheet
    Quote Logs Example: Quote 8212 Folder Link: file:///\kamsrv\Quotes\krew%20industrial\2023\8212 Quoting Master 8212 (Excel Workbook)

What I want to do is follows:

  1. Create a Macro on the Excel spreadsheet "Access" to pull the data within the selected array and export it into Access Database - tblQuoteData

  2. Each time a new quote is generated, the Excel spreadsheet has the associated Quote # at the end of it. This will need to be reflected in the VBA code for the Macro Button on the spreadsheet. How do I do this?

Any help would be greatly appreciated. Thank you

I've tried several different things:

  1. Launch database, create a table tblQuoteData (ensuring the column headers match the headers in Excel), create the Macro via Access to ImportExportSpreadsheet but this did not work when the spreadsheet gets a new name based on the quote number.

  2. Requested help through Microsoft Community, was pointed here instead.

  3. Tried to create my own macro in Excel but doesn't send to Access due to error on the code: problem with this code is that my Excel document and Access database are not kept in the same file folder on my server

Code Used

Rose Lavoie
  • 1
  • 1
  • For option 1, make sure spreadsheet has generic name, overwrite/rename new sheet to this generic name. Import process should then be stable. For option 3, if you want help then edit question with your code, not a link to sample code, as well as the error encountered. Modify code to deal with the different folder locations. – June7 Feb 13 '23 at 20:04
  • Hi June7 - I added a screenshot of the Code I tried... in Access. What I'd like to do is a Button to the Excel Spreadsheet and have it import the data into my Access tblQuoteData so that I can pull from that table and run Queries and Forms off it. – Rose Lavoie Feb 14 '23 at 20:23
  • Code should be posted as formatted text, not image. Run queries and forms where - in Access? – June7 Feb 14 '23 at 20:32
  • I tried typing out the code but it keeps asking me for proper formatting and I tried every way to do that, and Stack won't accept it. And yes, I run my Queries and Forms out of Access. – Rose Lavoie Feb 14 '23 at 20:35
  • Click `{}` icon then copy/paste code where it says "enter code here". – June7 Feb 14 '23 at 21:07
  • ignoring the need to tighten the question to a minimal reproducible example and assuming that the wizard for exporting data from Excel and the Wizard for importing data to access both failed then I expect you need to import the data cell by cell from each Excel file into Access. see the following links for a start: https://learn.microsoft.com/en-us/office/vba/api/access.application.filedialog https://www.datanumen.com/blogs/import-files-within-folder-access-table/ https://stackoverflow.com/questions/5729195/how-to-refer-to-excel-objects-in-access-vba – mazoula Feb 18 '23 at 06:45

0 Answers0