Create a Refresh-able Link from ARCHICAD to Excel!

[@edgarschefer394]

Man you are one of my favs in the archicad field here. Love what you are doing, nice and clean!

[@CONTRABIM]

Thanks Edgar! Appreciate the comment more videos coming soon to get ready for a big 2021!

[@koronassylora]

Thanks a lot! I am working on a group assignment and this is exactly what I needed!

[@CONTRABIM]

Great! Thanks for the comment, exactly the reason I wanted to share this workflow:)

[@EnderAydin]

Great share, thank you John.

[@CONTRABIM]

Thanks Ender, glad you liked it!

[@toofuerte]

Thanks for your great help!

[@mariansdraila]

When your cost estimating videos first came up I had no idea that you can automatically bring multiple files into excel and update them with a click of a button. That was eye opening! Seeing that, made me want to know how can I do that, and the only method that I could find at that time was by using the power query function. That's pretty much the reason I use power query and my method certainly has its downsides but I like the fact that It combines all the schedules into one single table. It's important to me to get all the information in a simple table format because at the end this table gets printed and delivered to the client / contractor. It would be nice to see how your deliverables look. Do you evere print them or they only get sent in a digital format?

[@CONTRABIM]

So my method breaks down each scope of work into an individual trade sheet, so I have a summary of that particular trade of construction. From there, I'll link those into two different summary sheets to organize directly by trade (CSI Masterformat), or by building system (Uniformat II). You're method sounds interesting, but I'm curious how it would handle component costs from composite structures and complex profiles. Maybe you can show a demo?

[@mariansdraila]

@@CONTRABIM Well, I don't use a standard as you do, and I might incorporate one in the future, but right now I don't know if there even is one available in my country (Romania). I basically made my own rules. I have a set of categories that I assign to my elements / components, and that helps me group the materials the way I want. I have a column in every schedule that shows the category and that column gets turned into a heading with the help of the pivot table. In older archicad versions you could not assign properties to materials which made things a bit hard, but now you can. Hopefully in the future we will be able to assign properties to composites and surfaces as well. That would make things a lot easier. So by being able to assign properties to materials you can assign different categories to different skins of the same composite, but you might have to have multiple instances of the same material ( for instance I might have a roof insulation and a wall insulation, and even if technically it's the same material I want to have it in separate categories). This is the method I used to get the schedules into excel kzbin.info/www/bejne/l2ioY2x-ar-Io6c

[@mariansdraila]

I wonder If you can create a similar connection between Word and Excel, where you would point out Word to a folder that contains multiple excel files and it would automatically refresh all it's links. I could only find a workflow where you have to relink each and every file manually.

[@youssefoeh9229]

Hi! great video!. Do you have an idea on how to link a refresh-able data from excel to archicad? I want to put price on material on excel an include this price in Archicad Thank you for your response

[@LAJCADForum]

This is a good question

[@BUSCANDOAVERDADE1717]

Shalom de Yeshua! Muito bom o vídeo! God Bless You Brother, in the name of Jesus, The Mashiach!

[@egorzakharov7931]

It is more convenient to use the publication in the XLSX format, and to combine several Schedules with Excel+PowerQuery. In this case, it will also be possible to convert the data.

[@CONTRABIM]

Thanks Egor, I'll have to check out that method and see if it works into my larger workflow!

[@mariansdraila]

@@CONTRABIM I also use power query to merge all my schedules into one single sheet but you have to have all the schedules set up with the same number of columns and ordered identically, at least with the method that I use. I then use a pivot table to get the final layout the way I want it. The down side is that the pivot table is non editable and there's more work to do in Archicad to get all the schedules with the same number of columns.

[@CONTRABIM]

@@mariansdraila thanks for the comment. I really do need to check this workflow out, but I've also developed my own tool for linking TXT and XLSX files into Excel. It's call CONTRABIM Excel Loader (CXL), and I use it to swap out 50+ reports at a time, from one Excel Estimate template to the next. I appreciate your notes on the column orders, and pivot tables, and I've run into issues with this in the past. From ARCHICAD, I like maximum flexibility, where you can switch columns around, add new properties and parameters, without breaking the work done in Excel. Each report can be unique, and constantly evolving, and with a good Index>Match formula these changes make no impact on the excel side.

[@egorzakharov7931]

@@CONTRABIM just in Russian: kzbin.info/www/bejne/pJO1d52aqbKqb5o

[@daviddelgadovendrell]

@@CONTRABIM Index>Match formula?

[@markozeko7539]

I hae a problem here. If I link txt file to excel I cannot overwrite it with new AC data. I have to close excel first then save it and opet excel again. What should I do? AC 27 and office 365.

[@realpulsecoin]

But now you use xlsx ive seen in newer videoes, imported by VBA? Why not import by power quary?

[@CONTRABIM]

Power Query is definitely an option to bring through data, which certainly can be refreshable also. Both methods work. I like using a macro in VBA to refresh data from a folder, that way it's much faster and easier that pointing Excel to individual links. Several different methods though for connecting data from Archicad into Excel now! Thanks for the question.

[@realpulsecoin]

@@CONTRABIM Thanks for the answer 👏 Love your work. Greetings from Norway 👍

[@GuillermoRaulGimenezEspinola]

hello, when update, the new data created on excel has eliminated

[@jonathanreich7298]

Great video. When I refresh the various functions I have added to the spreadsheet are deleted. I have checked settings but can't find the solution. Suggestions?

[@dmichaelsf]

Hello John, same problem here, did you find a solution? @contrabim is there anything i am doing wrong?

[@tiagovionet1681]

Very very good!!!

[@jakdutra]

And can you import it back to ArchiCAD when you're done editing the formulas? Thank you :)

[@CONTRABIM]

Hi Jakeline. This workflow is one directional into Excel. If you want to import property data back into Archicad, use the Property Export/Import workflow from this video: kzbin.info/www/bejne/lYPdlHajm9CiZrs

[@jakdutra]

@@CONTRABIM wow thank you! I'm watching it and will definitely try out next Monday. Do you also have a video with a Table that sums up the room areas and half of the Balcony? I found one in AC but it does not work :(

[@CONTRABIM]

@@jakdutra I don't have a video on that, but I'll add it to the list! Listing room areas is easy, to list 1/2 of a balcony area you'll need to apply a factor of 50% to reduce down the outputted area

[@jakdutra]

@@CONTRABIM I applied it, but it never works. For the Terrace, for example, it's 25% and it calculates 50%.

[@jakdutra]

@@CONTRABIM Just found it. It was just a matter of updating the Zone. Jesus... hahah kzbin.info/www/bejne/Y5O6hpmDbZuirdU&ab_channel=LEARNVIRTUAL

[@qingluo8676]

Great work. Is it possible to link back to ArchiCAD from excel without using PDF? How? As ArchiCAD cannot even work out a percentage, e.g. permeablility for planning. we have to use excel to complete the task. Then I hope we can have a live link back to ArchiCAD to show the percentage in a schedule. Please advise. Thanks.

[@CONTRABIM]

Hi Qing Luo. Yes you can link back using the Property Export>Import Function. When working in an interactive schedule, use that function to export and then re-import. I'll send a link in a moment to a video for you!

[@qingluo8676]

@@CONTRABIM Thanks very much for that.

[@hand989]

@@CONTRABIM please send me too!

[@Constantinesis]

Hello! Does the Get external data feature work with free Excel Online version? I can`t seem to find it. Is there a way I can achieve same connection with the Excel Online?

[@CONTRABIM]

Thanks for the question! I would think the free online version would have some sort of import function, but not sure about the same capabilities. Sorry I wish I knew more to share on that verison!

[@Constantinesis]

@@CONTRABIM Unfortunately the free Web Excel doesn't have this file import data feature but I managed to do something similar with a script in Google Sheets

[@juliandavidac]

Holy F*ck this guy is a genius, thank you so much

[@CONTRABIM]

Thanks Julian, I hope this video helped you out! There's some other methods of linking content from Archicad to Excel also, these days I prefer using a macro based workflow for added flexibility. Also the power query option as well.

[@amandafulgencio4222]

Do you know how can I exchange the excel datas to archicad? i have a list off elements code that I have to inserts on the elements of my model. But the archicad won´t read my .xlm file or .xls file.

[@CONTRABIM]

Thanks for the question. For your element codes, I'd recommend to create a corresponding property within the archicad property manager, then export your report using the Property Exchange workflow as shown in this video: kzbin.info/www/bejne/lYPdlHajm9CiZrs You can then add your elements codes, and then import back into the Archicad elements.

[@gurulakshmibuilders-planni5652]

Hello sir i change property value or cost means not update in scheduling of quantity is any solution ?

[@CONTRABIM]

Hi there, I’m a little unclear on the question. Property values and costs are going to be independent of quantities, the two are not interconnected unless setup as an expression based property. Try changing the model elements geometry to update a quantity, should work for you I hope:)

[@pre66]

Is there a chance to add the preset values as a dropdownmenu to choose from?

[@CONTRABIM]

Thanks for the question! So you're referring to data validation in Excel. Through this workflow from ARCHICAD to Excel, it's not one that will bring all the extra values from Property_Option sets with it. If you want to create this data validation (drop down) option, you can choose to export via interoperability>export properties to publish a report, that also contains the data validation drop downs. From there you could link that file into another file, but to be honest I havn't tried that method for bringing drop downs over. Maybe be possible, maybe not. I'll try a few tests and let you know!

[@patriciaherden9967]

Why do you prefer to use txt file instead of xlsx?

[@CONTRABIM]

Hi Patricia, sorry for the delay just saw this... For this basic workflow, I use the TXT file type as it is a little more neutral than the XLSX format. Specifically, I've found that MacExcel versions don't work well with the XLSX format, so that's why this method of TXT was preferred in the past.

[@galiafvelikiy4615]

thanks!

[@gurulakshmibuilders-planni5652]

Nice

[@mkonzingoma2042]

great