Excel VBA automation with multiple files

Kashif Javaid

In the course of one career, there may be more than one occasion where one need to pull some data from one or multiple excel spreadsheets into another main spreadsheet for summary or management update. This can typically be done by opening one file at a time and copying required information and pasting into the summary spreadsheet. Now this may be cool for one or few times, but if this task is required on daily basis, this could quickly become mind-numbing and outright boring. Luckily, excel has built in mechanism to automate these tasks: VBA.

Screen Shot 2014-12-15 at 9.56.52 AM

In this application note, I will show you, this kind of task can be easily automated using excel visual basic using an example.

Lets assume you have a big excel file that comes from a vendor or some other group in your company (a sample data file link is given at the end). This excel file gets updated daily and send out to you. Your task is to extract some particular regional data only and need to send it upper management periodically.

Here are the steps:

1) First load the visual basic editor as shown below:

LoadingVisualBasic2) Assume, there is a sale file where sale information is updated regularly for different Regions (see snapshot below). The problem is that it is not organized, and your task is to extract information for your region. Lets say it’s “West” and you need to send it to management daily. You could do it manually possibly filtering rows but task is boring and takes extra steps.

3) Insert a button into the active sheet with J1 cell as Region input we want. Shown below is what happened after user clicked on the Get Data button. Note that you need to have developer tab enabled to see the button and other GUI controls. Refer to Excel help to see how to enable it if its not already there.Screen Shot 2014-12-09 at 9.04.25 PM4) The main program is extremely simple and consist of four subroutines:Initialize, OpenFile, GetData and CloseFile. There are two variables, app and CustFile. Basically program create a new excel workbook customer file with the same data, but it does not change the source fileScreen Shot 2014-12-09 at 9.09.18 PM5) OpenFile and CloseFile, basically consist of code that open the file and create a new workbook. The beauty is that it does that automatically and do not care about the source file name. The only requirement is that data file need to be in the same folder.

6) GetData is where actual data is imported into summary workbook. This sub basically go over all the occupied rows and copy the data for each Region and OrderDate, TotalCost for each row into variables Region, OrderDate and TotalCost. RegionNeeded is used to store button input from cell J1. As you can see, this can become very powerful if you need to customize or have some special requirementsScreen Shot 2014-12-09 at 9.24.53 PM7) Moreover, I need to mentioned a helper function called fcol. Basically fcol function scan the first three row and up to thirty columns to get the column number based on the column header as specified by input argument of this functionScreen Shot 2014-12-09 at 9.34.50 PM8) Now there you have it, it’s a very simple program but could prove powerful and real time saver and can be adaptable to your particular requirements. I have attached both files if you would like to play with them. Modify it to make it work with other columns. Here are the downloads links: summary and SampleData.

9) Create a new folder, save both file there, open summary workbook and hit command button. Type different region such as ‘Central’ or ‘East’ and watch how the rows updated.

screen-shot-2017-02-20-at-9-37-53-pm

10) In conclusion, with some basic programming constructs such as for loop, and some if and else statements, a very powerful and useful excel automation can be created.

Advertisements
Posted in Automation | Leave a comment

10 everyday items replaced by iPhone or other digital devices

by Kashif Javaid

Yes, iPhone is the first smart phone where it made the leap to touch screen interface and started apps revolution.

Here is my list. Please add your items to comments section below:

1) Digital Alarm clock- who uses these anymore? please raise your hand.

71mGkKsP1wS._SL1500_.jpg

2) Books, Magazines and dictionaries

3) GPS

Screen Shot 2019-02-03 at 8.38.55 PM.png

4) Game boys and other portable console

5) Digital Camera

6) iPod and other music players

ipod_hero.jpg

7) Voice Recorders

8) Scanners

 

scanner.jpg

9) Photo Albums

 

10) Credits card via Apple pay

This is it. As we continue to move forward, we will see that one device can do much more eventually replacing other primary  computers and tablets. If there is anything more that is replace by smart phones, add into comments section below:

Posted in 5 Hi-Tech Silicon Valley Tour | Leave a comment

Multilayer Ceramics Capacitor (MLCC)

 By Kashif Javaid

4872694Now a days any modern portable electronic device has almost exclusively multilayer ceramics capacitors in it. In a typical cellphone, hundreds of these little critters as small as 0.3mm width to 0.6mm length are all over the main PCB (Printed Circuit Board) . So it seem like they have no drawback and easy to drop into where there is a capacitor needed. I made following graphic to remind myself that may not be the case. Note following diagrams only apply to class 2 type which are most commonly found.

Why MLCC’s are useful?

MLCC_advantages

When does a MLCC become problematic?

MLCC_disadvantages

 

The above are little handy charts which could be used on a engineering virtual wall.

Posted in Electronics Components | Tagged , , , | Leave a comment

Stanford University – Silicon valley pump house – 2nd Tour

This is one of my favorite tour. I just can’t believe a walk in a university could be that rewarding, but this is no ordinary school; it has pumped out constant stream of engineers and entrepreneurs for generations. Check out the pictures below and map to follow as I suggested.

  1. Cactus Garden/Stanford Mausoleum/Angel of grief
  2. Cantor Art Museum and Rodin Sculpture Garden
  3. Engineering Quad/Main Campus tour
  4. Hoover Tower
  5. Discovery Walk
  6. Red Barn
  7. Head for dish for the hike or go shopping in Stanford Mall
Posted in 5 Hi-Tech Silicon Valley Tour | Leave a comment

Birth of Silicon Valley – First Tour

By Kashif Javaid

If you have an hour or two, you can take this Silicon Valley 10 mile driving tour and be  part of the high-tech that created Silicon Valley.

Cities covered: Palo Alto, Mountain View, CA

  1. Start with Portola expedition site in El-Palo Alto park
  2. Federal Telegraph Company Historical Marker (913 Emerson st, Palo Alto, CA 94301)
  3. HP Garage (367 Addison Avenue, Palo Alto, CA 94301)
  4. William Shockley Transistors Lab (391 San Antonio Rd, Mountain View, CA 94040)
  5. Fairchild First Headquarter Historical Marker (844 East Charleston Road, Palo Alto, CA 94303)
  6. Computer History Museum or if hungry go to Chef Chu Restaurant where it has served Silicon Valley folks for decades

A Map is given below if you want to follow the tour as I recommended. It should take about an hour or two. When you are walking around, just imagine how some of earlier pioneer of hi-tech entrepreneurs have jumped started this valley. The big names are Fairchild, William Shockley, De-Forrest, HP etc

[Above – El-Palo Alto park]-Check out the tree on upper right side, this tree has historical significance where portola expedition in 1769 stayed after discovering the San Francisco bay. This is huge as this lead to construction of El-Camino real road and development of mission Santa Clara. Walk around the park and you can see a little history of Creek scatter through out the park

[Above – El-Palo Alto park] Plaque under the El-Palo Alto tree

[Above – Federal Telegraph Company marker] Not much to see here except the plaque, but the fact that you standing to a place where true birth of Silicon Valley really really started. Vacuum tubes were first conceived here; this lead to radio and communication revolution and laid the path to bunch of start-ups including HP (see below). Vacuum tubes are mainly replaced by transistors. We will visit the William Shockley transistor lab later in this tour.

[Above – HP Garage] Hewlett and Packard started the multimillion and multinational company in this very garage. Their first product was an oscillator which used vacuum tubes as developed in Federal Telegraph Company in our previous visit. Even though plaque say the Birthplace of Silicon Valley, but there were no silicon transistors invented at that time. so I believe this title should be reserved for our next two visits.

[Above – William Shockley Transistor lab original site] Not much to see here, but right front of the food stand, there lies a true birth of silicon valley. I must admit this birth of silicon valley was build on previous radio and communication tech boom. William Shockley was one of the scientist who is credited the invention of the transistor. He started silicon based transistor lab right in this building. because of his bad management style, eight of his employee left and started their own ventures. One of which we will meet  in the next visit.

[Above – Fairchild Semiconductor company original site] This is the beginning of modern electronics revolution when Robert Noyce, one of eight left from William Shockley lab which is right across the street, started his revolutionary silicon based integrated circuits company. Integrated circuits or IC’s are foundations of our every day electronics from cellphone to television.  It is interesting to note that Fairchild semiconductor is still alive and making integrated circuits, although they have shrunk considerably. They currently located in San Jose.

[Above Chef  Shu’s restaurant] Finally, take a break and have lunch or dinner. Don’t forget to check out all the famous peoples on their wall when you visit this restaurant.

Posted in 5 Hi-Tech Silicon Valley Tour | Leave a comment