Skip to main content

Posts

Showing posts with the label VBA

Error: the type or namespace name does not exist in the namespace using visual studio 2017

When compiling an error will occur that a name space does not exist. An example error like the message below:
The type or namespace name 'Microsoft.Office.Interop.Outlook' does not exist in the namespace 'Microsoft'(are you missing an assembly reference?)
The system is trying to find a reference for a specific namespace or dll.
One solution for this is to add the reference to the missing namespace.
Find the “solution explorer” window like the image below:


After opening the “solution explorer” window, right click the name of the project just below the solution explorer. Beside the C# icon.
A new window will open like the image below. Click “Add”, then click “Reference”.



After clicking “Reference”, the “Reference Manager” window will open.
Click “COM” option, then use the filter option to go directly to the desired namespace or reference. Since the error is a Microsoft namespace, type “Microsoft” and “Microsoft” namespace will be filtered from the list. See image below…

PowerShell Insert Multiple Lines or text to another file dynamically

Copying and pasting text to another file is a common thing to do, copy and paste done. Boom!
In PowerShell scripting copying and pasting text from one file to another dynamically isn’t as easy as it is done in a spreadsheet or document. Dynamically while the code is running  and doing things in the background without human intervention.
As the odds say, if there’s a will there’s a way. And making things efficient and easy is a good way in life.
If ever a scenario occurs that you need to copy text or lines in a file from one file to another, without breaking the code execution. Script below works just fine. Just insert the code where it is needed.

PowerShell code snippet:
#================================
$reader=[System.IO.File]::OpenText("D:\test-read-paste\main1.txt") #get-content can also be used
$lineNumberx=3 #adjust this number where you need to start inserting the text
#Loop line by line and process while($null-ne ($line=$reader.ReadLine())) {
$textToAdd=$line
$newLineToAd…

Excel Macro get cell values but skip other rows

Raw data that comes in pattern is easy to handle for further processing.
For example, if you have a data that has final value in every 7th row, so basically 1 to 6 is not needed and 7th row is needed, likewise 8th row to 13th row is not needed and 14th row is needed and the pattern goes on that every 7th row is the final data from the previous 6 rows.
If the pattern continues, it will be like: 1-6, 7 final value, 7 -13, 14 final value, 15 to 20, 21 final value, 22 to 27, 28 final value and the pattern goes on up to hundreds or thousands. It will be time consuming to get every 7th row manually.
For graphical representation it will be like:
1 2 3 4 5 6 7 8 9 10 11 12 13 14
From the image above every 7th row (green color) is needed and the rest is discarded.
Other issue comes in if the data has to be processed further in another sheet, doing this manually is so tedious and prone to error.
This is where VBA or Excel macro comes into play and simplify the process. The “step” keyword in VBA or even VB…

Disable Maximize and Minimize control in a form using Visual Studio

How to disable minimizing and maximizing a form in C# or VB.net?
How to create a border less form in Visual Studio?
Images below shows how to disable maximize and minimize control in a form.
Image below shows how to deactivate maximize option in a form:


Image of a form that does not allow maximizing:


Image below shows how to create a border less form, creating a border less form should provide an option within the form how to close the application.


Image of a border less form:



Till next time.. Have fun creating apps in Visual Studio...

================================ Free Android Apps:
Click  links below to find out more:
Excel Keyboard guide: https://play.google.com/store/apps/details?id=chrisjoms.myexcelapplicationguide

Heaven's Dew Fall  Prayer app for Android :
https://play.google.com/store/apps/details?id=soulrefresh.beautiful.prayer
Catholic Rosary Guide  for Android:
https://play.google.com/store/apps/details?id=com.myrosaryapp
http://quickbytesstuff.blogspot.sg/2014/09/how-to-reci…

Outlook VBA search for string in email body

How to search for a string in the body of Outlook emails?

How to search for a string in Outlook emails using VBA?

How to find a string in Outlook email body using VBA?

Outlook VBA code below searches a string on the email body and move the email to a specified folder.

Replace variables below with the actual folders and string to search in Outlook:

Destination_Folder_01
Folder_to_be_Searched
string to be searched

Folder_to_be_Searched - all emails in this folder will be searched and if the string matches the will be moved automatically to the destination folder. 


string to be searched - this string will match on the body of the emails

Sub MailItemContent_Move_Search()
Dim olItem As Outlook.MailItem Dim sText As String Dim myNameSpace As Outlook.NameSpace Dim myInbox As Outlook.MAPIFolder Dim myDestFolder2 As Outlook.MAPIFolder Dim mySearchFolder As Outlook.MAPIFolder
Set myNameSpace = Application.GetNamespace("MAPI") Set myInbox = myNameSpace.GetDefaultFolder(olFolderInbox)
‘Repla…

Word header does not print correctly

Ever printed a document and set the header as 1 inch. After printing the header is adjusted and it’s not the same with the specified settings on the header.
There are numerous solutions to this single issue. Updating printer driver may help to solve this problem. Selecting the proper size of the document before printing that is same size with the paper that is on the printer. And a lot of possible solutions.
Another solution to solve this issue is to check the headers and footers on page setup. On the document click on “File”, click “Print”. On the Print window, click “Page Setup” at the bottom on this window. See image below:




On “Page Setup” window, click the “Layout” tab and adjust the “Headers and Footers” as per requirement. See image below:


After adjust to a desired setting, click “Print” and if everything goes well. Then the printing will be as expected.

Cheers..till next time.
================================ Free Android Apps:
Click  links below to find out more:
Excel Keyboard gu…

How to search for string in Excel

There’s quite a lot of ways to do this in Excel, using Macro or Excel formula will be able to get the desired output.
Macro I think is good if the number of rows is not so large, but if the number of rows is quite large then a buffer overflow error might occur.
Formula can take quite large of data and able to deliver the result as expected.
ISNUMBER is a function that can check whether value is a number or not, the function itself tells what the formula will be doing.
Adding a search function to ISNUMBER then it can be used to search for a string with in a cell or the specified range in the formula.
Example below shows how ISNUMBER can be used to search for a specified string whether it exists or not. The position of the string doesn’t matter as long as the string exist within the cell, the formula will return TRUE, otherwise it will return FALSE if the string does not exist.
Here’s the example:
=ISNUMBER(SEARCH("search string", A5))
The “search string” is the string that will be se…

Excel filter option cannot be found

Working in a large data set in Excel but filter option cannot be seen or found even clicking the sort and filter option multiple times, the drop-down filtering options is nowhere to be found.
Well, just get back to the very basic steps. If it can’t be seen then it might be hidden, right?
So, when working in a large data set but some other rows is not needed and need to be hidden. Just leave the first row visible and do not hide it, then hide other rows.
The drop down filter option is in row 1, so if it is hidden then of course the drop down filter will also go with it.
To unhide row 1 or cell a1, press Ctrl + G. The go to options window will pop-up, type: A1 to go to cell A1. (see pic below)


Then, press ctrl+shift+9 to unhide the row. Or click on the format menu options and select unhide. (see pic below for excel 2016). Then the filter option will be displayed.



The keyboard shortcut I think will work on other versions of Excel.
If you need the filter option in Excel, row 1 must never be hid…