Loops

There are many varieties of these, but they are all basically the same in that they will repeat a line, or multiple lines, of code a set number times, or until a condition becomes True or False.   Excel VBA has 5 loops from which we can choose from. Depending on the situation they are … Read more

Categories Uncategorized

Conditional Statement Select Case

Select Case   The other method of checking for single or multiple conditions is the Select Case Function. This is another method used for decision making based on a condition or criteria. It, in my opinion, is much better than If etc. This has the syntax   Select Case <Expression to test> Case <Test1> Do … Read more

Categories Uncategorized

Conditional Statement If…Then…Else Statement

If…Then…Else Statement   Conditionally executes a group of statements, depending on the value of an expression.   Syntax Format: If condition Then [statements] Else [statements]   Or, you can use the block form syntax:   If condition Then [Statement] ElseIf condition Then [Statement] ElseIf condition Then [Statement] ElseIf condition Then [Statement] .  .  .  .  … Read more

Categories Uncategorized

Looping Through Code

Using conditional statements and looping statements (also called control structures), you can write Visual Basic code that makes decisions and repeats actions. Another useful control structure, the WITH statement, lets you to run a series of statements without having to re-qualify an object.     Using Conditional Statements to Make Decisions   Conditional statements evaluate … Read more

Categories Uncategorized

VBA code for export all email address and mail message from outlook to excel

Sub ExportToExcel()On Error GoTo ErrHandlerSet appExcel = CreateObject(“Excel.Application”)Set appExcel = Excel.ApplicationDim wkb As Excel.WorkbookDim wks As Excel.WorksheetDim rng As Excel.RangeDim strSheet As StringDim strPath As StringDim intRowCounter As IntegerDim intColumnCounter As IntegerDim msg As Outlook.MailItemDim nms As Outlook.NameSpaceDim fld As Outlook.MAPIFolderDim itm As ObjectstrSheet = “OutlookItems.xls”strPath = “C:Attendance”strSheet = strPath & strSheet Debug.Print strSheet ‘Select … Read more

Categories Uncategorized

Split word document into multiple documents vba

Option Explicit ‘This goes in the Declarations section of your code module. ‘Hopefully it is already there because you have ticked the ‘Require Variable Declaration’ checkbox. (Tools/Options, Editor tab.) Sub SplitIntoPages() Dim docMultiple As Document Dim docSingle As Document Dim rngPage As Range Dim iCurrentPage As Integer Dim iPageCount As Integer Dim strNewFileName As String … Read more

Categories Uncategorized

Create pdf file from ms word

Sub pdffilesfromword()Application.ScreenUpdating = False Dim DocName As String, PDFPath, Folderpath, From, Till, Message, fs Folderpath = “C:Merged Letters In PDF File From Word” Set fs = CreateObject(“Scripting.FileSystemObject”) If fs.FolderExists(Folderpath) = False Then fs.createfolder (Folderpath) Else: End If From = 1Till = 5 Message = (Till – From) + 1 While From <= Till ActiveDocument.MailMerge.DataSource.ActiveRecord = … Read more

Categories Uncategorized

VBA code for move file to other folder

Sub CheckIfFileExists() Dim LRow As Integer Dim LPath As String Dim LExtension As String Dim LContinue As Boolean Dim MPath As Integer Dim FNAme As String Dim sourceFile As String Dim targetFile As String Dim fso As Object LContinue = True LRow = 2 MPath = 2 LPath = “C:New Folder” LExtension = “.pdf” While … Read more

Categories Uncategorized

Rows and columns, and other new limits in new excel

< p style=”text-align: justify;”>What’s new in Microsoft Office Excel 2007 To enable you to explore massive amounts of data in worksheets, Office Excel 2007 supports up to 1 million rows and 16 thousand columns per worksheet. Specifically, the Office Excel 2007 grid is 1,048,576 rows by 16,384 columns, which provides you with 1,500% more rows … Read more

Categories Uncategorized

Easy-to-use PivotTables

What’s new in Microsoft Office Excel 2007 In Office Excel 2007, PivotTables are much easier to use than in earlier versions of Excel. By using the new PivotTable user interface, the information that you want to view about your data is just a few clicks away—you no longer have to drag data to drop zones … Read more

Categories Uncategorized