Excel-Buzz

Hello Everyone, This time, I have come up with another VBA code which parses the required stuff in the braces (.*) (assuming reader knows basis Regular Expression syntax). So straightaway, here is the code :- Function RegParse(ByVal pattern As String, ByVal html As String) Dim regex As RegExp Set regex = New RegExp With regex .IgnoreCase = True 'ignoring cases while regex engine performs the search. .pattern = pattern 'declaring regex pattern. .Global = False 'restricting regex to find only first match. If .Test(html) Then 'Testing if the pattern matches or not mStr = .Execute(html)(0) '.Execute(html)(0) will provide the String which matches with Regex RegParse = .Replace(mStr, "$1") '.Replace function will replace the String with whatever is in the first set of braces - $1. Else RegParse = "#N/A" ...

Hello Every One, This time I have come up with another VBA  trick which could be used as a replacement for traditional Index(table,Small(if(Column = Criteria, RowNum),nth)) constructions. This is much efficient than any way around based on Excel general Functions like I have stated earlier. So, for the sake explanation, there are two sheets. One is a Master Sheet which would contain Data in the form of a table having different Headings which could be used as criteria either individually or otherwise. Second sheets is an Extract sheet where data would be pulled from Master sheet based on Some criteria placed in the Extract sheet itself. here are some pictures of Master sheet layout and Extract Sheet.

Hello Everyone, In one of the previous post, I wrote about automating Sending Emails using Excel VBA. This time I have come up with a pretty similar code. The code below sends Outlook Meeting Invitations to recipients on one click. here is the Code:- Sub Send_Invite_Auto() Dim olApp As Outlook.Application Dim olApt As AppointmentItem Set olApp = New Outlook.Application 'Creating Outlook Session Set olApt = olApp.CreateItem(olAppointmentItem) 'Creating an Appointment With olApt .Subject = "Enter the subject here." 'Subject .Start = DateAdd("d", 5, Now) 'Enter Date + Time here. .Recipients.Add ("example@gmail.com") 'Recipient Name, Alias, or any other Attribute. .MeetingStatus = olMeeting 'olAppointmentItem with Meeting status olMeeting 'becom...

Hi Everyone, This time, I have come up a VBA Code to generate Password protected PDFs using Excel. Actually, Excel Vba has .ExportAsFixedFormat Method to generate PDFs but this hasn't any Parameter which takes password to protect the PDFs. So I have used PDFtk Tool which provide Command Line Interface to make PDFs protected using Password. Actually, You can do various things using PDFtk Tool command line varying from creating, merging, Protecting and many other. So Download the PDFtk Tool from the following link:- Download PDFtk Tookit Here are the Steps:- 1. Install the PDFtk Toolkit. 2. Use the following code to Print or Export the Activesheet with a password.

Hello Everyone, This time, I have come up with a code which will merge a particular sheet from Multiple Workbooks in a folder, into one Worksheet. While doing so, it will ignore Row 1st i.e. header row, Hidden and Blank Rows. So before using code, make sure following things:- 1. Change fPath variable in the code to an appropriate Folder Path holding all Input sheets. 2. Make a suitable heading row to match with those in the Input sheets. 3. Actually this code will paste copy data from Current Input sheet and paste it beneath the data imported from Former Input Sheet. 4. If you want more sheets to be imported then you can simply put those worksheets in the fPath folder after getting Former Imported Sheets out of the fPath folder. It will avoid Duplication as Code pastes data in the next of the last filled row and Former sheets will not be considered again. 

Hi Everyone, Sometimes while doing some VBA stuff, we want to target Sendkeys to a particular window which may or may not be active or present. Here is the code which will first check if the Target window is available by trying to activate it by AppActivate and then Sendkeys as desired. If Target window isn't available then you may wait until it shows up or show an error message. So Here are the code:- 1. For waiting until Windows shows up:- Sub Target_Sendkeys() On Error Resume Next 'Resuming next Statement on Error(5) Do Err.Clear 'Reseting the Err AppActivate "Blogger" 'Put the name of title bar of Target window here. DoEvents 'Avoidings hangs Loop While Err.Number = 5 SendKeys "%{ }r", True 'Restoring the window Application.Wait DateAdd("s", 1, Now) '...

Hi Everyone, In this article, I have explained that how you can force Excel to update External workbook links after a fixed interval or instantly. This involves a VBA code, so make sure to Enable Macros while opening workbook to get this done. Actually, I have used a Workbook_Open Event to initialise the Update_Links Subroutine and an Ontime Event that will recall the Update_Links Subroutine after an interval. So here we go:- Steps:- 1. Open your Target workbook which contains External links to other workbooks. 2. Press Alt + F11. Double Click ThisWorkbook Module. Copy and Paste the following code there:- Private Sub Workbook_Open() Call Update_Links End Sub

Hi Everyone, Sometimes we export data from our Softwares to Excel files and find that some data is looking as Numbers but not behaving as Numbers and same is the case with Dates as well. Here in this article, I have tried to overcome this problem with a procedure OR code which will convert the Numbers and Dates stored in Text format to their respective formats. Here are the steps:- 1. Select the Range. The Range should be one columnar Range. Preferably select whole column at a time. 2. Click "Text to Column" Option in Data Tab. 3. Select "Delimited" Option, and click Next. Uncheck all the Dilimiter options. 4. Click Next Again and then click Finished. That it. Now, you will notice that the format has changed.

Hi Everyone, I will start straight away with the code as Purpose is very much clear from the Title. Sending worksheet as Attachment is one way of doing that. But there are some extra steps involved and presentation is another reason why I am choosing this code over manual steps. The following code will mail the desired table or ranges or the whole worksheet to the desired recipients. So here are the the codes:- 1. For Selection  Select a range of cells to be mailed and run the following code:- Sub Mail_Selection_Worksheet() ActiveWorkbook.Save 'Saving workbook to counter '"the MailEnvelope method of Worksheet object failed" error With ActiveSheet.MailEnvelope 'Creating an Email Environment. .Introduction = "Add your Introduction here." With .Item 'Creating a MailItem .To ...