2-Years Birthday & Blog Renewal

Blog’s 2-years birthday

Yesterday the blog that you are currently reading accomplished two years of web existence! To tell you the truth, when I started this blog I couldn’t imagine that it will be alive after 2 years. I still remember the first teeny-tiny post that I wrote 2 years ago.

If you ask me what happened within these two years, I could briefly answer you with 5 short sentences:

• 77 posts (including this one) were written.
•  Over 38000 people have visited this blog.
•  The blog pages were read around 82000 times.
•  The Facebook page was liked from 80 people.
•  13 people subscribed and 4 followed us on Twitter (Twitter page is quite new).

Blog traffic

From the blog stats it is quite clear that the blog traffic increased dramatically within the last year. Almost the 30% of the total pageviews comes from two posts – Excel VBA – Read And Write Text Files (20%) and VBA Macro To Open A PDF File (10%) – which were written the previous year. However, the traffic also increased because I started working on this blog more seriously. For example, I am trying to write a new post either once a week or at least 3 times per month.


Blog renewal

In order to celebrate blog’s birthday I decided to renew its layout. If you have visited the blog in the past you will notice the difference. Just for comparison purposes I have uploaded the picture above in order to see the differences between the old and the new layout. The new template is much more compact and bright than the old one. The more obvious changes in the layout are:

• A completely new template (based on brighter colors) was used.
• Archives and author’s gadget were removed from the sidebar.
• The social network icons were compacted in one gadget.
• Floating buttons were removed for faster page loading.
• A new About This Blog page was added in order to help the new visitors.
• The old Let’s Work Together page was spitted in Hire Me and Μαθήματα Excel page.
• The files are now uploaded into the public folder of my Dropbox, so there is no limitation to the number of downloads per day.
• Disqus was set as the new commenting system of the blog. All the old blogger comments were imported to Disuqs, so no comment was lost.

Epilogue

First of all, I would like to thank all of YOU for visiting this blog.  

As long as I have free time I will continue to publish new content whenever is possible. Although the last two posts (the one with the books and this one) were somehow different than he typical posts I write, stay tuned because I will publish a new post soon. Last but not least, your opinion; what do you think about the new layout? Is it better than the old one?

Continue...

Suggested Books

Introduction

A week ago a blog reader (Hakim) asked my opinion about books for learning Excel and VBA. After suggesting him some books, I had the idea of writing this post in order to get together all the books that I would like to recommend to anyone who is interested in. So, this list could be considered as “my favorite books’ list”.

In general, books are great source of information and, personally speaking, I really enjoy reading and learning new stuff. So, the list that you will find below contains some of the books that I have read in the past and I consider remarkable. Check them out and you might find a new book to read!

Continue...

Open A PDF File With VBA

Judging from the blog stats, the subject of opening PDF files using VBA code is really popular. Having a discussion with a blog reader (Jean-Sébastien) in the comments of my previous post VBA Macro To Open A PDF File, I thought that I should try to write a more generic code that will work with both Adobe Reader and Professional.

In comparison with my previous attempt the VBA code below doesn’t use the Adobe object system or the sendkeys method. Instead, a combination of various API functions is used in order to find and manipulate the page number and page zoom textboxes. The general idea behind this code can be divided in four steps:

1. Check the file path and if is valid, use the FollowHyperlink method in order to open the PDF file.
2. With the FindWindow API function find the window of Adobe Reader or Adobe Professional that contains the opened PDF file and bring it to foreground using the SetForegroundWindow API function.
3. Find the subsequent child windows with the FindWindowEx API function.
4. Use the SendMessage and PostMessage API functions in order to send the desired page number and window zoom value to the corresponding textboxes.

Similarly to my previous post, the Spyxx software was used in order to specify the windows hierarchy in Adobe Reader/Professional. The picture above shows the window tree of a sample PDF document.

The VBA code in this post can be used with almost all the office programs. I have tested it with Access, Excel, Word and Power Point (both 2010 and 2003 versions) and works like charm. Since it doesn’t require any reference to Adobe library and no sendkeys are used is probably much easier to use and more reliable than my previous codes.

Continue...

Save Web Pages As PDF Files

Introduction

Sometimes a task that you consider as “simple” tends to be so demanding that at the end you blame yourself for your initial assumption. The previous sentence describes exactly what happened to me the previous days. Around 10 days ago, a blog reader (Sangeetha) asked me if is possible to convert HTML files (web pages) to PDF files using VBA. My initial reaction was yes, why not? But when I started writing the VBA code I couldn’t imagine that it will take me so much time to finish this project.

In the code below I used a lot of different techniques and methods which I will try to describe as detailed as possible in case someone needs to use a part only of the code. There are multiple lines of VBA code in order to:

• Select a folder using folder picker dialog.
• Determine if a folder exists.
• Set the default computer’s printer to your desired printer (using Windows Script Host Object Model Library).
• Check if a file name contains special/illegal characters.
• Open a web page in Internet Explorer and wait until is fully loaded.
• Print the web page as PDF to a specified file path using Adobe Professional. The latter involves:

- API functions (FindWindow, SetForegroundWindow andFindWindowEx) in order to “find” the print window of Internet Explorer and its “child” windows.

- API functions (SendMessage and keybd_event) for changing the PDF file path.

- A custom WMI (Windows Management Instrumentation) function in order to determine if the printer has finished printing.
- API functions (FindWindow and PostMessage) for finding the opened PDF document and closing it.

The above list describes more or less the sequence of actions that I followed in order to fulfill this task. However, I should mention that without the following tools it would be impossible to finish this project:

• Spy++ (SPYXX.EXE) is a utility that gives you a graphical view of the system’s processes, threads, windows, and window messages.
• API Viewer is a utility that helps you writing the API declarations, by providing the correct syntax of each function.

Continue...

Export Excel Ranges As Power Point Tables

Some months ago I published a VBA code for exporting all Excel charts to a new Power Point presentation. Having read that post, a blog reader (Hari) asked me if is possible to transfer Excel data to Power Point. I sent him a code that I wrote on the fly, but I thought that a similar code will interest a lot of people who want to include Excel data to their presentations. So, I decided to improve the initial VBA code and here it is!

The code is consisted of two subs; the first one creates a new Power Point presentation and sets the Excel ranges that will be exported. For each range the second sub creates a new table in a new slide and fills that table with the corresponding range values. By setting the desired Excel range in the first sub you can adjust the VBA code according to your needs. In the attached workbook for example I have set a steady range for each worksheet and the first sub loops through all the worksheets of the workbook. Each worksheet contains the quarterly earnings from a hypothetical shop.

Continue...

Draw A Polyline In AutoCAD Using Excel VBA

A friend of mine, who is surveying engineer, asked me recently if it is possible to draw a polyline in AutoCAD using coordinates from an Excel file. He had a large Excel file with points coordinates (x, y) and he wanted to connect them through a polyline in order to create a 2D profile. I manage to fulfill his request by using a lightweight polyline, which is a 2D line consisting of straight and arced segments.

I thought that the interaction between Excel and AutoCAD using VBA might be useful for all types of engineers who using AutoCAD (especially civil and surveying engineers), so I decided to post the VBA code that I wrote here. Although the example is quite simple I think that it illustrates the general idea about using VBA from Excel in order to draw objects in AutoCAD.

Continue...

VBA Macro To Convert PDF Files Into Different Format

A couple of days ago, Peter asked me on this post if it is possible to save a PDF file as an Excel table by using VBA. I started thinking his question and I thought that it could be quite useful if I can automate the procedure of saving a PDF file into other format. The procedure that I wrote requires Adobe Professional in order to work. So, if you have installed Adobe Professional at your computer and you need to convert some PDF files into Word files for example, take a look at the code below.

Apart from the main procedure I also wrote a loop in order to convert multiple PDF files at once. So, if you have a folder that contains PDF files you can use this tool to obtain their file paths. Then you can use the attached workbook in order to convert them to a different format. The code actually uses the Save As command of the Adobe Professional in order to save the files to the desired format. The available formats are:

• eps
• html and htm
• jpeg, jpg and jpe
• jpf, jpx, jp2, j2k, j2c and jpc
• docx
• doc
• png
• ps
• rft
• xlsx
• xml
• txt
• tiff and tif
• xml

Continue...

Daily Schedule Charts

Figure. 1: "Schedule 2" chart.

It is always useful to organize your daily tasks, especially on days that you have a lot of things to do. During the previous week I noticed that there are some days that I have a really heavy schedule, so I tried to help myself organizing the daily tasks. My initial thought was to write the daily tasks in a typical worksheet, as if I had a notebook. However, after creating the worksheet I decided to visualize the schedule in order to have a quick overview of the remaining tasks. Finally, I ended up creating two custom charts that depict the daily schedule.

These two charts are in reality a XY chart and a stacked bar chart, but with many customizations. On the first chart (named “Schedule 1”), I used custom error bars in order to represent the duration of each task (figure 2). On the second chart (named “Schedule 2”), I made some adjustments (hide one series, hide the Y axis etc.) so as to look like a Gantt chart (figure 1). Both charts can show up to 15 different tasks, which in my opinion are adequate for a typical day.

Continue...

External Workbook References (Add-In)

Problem

There are a lot of people that using external workbook references during their daily work. A sales manager for example could summarize the annual sales of a department by summing 12 monthly reports that are stored in 12 different workbooks. In this way he avoids copying all the data to a single workbook, making the annual report much more compact.

However there is a disadvantage of using external workbook references: if you move or delete one of the “independent” workbooks, then, when you reopen the “dependent” workbook, the following pop-up will appear:

If you choose update, another pop-up will be shown:

As you can easily imagine, if you have many external references in the “dependent” workbook it can be quite difficult to update them. Moreover, if the “dependent” workbook is used by many people, there is an increased possibility for a miscalculation, especially if an external reference fails to get the updated value.

Continue...

Vapor Pressure Calculation

As the title implies this freeware tool was created in order to help you calculate the vapor pressure of a component based on its known temperature. The calculation is based on Antoine’s equation, which is a vapor pressure equation and describes the relationship between vapor pressure and temperature for pure components. The Antoine’s equation is used in the form below:

Where:
Pvap: vapor pressure [mm Hg]
T: temperature [K]
A, B and C: Antoine’s coefficients 

According to Wikipedia: “Usually, the Antoine equation cannot be used to describe the entire saturated vapor pressure curve from the triple point to the critical point, because it is not flexible enough. Therefore, multiple parameter sets for a single component are commonly used. A low-pressure parameter set is used to describe the vapor pressure curve up to the normal boiling point and the second set of parameters is used for the range from the normal boiling point to the critical point".

However, by using two different sets of coefficients a severe problem is caused; the described vapor pressure is not continuous – at the normal boiling point the two sets give different results. In this workbook I decided to use a single set of Antoine’s coefficients, having in mind that in some cases there will be increased deviation between calculated and real vapor pressures.

Continue...

COPERT Micro: A Tool To Calculate The Vehicle Emissions In Urban Areas

This is somehow a special post: from Sunday till previous Tuesday I was in Brussels in order to participate at CONCAWE’s 10th Symposium. A special feature of this event was the "Young Researcher" poster competition.  The call for abstracts was at the previous November, so I submitted my proposal, which was finally selected after an initial selection. In the poster completion there were 14 posters from 10 European countries.

In the next paragraphs you can read a text version of the poster that I presented in the Symposium and you can download if you like the original pdf version that I submitted. So, although this post is quite different than the typical posts I write in this blog, please give it a try. You might find it interesting.

Continue...

Search Your VBA Code (Excel Add-In)

A couple of days ago, a blog reader (Harm) asked me if it is possible to search in all subs/functions/forms of the VBA code to see if there is a particular string present. Although I had written in the past a code that loops through all the VBA code of a workbook I decided to go one step further. So, I adjusted my old code and I created a new add-in.

The add-in works similarly to built-in search, but has the advantage of displaying the results in 3 listboxes. In this way you can simply select the desired value from the listboxes and with a single click you will be transferred to the line of code that contains the string you are searched for. This is quite useful in cases of public variables, which are used in many modules, as well as in workbooks that contain many lines of VBA code.

Continue...

Quick Back Up Of Your Files

All the freeware tools that are uploaded in this blog were developed for one main purpose: to make our daily routine easier. Probably one of the most common routine tasks that we have to cope with every day is the back up of our files. If your daily job involves computer use, I am sure that you will understand the importance of backing up your files at least once a week.

The previous days the following question was spinning in my mind: is it possible to automate the daily back up process? My notebook is a little bit old, my external hard disk has not many free disk space, so I was wondering: is it possible to find an efficient way to backing up my files quickly?

Continue...

Quick Motor Calculation For Various Applications

This workbook contains three short relationships that will help you find the motor power that is required for your application.  I have included the following applications:

• Pump motors
• Lift/elevator/crane motors
• Fan/compressor motors

All the relationships are based on a total efficiency factor, which for most of the cases can be considered something between 0.5 and 0.7. Of course these relationships do not substitute an analytical calculation, but, could be useful for a quick reference.

Continue...

Export A Large Access Table/Query To Excel

The previous days I had to update a large Access database. I had a large table – around 1.000.000 records/rows – that I had to export to an Excel workbook, perform some calculations/corrections and import the table back to the database. So, you might wonder what was the problem, right? Well, the problem was the large amount of records that I had to export from the Access table. Before I write some VBA code I tried two alternatives:

A) A simple copy paste using the Clipboard. I select the entire table and tried the CRL + C shortcut. Unfortunately, the result was the following message:

A table that contains 1.000.000 records divided by 65.000 gives around 16 groups. No way!

B) I tried the export feature of Access, but, although the Excel file was created, no data was inserted into the spreadsheet.

Being disappointed by my previous attempts I decided to try a VBA solution. So, I wrote a VBA function that can export a large Access table into an Excel spreadsheet.

Continue...