How to Compress Pictures in Excel using VBA

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

See also: How to Increase / Decrease Size of Images in Word Document using VBA

How to add description to Macro Functions in Excel VBA

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

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

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

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)

See also VBA Dir Function to Get Sub Directories

How to retrieve images of the Word document using VBA / Extract images in

How to Save Word Document as WebPage using VBA

I have some lazy friends who come up with strange requirements. One such guy wanted me to extract images from Word document into a separate folder, which he want to use later. I found that saving the Webpage creates the images in a folder and that would be enough for the sloth friend. Here is the code snippet of that.

This code Saves the given document in Temporary folder (See How to get Temp folder using VBA)

Then it deletes the file and folder from the location (See Different Ways to Delete Files in VBA) to be sure there is no clash.

It then saves the document and loops through the Images folder using Dir function (See Dir Function in VBA) and stores them in an array

Function Save_As_HTML(ByRef oTempWd As Document) As Boolean


Dim sDir
Dim iDir As Integer
Dim oShp As Word.Shape                      ' Word Shape Object

On Error GoTo Err_Save_As_HTML

sTempFolder = GetTempFolder()
sImageFolder = sTempFolder & "Save_As_HTML_files\"

Delete_File sTempFolder & "Save_As_HTML.html"
Delete_File sImageFolder & "*.*"
RmDir sImageFolder


oTempWd.SaveAs sTempFolder & "Save_As_HTML.html", FileFormat:= _
        wdFormatHTML, LockComments:=False, Password:="", AddToRecentFiles:=True, _
        WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
         SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _
        False


oTempWd.Close (False)
Erase arImageFiles

sDir = Dir(sImageFolder & "*.gif") ' You can also use PNG format
Do Until Len(sDir) = 0
    iDir = iDir + 1
    arImageFiles(iDir) = sDir
    sDir = Dir
Loop

Err_Save_As_HTML:
If Err <> 0 Then
   Debug.Assert Err = 0
   Debug.Print Err.Description
   Err.Clear
   Resume Next
End If

End Function 

How to Increase / Decrease Size of Images in Word Document using VBA

Scaling of Pictures / Images using Word VBA

The following code scales all the pictures of the Word document

Function Scale_Pictures(ByRef oTempWd As Document) As Boolean


Dim oShp As Word.Shape                      ' Word Shape Object


' -------------------------------------
' Scale Shapes Height and Weight
' -------------------------------------
For Each oShp In oTempWd.Shapes
    oShp.ScaleHeight 0.6, msoFalse
    oShp.ScaleWidth 0.6, msoFalse
Next oShp

End Function 

How to insert field in Word 2007/2010

To insert Field in a Word document, select the Quick Parts option from Insert tab



Click on the Field, which will throw the following dialog box

Select appropriate field and enter the values

How to Select a Web Page from Excel / Word using VBA

How to Select a Web Page from using Excel / Word VBA

The following code uses GetOpenFilename method to select the Webpage (HTML here)

Sub Select_A_HTMLPAGE()
Dim fHTML As Variant
fHTML = Application.GetOpenFilename("Webpage (*.htm*), *.htm*", _
  , "Select a HTML page:")
 If fHTML <> False Then
      MsgBox "Selected file is" & CStr(fHTML)
 End If
End Sub

How to Check Internet Connectivity using VBA

The following code snippet uses API functions to check Internet connectivity and also the type of connection
:

Public Declare Function InternetGetConnectedState _
                         Lib "wininet.dll" (lpdwFlags As Long, _
                                            ByVal dwReserved As Long) As Boolean

Private Declare Function InternetGetConnectedStateEx Lib "wininet.dll" Alias "InternetGetConnectedStateExA" ( _
ByRef lpdwFlags As Long, _
ByVal lpszConnectionName As String, _
ByVal dwNameLen As Long, _
ByVal dwReserved As Long) As Long


'Local system uses a modem to connect to the Internet.
Private Const INTERNET_CONNECTION_MODEM As Long = &H1

'Local system uses a LAN to connect to the Internet.
Private Const INTERNET_CONNECTION_LAN As Long = &H2

'Local system uses a proxy server to connect to the Internet.
Private Const INTERNET_CONNECTION_PROXY As Long = &H4

The following API functions are used

Function IsConnected() As Boolean
    
    Dim Stat As Long
    
    IsConnected = (InternetGetConnectedState(Stat, 0&) <> 0)
    
    If IsConnected And INTERNET_CONNECTION_LAN Then
        MsgBox "Lan Connection"
    ElseIf IsConnected And INTERNET_CONNECTION_MODEM Then
        MsgBox "Modem Connection"
    ElseIf IsConnected And INTERNET_CONNECTION_PROXY Then
        MsgBox "Proxy"
    End If
End Function

If you want to know just if it is connected or not you can use the following:

CBool(InternetGetConnectedStateEx(0, vbNullString, 512, 0&))

How to Dynamically Change Userform's Control properties from Excel Sheet using Excel VBA

How to Draw Rectangle in VBA using Excel Data

This is an exclusive request from Phil. If you find it interesting, I am more glad. The idea is to draw/resize a rectangle in userform based on values from Excel sheet.

I am using a label control for rectangle. Let us add a label control to userform and name it as LabelRect

To the WorkSheet_Change event of the required sheet add the following event

Private Sub Worksheet_Change(ByVal Target As Range)

Dim oLbl As MSForms.Label

Set oLbl = UserForm1.LabelRect

If Target.Address = "$B$1" Or Target.Address = "$B$2" Then

    If IsNumeric(Range("B1").Value) = True Then oLbl.Height = Range("B1").Value
    If IsNumeric(Range("B2").Value) = True Then oLbl.Width = Range("B2").Value
    oLbl.BackColor = vbGreen
    
    UserForm1.Show (False)
    
End If

End Sub

The code will get executed when there is a change in Value in column B1 and B2. Hence the label in the userform will be adjusted accordingly as shown below: