Skip to main content

Posts

Showing posts with the label VB.NET

PowerShell get folder capacity

Get the folder capacity using PowerShell via the old-school method.
Old-school since we will utilize the native “dir” command, just like the good old days of DOS.
PowerShell dir command output is not the same with the native windows system32 dir command line just like in DOS system.
The output will be in bytes since it is from the command line but of course, if you are command line junkie it will be easy to read even if the capacity is written in bytes.
First, fire up notepad write the dir command plus the full folder path of the specified folder in which you like to monitor or want to know the capacity.
 dir “c:\users\dmusic\music folder 001”
The path is enclosed in quotes since the folder has spaces and save the notepad as a batch file with “.bat” extension.
Open PowerShell command line window or PowerShell ISE and type the following command:
#change the path to where the batch file was saved
$folder_base_cap = d:\read_folder_cap.bat 
$ folder_base_cap | Select-String "bytes&qu…

Sort numbers, digits or figures in Excel 2016

To sort numbers, digits, or figures in Excel 2016 is quite straight forward.
There are two ways to do it via the Home tab or Data tab.
What is found on the Home tab is sorting of alphabets or A-Z option which can easily be seen beside the auto-sum function which is on the right most tab of the ribbon by default.
Screen shot below shows the sorting functionality found in Home tab.


The other way to sort by numbers, digits or figures in Excel 2016 is to click on “Data” click on “Sort” then an option will appear to sort by smallest to largest or largest to smallest.
A screen shot below shows where to find the function.


That’s it hopes it helps to make thing easier.


================================ Free Android Apps:
Click on links below to find out more:

Excel Keyboard shortcuts guide https://play.google.com/store/apps/details?id=chrisjoms.myexcelapplicationguide
Linux Android App cheat sheet: https://play.google.com/store/apps/details?id=com.LinuxMobileKit Multiplication Table for early learner…

How to escape in PowerShell

In bash script the backslash is use as escape sequence and even in Java programming.

But what's the equivalent of bash escape sequence in PowerShell?

Grave accent or back tick, which is found at the top left corner of the keybord below the "esc" key on some keyboard, This character "`" is the escape in PowerShell.

For example:

Write-host "This line is above `r`n and  `r`n this line is below"

Output is:
This line is above
 and
 this line is below

r is equal to carriage return character putting a backtick before r, `r tells PowerShell that r is not a character but a carriage return
n is a new line character putting a backtick before n, `n tells PowerShell that n is a new line character.

Another example:

$ sign in PowerShell is to indicate a variable

But what if you need to display a dollar sign as a character and not as a special character to declare a variable. Then we need to escape the dollar sign by prefixing a backtick before the character.

Exampl…

Excel VBA load all text files to listbox

Get all text files in a specified path to a  userform listbox using VBA.

The code below will read the directory for all text files and its filename will be loaded to listbox.

=====================

Dim fs, f, fc, f1

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder("C:\text_folder")

    Set fc = f.Files

    For Each f1 In fc

        If InStr(1, f1.Name, ".txt") Then

           'add all filenames to listbox
           UserForm1.ListBox1.AddItem f1.Name

        End If

    Next

=====================

Happy coding!


Cheers..till next time.



================================ 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
Multiplication Table for early learners https://play.google.com/store/apps/details?id=com.TableMultiplication
Catholic Rosary Guide  for Android:
https://play.google.com/store/apps/details?id=com.myrosaryapp
http://quickbytesstuf…

Excel VBA copy or reference a value

Excel VBA provide options on how to pass value from worksheet to worksheet or from workbook to another workbook.
To optimize or make the VBA code run faster is to make the worksheet interaction lesser.
Recording an Excel VBA macro to copy and paste a value will have a similar output to the code below.
Sub Macro1() ' ' Macro1 Macro '     Range("A2").Select     Selection.Copy
    Sheets("Sheet2").Select     Range("A3").Select     ActiveSheet.Paste
End Sub
what the code does is to select the cell on the active sheet for copying and select another worksheet and select the cell where the value will be placed and paste the copied cell value.
Steps are: a. Select the cell and copy the value b. Select the worksheet where the value is to be copied c. Next step, is select the cell on the selected worksheet d. Paste the value on the cell
Steps are absolutely fine, if it works as expected. But if you are copying hundreds or thousands of cells. It will be a w…

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 code.

===================
'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…

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…