[WordPress Plugin] – The WP Wingman

I was link-surfing from a question on linkedin.com and ended up with an article that referenced a plugin called The Dude. That inspired me to create a plugin that gives you random articles from Barney Stinson’s The Bro Code.

Installation is simple, just unzip and activate the plugin. You should be able to see it in your WordPress Admin Dashboard like this.

WP Wingman in Action

Sounds interesting? Download it from the link below or head over to GitHub and fork it to make the plugin legendary.

The WP Wingman
The WP Wingman
the-WP-wingman.zip
Version: 1.0
7.5 KiB
35 Downloads
Details

Many needles in a haystack – The excel solution

I am sure you have received a huge table of data in excel and have to search for a few keywords (not just one). As an example, let’s say you receive a huge list of employee names, ids etc from HR of employees who need to complete a particular training. You want to identify if any of your team members is on that list. Even with a small 10 member team, searching becomes a chore. Here is how to create a simple macro that can be invoked anytime to perform a looped search.

Setup

Open a new excel workbook. Save the workbook as an Excel Macro-Enabled Workbook. The workbook will get saved with a .xlsm extension. Press Alt+F11 to open the VBA IDE. You will see the Project Explorer to the left. Right click on Microsoft Excel Objects, choose Insert from the menu and select Module. We will be adding our code to this module

01-sheet-setup-excel

Now back to the excel sheet (grid). There is not much setup to do here; Our macro will work with any selection irrespective of the column/row. In any case, if you want to format and beautify this, go ahead and do so.

02-sheet-formatting-excel

On to coding. Double click the Module (most likely called Module1) and add the following code

Option Explicit
 
' MACRO to perform looped find
Public Sub LoopedFind()
 
    ' How to use
    '------------
    ' 1> Select the range of cells which need to be searched
    ' 2> Invoke the LoopedFind macro (Alt+F8 -> Select LoopedFind)
    ' 3> Navigate to the folder where the excel sheet to be searched is located
    ' 4> Results will appear as new columns in the same sheet
 
    Dim FileName As Variant
    Dim SrcWkbook As Workbook
    Dim SrcWksheet As Worksheet
    Dim DestWksheet As Worksheet
    Dim RangeSource As Range
    Dim Cell As Range
    Dim FoundCell As Range
    Dim SrcFilePath As String
    Dim SrcFileName As String
    Dim SrcWksheetName As String
    Dim RowNum As Long
    Dim lColumn As Long
 
    Set RangeSource = Application.Selection
 
    Set DestWksheet = ActiveWorkbook.ActiveSheet
 
    ' Show File Open window with only xls files. If you are
    ' looking for .xls (not the newer fancy .xlsx ones)
    ' changes below
    FileName = Application.GetOpenFilename( _
        FileFilter:="Excel Files (*.xlsx), *.xlsx", _
        FilterIndex:=1, _
        Title:="Select a Workbook")
 
    If FileName = False Then Exit Sub
 
    Application.DisplayStatusBar = True
    Application.StatusBar = "Macro started"
 
    Set SrcWkbook = Workbooks.Open(FileName:=FileName)
 
    For Each SrcWksheet In SrcWkbook.Sheets
 
        SrcFilePath = SrcWkbook.Path
        SrcFileName = SrcWkbook.Name
        SrcWksheetName = SrcWksheet.Name
 
        For Each Cell In RangeSource
 
            ' Identify the last column with data for the current row
            lColumn = DestWksheet.Cells(Cell.Row, Columns.Count).End(xlToLeft).Column + 1
 
            Application.StatusBar = "Currently Finding : " & Cell.Value & " in " & SrcWksheet.Name
 
            Set FoundCell = SrcWksheet.Cells.Find(what:=Cell.Value, _
                    LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False)
 
            If Not FoundCell Is Nothing Then
                RowNum = FoundCell.Row
                DestWksheet.Cells(Cell.Row, lColumn).Value = "› " & SrcWksheetName & " » " & FoundCell.Address
 
            End If
            Set FoundCell = Nothing
 
        Next Cell
 
    Next SrcWksheet
 
    SrcWkbook.Close Savechanges:=False
 
    Application.StatusBar = False
 
End Sub

I won’t go too much into details, but here is the basic algorithm.

03-sample-data-excel

  • Allow user to select the file which needs to be searched in (your haystack basically)
  • Loop through each sheet in the user selected workbook
  • For each cell in the selected source, find if the value exists in the sheet of the opened workbook
  • If match found, populate the next available column beside the cell value being searched (needle) with the worksheet name and cell address

Save the workbook. Add some data to test. I have put in names of some cricketers in my workbook like the image to the right. To search, I select the data and press Alt+F8. This brings the list of available macros. Once you choose "LoopedFind", the code begins executing and you can choose the file with the large amount of data. The final results are here to see.

04-output-and-source

DestWksheet.Cells(Cell.Row, lColumn).Value = "› " & SrcWksheetName & " » " & FoundCell.Address

This line above is the code that outputs the worksheet name and address. However, you can change this to suit your purpose. If you need a specific data retrieved you can manipulate FoundCell.Address to reference another column in the row where data is found and get its value. (Somewhat like a Dynamic VLOOKUP that picks up the filename at runtime)

I wish I had taken some effort to put this piece of code earlier. Not just with names, ids, I see this sheet being useful in a number of places. For one, whenever we work on a release in the mainframe environment, we create an impact analysis report. It is usually a mammoth sheet with the list of all copybook changes and the impacted programs. Out of the 1000 rows, my team would be responsible for a smaller fraction of programs. Instead of manually checking my list of programs against the IAR, I could perform this looped find and quickly know how many my team’s programs are impacted.

Hope you found this useful. Share your thoughts and feedback through the comments section.

[Tutorial] Create a folder structure in Excel

I am creating a knowledge repository for my team and I wanted to create a neat index file. The aim is to have a tree structure and the end result to be a HTML page. I have started with the HTML Kickstart framework and with some help from the internet, I am in the progress of creating a nice intranet page for the repository (I will probably add a post on my experience once complete). In the meanwhile, I need a temporary index that I can share with others so that they can access documents right away.

I decided to go the Excel way, but without macros or other complex steps. You too can create a basic tree structure for any folder to help you keep track of its contents. So if you have the folder ready and Microsoft Excel is just a double-click away, let’s get started.
1 As always start with a blank new workbook. The screenshots are from Excel 2013, but would work on pretty much every version from 2003.

2 Hide Gridlines. From the ribbon, goto View and uncheck the Gridlines box to remove the grid and be left with a blank white worksheet.
0002-Hide-Gridlines-Excel

3 Adjust Column Widths. Select fifteen columns from C through Q to 4. Here are the screenshots on how to do it.
0003-adjust-column-widths-excel

4 Navigate to the folder of choice and with the Shift Key pressed, right click on an empty area of the folder. In the contextual menu, select Open command window here.
0004-folder-right-click-windows

5 List the contents of the directory (including sub-directories) into a text file by issuing a dir /s /b > tree.txt command from the command window.
0005-capture-dir-command

6 Open the text file tree.txt (it will now be visible in the Explorer window) and copy the entire content. Paste the content into columns A & columns C of the excel worksheet.
0005-dir-result-in-excel

7 Select contents of Column A & Column C and sort them on Column C by alphabetical order.
0007-sorted-data-in-excel

8 Select all cells of Column A and change the font color to White (or the background color of the sheet if you have at some point changed it).
0009-font-color-excel

9 Split data into columns with text to columns by selecting all data in column C and the Text to Columns option on screen. The delimiter is \.
0008-delimited-text-in-excel

10 Select all the cells from column C to Q with data and we shall apply some conditional formatting to make the content look better. From the Home tab in the ribbon, select Conditional Formatting and click on New Rule. From the popup window, select “Use a Formula to determine which cells to format” and enter the following formula =AND(NOT(ISBLANK(C2)),C2=C1) Please note that I have filled data from row 2 and cell selection has started from C2
After entering the formula, click on Format and make the following changes. First, within the Font tab, set Color as White (or the worksheet background color). Next, in the border tab, give a right border of your choice. I have chosen a dotted grey line.
0010-conditional-formatting

11 Here is the resultant screen after conditional formatting.
0012-final-columns-after-formatting

12 You can hide (or even delete) the columns which contain the information upto the current folder. In my example, I was in the FirefoxPortable folder and I could hide/delete all columns that contained D: or Program Files or FirefoxPortable. It reduces clutter and makes the sheet more presentable.
0013-hide-columns-in-excel

13 Create hyperlinks in column B to navigate to the directory/file within seconds by entering the formula =HYPERLINK(A2,8) in cell B2. There is nothing magical about the number 8, it is just that it represents a mouse icon in Wingdings. You can choose your own font and your own text instead of 8
0014-make-hyperlink-in-excel

14 Finally, drag the bottom right corner of cell B2 till the last row with data. This copies the formula in cell B2 making all other cells in Column B a hyperlink.
0015-drag-and-fill-formula

There you go, a navigable Folder structure right out of Excel.

Excel Macro : Repetitive Ctrl+C (and some more)

My Excel skills are being tested quite often now. I had to work on a repetitive task to close a large list of support incidents. The input list of incident numbers were in an excel sheet and for each, I had to go to the incident management software and update some fields (primarily drop-down selections) prior to closure. As any gamer would attest, it is not much fun having to switch between two sides of the keyboard when also working with a mouse. Hence the act of Ctrl+C & Ctrl+V would be interrupted by having to use the down arrow and get to the next cell. I decided to write an Excel Macro that would go to the next cell below, select it and copy it. If you think it would be tough, think again. Here is the code.

Public Sub NextRowCopy()
 
    Dim currRow As Integer
 
    currRow = ActiveCell.Row
    currRow = currRow + 1
 
    Range("C" & currRow).Select
    Selection.Copy
 
End Sub

I pasted this in a new module in my workbook (Alt+F11 » Right Click Workbook in Project Explorer to the left and Insert » Module). As you would have guessed, the incident numbers were in column C. After saving the macro, I went back to the workbook. I pressed Alt+F8 to see the NextRowCopy macro in the popup window. Selected it and clicked on Options, assigned it a shortcut Ctrl+Q (again for the same reason that Q was to the same side of the keyboard as C).

Once I updated the Incident, I would do a Ctrl+Q and I would have the next incident number to be worked on.

The And Some More part

Most of the fields to be updated in the system were dropdown selections, but there was a standard text to be updated as well. I couldn’t copy it from excel since it would change my cursor location and I didn’t want to have to open a notepad and cycle three windows for this. I searched for a multiple clipboard manager and decided to use QuickTextPaste. It lets you assign shortcuts to predefined text, run programs and more.

My task was completed and while looking back at my macro, I think I could have had excel do the copy of the predefined text as well. Let us say that the existing data was from columns A through N. I could add the text I needed to paste in Column Q and have the macro check where I was currently and move to the right cell.

Public Sub NextRowCopy()
 
    Dim currRow As Integer
 
    currRow = ActiveCell.Row
    currCol = ActiveCell.Column
 
    If currCol = 3 Then
        ' We are in the incidents column. Switch to column O
        currCol = "O"
    Else
        ' We are in new column O, switch to column C in the next row
        currCol = "C"
        currRow = currRow + 1
    End If
 
    Range(currCol & currRow).Select
    Selection.Copy
 
End Sub

Each time the macro ran, my clipboard had either the incident to be searched or the text to be entered to close the incident. Total time saved:  30 minutes 

[Template] – Meeting Minute Taker in Excel

Slicers are a pretty cool filtering feature available in Excel 2010 and above. In a few words, slicers help you filter a table (pivot or otherwise) by simply clicking on the items you wish to see. You can create multiple slicers for a table, letting you play with data in ways which was not possible with simple Filters. Using filters was often cumbersome since you had to scroll left & right to find the right columns. Data available for filtering would be restricted to available entries from previous filters and often left you wondering which column was filtered causing your table to show partial data.

If I have you hooked on slicers, head over to the post at office.com and be sure to come back here for the meeting minutes template.

Meeting Minutes Template

The template consists of two sheets – Meeting Minutes and Team List. Some data has been pre-populated so that you can see how the slicer works. This data can be safely deleted (Select cells & hit delete, don’t delete entire rows). First, head over to the Team List sheet and enter your team members in the table. As you add more data, you will notice that the table automatically expands and you can see the alternate shading. On the Meeting Minutes sheet, you might want to replace The Science of Deduction with your own team name. Begin entering data in the table below the slicers and you are good to go.

Notes

Here are some additional points you will want to consider

  • The drop-down for Type and Status is from Data Validation. Feel free to change the list, by selecting the column in the table and modifiying the Data Validation Settings.
  • You can add more columns to the Meeting Minutes/Team List. If you add columns after the last column, make sure you the table has expanded to cover the new columns. Excel will automatically do this for you, however, if it doesn’t happen you need to pull the small blue inverted L shape on the last cell on the last row of the table
  • New slicers can be inserted easily. Select an cell within the table, a new tab should be visible in the Office Ribbon called Design (under Table Tools). Click on Insert Slicer and follow the simple instructions on screen
  • You can switch between sheets using the buttons on the left side of the sheet.

Download

Meeting Minute Tracker
Meeting Minute Tracker
Meeting_Minute_Tracker.xltx
21.9 KiB
486 Downloads
Details

Reset restrictions passcode on iPad/iPhone

A friend of mine had setup the restrictions (parental controls) passcode on his iPad and over time had forgotten what it was. His initial internet search brought him to one conclusion – factory reset the device and lose all data. (Even if he took a backup of his iPad now and restored it, the restrictions settings would be restored and he would be back to where he started).

iOS restrictions screen

One post on apple’s support forum mentioned that the settings could be edited from a backup, so I decided to search more on those lines. I came across a video from iDeviceBlogMovies which gave instructions for editing backups. The video refers to outdated iOS, so I decided to write my experience (updated to iOS 8)

You need not Jailbreak your device. This method works fine for all devices.

Remove restrictions Passcode on your iOS device

Built upon from the original youtube video

Step 1: Backup your device: Connect your device to iTunes and manually create a backup (Device > Summary)

iTunes Manual Backup

Step 2: Download iBackupBot: From the icopybot site download the iBackupbot program for either Windows or Mac OS based on your computer’s operating system. It is a trial version, but all features work fine.

Download links for iBackupBot

Step 3: Run iBackupBot: Run the iBackupBot application. You will see a list of available backups in the left portion of the application window.

iBackupBot List of Backups

Step 4: Take a backup of your backup: One can never be too careful these days, so right click on the latest backup and click on Duplicate. Select the target folder to store your backup and let the application complete the duplication process

iBackupBot - Duplicate a backup

Step 5: Navigate to System Files > Home Domain > Library > Preferences: Once you select preferences, the right window pane will be populated with a list of files. The actual list of files varies from device to device, but the file we are looking for is com.apple.springboard.plist.

iBackupbot - Backup structure

Step 6: Editing the com.apple.springboard.plist file: Double click the com.apple.springboard.plist file. Press cancel when it gives a message that you are using the trial version and you need to register. In the XML file, search for SBParentalControlsMCContentRestrictions. Below this <key>, you will have a <dict> and corresponding </dict> tags.

iBackupbot - Parental Controls XML

Press enter after the line with </dict> and add the following two lines

   <key>SBParentalControlsPIN</key>
   <string>1111</string>

What you have done is set the Restrictions Passcode to 1111.

Step 7: Save & Restore: Save the file (Ctrl+S works). Right click the com.apple.springboard.plist file and choose Restore selected item(s) to device. The application will rebuilt the backup and restore it to your device. The step takes a few minutes, so be patient.

iBackupbot - Restore selected item

Step 8: Finished: Your iPad will restart and you can go to the restrictions settings and enter the passcode 1111 to manage restrictions or disable the restrictions passcode.

PS: If things don’t got as intended, restore the duplicate of the backup (from iTunes/iBackupbot) and try again. It could be an error while editing the xml. Please make sure you have sufficient charge on your iDevice before attempting this.

Brute Force algorithm for numeric password protected archives

Long time ago, I stored some documents in a zip file and for good measure password protected it so that no one would be able to extract it. While sounding like a good plan at that time, I now have no clue as to what the password was. The only thing I am sure of is that it is a 6 digit numeric code.

The market is full of tools that let you open a zip file after you have forgotten the password. However, after my recent “won-the-battle-lost-the-war” attempt to convert a unix shell script for Android APK creation into windows, I was keen on writing a shell script that would do what I wanted.

What you need

  1. 7-zip (any version, even the portable one would do)
  2. Notepad (or any other text editor)

Let’s code

Find out the path to the 7zip compression program. If it is installed it should be in your Program Files directory. The executable for the portable version will be within the App/7-Zip directory (App/7-Zip64, if you are running on a 64-bit computer). Note that we need the command line version of 7zip and not the GUI version, though it appears that both would be in the same directory. For the sake of simplicity, copy the zip file to the same folder as your 7z.exe file. Fire up your text editor and enter the following code.

@echo off
rem Brute Force script to extract documents from password protected archive
 
for /l %%X in (100000, 1, 999999) do (
    echo "Processing.. " %%X
    7z e docs.zip -p%%X -o.\%%X
)

Code Explained

set enableDelayedExpansion allows variables to be expanded at runtime instead of at the time of parsing.
the for loop increments the variable X from 100000 to 999999 by 1 in each iteration. /l option indicates that the loop is with a list of numbers
echo is pretty much self explanatory, so onto 7z. The parameter e is for extract, -p is for inputting the password so -p%%X will get expanded as -p100000 for the first iteration
-o is used for specifying the output directory. Each instance of 7zip creates a zero byte file when the password is incorrect. To prevent the batch program from prompting for file overwrite everytime, I will extract to a new folder identified by the password. The folder that finally has the actual content will also be the password that I need (two solutions in one shot).

Iteration 2

As the script started and had created about 1000 folders, I cancelled it. Why? Because (a) 7zip was trying to extract all files (about 500 of them) in each iteration and (b) it would be as difficult to find the folder with the data as it was to manually try each password in 7zip. In the next iteration, I decided that I would try to extract only 1 file (let’s say notes.txt) and instead of putting it in a new directory for every loop, I would rename the file to the password being attempted.

At the end of the script, I can sort by size in the same folder and pick up the password and unzip all the required data.

@echo off
rem Brute Force script to extract documents from password protected archive
 
for /l %%X in (100000, 1, 999999) do (
    echo "Processing.. " %%X
    7z e docs.zip notes.txt -p%%X -o.\output
    cd output
    ren notes.txt %%X.txt
    cd ..
)

I redirected my extract: notes.txt to the output subfolder and renamed it to the current password being attempted. I had trouble getting rename to work from the parent folder, so the lines cd output and cd .. was required.

Continuous improvement

While the script was running, I checked the Windows task manager to see how much CPU intensive the script was. It stayed around 1% for the most and didn’t consume more than 1500K of memory. I decided to do some parallel processing. I created 10 batch scripts each running for 100K possibilities. Since 7zip would try to extract the file with the same name into the same output folder in each “thread”, you can either separate the output folders (as I did) or let each thread try and extract a different file from your zip. On hindsight, the second option would work better (assuming similar files being extracted) since you could sort on size in the output folder and the moment you have a non-zero file created, you can terminate all the batch processes.

@echo off
rem Brute Force script to extract documents from password protected archive
rem Covers numbers from 100000 to 199999
 
for /l %%X in (100000, 1, 199999) do (
    echo "Processing.. " %%X
    7z e docs.zip notes.txt -p%%X -o.\output1
    cd output1
    ren notes.txt %%X.txt
    cd ..
)

Second set

@echo off
rem Brute Force script to extract documents from password protected archive
rem Covers numbers from 200000 to 199999
 
for /l %%X in (200000, 1, 299999) do (
    echo "Processing.. " %%X
    7z e docs.zip notes.txt -p%%X -o.\output2
    cd output2
    ren notes.txt %%X.txt
    cd ..
)

and so on.

At the time of writing, the scripts have been running for around an hour. Though I don’t have my password, I feel good about having learnt a bit on shell scripting and 7zip.

Edit (on 2nd Dec)
Disks formatted in NTFS allow for virtually unlimited number of files in a directory (4,294,967,295). FAT32 has a restriction of 65,534. Hence if you were running the above script on a drive formatted in the older way, you will face issues. Again, who wants to look at a folder that has 99999 files and find one that has data. I refined my script to delete the file if it was empty else rename the file and wait for the user to Terminate the batch program

@echo off
rem Brute Force script to extract documents from password protected archive
rem Covers numbers from 100000 to 199999
 
set file="notes.txt"
set minbytesize=0
 
for /l %%X in (100000, 1, 199999) do (
    echo "Processing.. " %%X
    7z e docs.zip notes.txt -p%%X -o.\output1
    cd output1
	for %%A in (%file%) do set size=%%~zA
	if %size% GTR %minbytesize% (
		echo Not empty file
		ren notes.txt %%X.txt
		set /p continue=Ctrl+C to terminate
	) else (
		del notes.txt
	)
    cd ..
)

This gave me a strange error. I suspect that a for within a for messes things up. So I created a separate batch file to do the filecheck and copied it into the output1 folder. Here are the two scripts

@echo off
rem Brute Force script to extract documents from password protected archive
rem Covers numbers from 100000 to 199999
 
set file="notes.txt"
set minbytesize=0
 
for /l %%X in (100000, 1, 199999) do (
    echo "Processing.. " %%X
    7z e docs.zip notes.txt -p%%X -o.\output1
    cd output1
    call filesize.bat
    cd ..
)

Here is filesize.bat. You need to place it in the outputn directory.

@echo off
rem Check filesize and delete file if empty
set file="notes.txt"
set minbytesize=0
for %%A in (%file%) do set size=%%~zA
if %size% GTR %minbytesize% (
   echo Not empty file
   ren notes.txt %%X.txt
   set /p continue=Ctrl+C to terminate
) else (
   del notes.txt
)

Updated code (more stable) at GitHub. Copy/Fork, it is all up to you.

PS: If you are curious, I managed to crack the password and retrieved my data.

Eztrieve – Useful Tip on Signed Numbers & Packed Decimals

One of my colleagues was working on an EZT to identify records that had negative values in a packed decimal field and report it in a displayable format in an extract file. He moved the field to a working storage [code]WS-BALANCE[code] which was defined as WS-BALANCE W 9 P. The program logic was checking for the value of INPUT-BALANCE as lesser than zero prior to extraction. No data was reported (though File-Aid was able to show multiple records with a -ve value in the file).

We began debugging the program. At the first glance I couldn’t find anything in error with his program. One thing was that I had always defined my packed decimals with a 0 at the end. He decided to give it a shot. The variable was defined as INPUT-BALANCE 100 9 P 0 and he checked for INPUT-BALANCE’s value again. This time the progam extracted records and he had his output. His troubles were not over though, the negative amounts appeared strange in the output file. For example, the number 426 showed up as 42O. The root cause turned out to be in the file definition. The output file layout had a zero to indicate number of decimalsOPDUE-H-BAL 38 17 N 0. This was interpreted as having to store sign and messed up the data. Removing this, fixed the extract.

Two lessons we learnt here were:

  1. Define numeric working storage variables with zero length decimal place so that they can store sign
  2. Do not define numeric output record layout records with zero length decimal places if you plan to use the data as-is for numeric value (such as when being extracted for another system)

Editing Proxy settings via script

Based on where you are using your laptop, you may have to activate or de-activate Proxy settings. For example, when working from my corporate network, I can use the internet only via the company proxy. It is quite cumbersome to go through the entire multi-step process to change the settings each time. With a bit of internet research, I was able to automate the process through VB Scripting. Turns out system proxy settings are available in the registry. By referencing the settings, you can update entries such as enabling proxy, entering server details or even setup automatic configuration scripts.

Proxy Path to the Internet

The registry entry for enabling proxy is HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ProxyEnable. This has two states 0 – to disable and 1 to enable. The proxy server settings is in HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ProxyServer. Here is the code to toggle the proxy settings

Option Explicit 
Dim WSHShell, strSetting
Set WSHShell = WScript.CreateObject("WScript.Shell")
 
' Determine current proxy setting to toggle it
strSetting = wshshell.regread("HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ProxyEnable")
 
If strSetting = 1 Then 
   NoProxy
Else 
   Proxy
End If
 
'Subroutine to Toggle Proxy Setting to ON
Sub Proxy 
   WSHShell.regwrite "HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ProxyEnable", 1, "REG_DWORD"
   WSHShell.regwrite "HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ProxyServer","192.168.26.116:80","REG_SZ"
   Wscript.Echo "Proxy Enabled"
End Sub
 
'Subroutine to Toggle Proxy Setting to OFF
Sub NoProxy 
   WSHShell.regwrite "HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ProxyEnable", 0, "REG_DWORD"
   WSHShell.regwrite "HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ProxyServer","","REG_SZ"
   Wscript.Echo "Proxy Disabled"
End Sub

More Options

If your network admins have a automatic configuration script, you can add it by the following

WSHShell.regwrite "HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings\AutoConfigURL","http://intranet/www.pac","REG_SZ"

If you still want to tweak the script to automatically determine where you are (not geolocation) and set proxy accordingly, you can play around the settings a bit more

ipQuery = "SELECT IPAddress FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled=TRUE" 
 
impLevel = "winmgmts:{impersonationLevel=impersonate}" 
 
Set IPConfigSet = GetObject(impLevel).ExecQuery(ipQuery) 
 
For each IPConfig in IPConfigSet 
    If Not IsNull(IPConfig.IPAddress) Then 
        For i=LBound(IPConfig.IPAddress) to UBound(IPConfig.IPAddress) 
           If Not Instr(IPConfig.IPAddress(i), ":") > 0 Then 
               If Left(IPConfig.IPAddress(i), 3) = "192" Then
                  proxySrv = "192.168.100.25:80"
               If Left(IPConfig.IPAddress(i), 2) = "10" Then
                  proxySrv = "10.100.25.63:8080"
           End If 
        Next 
    End If 
Next

You can then use the field proxySrv to update the registry Proxy entry. The options are endless, hope you found this article useful. Add your comments and thoughts below.

Credits: Various stackoverflow & tek-tips.com answers which served as the starting point to the code

Old meets New: REXX code on GitHub

After a while, I now have access to a mainframe and though my work doesn’t need me to login every day, I am still drawn to play around with REXX. I have been meaning to create a database of custom functions written in REXX that can simply be added to your code and speed up coding.
For example, I have needed to replace one string with another in more than one REXX program. I created my first REXX function wordrpl (Word Replace).
It would be a good idea to build a database of REXX functions and keep it in a place where others can access, and better, contribute. This brings me to my recent experiences with GitHub.

GitHub is a free host for developers that allows one to share code with anyone. It uses the Git revision control system and allows users to “fork” existing projects, build more into it and merge it back so that the original repository now has more content. I have been using GitHub to host a couple of my own projects and also contribute to some really cool repositories like Popular-UIs, Metro-bootstrap. I do realize that putting REXX code in a Git repository, far away from the green screen and the EBCDIC environment is going to entail additional effort. However, when you really think about it, most developers still use the internet for finding solutions to their mainframe problems. Why not Git?

The repository created is rexx-fns. Working with GitHub is a breeze. Sign-up (FREE), fork the rexx-fns repository. You can edit code directly though the web interface or using a desktop application (GitHub has their own application) make as many changes as you want. Commit to the changes and submit a request for merging to the original code repository. It is as simple as that.

Git Flow - Fork, Code and Pull

So far, I have created 3 functions and loaded them.

  • WordRpl: Function to replace text within a string
  • LuhnsCal: Calculate the check digit for a number using the Luhn’s algorithm
  • LuhnsChk: Check if a number is valid by running the Luhn’s algorithm

In the pipeline are functions related to date & time validation. Head over to GitHub and add to this growing repo.

PS: I must ask that you do not place any copyrighted code in the repository. I have no way to validate it and I will take your word when you submit a pull request. If you are in doubt, ask your peers/managers. They will help you. Err on the side of caution, please.