Hello,
I have a problem that I've been trying to find a solution to for some time now. I've managed to boil it down to a simpler scenario and I'm going to try to lay it out here. It is only partially a Archicad problem as the solution might as well be in Excel...I dont know for shure, my abilities are limited and I try to find my way with the help of Chat GPT.
The premise is this, I've created a few schedules that I want to export to Excel and after that merge into a single table. My plan is to use Power Query for this, but I've tried and had the same trouble using VBA as well.
For the sake of this example, I'll consider these schedules to be very simpe, just 2 columns. One called Element and the other Quantity. I think its important to mention that my schedules are created with Elements, Components or Surfaces and as such, the column headders (Element and Quantity) are custom text. In actual fact, the column Element comes from Element ID in the Element schedule, from Building material name in the Component schedule and from Surface name in the Surfaces schedule. Similarly with the Quantity headder, it colects data from different sections in Archicad in each of the 3 types of schedules. To summarise, the columns are made to look the same and have the same headder names even though the origin of the information differs. I then export .xmlx files with this info.
Now in Excel, the process should be fairly simple. In a new worksheet I go to Data - Get Data - From File - From Folder and I select the folder where my files are. A window opens where I can Combine, Load or Transform the data. I select Combine. In the next window I can preview the files I'm about to combine and they look normal, as expected. Then I press Ok to go ahead with the Combine step and when prompted, I choose a place in my workbook where the resulting combined data should be inserted. All simple and good, except the files dont get combined and I get all sorts of errors.
I've made multiple experiemnts and spent a lot of time trying various alterations to the process. My conclusion is that Power Query cant merge them properly becouse it dosent perceive them as being similar. I've tryed with files that were copies of eachother an all worked fine and smooth but as soon as something varies, like the source of the file. (Component schedule instead of Elements schedule for example), or even if both files are created with Elements schedule but the columns have different sources (Quantity originating in refference line length or in wall height for example), the whole process starts falling apart.
What am I missing? I'm trying to simply merge Excel files originating in Archicad, that have similar structures. At the end I want to have a single Sheet in Excel that is composed of a succesion of all the files that I export. Is the solution in Archicad or Excel?
Thank you. Hope for a solution.
Operating system used: Windows
