Saturday, 4 June 2016

VBA Macro to Convert Dates to MDY Format

Text to Columns Method for Dates Conversion to MDY Format
Sub Convert_Dates()

    CL = Selection.Column
    CLN = Split(Cells(, CL).Address, "$")(1)
    
    Selection.TextToColumns Destination:=Range(CLN & 1), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 2), TrailingMinusNumbers:=True

    Selection.TextToColumns Destination:=Range(CLN & 1), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True

    Selection.TextToColumns Destination:=Range(CLN & 1), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 3), TrailingMinusNumbers:=True

End Sub

Friday, 3 June 2016

How to Export Excel Range as a Picture to Target Folder

VBA Macro to Export Excel Named Range as a Picture to Target Folder
The following Macro will export the each Named Range like "MyRng*" as a image to the specified target folder.
Sub Export_Ranges_As_Images()
 Dim Nam_Rng As Range
 Dim RngExp As Range
Dim  Nam as Name

 K = 123456

 For Each Nam In Names

    If Nam.Name Like "MyRng*" Then
       K = K + 1
       
       Set RngExp = ThisWorkbook.Names(Nam.Name).RefersToRange
       Set RngSht = ThisWorkbook.Sheets(RngExp.Parent.Name)
       
       RngExp.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
       
       Set Tgt_Cht = RngSht.ChartObjects.Add(Left:=RngExp.Left, Top:=RngExp.Top, _
                       Width:=RngExp.Width, Height:=RngExp.Height)

           With Tgt_Cht
               .Name = "TempArea"
               .Activate
           End With
    
       RngSht.ChartObjects("TempArea").Chart.Paste
       RngSht.ChartObjects("TempArea").Chart.Export "C:\Users\Tamatam\Desktop\Temp\" & "Image_" & K & ".jpg"
       RngSht.ChartObjects("TempArea").Delete
    End If

 Next

 End Sub
------------------------------------------------------------------------------------------------------------------------------
Output :


Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts