Thursday, March 29, 2018

Only Numeric with Single Decimal - JavaScript


//Keypress event on a Class
$('.onlyNumeric').on('keypress', function(e) {
return isNumber(event, this);
   });

//Function to validate Number, Decimal and Negative sign
function isNumber(evt, element) {
        var charCode = (evt.which) ? evt.which : event.keyCode

        if (
            (charCode != 46 || $(element).val().indexOf('.') != -1) &&  
            (charCode < 48 || charCode > 57))
            return false;

        return true;
    }    

Friday, March 23, 2018

Send Email with Powershell

Example1
# You must have Site Collection Access to use following process.

function SendEmail($requestor)
            {
                try
                {
                    $emailTo = $requestor
                    $emailSubject = "Test"
                    $emailBody = "Hi,This is a Test Email."

                    $emailUser = "yourEmail@myDomain.com"
                    $emailPwd = "pass@123"
                    
                    Send-PnPMail -To $emailTo -Subject $emailSubject -Body $emailBody -From $emailUser -Password $emailPwd
                    Write-output "Email sent: Successful!"
                }
                catch
                {
                    #known suppression
                    Write-output "Email sent: Failed! Reason:"$_.Exception.Message.ToString()
                }
            }
SendEmail("myEmail@myDomain.com")


Example 2
# Relay emailID example.

$smtp = new-object Net.Mail.SmtpClient("relay-mailbox.com") #Dns name or IP

$smtp.Send("fromEmail@myDomain.com", "toEmail@myDomain.com", "PowerShell Email", "This is a email from powershell")

Delete List Item with Excel Data Using Powershell


function DeleteListItem(){

    $inputfile = Get-FileName "c:\"
    if($inputfile -ne "")
    {
        $objExcel=New-Object -ComObject Excel.Application
        $objExcel.Visible=$false
        $WorkBook=$objExcel.Workbooks.Open($inputfile)
        $worksheet = $WorkBook.sheets.Item(1)
    
        ##########################
        $listName = "PnPList"

        $intRowMax = ($worksheet.UsedRange.Rows).count
        for($intRow = 2 ; $intRow -le $intRowMax ; $intRow++)
        {
            $ID = $worksheet.cells.item($intRow,1).value2
            Remove-PnPListItem -List $listName -Identity $ID -Force # -Force will delete without confirm
        }  
        $WorkBook.close()
        $objexcel.quit()

        ##########################
    }
}

Function Get-FileName($initialDirectory)
{
    [System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") | Out-Null
    
    $OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog
    $OpenFileDialog.initialDirectory = $initialDirectory
    $OpenFileDialog.filter = "xlsx (*.xlsx)| *.xlsx"
    $OpenFileDialog.ShowDialog() | Out-Null
    $OpenFileDialog.filename
}

DeleteListItem # Call Function

Update List Item with Excel Data Using Powershell



function UpdateListItem(){

    $inputfile = Get-FileName "c:\"
    if($inputfile -ne "")
    {
        $objExcel=New-Object -ComObject Excel.Application
        $objExcel.Visible=$false
        $WorkBook=$objExcel.Workbooks.Open($inputfile)
        $worksheet = $WorkBook.sheets.Item(1)
    
        ##########################
        $listName = "PnPList"

        $intRowMax = ($worksheet.UsedRange.Rows).count
        for($intRow = 2 ; $intRow -le $intRowMax ; $intRow++)
        {
            $ID = $worksheet.cells.item($intRow,1).value2
            $Title = $worksheet.cells.item($intRow,2).value2
            $Description = $worksheet.cells.item($intRow,3).value2

            $itemValue = @{"Title"=$Title;"Description"=$Description} #item value
            Set-PnPListItem -List $listName -Identity $ID -Values $itemValue            
        }  
        $WorkBook.close()
        $objexcel.quit()

        ##########################
    }
}

Function Get-FileName($initialDirectory)
{
    [System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") | Out-Null
    
    $OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog
    $OpenFileDialog.initialDirectory = $initialDirectory
    $OpenFileDialog.filter = "xlsx (*.xlsx)| *.xlsx"
    $OpenFileDialog.ShowDialog() | Out-Null
    $OpenFileDialog.filename
}

UpdateListItem # Call Function

Create List Item with Excel Data Using Powershell


function AddListItem(){

    $inputfile = Get-FileName "c:\"
    if($inputfile -ne "")
    {
        $objExcel=New-Object -ComObject Excel.Application
        $objExcel.Visible=$false
        $WorkBook=$objExcel.Workbooks.Open($inputfile)
        $worksheet = $WorkBook.sheets.Item(1)
    
        ##########################
        $listName = "PnPList"

        $intRowMax = ($worksheet.UsedRange.Rows).count
        for($intRow = 2 ; $intRow -le $intRowMax ; $intRow++)
        {
            $Title = $worksheet.cells.item($intRow,1).value2
            $Description = $worksheet.cells.item($intRow,2).value2

            $itemValue = @{"Title"=$Title;"Description"=$Description} #item value
            Add-PnPListItem -List $listName -Values $itemValue
        }  
        $WorkBook.close()
        $objexcel.quit()

        ##########################
    }
}
# Below function for Dialog Box to select file.
Function Get-FileName($initialDirectory)
{
    [System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") | Out-Null
    
    $OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog
    $OpenFileDialog.initialDirectory = $initialDirectory
    $OpenFileDialog.filter = "xlsx (*.xlsx)| *.xlsx"
    $OpenFileDialog.ShowDialog() | Out-Null
    $OpenFileDialog.filename
}

AddListItem # Call Function

Monday, March 19, 2018

capitalize , lowercase, uppercase to HTML input Text

I have found number of JavaScript to convert text to upper while keypress. But the way we can use CSS is the best... capitalize , lowercase, uppercase

Css :
input.upper { text-transform: uppercase; } input.lower { text-transform: lowercase; } input.capital { text-transform: capitalize; }

Input Field Example.

UPPER CASE :
lower case :
Capital Case :

Saturday, February 24, 2018

Download URL Link using VBA

'Add module in VBA, Copy and Paste following code into it.

Option Explicit

Private Declare Function URLDownloadToFile Lib "urlmon" _
Alias "URLDownloadToFileA" (ByVal pCaller As Long, _
ByVal szURL As String, ByVal szFileName As String, _
ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

Dim Ret As Long

Sub Sample()
    Dim ws As Worksheet
    Dim LastRow As Long, i As Long
    Dim strPath As String

    '~~> Name of the sheet which has the list
    Set ws = Sheets("Sheet1")

    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

    For i = 1 To LastRow '<~~ 2 because row 1 has headers
        Ret = URLDownloadToFile(0, ws.Range("A" & i).Value, ws.Range("B" & i).Value, 0, 0)
'URLDownloadToFile(0, ,
        If Ret = 0 Then
            ws.Range("D" & i).Value = "File successfully downloaded"
        Else
            ws.Range("D" & i).Value = "Unable to download the file"
        End If
    Next i
End Sub