Reset VBA Password

 Reset VBA Password

1) Rename XLSM to Zip extension.

2) Don't extract this zip, but Open the file with 7-Zip

3) Copy file from xl -> vbaProject.bin (you can drag and drop from 7-Zip).

4) Don't clse zip application

5) open vbaProject.bin with HexEditor and find for "DPB=" and replace it with "DPx", save the file and move back to xl folder in the zip file.

6) Rename zip with XLSM now, and open the file.

if prompted to "Continue Loading Project", click Yes. If prompted with errors, click OK.Press Alt+ F11 to open the VBA editor.

While press it will show error “Unexpected error (40230)”, just click OK (6 or 7 times) until it goes away.

Then it will open Automatically

Then set a new password and save the file...

Email with Attachment using Excel VBA

One of my friend ask me, is it possible to send multiple emails with attachments using Excel VBA. I said YES, everything is possible. Below is just small code that can help you to do it.

Note: Before you apply following code you need to add reference of OutLook Private Sub SendEmailWithAttachment() Dim outlookApp As Outlook.Application Dim eMail As Outlook.MailItem Dim sourceFile, toEmail as String Set outlookApp = New Outlook.Application Set eMail = outlookApp.CreateItem(olMailItem) For i = 2 To 10 ' this is a loop where you will fatch the data from excel rows sourceFile = Cells(i, 2) ' getting path of the file you added in your sheet eMail.Attachments.Add sourceFile toEmail = Cells(i, 1) & ";" eMail.To = toEmail eMail.Subject = "Add your Fix Subject Line or you can get data from the excel" eMail.Body = "Hi," & vbNewLine & "This is your body area" & vbNewLine & "Thanks" eMail.Send Next i End Sub

Happy Coding :)

Datatables - Keeping selected page number after callback

I save the DataTable state in the local storage to avoid passing the page number all over my app. This is how I do it:

$('#offersTable').dataTable({ "bStateSave": true, "fnStateSave": function (oSettings, oData) { localStorage.setItem('offersDataTables', JSON.stringify(oData)); }, "fnStateLoad": function (oSettings) { return JSON.parse(localStorage.getItem('offersDataTables')); } });

This is very useful when you go to another page and you want to go back (using the back button) to the last selected page.

reference

Sort JSON Array with Value

Example:Sort JSON Array

mydata = [{ "id": 1, "first_name": "Jean", "last_name": "Esome", "age": 63}, { "id": 2, "first_name": "Rubi", "last_name": "Simonassi", "age": 18}, { "id": 3, "first_name": "Theo", "last_name": "Mully", "age": 34}, { "id": 4, "first_name": "Barbabra", "last_name": "Castagnasso", "age": 82}, { "id": 5, "first_name": "Mahmud", "last_name": "Boothebie", "age": 25}]; function sortResults(obj,prop, asc) { obj.sort(function(a, b) { if (asc) { return (a[prop] > b[prop]) ? 1 : ((a[prop] < b[prop]) ? -1 : 0); } else { return (b[prop] > a[prop]) ? 1 : ((b[prop] < a[prop]) ? -1 : 0); } }); } //Call Function sortResults(mydata,'first_name', false); // true = ascending / false = descending

Selecting distinct values from a JSON

 

eData is the JSON array

var items = eData.map((d)=> {return d.year})

var uniq = items.filter(GetUnique)

function GetUnique(value, index, self) { 
    return self.indexOf(value) === index;
}

Replacing SharePoint Designer with VS Code

“Replacing SharePoint Designer with VS Code”

- install VS Code

- Add extension SPGO to your VS Code

- Execute following 2 thing

------ Connect to workspace

Press F1 : write > SPGO Configuration Workspace

put the URL and some relevant default inputs

-- new SPGO.json file will be created something like:

{

  "sourceDirectory": "src",

  "sharePointSiteUrl": "https://domain.sharepoint.com/itBudget",

  "workspaceRoot": "f:\\SPApps\\itBudget",

  "publishWorkspaceOptions": {

    "destinationFolder": "/",

    "globPattern": "f:\\SPApps\\itBudget\\src\\**\\*.*",

    "localRoot": "f:\\SPApps\\itBudget\\src"

  },

  "publishingScope": "SaveOnly",

  "authenticationType": "Digest",

  "remoteFolders": ["/SiteAssets/Custom/js", "/Pages", "/SitePages"]

}

In case if SiteAssets folder is not getting downloaded, try to add the pattern "/SiteAssets/**/*"

Press F1 again: write > SPGO Populate local workspace

enter : registered EMail and Password.

Source found at following URLs

1) Replacing SharePoint Designer with VS Code Part 1: Setting up the Environment

2) Replacing SharePoint Designer with VS Code Part 2: Configuring SPGo in VS Code

3) https://marketplace.visualstudio.com/items?itemName=SiteGo.spgo

Unable to open SharePoint Designer Site

Unable to open SharePoint Designer Site

Remove all the content from following folders to avoid issue

• %APPDATA%\Roaming\Microsoft\Web Server Extensions\Cache
• %APPDATA%\Microsoft\Web Server Extensions\Cache
• %APPDATA%\Microsoft\SharePoint Designer\ProxyAssemblyCache
• %USERPROFILE%\AppData\Local\Microsoft\WebsiteCache
• %APPDATA%\Microsoft\SharePoint Designer