Posts

Showing posts with the label VB.NET

Excel find last row with value in a specific column

1. How to find last row with value using vba in a specific column?

2. How to find last row with value  using vba in a worksheet?

The questions above are entirely two different scenarios.

The first question will find the last row with value in a specific column within the worksheet.

While the second question find the last row with value of the entire worksheet.

The first question is applicable if you need to find the last row in different columns.

Let's say Column 1 or Column A, or Column 2 or Column B etc.

Example code below will find the last row in a specific column via VBA.

===================
'command Cells(Rows.Count, 1) = find the last row with value in Column 1 for Sheet3

Dim last_row As Integer

last_row = Worksheets("sheet3").Cells(Rows.Count, 1).End(xlUp).Row

Msgbox last_row

'replace 1 with the desired column 
'ex: command Cells(Rows.Count, 7) = find the last row with value in Column 7
===================

If you don't need to specify a worksheet or a sp…

VB.Net remove line feed or new line

In VB.net or any programming language, if the program requires getting data from external programs, results or data from third party software that needs to be processed  may or may not have expected desired output.

For example, getting a string from a third party software. String is quite straight forward and it's quite basic.

But the issue arises,  if the desired output requires a specific string or text and other text should be discarded or deleted. Regex or any other methods might help on getting the specific text or data.

One basic issue, if the string needs to be formatted in one line but a line feed or a new line keeps breaking the line.

The first instinct that I think of, is to use "Trim" function but it doesn't solve the issue. Trim, Ltrim or Rtrim just delete spaces but it doesn't delete line feed.

In VB.Net a line feed can easily be removed by this code snippet below:

Dim strNoLineFeed As String = String_with_Line_Feed.Replace(vbCr, "").Replac…

Vbscript send email via Office 365

Sending automated email via Office 365 account requires the use of SSL or in some instances you need to use SSL and TLS both enabled on the script.

Below is an example that works fine using Vbscript.

This is useful if you need to automate email messages via Task Scheduler or Windows event attached to this script, when the event is triggered the script will execute the VBS file and send the message.

Modify the message subject, body and of course, the email domain name, user and password.

Open notepad copy and paste the code, save as "filename.vbs"

The quotes are important when doing save as via notepad or else the file will be save as .txt.

Enjoy! Cheers!

'=======================
Dim xstrAll(), strMessage,intupper()

Set objFSO = CreateObject("Scripting.FileSystemObject")

'Send the mail
SentMail


Sub SentMail

strMessage =  strMessage & "Your other message..."

strTo= "your.office365@domain_account.com" 
strFrom="your.office365@domain_accou…

Excel formula fixed cell address

How to insert a formula with a fixed cell address? Absolute referencing will come to the rescue.

Absolute reference in Excel is prefix with a dollar ($) sign.

$A$1: This means that Column "A" and Row "1" will not change when copied to another cell.

Example:

=SUM(A$1,$A2,$A$1)


The above formula when copied from one cell to another cell, $A$1 will remain the same.

While A$1,$A2 will change its location when copied to another cell.

 A$1: This means that column "A" will change while row location (which is number 1) will not change when copied.

 So this absolute reference will change like D$1, G$1 or E$1 depends on the location. So number "1" value will never change.

 $A1: The column "A" will not change when copied from one cell to another cell, while the row value which is number "1" will change.


So the above absolute reference will change like $A3, $A10, $A12 etc., Column "A" will be constant while the row changes.


So…

Word VBA Select Page

How to select a page in Word VBA?

Sub Select_Home()

'Select the first page of the document (page 1)
'Like pressing Ctrl + Home

Selection.GoTo wdGoToBookmark, , , "\StartOfDoc"


End Sub

Sub Select_document_end()

'Select the end of the document
'Like pressing "Ctrl+End"

Selection.GoTo wdGoToBookmark, , , "\EndOfDoc"

End Sub

Sub Select_via_Page_Number()

'specify the page number
'Example below will select page 5

Selection.GoTo wdGoToBookmark, , , "5"

End Sub

Tested and working using Word 2010.

Cheers..till next time :) Happy coding.

================================
Free Android Apps:

Click on links below to find out more:

Linux Android App cheat sheet:
https://play.google.com/store/apps/details?id=com.LinuxMobileKit


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-r…

Touch file equivalent in Windows

PowerShell split and join string

How to split a string by space using PowerShell?
PowerShell provides an easy way to do it using "split" method in PowerShell.
The split method accepts any delimiter; it can be space, dash, comma or other characters as long as it has a pattern on the string.
The split method will literally split the string and the output is displayed on a separate line or separated via a line feed.
Example:
$string = "This is my test string"
$split_string_array = $string -split " "
$split_string_array
Output will be: This
is
my
test
string
If there is a need to remove the spaces but combine the string in one line, PowerShell also provides the "join" method which basically combine or joins the string in one line.
Example: $string = "This is my test string"
$split_string_array = $string -split " "
$join_string_array = $split_string_array -Join ""
Write-Host $join_string_array
Output: Thisismyteststring
Using the join method, a string can be manipu…

Set IP Address using Netsh and PowerShell

How to set IP Address of the remote computer using PowerShell?
How to set DNS IP Address of the remote computer using PowerShell?
How to set static IP and DNS address in local or remote computer via PowerShell?

Script below will set local IP or set remote IP Address using PowerShell with the help of Netsh command.

For the script to work Winrm or Windows Remote Management must be enabled either on the local PC or remote computer or server.
The script of course needs to run at an elevated mode with the proper rights or privileges or in simple words the script will work only if you have the administrator rights.

Here’s the script:
#===============================
$set_IP= { netshinterfaceipsetaddress"Wi-Fi"static192.168.1.107255.255.255.0192.168.1.254}
$set_DNS1= {netshinterfaceipadddns"Wi-Fi"8.8.8.8 }
$SET_DNS2= { netshinterfaceipadddns"Wi-Fi"4.2.2.2index=2  }
$show_ipconfig= { ipconfig/all }

Invoke-Command-ScriptBlock$set_IP-ComputerName. Invoke-Command-ScriptBl…

PowerShell check video driver

Get the details of the video card such as driver, version and the video card name.
A simple script to get the details of the video card, script will get the driver and version to check whether a new driver needs to be installed or updated.
To check remote computers, replace the dot with the name of the remote computer to be checked.
Here’s the script:
#==============================
$computer_name="."
$video_details=gwmi-classWin32_VideoController-computername$computer_name
"Video Name : "+$video_details.Name
"Video Description : "+$video_details.Description
"Video Driver Date : "+$video_details.DriverDate
"Video Version : "+$video_details.DriverVersion
"Video Processor :"+$video_details.VideoProcessor
"Video Drivers : "+$video_details.InstalledDisplayDrivers
#==============================
Sample output:
Video Name : Intel(R) HD Graphics Video Description : Intel(R) HD Graphics Video Driver Date : 20121126000000.000000-000 Vide…

VB.Net check string for special characters

Code snippet below will check for letters and numbers on the string.
Sub check_for_special_char()
        Dim regexItem As Regex = New Regex("^[a-zA-Z0-9 ]*$")         Dim xval_regex As Boolean
        xval_regex = regexItem.IsMatch(TextBox1.Text)         '        MsgBox(xval_regex)
        If xval_regex = "False" Then
            MsgBox("No special characters/symbols allowed.") 
              else             MsgBox("No special characters found on the string") 
        End If
  End Sub
To bypass or include some other characters such as "-" dash sign or the ampersand "&" use the code below.
 Sub check_for_special_char()
        Dim regexItem As Regex = New Regex("^[a-zA-Z0-9\-& ]*$")         Dim xval_regex As Boolean
        xval_regex = regexItem.IsMatch(TextBox1.Text)         '        MsgBox(xval_regex)
        If xval_regex = "False" Then
            MsgBox("No special characters/symbols allowed.&…

Excel count non-empty cells or blank cells

Excel provides a function to check blank cells and also provide functions to check cells that are not empty.

CountA function will check cells that are not empty.
Countblank function as its name literally implies will check or count for blank or empty cells.

Example for Countblank function:

=COUNTBLANK(A1:B11) --- Function will check for empty spaces from A1 to B11 (2 columns)
=COUNTBLANK((A1:A11)) --- Function will check for empty spaces in a single column. Note the double parentheses
Example for CountA function:
=COUNTA(A1:A11) --- Function to check cells with values in a single column (Column A only)
=COUNTA(A1:B11)  --- Functio to check cells with values in two columns from A1 to B11 (Column A and B)

To enter formula above using VBA:
==============================
Sub VBA_Formula()
Dim Cell_Formula As String Dim xvalue As String Cell_Formula = "=COUNTA(A1:A11)" 'Cell_Formula = "=COUNTBLANK((A1:A11))" 'Cell_Formula = "=COUNTBLANK(A1:B11)"
'Set cell for…