User Profile
alex_n
Brass Contributor
Joined 4 years ago
User Widgets
Recent Discussions
Re: MS Access 365 - External Link to Folder
George, thanks for the detailed response. It does seem a bit of a work in order to achieve the task. I am open for alternative approaches using Access. Having an excel file as a middle man here is no option as I am trying to avoid using excel as it is getting inflated already with more appends and it requires additional steps that my cause unnecessary errors along the way.MS Access 365 - External Link to Folder
Hello Community! I currently have an excel file (master) that uses PowerQuery that is linked to a folder in a SharePoint with several excel files (reports). Master consolidates 'pricing_report' sheets of the files in the folder. PowerQuery performs filters, cleaning, etc and returns a table consisting of all the reports in the folder. As transactions grow, the Master file is growing big in size and it is getting slow to run. I want to have the same setup in Access where I have it linked to the folder with the excel reports (new reports are dumped in the folder on a regular basis). File names do not stay consistent. In Access I tried using the External Data tab, Import & Link but I don't see where it links to a folder. Please advise on how to achieve it. Thanks in advance. Alex.Re: Combine data from multiple excel workbooks in a folder into one master file
Can anyone assist me with a VBA code approach to achieve the result? At the link, I have posted the tables and Summary Master file as an expected outcome. It should pick certain columns (each column is name ranged) and combine all in one file. Thanks in advance. https://1drv.ms/f/s!Aqv4zBSFNKaymn_m8d9SM-0VmzlB?e=T0IglGRe: Combine data from multiple excel workbooks in a folder into one master file
I cannot find a way where I can populate multiple columns using the name ranges as opposed to populating the entire tab where the contents of the columns will be mixed up as new columns were added with reports 3 and 4. It only lets me populate one name range column at a time. Here is the parameters list when I invoke custom function: 'Summary' is entire tab 'Contract' through 'Year' are name ranged columns. It is missing 'Overhead' name ranges that was added to report 4.Re: Combine data from multiple excel workbooks in a folder into one master file
Hi. This solution is great and so intuitive. But there's one thing that needs to be considered and it will be golden. Going forward, they added a new column in the Report file and combining the report files result in one of the columns showing incorrect content as the data shifted to the right with the additional column and created a mess. Consequently, I have two data sets. I will add them to the data model so I can combine them. Also, each column is a name range. I tried to select (or pick out) certain columns I want populated but it does not let me select more than one name range. Just to test, I added two name range columns with separate steps and it resulted in tripled or quadrupled line items. What would be a solution here to resolve this? Thanks again.Combine data from multiple excel workbooks in a folder into one master file
I use Office 365 Hello, Please note that I made few changes to the criteria and the files that correspond to the new changes. I need help with VBA code that need to perform the below: There are reports (excel files) with different file names but all have 'summary' tabs with the same column names. The reports are stored in a folder where the master file (Summary Master) needs to retrieve the data from certain columns from 'summary' tab of each file and combine all into 'summary master' tab of summary file. Here is the link to the example files: https://1drv.ms/f/s!Aqv4zBSFNKaymn_m8d9SM-0VmzlB?e=upFdiI 1. Summary Master.xlsm - master file with 'Summary Master' tab. This file performs the combining 2. Reports - folder where all reports are stored A few things to note: a. number of files is not set, it will increase by day b. report file has multiple tabs c. when a new report file is added to the Reports folder, the summary needs to append the new data Thank you in advance.Excel Query: Custom Column
Hello, I have an excel file with two queries with external data sources linked to a sharepoint: Purchase Item List and FX_Rate which is just a connection. Purchase Item List has a column 'Unit Price in Local Currency' with prices in different foreign rates. FX_Rate has the exchange rate to USD. I want to add a column to Purchase Item List called "Unit Price in USD" that converts the foreign rate to USD using the conversion rates in the second table. Below are the sample tables that represent both tables. Please help me to achieve that. We can use column 'Currency' in table Purchase Item List and column 'local currency' in table FX_Rates to achieve a connection between the two tables. And this is where I am stuck and can't move any further. Thanks in advance, Al
