Every day I create an Excel spreadsheet for work. I use an Access program which has external links to a couple Excel reports, and a couple *.csv* reports. After I run the Access macro, I opened up an Excel file, Refresh All, and then run a macro in there which is linked to the Access program. This creates my final spreadsheet.
1. Is there any need to use the Linked Table Manager in the Access program to update links every single day if the file name for the report as well as their location is never changing? The only thing that changes is the data within the reports.
2. When I run the macro in Access, it goes through dozens of queries and tables to do whatever is doing. Is there an easy way to see all the queries and such in a design view without having to click on every single one to open it? Basically, I would like to be able to paste every single VBA code or formula from the access file onto a word document so I can look it over and try to make improvements.
3. My final Excel file does have pivot tables, is saved macro-free, and as a shared workbook in a network folder. My team needs to be able to make manual updates to the file at any time, including at the same time, without any hassle. When we generate this file each day we open the previous day’s file, ‘Accept All Changes’ and take it out of Shared Mode to use it as a *.csv* file for the Access program.
The final Excel file includes two tabs with pivot tables.
Are there any recommendations on a better way to save the file? My team needs to be able to open the file from a network folder and make edits to it. We also provide the file to our customers either through email or by placing a copy into another network folder for them to use. I want our customers to be able to manipulate the file and the pivot table to change filters and dates, add columns, etc. But I don’t want their changes to be saved unless they save a copy of the file for themselves.
1. Is there any need to use the Linked Table Manager in the Access program to update links every single day if the file name for the report as well as their location is never changing? The only thing that changes is the data within the reports.
2. When I run the macro in Access, it goes through dozens of queries and tables to do whatever is doing. Is there an easy way to see all the queries and such in a design view without having to click on every single one to open it? Basically, I would like to be able to paste every single VBA code or formula from the access file onto a word document so I can look it over and try to make improvements.
3. My final Excel file does have pivot tables, is saved macro-free, and as a shared workbook in a network folder. My team needs to be able to make manual updates to the file at any time, including at the same time, without any hassle. When we generate this file each day we open the previous day’s file, ‘Accept All Changes’ and take it out of Shared Mode to use it as a *.csv* file for the Access program.
The final Excel file includes two tabs with pivot tables.
Are there any recommendations on a better way to save the file? My team needs to be able to open the file from a network folder and make edits to it. We also provide the file to our customers either through email or by placing a copy into another network folder for them to use. I want our customers to be able to manipulate the file and the pivot table to change filters and dates, add columns, etc. But I don’t want their changes to be saved unless they save a copy of the file for themselves.
