Oct 26, 2012

Folder Details Generator VB Script.

Want to see Folder Structure at a browse of locating the folder. The following  Visual Basic Script generates the Folder details and Stores as a CSV file in the location where you Browsed the Folder. To Use this follow these simple steps.
Steps to be followed:
1) Open a .txt file and copy the following code.

'-------------------Start of code for Folder Details Generator
Option Explicit
'On Error Resume Next

Dim indentSpace,strPrompt,intOptions,strTargetPath,strFolderPath,objShell,wshShell,objFSO,objNewFile,objFolder
Const WINDOW_HANDLE = 0
Const BIF_EDITBOX = &H10
Const BIF_NONEWFOLDER = &H200
Const BIF_RETURNONLYFSDIRS = &H1
Set objShell = CreateObject("Shell.Application")
Set wshShell = CreateObject("WScript.Shell")
Set objFSO = CreateObject("Scripting.FileSystemObject")

'**Browse For Folder To Be Processed
strPrompt = "Please select the folder to process."
intOptions = BIF_RETURNONLYFSDIRS + BIF_NONEWFOLDER + BIF_EDITBOX
strTargetPath = wshShell.SpecialFolders("MyComputer")
strFolderPath = Browse4Folder(strPrompt, intOptions, strTargetPath)

Set objNewFile = objFSO.CreateTextFile("folderDetails.csv", True)
Set objFolder = objFSO.GetFolder(strFolderPath)
' CHANGE STARTS HERE :

Call traverseFolders(strFolderPath, objNewFile, "")


objNewFile.Close
Wscript.Echo "Operation Completed" & Chr(10) & "A file called folderDetails.csv has been generated in the folder from where this script was run."

'routine to count number of files or subfolders
Sub countNumOfSubObjects (fPath , objType , ByRef objCount )

    Dim currFSObj, currFolder, currSubFolders, subFolder
    
    Set currFSObj = CreateObject("Scripting.FileSystemObject")
    Set currFolder = currFSObj.GetFolder(fPath)
    
    Set currSubFolders = currFolder.SubFolders
    
    If objType = "FILE" Then
        objCount = objCount + currFolder.Files.Count
    ElseIf objType = "FOLDER" Then
        objCount = objCount + currSubFolders.Count
    End If
    
    If Not (currSubFolders Is Nothing) Then
        For Each subFolder In currSubFolders
            countNumOfSubObjects subFolder.Path, objType, objCount
        Next
    End If

End Sub


'Traverse routine
Sub traverseFolders(fPath, objNewFile, indent)
    Dim totalSubFolders , totalSubFiles 
    Dim currFSO, currFolder, currSubFolders, currFiles, lname, folder
    Set currFSO = CreateObject("Scripting.FileSystemObject")
    Set currFolder = currFSO.GetFolder(fPath)
    'get subfolders in current folder.
    Set currSubFolders = currFolder.SubFolders
    'Wscript.Echo "In Folder " + currFolder.Name
    
    objNewFile.WriteLine ("Folder path: " + currFolder.Path)
    objNewFile.WriteLine (indent & "Size of this folder: " & FormatNumber((((currFolder.Size)/1024)/1024),3,-1,0,0) & " MB")
    objNewFile.WriteLine (indent & "Number of subfolders: " & currSubFolders.Count)
    objNewFile.WriteLine (indent & "Number of files: " & currFolder.Files.Count)
    objNewFile.WriteLine ("")
    objNewFile.WriteLine ("Folder Name,Total No. of subfolders,Total No. of files,Size (KB),Size (MB)")
    
    'If this folder has subfolders
    If Not (currSubFolders Is Nothing) Then
        'get the details of all the subfolders
        For Each folder In currSubFolders
            totalSubFolders = 0
            totalSubFiles = 0
            
            countNumOfSubObjects folder.Path, "FILE", totalSubFiles
            countNumOfSubObjects folder.Path, "FOLDER", totalSubFolders
            
            objNewFile.WriteLine (folder.Name & "," & totalSubFolders & "," & totalSubFiles & "," & FormatNumber(((folder.Size)/1024),3,-1,0,0) & "," & FormatNumber(((folder.Size) / 1024)/1024,3,-1,0,0))
        Next
    End If

End Sub

'**Browse4Folder Function
Function Browse4Folder(strPrompt, intOptions, strRoot)
    Dim objFolder, objFolderItem

    On Error Resume Next

    Set objFolder = objShell.BrowseForFolder(0, strPrompt, intOptions, strRoot)
    If (objFolder Is Nothing) Then
      Wscript.Quit
    End If
    Set objFolderItem = objFolder.Self
    Browse4Folder = objFolderItem.Path
    Set objFolderItem = Nothing
    Set objFolder = Nothing
End Function
'-------------------End of code for Folder Details Generator
2) Save the txt file with extension ".vbs"
3) Double click the .vbs file
4) It asks you to browse the Folder for which you require Folder details.

5) Browse the folder. Click OK
6) The folder Details will be generated and save s as file in the Folder you Browsed

Isn't that amazing. So start Using. This Helps those who are going with configuration management.

Interactive Quiz in Power Point-VBA programming

Ever Thought of making a interactive  Power point Quiz in a school /college events? Want to give a nice look and feel quiz that displays whether your answer is right or wrong and proceeds you to the next Question. Here is the way. Follow the following simple steps and You are gonna make a quiz that makes you a star in your class.

VBA Macro programming aids this presentation. To start with we need to open VBA editor in power point. The following points takes you through this.


Steps to be followed :
1. Open the powerpoint Presentation. Save it.
2.  Press Alt + F11 or click Tools –> Macro –> Visual Basic Editor to open the VB editor.
3. Now the editor is open paste the following code:
'-------------Code to identify Wrong Answer

Sub Wrong()
MsgBox ("OOPS!! Wrong Answer!! CLICK OK TO PROCEED TO NEXT QUESTION")
SlideShowWindows(1).View.Next
End Sub
'-------------End of Code Code to identify Wrong Answer

'-------------Code to identify Right Answer

Sub Right()
i = i + 1
If (i > 5) Then
MsgBox ("That's right You are doing marvelous !!! Click OK")
End If
MsgBox ("That's right! CLICK OK TO PROCEED TO NEXT QUESTION")
SlideShowWindows(1).View.Next
End Sub


Sub RightLast()

MsgBox ("That's right!!! Click OK")

SlideShowWindows(1).View.Next
End Sub

'-------------End of Code Code to identify Right Answer

4. Save and close the editor.
5. Go to Ppt. Create any Question in the Blank Slide

6. Now Click Inset Tab->Shapes->Action Buttons->Place any option in slide
7. Right Click on any action button and you will see action to be performed.
8. If you are going to place the action button for right answer select Run Macro->Right
9.If you are going to place the action button for Wrong answer select Run Macro->Wrong
10. If you are going to place the action button for Last Question right answer select Run Macro->RightLast
11. You can add any number of Questions Like this.
12 Save and start slideshow.
13. When Prompted click enable macros

Whats more Your Quiz is ready.!!!

Enjoy. You can use different UI For Questions. A sample is shown for you in the picture.







Oct 25, 2012

Macro to missing attachment Outlook-VBA Excel Programming


I just faced problems in sending mails without subject or to wrong recipients(later again recalling it). So I felt like I can write some macro for it. And here it is.
If it’s known to you its ok. Else you can use it.
Steps to be followed :
1. Open outlook
2.  Press Alt + F11 or click Tools –> Macro –> Visual Basic Editor to open the VB editor
3.  Press Ctrl + R or click view –> Project Explorer to open the project on the left side pane.
4.  Expand Project1 and you would find “Microsoft Office Outlook Objects”, Expanding this would show you “ThisOutlookSession”.
5.  Double click on “ThisOutlookSession” and paste the following lines.

‘------------------------------------------------Start of code for missing attachments
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
' Declare a variable to save the body content.
Dim strBody As String

' Conver the body content to lowercase.
strBody = LCase(Item.Body)

' /* Check if the body contains the keyword "attach". */
If Not strBody Like "*attach*" Then
Exit Sub
End If

' /* Check the number of attachment(s). */
If Item.Attachments.Count = 0 Then
MsgBox "No Attachment currently.", vbExclamation, "Message"
Cancel = True
End If
End Sub
‘------------------------------------------------end of code for missing attachments
Save the project and close it. Now try sending an email without subject line. A message box would pop up saying that there is no subject line and would you wish to continue sending the mail. Clicking “yes” would continue sending the mail and Clicking “No” would stop your mail. Once subject is done you will get a recipient check. That’s it.

NOTE:  If you restart outlook or the system.. it  may not won’t work as this macro is not digitally signed. For that Adjust Macro Security or use digital signature.





PDF File scanning using VBA Excel Programming

Ever Wondered How to scan text/Data from a PDF file or document and make it useful or format able data?. No more you need to but a costly software to do this. Here is an easy way. You just need a Microsoft Excel.Wonder How follow these simple steps.

STEPS:
1)Keep cursor on the PDF Schematic.Press CTRL+A or click Select All option
(All the text data will be selected)
2) Copy the data from PDF Schematic
3) Paste it into an Excel Sheet column
4)Select the column (data pasted) and Click on sort A to Z
5)Go to Data in Menu bar and Click on Text to Columns option (A small window will be opened)
6)Click on Delimited -> Click Next ->Select Tab and Space in Delimiters list -> Click Next ->adjacent columns). 

Now you got the raw data. You can record or write  a macro/ VB script to work on this data and extract in the required form. Table / Paragraph, HTML etc..The VBA Example will be added in the following post.

Excel VBA "Automation Error" and "Object library Invalid Error" In VBA Programming. A Simple Solution.

Are you using Excel VBA for some programming. And You started getting Automation Error in Excel?? or object library invalid error.Don't worry here is a simple solution. this problem is because of *.exd files. You can delete them using the following simple steps and your Excel works fine all again.


Please follow the steps to resolve the issue.

These files should be located in the folder: %APPDATA%\Microsoft\Forms\

On Windows XP, it is usually located in:
C:\Documents and Settings\%USERNAME%\Application Data\Microsoft\Forms\

And on Windows Vista/7:
C:\Users\%USERNAME%\AppData\Roaming\Microsoft\Forms\
Then open your Excel and try for the Macro Now.

Note: Close all Opened Excels before following these steps.

Outlook Meeting reminders as SMS to Mobile; Never miss that meeting again.

Missing meetings because You are at home or out from system..Never Miss that Damn meeting again. Here is the solution. Sync your Outlook calendar to Google calendar and that's it ;you will get Meeting notification before the meeting as an SMS. Whats more You are never gonna miss any important meeting. Follow these Simple Steps.


We will set up Google calendar to send reminder SMS for the meetings which are synced to Google calendar from Outlook's calendar.

As a First Step , sign in to Google calendar at https://www.google.com/calendar using your gmail account. Go to settings=>mobile_setup and setup and activate your mobile number.
Download and install Google calendar sync from here . You can read more about the sync app here . Sign into this application using the same Google account which you used to sign-in to Google calendar. Select the type of sync to be performed and the interval of sync.

Now comes a bit tricky part. Google doesn't enable SMS notifications by default for events synced from Outlook. So, we will have to make Google do it! (through scripting). Open a new spreadsheet in Google Docs ( google.com/docs). In the spreadsheet's menu, go to tools=>script_editor . Paste the code:
‘ -------------------------------------------------Script to be added

function SmsReminders() {

var today = new Date();
var Calendar = CalendarApp.getDefaultCalendar();
var events = Calendar.getEventsForDay(today);

for (var i = 0; i<10; i++) {
   events[i].addSmsReminder(30);
}

}

‘--------------------------------------------------Script to be added
Now save the script.
Click "Triggers"  on the same window > Current Script trigger's > Add  a trigger. Set it "Time-driven | Hours timer | Every 1 Hour > Save.
Note: This will run the script every 1 hour and the script would add SMS reminder to all the events it sees in the Google calendar, to be sent 30 mins before the event.
Save the Script and now the script editor window can be closed.
Save the spreadsheet.

You are done! To summarise, we synced our outlook events to google calendar and since google doesn't send SMS reminder for synced events by default, we enabled it though a script. All actions must be performed through the same google account!

You can  leave a comment in case of any difficulty faced

Oct 1, 2012

Tech Tips

Welcome to tech tips. One stop place for most famous tips and Tricks in your Application Programming