Skip to main content

Posts

Excel VBA set worksheet visibility

Set excel worksheet visibility by changing its property.
Simple and a one liner code to hide and unhide worksheet in Excel.
Sub Visible_Sheet()
Worksheets(1).Visible = 1  'Make the worksheet visible
End Sub
Sub Hide_RightClick_Unhide()
Worksheets(1).Visible = 0    'Hide Worksheet to Unhide Right click on Sheet name and unhide the worksheet
End Sub
Sub Super_Hide()
Worksheets("calcx").Visible = 2
 'Hide Worksheet but right click on Sheet name will not show whether the sheet is hidden or not
End Sub



Cheers! Till next time.

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

PowerShell validate list of email addresses

Validate list of email addresses in a text file and check which email is a valid email and which one has the incorrect format.
PowerShell code below can easily check or validate incorrect email, output will show true if valid email and false if the email is not valid.
To check list of valid emails PowerShell utilizes regex. This is useful to check typo errors when sending out mass email or a list of emails which has not yet been verified.

But of course, the code will not be able to check whether the emails are active or not.

#===============================
$reader = [System.IO.File]::OpenText("c:\all_emails.txt") #get-content can also be used
while($null -ne ($line = $reader.ReadLine())) {
    #$line     $regx="[a-z0-9!#\$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#\$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?"
    [regex]::Match($line, $regx, "IgnoreCase ")  |  format-Table success, value -AutoSize }

#========…

PowerShell search files/folder names with regex

Searching files with regex will come in handy provided you know the pattern you want to look for.
Searching for files that you don't have any clue or idea which one to find is such a dreadful situation.
Regex will help to filter files base on the pattern set from a fragmented memory due to multi-tasking world.
For example, you are quite sure that the file you're looking for starts with s and followed by letter k.
So files like skype_password.txt, skew animation.mp4, skbanner.logo, skin.css, skilled-listing.docx or any files that begins with "sk" will be filtered.
Here's the PowerShell code to do it:
#===================
$regx_filters = get-childitem "c:\all_files_n_rumble\" -recurse | where-object {$_.name -match '^s[k]' } Write-Output $regx_filters
#===================
To get folder or directory names with regex:
#===================
$regexPattern ="^l[i]" Get-ChildItem -Path "c:\all_folder_n_mixes\" -Recurse | Where-Object {$_.PS…

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…

PowerShell search text file contents

PowerShell code below will search recursively all text file on the path specified. If the pattern matches on the text file, the contents will be copied and all matching patterns will be consolidated on a single file.
#=============================
Get-ChildItem -Path "d:\all_text_files \" -recurse | ForEach-Object {
  @'
= = =>>>The file name - {0}
{1}
- - - - - - -
`r`n
'@ -f $_.Name, (Get-Content $_.FullName )
}  | Select-String -pattern "router" | Out-File 'd:\d_output\output.txt'
#=============================
Code explanation:
Get-ChildItem -Path "d:\txt notes\" -recurse | ForEach-Object  >>> Get all the items recursively on the path specified and for each object process each object
  @'  '@ >>> PowerShell string format that any text or string between the @ symbol will be process exactly as it is displayed, it’s like what you see is what you get.
{0} {1} >>> are place holders, in which the value w…

Linux basic device utility tools

Knowing the devices in your computer is quite important in order to check what devices are installed or running in the computer
Getting to use Linux or starting to use it may seem daunting for others.
But Linux is awesome aside that its free and open source, in order to get used to it then you have to use it.
How to check installed USB drives or external USB devices in Linux?
How to check what graphic chipset or video chipset your computer is using in Linux?
How to check the audio chipset in your Linux system?
All these questions will pop-up once you start using Linux.
Or how would to check whether the inserted USB thumbdrive or an external USB drive is detected or not.
Well, some Linux distro provides a GUI interface to do it.
But the very basic way to check is to open via the Terminal window.
Or basically the command line in Linux.
ls - or list directory contents is the most basic way to learn on command line
ls will list the files and directories
To issue the commands below, open …