Activities report in Infopath

Description

Activities report manager is an Infopath solution which automates the work with the Techedge web time report. It is dedicated to my colleagues who work in Techedge.

This project was experimental. I wanted to discover deeper the functionalities of Infopath and the opportunities for creating automation applications.

The project could have been published a couple of weeks earlier, however, in the phase of final testing I discovered that it posted data on the web time report disregarding the month's state. This means that basically it could submit data to the web time report for any period of any year. So, it took me some time to implement additional conditions to make the submitting of data available only for the open months.

How this works

Check the presentation video to see it in action:

For working with the application you need Infopath 2007 and .NET framework 2.0 installed. After the installation you will find an .XSN file which is an Infopath template. Based on it you can create new documents with your data (xml file).

Download

If you liked the tool and wish to download it feel free to do it. We provide it free of charge. Use and enjoy!

ActivitiesReport.msi

CBI (Customer to Business Interaction) Excel data export

Hi guys,
we are back. It took us some time to restart the blog activities. Though, we never ceased our business activities and are always open to help businesses in automating tasks. :)

Recently we had an opportunity to develop an Excel add-in to carry out custom export of data regarding the employees salary payments into a CBI (Customer to Business Interaction) format. This is a standard for the text flat file data formatting implemented by banks for batch data loading. More information could be found on www.cbi-org.eu

Preface

As it happens often small companies manage data about their employees in a kind of database which can be MS Access or even MS Excel. The easiest way to handle the task of data management at this level is to use an Excel table where insert salary data in one column. The bank requires the information to be sent in a text file which is specifically structured. the data export in this specific structure has been implemented in the add-in.

Description

In order to create the table correctly formatted for the add-in to function it's necessary to open Excel, in the menu YellowRiver/CBIexport find the button Create new sheet.

A new worksheet will be created for you.

The data table would have the following columns:

1. Fiscal code of the employee
2. His address
3. ZIP code
4. Commune he lives in
5. His Bank name
6. ID (currently not used)
7. Name and surname
8. IBAN (International Bank Account Number)
9. Issue date
10. Comment
11. Amount

Notice that some of the columns are hidden.

Toghether with this information the add-in needs also to know the following information about your company:

1. Company registration code
2. IBAN to transfer money to
3. Payment issuer (generally, the company's registration code)
4. Bank's registration code
5. Company name
6. Company address
7. Region
8. VAT account

The add-in allows you to have a multiple choice of company registration code and bank's IBAN in case if you have different company registrations and need to vary the account you transfer money to.

When you have your table filled with data just navigate the same menu and find the button Export. The exported document is ready less than in a second and a window showing it is opened.

You will notice that the generated text file contains all the data from Excel. Now the file is ready to send to the bank on the bank's web-site.

Installation

The installation of the add-in is simple as it is. Just run the downloaded file and select the path where you wish to install the add-in (the file .XLAM). The default path is OK since this is the default MS Office add-ins path.

To activate the add-in you have to go to Excel options\add-ins\Excel add-ins and select ExportCBI.xlam.

When you do it you might encounter a security warning.

Authorise the macro execution.

As soon as you do it you will find the afore mentioned menu YellowRiver\CBIexport in the Add-Ins tab of Excel.

Miscellaneous

The add-in is not protected so you may change it. This add-in is not universal so if you need any custom changes feel free to contact us.

The add-in's language is Italian. But I tried to give the English names for the fields. You can easily change them.

The application is freely available on our web-siteWWW.YRIVER.IT in the chapter SOLUTIONS where you can also find out how else we can make Excel work for you.
Download it ExportCBI.exe.
Feel free to contact us if you have any ideas, suggestions and proposals.

Excel PACMAN

Hi all,

We are currently a bit busy with our jobs. The crisis makes it feel. But even though we do not manage to update our blog regularly it does not mean we do nothing. :)

Today we are glad to announce that we have finally finished the testing phase of the great old and well-known game Pacman created in Excel'2007! Admire once again the huge potential and power Excel (and basically the whole MS Office Suite) has!

Check the video presentation to understand what it really is. :)



And here's the link to download the game:
ExcelPacman.rar

Feel free to contact us if you experience troubles with the game.
Feel free as well to contact us if you experience any difficulties in solving problems in Excel and MS Office, we will most probably be able to provide you with help and support within hours! :)

All the best wishes from YellowRiver,
Valera :)

Automate Excel charts building and moving to PowerPoint presentations

Hi,

Today we would like to present you yet another Excel add-in that we created for one our client. This is another implementation of the tool to build and transfer Excel charts into PowerPoint presentations.

The tool is quite universal so you could easily adapt yourself to use it.
The solution is quite simple and it uses at maximum the natural capabilities of Excel.

So, imagine you have a huge set of data which you have to analyse somehow, create charts on separate subsets of data and transfer them into PowerPoint presentations to then submit them to your clients. With this add-in you are actually supposed to have
- a table with data
- an area where you can insert row numbers
- an area that is filled dynamically with data by means of formulas and that takes a row number as an input
- sheet charts that have as data source the before mentioned area

What you then have to do to automatically build and transfer dozens of charts into a Power Point presentation is:
- you select the rows you are interested in
- you select button Build charts from the YellowRiver menu bar
- you indicate the row input cells and select the chart to be built and transferred
- and you run the tool
These are the simple clicks to do with your mouse to build and transfer charts.

Below you can see how the application works on practice:


Visit our web-site WWW.YRIVER.IT to see other projects, solutions, ideas and find out more about how we can make Excel work for you.

BOM - Bill Of Materials

Hi,

today we would like to tell you about our new solution that we created to provide you with a simple and available instrument to calculate the production cost of your products.

Quite often working with small “factories” we faced the situation when the factory owners could not tell us the exact production cost per unit for the goods they produced. It was a pity situation since the pricing policy is totally distorted in this case. And in addition the management had no idea about the rate of use of the resources, about inefficiencies, about additional opportunities etc.
Therefore, we created this very simple, intuitive, and powerful tool to manage the hierarchy of products – the Bill Of Materials.

By means of this tool you can:
- Define multiple parent-child relationships between your raw materials and components which are made of them (different components can be done from the same raw materials)
- Define the process time for the raw material to become the component (in minutes)
-Define the amount of raw materials needed to produce one unit of some component
- Define the acquisition cost per one unit of raw materials
- Define fixed costs (labour, energy, administrative, other) which are then allocated to the products and components based on their process time
- Visualize and navigate the hierarchy of your materials
o Drill down
o Drill up
o Refresh
o Display only raw materials
o Redisplay all
- Change the visualization mode from table to hierarchy (or tree) with different indentation types per elements

Below you can see the working example of the BOM application:



The application is freely available on our web-site WWW.YRIVER.IT in the chapter OUR PROJECTS where you can also find out how else we can make Excel work for you.
Download it BOM.ZIP.
Feel free to contact us if you have any ideas, suggestions and proposals.

Best wishes from YellowRiver,
Valera :)

Permeability of soils – the Excel application

Hi,

Today we would like to introduce you one of our projects that we did for Politecnic University of Insubria. This is an Excel application that helps calculating the permeability of soils.

"Why in Excel?" you might ask. Excel is a powerful and flexible framework to create applications with dynamic interfaces. In this project we needed to implement the tool to manage different points that took measures of different parameters of soils. Their particular feature is that each point is characterised by a variable number of discontinuity families. Therefore, one point had to expose an interface to input data in 3 families, the other one – in 15 families, yet another one – in 35 families etc.
At first we were requested to develop a stand-alone application. However, after having thought carefully of the problem we offered to develop the application in Excel.

Here are the advantages in using Excel:
- Familiar and simple user interface (it’s Excel itself)
- Open code (yep, Excel with its native VBA support provides us with a possibility to create applications with open code; the University, in turn, wanted to have the possibility to control and modify any time the business logic of the application)
- Ease in creation of a beautiful and dynamic interface (one point has k discontinuity families, each has a number of parameters for input, but that also serve as output parameters, k! angles between families to input and output, k ^ 2 interconnection values for discontinuity families and k hydraulic conductivity values per each point – and this all dynamic and on a single worksheet)
- Fast development (Excel is already a powerful tool which we only enhance with our advanced functions. Anyway, 50% of all the work has already been done by Microsoft)
- Data and application in one place (Excel application can be a calculation engine and a database in the same time)
- Flexibility in distribution and power of management (if you want to change the year and archive your database or switch to another measurement area or start new measurement period you have to simply create a copy of the workbook)

Below you can see how the application works:



Visit our web-site WWW.YRIVER.IT to see other projects, solutions, ideas and find out more about how we can make Excel work for you.

Best wishes from YellowRiver,
Valera :)