topic Re: Tip: Extracting data for Access using Publisher in AC 10 in Collaboration with other software

Tip: Extracting data for Access using Publisher in AC 10

Karl Ottenstein — Sun, 05 Nov 2006 01:35:54 GMT

A relatively easy way to extract data from ArchiCAD 10 for sophisticated manipulation and reporting in Microsoft Access is to use Publisher.

Now that Schedules are publishable items in 10, we can create a data publishing set that contains as many schedules as we need to generate all of the data required for whatever analysis and reporting we're doing (limited by the restrictions on what data can be extracted with Interactive Schedules, of course). Any/all object parameters can be obtained, but not necessarily all parameters of all elements. No access to attribute information, etc... only SQL (non-publishable) and ODBC (not available for 10 as of this writing) provide that.

Once you have created your schedules and dragged them into a new publisher set - and set up the 'save as' folder for the set - select all of the items, and change their 'save as' type to be Excel/XLS. Click Publish.

In Microsoft Access, create links to each of these XLS files (Excel can also create links to text csv files). Note: do not import the xls (or text) files. It is essential that you create links so that updates will be dynamic. Create all of the queries, forms, reports that you need for your data solution.

Subsequently, simply click 'Publish' in ArchiCAD to update the on-disk xls files - which are dynamically linked to your Access database...and see the updated information there.

Note: 'list schemes' can also be published and linked.

For Mac people, I'm not sure that there is as good a solution as provided by Access. The last version of FileMaker that I have (7) can only link to another FIleMaker file - not to text or excel files, making this tip/trick unusable there. Perhaps the current version of FM is more flexible (although in my opinion, Access is still vastly more powerful).

Cheers,
Karl

Re: Tip: Extracting data for Access using Publisher in AC 10

Anonymous — Wed, 08 Nov 2006 11:43:42 GMT

Karl
Can you export 2d graphical images of parts etc to access as well ?
Just wondering...
Andrew (AJH)

Re: Tip: Extracting data for Access using Publisher in AC 10

Karl Ottenstein — Wed, 08 Nov 2006 17:38:44 GMT

aj wrote:
Can you export 2d graphical images of parts etc to access as well ?

Did not test - but would be very surprised if so.

Karl

Re: Tip: Extracting data for Access using Publisher in AC 10

TomWaltz — Wed, 08 Nov 2006 17:59:46 GMT

I'm just learning FileMaker now. It will definitely OPEN the exported file but I have not had a chance to see if it can LINK to the file.

Re: Tip: Extracting data for Access using Publisher in AC 10

Karl Ottenstein — Fri, 25 Mar 2011 23:51:04 GMT

Karl wrote:
For Mac people, I'm not sure that there is as good a solution as provided by Access. The last version of FileMaker that I have (7) can only link to another FIleMaker file - not to text or excel files, making this tip/trick unusable there. Perhaps the current version of FM is more flexible (although in my opinion, Access is still vastly more powerful).

I've become a Mac person since this tip was posted. 😉

Wanted to update this thread to note that FileMaker Pro 11 now supports what they call "recurring imports" in which an Excel sheet or CSV (etc) can be set to import every time the database is opened. This now gives Mac users the same ability mentioned below for MS Access users. Linking seems smarter to me, but automatic re-import gives the same result.

Cheers,
Karl