VBA to paste text from clipboard | VBA program to paste text from clipboard

You can use below code to paste data from clipboard.


Sub PasteFromClipboard(b As Boolean)
    Dim clipboard As MSForms.DataObject
    Dim str1 As String

    Set clipboard = New MSForms.DataObject
   
    clipboard.GetFromClipboard
    str1 = clipboard.GetText
End Sub

VBA to copy text to clipboard | VBA program to copy text to clipboard

You can use below code to copy a text to clipboard.

Sub CopyToClipboard(strCopy As String)
On Error Resume Next
    Dim clipboard As MSForms.DataObject
    Dim strSample As String

    Set clipboard = New MSForms.DataObject
    clipboard.SetText strCopy
    clipboard.PutInClipboard
On Error GoTo 0
End Sub

How to get a list of all the sheets in an Excel file in Excel VBA Macros | VBA code to get list of sheet names

How to get a list of all the sheets in an Excel file in Excel VBA Macros | VBA code to get list of sheet names :

Sub GetsheetNames()
Dim sht As Worksheet
'for backup
For Each sht In ActiveWorkbook.Sheets
    Selection.Value = sht.Name
    Selection.Offset(1, 0).Select
Next sht

End Sub

Add attachments to an email object Excel VBA | Email automation | Insert Attachments VBA

Sometimes when you work on bulk emailing using VBA, you have to insert attachments to the email.

In such cases, you can use the Add method under mail item to insert attachment to the email object.

Please refer the code below.


Sub AddAttachment() Dim MailItem As Outlook.MailItem Dim MailAttachments As Outlook.Attachments Set MailItem = Application.CreateItem(olMailItem) mailItem.Attachments.Add "C:\Documents\abcd.xlsx" mailItem.Display End Sub

Ask user to select a file during the macro VBA | How to get pop up window to select a file in VBA

Ask user to select a file during the macro VBA :


Function GetAFileName(StrPath As String) As String
Dim Fldr As FileDialog
Set Fldr = Application.FileDialog(msoFileDialogFilePicker)
With Fldr
    .Title = "Select a File"
    .AllowMultiSelect = False
    .InitialFileName = StrPath
    If .Show <> -1 Then MsgBox "You Should Select a File", , "Warning": GetAFileName (StrPath)
    GetAFileName = .SelectedItems(1)
End With
Set Fldr = Nothing
End Function

How to block users opening an Excel file in VBA | Secure Excel file with VBA

If you want to secure your file to make sure that only you can open it, then you can add the below code in Workbook_Open function in your Excel file.

Note : Make sure you enter some thoughtful condition to make sure that only you open the file referring the code below.


Private Sub Workbook_Open()
    If Environ("username") <> "mm" Then ThisWorkbook.Close False
End Sub

Ask user to select a folder during the macro VBA | How to get pop up window to select a folder in VBA

Ask user to select a folder during the macro VBA | How to get pop up window to select a folder in VBA


Function GetFolder(StrPath As String) As String
Dim Fldr As FileDialog
Set Fldr = Application.FileDialog(msoFileDialogFolderPicker)
With Fldr
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    .InitialFileName = StrPath
    If .Show <> -1 Then
    MsgBox "You Should select a Folder!", , "Warning": GetFolder (StrPath)
    End If
    GetFolder = .SelectedItems(1)
End With
Set Fldr = Nothing
End Function

Workday formula using Excel VBA / How to find next working day in Excel VBA

VBA doesn't have an inbuilt function to find the next working day / previous working day.

You can use below code if you are using a higher version of Excel.

Function FindWorkDay(InpDate As Date, DaysCount As Long)
FindWorkDay = WorksheetFunction.WorkDay(InpDate, DaysCount)
End Function

Tips to crack RPA interview for freshers

Hi RPA Aspirants,

In this post I'm giving important tips to RPA freshers who wanted to pursue RPA as their career :

First of all let's look into RPA technology.

Robotic process automation (RPA) is the latest technology to automate business processes. In traditional desktop automation tools, a developer automate a list of actions to automate a business process by writing codes in scripting language and also connects to the back-end system using internal application programming interfaces (APIs) or scripts. But in RPA, systems develop the action list by watching the user perform that task in the application's graphical user interface (GUI), and then perform the automation by repeating those tasks directly in the GUI. This can lower the barrier to use of automation in products that might not otherwise feature APIs for this purpose.


Important Tips

1) Be thorough on basics: 

If you are new to RPA, the interviewer will more concentrate to test your knowledge on the basics of PRA and also in general programming. This may include questions regarding the technology, RPA products, basic programming concepts, basics actions on the RPA tool which you trained.

2) Refer Frequently asked Questions: 

Do a very good research in Google and also in RPA forums to find the frequently asked questions. I will also post FAQs very soon in my blog.

For example in Blueprism, most important parts are Login agent, Work queue configuration and Scheduler.

In Automation Anywhere, you should prepare for questions in Meta Bots, Task Bots and Object Cloning.

In UiPath, key elements are workFlow, Sequence and Orchestrator.


3) Practice sample projects: 

Major RPA vendors are providing trial versions and also a discussion forums or community. You should refer the use cases in the forums and do small projects yourself.

4) Sell yourself: 

Like any other interviews, the presentation really matters.

5) Be yourself. 

A certificate from a RPA vendor will not land you a job. RPA is very wide and deep. If you act like a pro in front of the interviewer, he might tend to ask more questions from real world scenarios which you cannot answer if you actually don't have a hand on real time projects.

I will be posting more RPA interview stuff very soon in my Blog. Best of luck.

How to get computer name using VBA Excel MS Access/ VBA code to find the computer's name

Sometimes you might require to find user's computer name various scenarios like to track which computer has accessed the file or database, etc.

We can find the computer's name using below VBA code.

Function FindComputerName() as String
      FindComputerName = environ(6)
End Function


Note: Copy paste above function to your module, You can use it as an Excel function or call from a sub procedure.