VBA Tips & Tricks

How to create a Trendline Chart using Excel VBA

2012-01-21T08:01:00.000-08:00

Excel VBA - Trendline Charts

Here are some snippets useful to create a TrendLine Chart in Excel

Have used the entire data from the given sheet to create the chart. Have used the UsedRange function to get that.

If you want to have a specified range you can pass that also

Sub Create_TrendLine_Chart_Excel_2003(ByRef oRep As Worksheet, ByVal iLeft As Double, ByVal iTop As Double, ByVal sChartTitle As String, ByRef oSource As Range)
Dim oChts As ChartObjects           '* Chart Object Collection
Dim oCht As ChartObject             '* Chart Object
On Error GoTo Err_Chart
    Set oChts = oRep.ChartObjects
    Set oCht = oChts.Add(iLeft, iTop, 400, 450)

    oCht.Chart.SetSourceData oSource, PlotBy:=xlColumns
    oCht.Chart.ChartType = xlLineMarkers

    oCht.Chart.HasTitle = True
    oCht.Chart.ChartTitle.Text = sChartTitle

    oCht.Chart.Legend.Position = xlLegendPositionRight

    oCht.Chart.HasAxis(XlAxisType.xlCategory) = True
    oCht.Chart.Axes(XlAxisType.xlCategory, xlPrimary).HasTitle = True
    oCht.Chart.Axes(XlAxisType.xlCategory, xlPrimary).AxisTitle.Characters.Text = ""

    oCht.Chart.HasAxis(XlAxisType.xlValue) = True
    oCht.Chart.Axes(XlAxisType.xlValue, xlPrimary).HasTitle = True
    oCht.Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Percentage Done" '.Axes(Type:=XlAxisType.xlValue).AxisTitle.Text = "% Done"
    oCht.Chart.Axes(xlValue).MaximumScale = 1

    oCht.Chart.Axes(xlCategory).TickLabelSpacing = 1
    oCht.Chart.Axes(xlCategory).TickLabels.Font.Size = 8

    'oCht.Chart.SetElement (msoElementPrimaryCategoryGridLinesMajor)
    If Not oCht Is Nothing Then Set oCht = Nothing
    If Not oChts Is Nothing Then Set oChts = Nothing

Err_Chart:
If Err <> 0 Then
   Debug.Assert Err = 0
   Debug.Print Err.Description
   If Err.Number = 94 Then 'Invalid Use of Null Error
        Err.Clear
        Resume Next
   Else
        Err.Clear
        Resume Next
   End If
End If

End Sub

For some reason the above was creating a problem in Excel 2007 and above. Hence created a separate snippet for it

Sub Create_TrendLine_Chart_Excel_2007(ByRef oRep As Worksheet, ByVal iLeft As Double, ByVal iTop As Double, ByVal sChartTitle As String)
Dim oChts As ChartObjects           '* Chart Object Collection
Dim oCht As ChartObject             '* Chart Object
On Error GoTo Err_Chart
    Set oChts = oRep.ChartObjects
    Set oCht = oChts.Add(iLeft, iTop, 400, 450)

    oCht.Chart.ChartWizard Source:=oRep.UsedRange
    oCht.Chart.ChartType = xlLineMarkers

    oCht.Chart.HasTitle = True
    oCht.Chart.ChartTitle.Text = sChartTitle

    oCht.Chart.Legend.Position = xlLegendPositionRight


    oCht.Chart.HasAxis(XlAxisType.xlCategory) = True
    oCht.Chart.Axes(XlAxisType.xlCategory, xlPrimary).HasTitle = True
    oCht.Chart.Axes(XlAxisType.xlCategory, xlPrimary).AxisTitle.Characters.Text = ""

    oCht.Chart.HasAxis(XlAxisType.xlValue) = True
    oCht.Chart.Axes(XlAxisType.xlValue, xlPrimary).HasTitle = True
    oCht.Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Percentage Done" '.Axes(Type:=XlAxisType.xlValue).AxisTitle.Text = "% Done"
    oCht.Chart.Axes(xlValue).MaximumScale = 1
    'oCht.Chart.SetElement (msoElementPrimaryCategoryGridLinesMajor)

Err_Chart:
If Err <> 0 Then
   Debug.Assert Err = 0
   Debug.Print Err.Description
   If Err.Number = 94 Then 'Invalid Use of Null Error
        Err.Clear
        Resume Next
   Else
        Err.Clear
        Resume Next
   End If
End If

End Sub

ChartType = xlLineMarkers makes this Chart a TrendLine. You can try your luck by selecting other types

How to Extract Properties from Excel/Word without Opening File using VBA

2011-11-06T03:34:00.000-08:00

How to get CustomProperties from Excel Workbook/Word Document (VBA) without physically opening the file

There are many cases where we need to get the document property without the file being opened in VBA.

This can be achieved by using the objects available in DSOFile.dll. This file can be downloaded from http://support.microsoft.com/kb/224351

Once this downloaded and installed. You need to add a reference to DSO Ole Document's property library (refer image below)

Function GetPropFromDSO(ByVal sFile As String, ByVal sCP As String) As String

Dim oFil As DSOFile.OleDocumentProperties
Dim oCP As DSOFile.CustomProperties

On Error GoTo Err_Tp
    
    Set oFil = New OleDocumentProperties
    
    oFil.Open sFile, True
    
    Set oCP = oFil.CustomProperties
    GetRevFromDSO = oCP(sCP)
.Value    
    oFil.Close

Err_Tp:
If Err <> 0 Then
    Err.Clear
    Resume Next
End If

End Function

The function gets the Filename and the Property to be extracted and returns the property value.

Here are some important custom properties

How to know if a Excel Workbook has Macro without opening it

How to link Excel Table to ListBox using VBA

2011-09-06T09:18:00.000-07:00

Fill a ListBox from Excel Table using VBA / Populate a ListBox from Excel Table using VBA

Let us take a Excel table as shown below - a list of Top 10 All time hits .

Let us assume that we need to populate the Listbox with values from Column 2

The following code will help you populate the data

Dim oWS As Worksheet
    Set oWS = ThisWorkbook.Sheets(3)
    Me.ListBox1.List = oWS.ListObjects(1).ListColumns("Title").DataBodyRange.Value
End Sub

How to XCOPY files using VBA

2011-05-25T19:53:00.000-07:00

How to copy set of files from one folder to another using VBA / How to run DOS Commands in VBA

After a long hibernation I am posting in this blog, thanks to Yaswi.

There is nothing like using the command prompt. This gives a good satisfaction for any programmer / administrator as s/he moves around the files, typing the commands etc

Here is a simple code that moves all the files from one folder to another using XCOPY. You can use all the options of XCOPY with VBA

Sub Copy_Bunch_Of_Files()

Shell "cmd /c xcopy /y c:\temp\*.* C:\Temp\Backup"

End Sub

How to copy RichTextBox contents to Word document

2010-09-12T20:26:00.000-07:00

How to insert Rich Text Box Content to Word document using VBA

Let us have a form with a RichTextBox and a Command Button as shown below

The following VBA code will copy the Contents of RichTextBox to the First Paragraph of the ActiveDocument

Private Sub cmdCopyRTFContent_Click()
    
    Dim oRange As Word.Range            ' Word Range
    Dim sPath As String                 ' Temp Path
    
    Set oRange = ActiveDocument.Paragraphs(1).Range
    
    sPath = "c:\shasurdata\Temp.rtf"
    
    Open sPath For Output As 1
        Print #1, RichTextBox1.TextRTF
    Close #1
    
    oRange.ImportFragment sPath
    
End Sub

The program Exports the contents of RichTextBox to a RTF file and then imports to the Word document

How to edit Linked Objects using Word VBA

2010-08-25T00:12:00.000-07:00

How to open and edit Linked Excel files from Word using VBA

One can insert an object in word by either linking or embedding. We have already seen How to Read and Edit Embedded objects using VBA, The following code will throw light on accessing a linked object from Word (Excel sheet) and editing the same.

Sub Edit_Linked_Excel_Objects()




Dim oXL As Excel.Application ' Excel App Object

Dim oWB As Excel.Workbook ' Workbook Object

Dim sWB As String ' Linked String

Dim oIShape As InlineShape ' Inline Shape Object



On Error GoTo Err_Report



Set oXL = New Excel.Application



For Each oIShape In ActiveDocument.InlineShapes

If InStr(1, oIShape.OLEFormat.ProgID, "Excel") Then



' Check if the Object is Linked

If oIShape.Type = wdInlineShapeLinkedOLEObject Then



' Get the Source Name of Linked Workbook

sWB = oIShape.LinkFormat.SourceFullName



If Len(Dir(sWB)) <> 0 Then

Set oWB = oXL.Workbooks.Open(sWB, , False)

oWB.Sheets(1).Range("A1").Value = "ID"

oWB.Save

oWB.Close False

oIShape.LinkFormat.Update

Else

MsgBox "Linked file not found"

End If

End If

End If







Next oIShape



Finally:



oXL.Quit

If Not oXL Is Nothing Then Set oXL = Nothing

If Not oWB Is Nothing Then Set oWB = Nothing

If Not oIShape Is Nothing Then Set oIShape = Nothing



Exit Sub

Err_Report:

MsgBox Err.Description & " - " & Err.Number

Err.Clear

GoTo Finally



End Sub

Hide Sheet Tabs using VBA / Hide Excel Sheet Tabs (2007/2010)

2010-08-21T06:04:00.000-07:00

How to Hide Excel Sheet Names using VBA

If you want to hide the Sheet Tab (as shown below) you can do that using Excel Options

Hide / Unhide Sheet Tab using Excel VBA

Uncheck the Show sheet tabs checkbox from Advanced Tab of Options Menu

Hide / Unhide Sheet Tab using Excel VBA

You can do the same through Excel VBA

ActiveWindow.DisplayWorkbookTabs = False

How to Read Excel Sheet embedded in Word Document using VBA

2010-08-06T20:24:00.000-07:00

How to edit Embedded Objects (Excel Workbook) using Word VBA

In our previous posts we have seen how to Embedd an Word Document in Excel Object . Now let us try to read Excel spreadsheet embedded in Word document.

Excel Object Libarary Reference

You need to add a reference to the Excel Object Libary as shown above from Tools --> References from Visual Basic Editor (VBE)

The code loops through the available InlineShapes and activates them if they are Excel Spreadsheet. Then it is assigned to an Excel workbook object, which can be programatically handled.

Sub Edit_Embedded_Excel_Objects()

Dim oWB As Excel.Workbook
Dim oIShape As InlineShape


For Each oIShape In ActiveDocument.InlineShapes
    If InStr(1, oIShape.OLEFormat.ProgID, "Excel") Then
        oIShape.OLEFormat.Activate
        Set oWB = oIShape.OLEFormat.Object
        oWB.Sheets(1).Range("A1").Value = "ProdID"
    End If
Next oIShape

End Sub

The code edits the value of the cell as shown below:

Edited Embedded Object using VBA

See how other Embedded objects are programmed

How to Extract All Formula's in Excel Sheet using VBA

2010-08-06T03:30:00.000-07:00

Highlight all cells containing Formulas using Excel VBA

The following snippet highlights all cells that contain formula

Sub HighLight_Formula_Cells()

Dim oWS As Worksheet
Dim oCell As Range

Set oWS = ActiveSheet

For Each oCell In oWS.Cells.SpecialCells(xlCellTypeFormulas)
    oCell.Interior.ColorIndex = 36
    MsgBox oCell.Formula
Next oCell


End Sub

How to Connect XLSX file (Excel Workbook) through ADO

2010-08-04T19:26:00.000-07:00

Using Excel (Xlsx) file as a database using VBA (ActiveX Data Objects - ADO)

In the past we have already seen how to Connect to an Excel file using ADO and query its contents. That was using Microsoft Excel 2003 or earlier. With Office 2007 the file formats haver changed to XLSX, which might create the following problems

External table is not in the expected format - Excel ADO VBA

or

Could not find installable ISAM.
Excel ADO VBA

to solve that use the following Connection string:

cN.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\comp\documents\visual studio 2010\Projects\ExcelWorkbookDocLevel\ExcelWorkbookDocLevel\ExcelWorkbook1.xlsx;Extended Properties=Excel 12.0;Persist Security Info=False"

Excel VBA Autofilter - Specify Multiple Criteria using Array

2010-07-27T19:57:00.000-07:00

How to pass an Array as Criteria in Excel Autofilter - VBA

After long time let us revisit our good old Autofilter Fruits example. The following figure shows the data available

If you need to filter say Oranges and Apples alone, you can either pass both criteria (Yes! I have avoided using - Mutliple criteria) or can try using an Array where you can pass multiple values



Sub AutoFilter_Using_Arrays()



Dim oWS As Worksheet



On Error GoTo Err_Filter



Dim arCriteria(0 To 1) As String



Set oWS = ActiveSheet



arCriteria(0) = "Apple"

arCriteria(1) = "Orange"



oWS.UsedRange.AutoFilter Field:=2, Criteria1:=arCriteria, Operator:=xlFilterValues



Finally:



If Not oWS Is Nothing Then Set oWS = Nothing



Err_Filter:

If Err <> 0 Then

MsgBox Err.Description

Err.Clear

GoTo Finally

End If

End Sub

If you leave out the Operator in Excel VBA Autofilter- Only Last Value of the Array Will be displayed

You can also pass the values directly like:



oWS.UsedRange.AutoFilter Field:=2, Criteria1:=Array("Apples","Peaches","Grapes), Operator:=xlFilterValues

Program/Macro to Highlight Editable Ranges in Protected Sheet

2010-07-25T01:21:00.000-07:00

How to identify Editable ranges in a protected Excel sheet using VBA

My good friend Srikanth Srinivasan is a Project Manager whom Microsoft will definitely want to hire as evangelist. He uses the functionality of Excel to great extent and made it ubiquitous.

The following code was for him, which highlights the ranges that are not protected in Excel sheet



Sub HighLight_Editable_Ranges()





    Dim oWS As Worksheet

    Dim oRng As AllowEditRange

    

    Set oWS = ActiveSheet

    

    oWS.Unprotect

    

    For Each oRng In oWS.Protection.AllowEditRanges

        oRng.Range.Interior.ColorIndex = 35

    Next oRng

    

    oWS.Protect



End Sub

How to retrieve value from Content Controls using Word VBA

2010-07-23T00:01:00.000-07:00

The following snippet validates the user selection using VBA. This code uses the content control created in previous example - (How to add Content Controls using VBA)

Sub Validate_ContentControl()

Dim oCC As ContentControl
Dim OCCEntry As ContentControlListEntry

Set oCC = ActiveDocument.ContentControls(1)

For i = 1 To oCC.DropdownListEntries.Count
     If oCC.DropdownListEntries.Item(i).Text = oCC.Range.Text Then
        Set OCCEntry = oCC.DropdownListEntries.Item(i)
        ' Check the text against value - can be checked directly with text
        If OCCEntry.Value = 1 Then
            MsgBox "Correct"
        Else
            MsgBox "Try Again"
            Exit Sub
        End If
     End If
    
Next i

How to add Content Controls using VBA

2010-07-22T23:50:00.000-07:00

Add Combobox to Word document using VBA

The following code would add a Combo Box control to the existing Word document:

Sub Add_A_ContentControl()

Dim oCC As ContentControl

Set oCC = ActiveDocument.ContentControls.Add(wdContentControlComboBox, Selection.Range)
oCC.SetPlaceholderText , , "Which Team Won the World Cup 2010"

oCC.Title = "World Cup Teams"
oCC.DropdownListEntries.Add "Spain", 1
oCC.DropdownListEntries.Add "Netherlands", 0
oCC.DropdownListEntries.Add "France", 2
oCC.DropdownListEntries.Add "Uruguay", 3

' Prevents the Control from being deleted
oCC.LockContentControl = True
End Sub

Lock the control by setting the LockContentControl attribute to prevent it getting accidentally deleted.

The content control gets added as shown below

Combobox Content Control in Word VBA

GetObject Error with Internet Explorer

2010-07-05T20:17:00.000-07:00

How to get active Internet Explorer Object using Getobject in VBA

Set IEBrowser = GetObject(, "InternetExplorer.Application")

Using GetObject for Internet Explorer in VBA throws Runtime error 429 - ActiveX can't create object. The solution for this is to use ShellWindows

Runtime error 429 - ActiveX can't create object - GetObject for Internet Explorer

Public Function IENavigate(ByRef IEBrowser) As Boolean

Dim theSHD As SHDocVw.ShellWindows
Dim IE As SHDocVw.InternetExplorer
Dim i As Long
Dim bIEFound As Boolean

On Error GoTo Err_IE
    
    Set theSHD = New SHDocVw.ShellWindows
    For i = 0 To theSHD.Count - 1
        Set IE = theSHD.Item(i)
        If Not IE Is Nothing Then
            If InStr(1, IE.LocationURL, "file://", vbTextCompare) = 0 And Len(IE.LocationURL) <> 0 Then
                If IE.Visible = True Then bIEFound = True: Exit For
                
            End If
        End If
    Next

    If bIEFound = True Then
        Set IEBrowser = IE
        IENavigate = True
    Else
        IENavigate = False
    End If
      
' -------------------------------------
' Error Handling
' -------------------------------------
Err_IE:
    If Err <> 0 Then
        Err.Clear
        Resume Next
    End If
End Function

The above code uses Microsoft Internet controls reference:

Microsoft Internet controls reference

without which the following error might occur

---------------------------
Microsoft Visual Basic for Applications
---------------------------
Compile error:

User-defined type not defined
---------------------------
OK Help
---------------------------

Once you get the Internet Explorer object, you can use it as shown below:



Sub GEt_IE()



  Dim IEBrowser As InternetExplorer

  IENavigate IEBrowser

  If Not IEBrowser Is Nothing Then

    MsgBox IEBrowser.Document.Title

  End If

How to extract file name from FullPath string using VBA

2010-07-02T19:50:00.000-07:00

Extract Name of the File from Path / Fullname using VBA

There are many methods to extract the filename from a given string. You can use FileSystemObject's function GetFileName or can use Arrays to get the last element of the array split by path separator

Here we use even simpler functions like Dir and InStrRev to achieve the same

Dir function will retrieve the name only if the file exists:



strFilePath = "C:\Users\comp\Documents\sample.xlsx"



sFileName = Dir(strFilePath)

If the file doesn't exist, Dir function will return an empty string. The following would be a better option



strFilePath = "C:\Users\comp\Documents\sample.xlsx"



sFileName = Mid(strFilePath, InStrRev(strFilePath, "\") + 1, Len(strFilePath))

Try it out and post the options you use

How to simulate speech Echo in VBA

2010-06-24T20:36:00.000-07:00

The following snippet simulates ( a sort of ) the Echo effect in VBA. This uses Microsoft Speech Object Library



Sub Voice_It_Out()



Dim oVoice As SpVoice                               ' Voice Object



' --------------------------------------------------------------

' Code for http://vbadud.blogspot.com

' --------------------------------------------------------------



Set oVoice = New SpVoice





For iVol = 100 To 10 Step -10

    oVoice.Volume = iVol

    oVoice.Speak "Echo!"

Next iVol



End Sub

VBA : How to convert text file to speech (audio) using VBA

2010-06-24T20:33:00.000-07:00

Text to Speech using Excel VBA : Audio/Speech from input file

If you want to spell out the content of text file using VBA you can do it as shown below:

Sub Speech_FromFile_Example()

Dim oVoice As SpVoice                               ' Voice Object
Dim oVoiceFile As SpFileStream                      ' File Stream Object
Dim sFile As String                                 ' File Name

Set oVoice = New SpVoice
Set oVoiceFile = New SpFileStream

' --------------------------------------------------------------
' Code for http://vbadud.blogspot.com
' --------------------------------------------------------------

oVoice.Speak "This is an example for reading out a file"

sFile = "C:\ShasurData\ForBlogger\SpeechSample.txt"

oVoiceFile.Open sFile

oVoice.SpeakStream oVoiceFile

End Sub

The above code creates a filestream and reads the text file and the Voice object speaks it out!

The code requires Microsoft Speech Object Library (see figure below)

Voice Messages in VBA

How to get Author details from Track Changes using VBA

2010-06-24T20:23:00.000-07:00

Word VBA - extract Revision Author information

If you want to know the details of track revisions, for example, Author name etc the following code will help you:

Sub Get_TrackRevision_Author()

Dim oRev As Revision
Dim oRange As Range

' -----------------------------------------------------------
' Change the line below to suit your needs
' -----------------------------------------------------------
Set oRange = Selection.Range

' -----------------------------------------------------------
' Coded by Shasur for http://vbadud.blogspot.com
' -----------------------------------------------------------

For Each oRev In oRange.Revisions
    MsgBox oRev.Range.Text & " " & oRev.Author
Next oRev


End Sub

The following code provides you more information (like if the comment is inserted / deleted)

If oRev.Type = wdRevisionDelete Then
        MsgBox oRev.Range.Text & " deleted by " & oRev.Author
    ElseIf oRev.Type = wdRevisionInsert Then
        MsgBox oRev.Range.Text & " added by " & oRev.Author
    Else
        MsgBox oRev.Range.Text & " " & oRev.Author
    End If

If you want to know Date of Revision using VBA then the following can be added

MsgBox oRev.Range.Text & " " & oRev.Author & " " & oRev.Date

How to Save Excel Range as Image using VBA

2010-06-13T19:37:00.000-07:00

How to copy Excel Range as Image using VBA / How to export Excel Range as Image

The following code saves the Excel Range (A1:B2) as an image.

It uses the Export function of the Chart object (Refer :How to Save a Chart as Image using Excel VBA)
to save as Image

Sub Export_Range_Images()

' =========================================
' Code to save selected Excel Range as Image
' =========================================

Dim oRange As Range
Dim oCht As Chart
Dim oImg As Picture



Set oRange = Range("A1:B2")
Set oCht = Charts.Add


oRange.CopyPicture xlScreen, xlPicture


oCht.Paste

oCht.Export FileName:="C:\temp\SavedRange.jpg", Filtername:="JPG"

End Sub

How to Compress Pictures in Excel using VBA

2010-05-27T19:57:00.000-07:00

How to Programatically Compress Pictures/Images in Excel using VBA

If you are trying to compress pictures, you will normally be doing using the following dialog:

Compress Pictures Excel Dialog

Compress Pictures Excel Dialog

The same dialog can be automated using Excel VBA and SendKeys as shown below:

Sub Compress_PIX()

Dim octl As CommandBarControl

With Selection
    Set octl = Application.CommandBars.FindControl(ID:=6382)
    Application.SendKeys "%e~"
    Application.SendKeys "%a~"
     octl.Execute
End With

End Sub

Supressing "Compressing Pictures May reduce the quality of your images.." dialog is also taken care by SendKeys

The code uses CommandBarControl to find the Command and then execute the dialog

How to add description to Macro Functions in Excel VBA

2010-05-27T19:43:00.000-07:00

How to add argument description to Macros/User Defined Functions in Excel VBA

User-defined functions are created in Excel for helping the Excel users. It would be good to add descriptions of the arguments used in the functions. This can be done using Application.Macrooptions method

Let us assume a small User Defined Function that takes an argument:

Function A_Sample_UDF(ByVal sArg)

MsgBox "Sample UDF " & sArg

End Function

The following code will add the UDF to information category

Sub Add_UDF()

Dim ArgDes As Variant

ArgDes = Array("First Arg")

Application.MacroOptions Macro:="Personal.XLSB!A_Sample_UDF", Description:="Sample Function", Category:="Information", ArgumentDescriptions:=ArgDes

End Sub

How to Extract TextBox Contents from All Slides using Powerpoint VBA

2010-05-27T19:21:00.000-07:00

Extract Text from Textboxes in Powerpoint slides using VBA

Dedicated to good blogger friend Rahul. This code snippet loops through the slides and extracts the contents of the Textboxes

Sub Extract_TextBox_Text_FromSlides()

Dim oPres As Presentation
Dim oSlide As Slide
Dim oShapes As Shapes
Dim oShape As Shape

Set oPres = ActivePresentation

' --------------------------------------------------
' coded by Shasur for http://vbadud.blogspot.com
' --------------------------------------------------

For Each oSlide In oPres.Slides
    Set oShapes = oSlide.Shapes
    For Each oShape In oShapes
        If oShape.Type = msoTextBox Then
        
            Debug.Print oSlide.Name & vbTab & oShape.TextFrame.TextRange.Text
            
        End If
    Next oShape
Next oSlide



End Sub

How to get OS Version using VBA

2010-05-26T19:43:00.000-07:00

How to retrieve Operating System Information using Excel/Word VBA

The version information of OS can be retrieved using the WIN API functions given below

Private Type OSVERSIONINFO
    dwOSVersionInfoSize As Long
    dwMajorVersion As Long
    dwMinorVersion As Long
    dwBuildNumber As Long
    dwPlatformId As Long
    szCSDVersion As String * 128
End Type

Private Declare Function GetVersionEx Lib "kernel32" _
      Alias "GetVersionExA" (lpVersionInformation As _
      OSVERSIONINFO) As Long

The following sub uses GetVersionEx function to get the Major and Minor version of OS

Sub Get_OS_Version_VBA()

' -------------------------------------------------------------
' Code to Get Version of Operating System through VBA
' -------------------------------------------------------------

Dim oOSInfo As OSVERSIONINFO
oOSInfo.dwOSVersionInfoSize = Len(oOSInfo)


GetVersionEx oOSInfo

' -------------------------------------------------------------
' Coded for http://vbadud.blogspot.com
' -------------------------------------------------------------

MsgBox "Version of Current OS is " & oOSInfo.dwMajorVersion & "." & oOSInfo.dwMinorVersion

End Sub

How to iterate through all Subdirectories till the last directory in VBA

2010-05-22T23:41:00.000-07:00

List all Level SubDirectories using VBA

The following code lists all the directories under c:\Temp

Function GetSubDir(ByVal sDir)

    Dim oFS As New FileSystemObject
    Dim oDir
    
    Set oDir = oFS.GetFolder(sDir)
    For Each oSub In oDir.SubFolders
        MsgBox oSub.Path
        GetSubDir oSub.Path
    Next oSub
End Function

You can call the function like shown below

GetSubDir "C:\Temp\"

The code uses FileSystemObject from Microsoft Scripting RunTime. You need to add reference to this library (see figure below)