Friday, March 23, 2018

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

Thursday, February 8, 2018

SharePoint REST Filters Operators

Filter operators

Following are the various filter operators that can be used with REST interface

eq Equal to
ne Not equal to
gt Greater than
ge Greater than or equal to
lt Less than
le Less than or equal to
and Logical and
or Logical or
not Logical negation
add Addition
sub Subtraction
mul Multiplication
div Division
mod Modulo
( ) Precedence grouping

Monday, February 5, 2018

SP.ClientContext (siteURL) error this.set_formDigestHandlingEnabled

When you are using Site URL to get ClientContext use 'new' keyword while define else you will see error...
this.set_formDigestHandlingEnabled


example.
$(document).ready(function () {

    //get context 
    context = new SP.ClientContext(siteURL);
    web = context.get_web();

});