The long-forgotten ExecuteExcel4Macro

In this article we describe macro function ExecuteExcel4Macro, a long-forgotten and effectively obsolete function that, nevertheless, helped us automate the recruiting process using only Excel files, a list of candidates and the document library on SharePoint.

Needless to say that it was just a temporary solution and that we have already replaced it with a professional Russian software that automates Talent processes…

Briefly about ExecuteExcel4Macro

Function ExecuteExcel4Macro can read data from one cell of an Excel workbook, no matter which: open or closed, stored on the internal hard drive or on the Net.

See the programming code below and pay attention to the fourth case, where the function reads data from the named cell using only the name of the ‘range’ and the full name of the workbook.

Sub Hello_ExecuteExcel4Macro()
?
'1: From an internal hard drive using the R1C1 references:
Debug.Print _
ExecuteExcel4Macro("'C:\TEMP\[Workbook.xlsx]Worksheet'!R1C1")
?
'2: From an internal hard drive using the named range applied to one cell:
Debug.Print _
ExecuteExcel4Macro("'C:\TEMP\Workbook.xlsx'!NAMED_RANGE_OF_ONE_CELL")
?
'3: From the Net using the R1C1 references:
Debug.Print _
ExecuteExcel4Macro("'https://binaries.templates.cdn.office.net/support/templates/en-gb/[tf10000091.xlsx]Time Sheet'!R5C2")
?
'4: From the Net using the named range applied to one cell:
Debug.Print _
ExecuteExcel4Macro("'https://binaries.templates.cdn.office.net/support/templates/en-gb/tf10000091.xlsx'!WorkweekHours")
?
End Sub        

The limitation of ExecuteExcel4Macro function

Outdated though it is, the function can be adapted to extracting any type of data available in Excel apart from the string more than 255 characters, in which case the function returns nothing.?

As a workaround, the long text in the source workbook needs to be placed into several cells so that the number of characters in each cell would not exceed the limit.

And as for the data types, the function imports data as vbDouble, vbString and vbBoolean.

An example of the application

Let us assume that our users enter data in many Excel workbooks based on the same template and store them to the document library on SharePoint. Say, more than 20 recruiters, more than 1000 job requisitions stored in more than 100 folders on SharePoint.

So as to control the process, the data from all the workbooks needs to be gathered together into one table.

As demonstrated on the picture below, the target workbook has the summary?table and the VBA procedure, which in turn:

1. Checks the named columns of the target table, e.g. Name1 and Name2
2. Searches for the source workbooks in the required directory on SharePoint
3. Imports data from the named celled of the source workbooks into the overall table into the respective named columns

In this article we do not explain how to get the list of the files to be queried because it can be done in many ways depending on the files’ location. For example, we mapped SharePoint as a network drive.

Similarly, we do not explain how to work with cycles and arrays (or collections, if more preferred) because the use of these tools may significantly vary.

However, regardless of all the differences, the underlying concept is the same: data can be easily collected into one spreadsheet from multiple Excel workbooks stored on the Net.

#Excel #SharePoint #ExecuteExcel4Macro