Need to export data to excel, then link that workbook back to ms access

[mkaeser]

Hello I have a question about the best way to go about linking an excel workbook to an ms access table? I have researched hyperlinks and attachments and ole objects and I am not sure what is the best option. I believe hyperlinks will be best, because the attachments will bloat the database, but I am not sure how to go about doing that in vba? My process is such:

1)Users enter project information
2)Users enter the sample information for each project
3)The project and sample information is combined by a query
4)The user clicks a button to print forms, which runs the query and opens an excel workbook template.
5)The data is exported to a specific sheet in the workbook
6)Using vba in excel, the data is used to populate cells in different sheets depending on critera.

After all of this is accomplished, I need the user to SAVE AS the excel workbook and it needs to be "attached" to the ms access 'project' table. I am not sure what is the best way to link this back to the database. I would like to automate it with vba if possible, any ideas would be great thanks!

 

[hockyjocky]

I do I think I do something similar to this in the program I am writing. User inputs data and then I push data to excel spreadsheet. Spreadsheet has macros in it which I invoke from Access and they do their magic. Then automate save as to a filename with path I create in code . then create a hyperlink to that file and it is saved in a table along with alias name, properties, description, time created, who created and the full path. Then can simply populate a combo box with links to the reports based on one of the properties of the file. Say, all reports created with a certain date range, or all attached to a certain project. Also of course since you have the full path and filename, it is easy to delete the file when you delete the link, if you want to. Is this kind of what you are wanting to do. You can do all of that with vba code. I created a table to contain all the hyperlink data so that one could have multiple links for multiple criteria. You could simply embed the project id in the links table and then could have multiple reports associated with it.