Access and Excel Reports

Mdl0377

New Member
Joined
Apr 30, 2023
Messages
3
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. Web
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.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
1- no need
2- use the database documenter. Check through all the options for what you need
I'll let others answer about how to share/edit your workbook as that's not really my area. From what I remember, sharing wb's is a pain.
 
Upvote 0
Solution

Forum statistics

Threads
1,222,143
Messages
6,164,174
Members
451,880
Latest member
2da

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top