tag:blogger.com,1999:blog-75705402093698669212024-03-16T06:39:28.719+05:30KrishTalk.comSome useful tips on daily computer and mobile usage and programming. I post information and help topics on new technologies, Mobile, Computers, Office Automation. Krishna Shttp://www.blogger.com/profile/17983452641688345252noreply@blogger.comBlogger59125tag:blogger.com,1999:blog-7570540209369866921.post-6407843865474570392024-02-12T10:23:00.008+05:302024-02-12T10:31:01.704+05:30How to copy screenshot to clipboard in New Apple Mac OS <p><span style="font-size: large;">If you have upgraded your Apple Mac OS to 14.x.x, then you may find it strange that the screenshots are not copied into the clipboard. The screenshots taken are saved to Desktop by default. And also it appears on the bottom right corner for a few seconds so that you can click on that copy image. </span></p><p><span style="font-size: large;">But that is annoying! You find it very inconvenient to click on multiple times to copy the screenshot you just took. </span></p><p><span style="font-size: large;"><br /></span></p><p><span style="font-size: large;">However, there is a built-in option to copy the screenshot into the clipboard, so that you can paste it anywhere you want. </span></p><p><span style="font-size: large;">To copy the screenshot into the clipboard press hold the Control key also along with COMMND and SHIFT. </span></p><p><span style="font-size: large;"><b>COMMAND + SHIFT + <span style="color: red;">CONTROL </span>+ 3 </b> To take a screenshot of the full screen and copy the screenshot into Clipboard. </span></p><p><span style="font-size: large;"><b>COMMAND + SHIFT + <span style="color: red;">CONTROL</span> + 4 </b> To enable drag and drop then take a screenshot of the selected portion and copy the screenshot into Clipboard. </span></p><p><span style="font-size: large;"> </span></p><p><br /></p>Krishna Shttp://www.blogger.com/profile/17983452641688345252noreply@blogger.com0tag:blogger.com,1999:blog-7570540209369866921.post-9653504077775513032023-01-06T17:08:00.007+05:302023-01-06T17:21:50.235+05:30Apple MacBook Notes strike through Menu option and Setting Keyboard shortcut.<span style="font-size: medium;"><b>Apple Notes</b> is the probably most widely used App for taking notes on Apple MacBook or Apple Mac in general. In this tutorial, I will show you how to set a keyboard shortcut for Strike Through. </span><p></p><div><span style="font-size: medium;">Even the menu navigation is not easily accessible. You have to reach the 2nd level of the menu item. And If you are coming from a Windows background, it will even be tricky to find the option in the menu. </span></div><div><span style="font-size: medium;"><br /></span></div><div><span style="font-size: medium;">First of all the strike thought option is available Under Format -> Fonts. </span></div><div><span style="font-size: medium;"><br /></span></div><div><span style="font-size: medium;">To make it handier, you can set a keyboard shortcut so that, if you are using Notes more often, then it will be very useful. </span></div><div><span style="font-size: medium;"><br /></span></div><div><span style="font-size: medium;">Follow these steps to set a keyboard short to strike through in the Mac. It is limited to only the Notes App. </span></div><div><span style="font-size: medium;"><br /></span></div><div><span style="font-size: medium;">Go to <b>System Preferences</b>, and select <b>Keyboard</b>. Then select <b>Shortcuts</b> Tab </span></div><div><span style="font-size: medium;"><br /></span></div><div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEi8V6ag2s2adyFbq5GV1q56vSkwY2tltdJ1TZUTRATp2tZzL5hgObpmBGF5fZ3gPROkJQZiA26D_x0kvOhZJ_DwU6heTUXVBX6T26iUhYvj5rhhquwYck64z3rKlo7-TkLdoksLvSLb5VG_wIWcpdzRZYxK0X70YeGMsoCgZmLPglhEH2WELwu0pNe9" style="margin-left: 1em; margin-right: 1em;"><span style="font-size: medium;"><img alt="" data-original-height="830" data-original-width="1276" height="208" src="https://blogger.googleusercontent.com/img/a/AVvXsEi8V6ag2s2adyFbq5GV1q56vSkwY2tltdJ1TZUTRATp2tZzL5hgObpmBGF5fZ3gPROkJQZiA26D_x0kvOhZJ_DwU6heTUXVBX6T26iUhYvj5rhhquwYck64z3rKlo7-TkLdoksLvSLb5VG_wIWcpdzRZYxK0X70YeGMsoCgZmLPglhEH2WELwu0pNe9" width="320" /></span></a></div><div class="separator" style="clear: both; text-align: center;"><span style="font-size: medium;"><br /></span></div><div><span style="font-size: medium;">Then select <b>App Shortcuts</b> in the left-hand side pane. Then click on the <b>'+'</b> Icon at the bottom center of the menu. </span></div><div><span style="font-size: medium;"><br /></span></div><div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEhp6ErLL-JXEg58-Lgp0pvGDk0eFZJd_dtrWLm9-nwU2WnZQXIbK2dUbVPcKN6wYzLOKBUaLyxTqGw_ucJ3Gj36apLwE6iSlDtWWP_R3vxRy0UqQkgICxtkQwy0mBkosgGEPlefdn1SBuigD4kWWSwdrI0EpyaZt74YCc9NkestdvTM7O0zLc70FtY2" style="margin-left: 1em; margin-right: 1em;"><span style="font-size: medium;"><img alt="" data-original-height="746" data-original-width="1260" height="189" src="https://blogger.googleusercontent.com/img/a/AVvXsEhp6ErLL-JXEg58-Lgp0pvGDk0eFZJd_dtrWLm9-nwU2WnZQXIbK2dUbVPcKN6wYzLOKBUaLyxTqGw_ucJ3Gj36apLwE6iSlDtWWP_R3vxRy0UqQkgICxtkQwy0mBkosgGEPlefdn1SBuigD4kWWSwdrI0EpyaZt74YCc9NkestdvTM7O0zLc70FtY2" width="320" /></span></a></div><span style="font-size: medium;"><br /><br /></span></div><span style="font-size: medium;">The new dialog appears. Select the below options. </span></div><div><span style="font-size: medium;"><br /></span></div><div><span style="font-size: medium;">For APplication select Notes. As this keyboard shortcut is only applicable for Notes, select Notes. </span></div><div><span style="font-size: medium;">For the Menu title type <b>Strikethrough. </b>This is important and it should match the menu command present in the Notes app. The string should match. </span></div><div><span style="font-size: medium;"><br /></span></div><div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEjiv2_Bz2A4UirkagWIs3lY9AlEbsV0Rg2UtBgkS8R3RABVt1f212hOMC28aqGyadkLgm6zmuTyvqUVLNKxM8RlOid89HqO_LRN0vj7DjPLwcnAU4AI-5pkN8fgU8kSQkevn4IDRe98MmpUu0GjxKog3ZbQTIUUZbwzKqt-qXvBlAguFN9Q-VXkaRXG" style="margin-left: 1em; margin-right: 1em;"><span style="font-size: medium;"><img alt="" data-original-height="402" data-original-width="928" height="139" src="https://blogger.googleusercontent.com/img/a/AVvXsEjiv2_Bz2A4UirkagWIs3lY9AlEbsV0Rg2UtBgkS8R3RABVt1f212hOMC28aqGyadkLgm6zmuTyvqUVLNKxM8RlOid89HqO_LRN0vj7DjPLwcnAU4AI-5pkN8fgU8kSQkevn4IDRe98MmpUu0GjxKog3ZbQTIUUZbwzKqt-qXvBlAguFN9Q-VXkaRXG" width="320" /></span></a></div><span style="font-size: medium;"><br />Finally, select the shortcut command you want to use. Make sure this key combination is available not assigned to any other command. I have selected <b>Shift + Command. + X. </b>Since X kind of matches with the action phonetically and pictorially. It makes sense to use <b>X with other key combinations</b> for strike through</span></div><div><b><br /></b></div><div><br /></div>Krishna Shttp://www.blogger.com/profile/17983452641688345252noreply@blogger.com1tag:blogger.com,1999:blog-7570540209369866921.post-6305248734557684412022-06-14T00:41:00.131+05:302023-01-02T20:13:11.891+05:30How to create a self signed SSL certificate and how to use it with your web Server. <p><span style="font-size: medium;">SSL certificate is the foremost important aspect of any website. If your website is running without SSL, modern browsers will stop the users from entering the website. Of course, there are ways to bypass that, at your own risk. As long as your website has no commercial data exchange, sensitive user information, etc, you are still good to run a website without a proper SSL certificate. For example, a blog can run without an SSL. </span></p><p><span style="font-size: medium;">Note that Google AdSense accepts websites without SSL. </span></p><p><span style="font-size: medium;">But the topic of this post is about Self Signed SSL certificate. How to create one, how to use it, and where to use it. </span></p><p><span style="font-size: medium;">Let us see where to use it. For any website which is used for internal purposes, it's always a good idea to use Self Signed SSL, instead of paying for a real SSL certificate. Internal I mean, within a company, team, or for personal uses, for testing purposes, etc. </span></p><p><span style="font-size: medium;">Now, let us see how to create a self-signed SSL certificate. </span></p><p><span style="font-size: medium;">What you need</span></p><p><span style="font-size: medium;">A Linux machine. </span></p><p><span style="font-size: medium;">OpenSSL utility </span></p><p><span style="font-size: medium;">Apache2 Web Server with a sample website. </span></p><p><span style="font-size: medium;"><br /></span></p><p><span style="font-size: medium;">Let us start...</span></p><p><b><span style="font-size: medium;">Install prereqs. </span></b></p><p><span style="font-size: medium;">You need to install the mod_ssl to enable httpd or Apache to work with SSL certificates. I am using CentOS-based Linux, so I will use the yum package manager utility. </span></p><p><span style="font-size: medium;">Perform all the steps as a non-root user with sudo permission. </span></p><p><span style="font-size: medium;">$ <span style="background-color: #1e1e1e; color: #d4d4d4; font-family: Menlo, Monaco, "Courier New", monospace; white-space: pre;">sudo yum install mod_ssl</span></span></p><p><span style="font-size: medium;"><br /></span></p><p><span style="font-size: medium;">This should install the SSL module for the httpd services. Please note that in CentOS it is called httpd whereas in Ubuntu it is called as Apache2. </span></p><p><span style="font-size: medium;">The certificate and key will be created under the default location. </span></p><p><span style="font-size: medium;">$ <span style="background-color: #1e1e1e; color: #d4d4d4; font-family: Menlo, Monaco, "Courier New", monospace; white-space: pre;">sudo mkdir -p /etc/ssl/private</span></span></p><p><span style="font-size: medium;">$ <span style="background-color: #1e1e1e; color: #d4d4d4; font-family: Menlo, Monaco, "Courier New", monospace; white-space: pre;">sudo mkdir -p /etc/ssl/certs</span></span></p><div><span style="font-size: medium;"><br /></span></div><p><span style="font-size: medium;"><b>Create the certificate and the private key</b> </span></p><p><span style="font-size: medium;">Use the below OpenSSL command to create both the certificate and private key. The one command does create both files. There is no additional step like creating CSR - Certificate Signing Request. Since this is a self-signed certificate, you are not going through the actual process of getting the certificate signed by the CA - Certification Authority. Examples of CA are, <span face="arial, sans-serif" style="background-color: white; color: #202124;">Symantec</span> Inc, DigiCert, etc. </span></p><p><span style="font-size: medium;"><br /></span></p><p><span style="font-size: medium;">$ <span style="background-color: #1e1e1e; color: #d4d4d4; font-family: Menlo, Monaco, "Courier New", monospace; white-space: pre;">sudo openssl req -x509 -nodes -days 365 -newkey rsa:2048 </span><span style="background-color: #1e1e1e; color: #d4d4d4; font-family: Menlo, Monaco, "Courier New", monospace; white-space: pre;">-keyout /etc/ssl/private/host2-selfsigned.key -out /etc/ssl/certs/host2-selfsigned.crt</span></span></p><p><span style="font-size: medium;">This will go into an interactive screen. It will prompt various inputs. </span></p><p><span style="font-size: medium;">Refer to the below screenshot for the inputs I have provided. </span></p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEjzjc-8clPkw32TPDGxiikzrMp5nwPytENF3yieu83odc6KE2eRENPWmNSkyI1Kd60jMVnrO5-X4SofCezJeRfeSSM7zlFB46DFz9ls8Ha4o3caS7dwd4Gup8nIRn7zwvl4it-mn_Rg2hPuDUPhjqgchhmfcJ_lPZWCSed3SO7fyciVtdmgbDBn4X5Y" style="margin-left: 1em; margin-right: 1em;"><span style="font-size: medium;"><img alt="" data-original-height="406" data-original-width="1432" height="182" src="https://blogger.googleusercontent.com/img/a/AVvXsEjzjc-8clPkw32TPDGxiikzrMp5nwPytENF3yieu83odc6KE2eRENPWmNSkyI1Kd60jMVnrO5-X4SofCezJeRfeSSM7zlFB46DFz9ls8Ha4o3caS7dwd4Gup8nIRn7zwvl4it-mn_Rg2hPuDUPhjqgchhmfcJ_lPZWCSed3SO7fyciVtdmgbDBn4X5Y=w640-h182" width="640" /></span></a></div><span style="font-size: medium;"><br />Please note that the CN - Common Name should match your domain, hostname, or IP address, as per your httpd settings for the website. In other words. I have used IP address here. </span><p></p><p><span style="font-size: medium;">If the command is successful, it exits from the OpenSSL interactive session. </span></p><p><span style="font-size: medium;">Now check the below locations for certificates. </span></p><p><span style="font-size: medium;">For SSL certificate check this location <span style="background-color: #1e1e1e; color: #d4d4d4; font-family: Menlo, Monaco, "Courier New", monospace; white-space: pre;">/etc/ssl/certs </span></span></p><p><span style="font-size: medium;">For the private key check this location <span style="background-color: #1e1e1e; color: #d4d4d4; font-family: Menlo, Monaco, "Courier New", monospace; white-space: pre;">/etc/ssl/private</span></span></p><p><span style="font-size: medium;">$ <span style="background-color: #1e1e1e; color: #d4d4d4; font-family: Menlo, Monaco, "Courier New", monospace; white-space: pre;">ls -ltr /etc/ssl/private/</span></span></p><p class="p1" style="font-family: Menlo; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span class="s1" style="font-size: medium; font-variant-ligatures: no-common-ligatures;">-rw-r--r--. 1 root root 1704 Jun 12 17:09 host2-selfsigned.key</span></p><p class="p1" style="font-family: Menlo; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span class="s1" style="font-size: medium; font-variant-ligatures: no-common-ligatures;"><br /></span></p><p class="p1" style="font-family: Menlo; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span style="font-size: medium;"><span class="s1" style="font-variant-ligatures: no-common-ligatures;">$ </span><span style="background-color: #1e1e1e; color: #d4d4d4; font-family: Menlo, Monaco, "Courier New", monospace; white-space: pre;">ls -ltr /etc/ssl/certs/</span></span></p><p class="p1" style="font-family: Menlo; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span style="background-color: #1e1e1e; color: #d4d4d4; font-family: Menlo, Monaco, "Courier New", monospace; font-size: medium; white-space: pre;"><br /></span></p><p class="p1" style="font-family: Menlo; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span class="s1" style="font-size: medium; font-variant-ligatures: no-common-ligatures;">-rw-r--r--. 1 root root 1419 Jun 12 17:09 host2-selfsigned.crt</span></p><p><span style="font-size: medium;"><br /></span></p><p><b><span style="font-size: medium;">Configure the New SSL certificates in the Apache server </span></b></p><p><span style="font-size: medium;">Create a new config file under <span style="background-color: #1e1e1e; color: #d4d4d4; font-family: Menlo, Monaco, "Courier New", monospace; white-space: pre;">/etc/httpd/conf.d/</span> to configure the Virtual Host and SSL certificate details. </span></p><p><span style="font-size: medium;">I am creating the file with the IP address of my host. </span></p><p><span style="font-size: medium;">$ <span style="background-color: #1e1e1e; color: #d4d4d4; font-family: Menlo, Monaco, "Courier New", monospace; white-space: pre;">sudo vi /etc/httpd/conf.d/152.70.72.193.conf</span></span></p><div style="background-color: #1e1e1e; color: #d4d4d4; font-family: Menlo, Monaco, "Courier New", monospace; line-height: 18px; white-space: pre;"><div><span style="font-size: medium;"><VirtualHost *:443></span></div><div><span style="font-size: medium;"> ServerName 152.70.72.193</span></div><div><span style="font-size: medium;"> DocumentRoot /var/www/html</span></div><div><span style="font-size: medium;"> SSLEngine on</span></div><div><span style="font-size: medium;"> SSLCertificateFile /etc/ssl/certs/host1-selfsigned.crt</span></div><div><span style="font-size: medium;"> SSLCertificateKeyFile /etc/ssl/private/host1-selfsigned.key</span></div><div><span style="font-size: medium;"></VirtualHost></span></div></div><p><span style="font-size: medium;"><br /></span></p><p><span style="font-size: medium;">Now check the config files are valid by running </span></p><p><span style="font-size: medium;">$ <span style="background-color: #1e1e1e; color: #d4d4d4; font-family: Menlo, Monaco, "Courier New", monospace; white-space: pre;">sudo apachectl configtest</span></span></p><p><span style="font-size: medium;">You should get Success OK status. This confirms that all your config files are correct. </span></p><p><span style="font-size: medium;">Now restart your httpd. </span></p><p><span style="font-size: medium;">$ <span style="background-color: #1e1e1e; color: #d4d4d4; font-family: Menlo, Monaco, "Courier New", monospace; white-space: pre;">sudo systemctl restart httpd.service</span></span></p><p><span style="font-size: medium;">Now access the web application URL https://152.70.72.193/ </span></p><p><span style="font-size: medium;">If you are accessing the URL for the first time after the certificates are installed, then you will be warned by the browser. It clearly says the website uses an invalid certificate. </span></p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEi4tExwMWyzWBselUuzDKVkJvQWuZ4RtCtXIYDyPzPaQKZiCZvrPomfuOH9sUIUyoSCNhNCd3taWwhfnIfx-e49uhIOYPs9P1sTibY8z7EfcfOB1xDTmEW1oxEcnSahp2_z0vWdGovZIAPmBZRzNEykG9Cj5tm2LW-vhQWMVVwxim9jAg8TSoILszQe" style="margin-left: 1em; margin-right: 1em;"><span style="font-size: medium;"><img alt="" data-original-height="600" data-original-width="1652" height="116" src="https://blogger.googleusercontent.com/img/a/AVvXsEi4tExwMWyzWBselUuzDKVkJvQWuZ4RtCtXIYDyPzPaQKZiCZvrPomfuOH9sUIUyoSCNhNCd3taWwhfnIfx-e49uhIOYPs9P1sTibY8z7EfcfOB1xDTmEW1oxEcnSahp2_z0vWdGovZIAPmBZRzNEykG9Cj5tm2LW-vhQWMVVwxim9jAg8TSoILszQe" width="320" /></span></a></div><span style="font-size: medium;"><br /><br /></span><p></p><p><span style="font-size: medium;">Once you accept and click on the continue button, the page should load properly as shown below</span></p><p><span style="font-size: medium;"><br /></span></p><p><span style="font-size: medium;"><br /></span></p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEh9EiiaunfZrBJ7tknrgZpQN6IchTM17lB2sAjFcfiJyZynUe2gvH4gecRSEJS1PDP5c0v_1v6XqjSHDmQV48rCGN2oBuZTa9gZnLi39WKst49WA4dbhk-G7Bi4OrEQB2n9h_wlJ_A41gp7ValL4e31GqEMbsL4K_it0Sh8x7OEwg_S2OTOn3XoRSa2" style="margin-left: 1em; margin-right: 1em;"><span style="font-size: medium;"><img alt="" data-original-height="234" data-original-width="1106" height="68" src="https://blogger.googleusercontent.com/img/a/AVvXsEh9EiiaunfZrBJ7tknrgZpQN6IchTM17lB2sAjFcfiJyZynUe2gvH4gecRSEJS1PDP5c0v_1v6XqjSHDmQV48rCGN2oBuZTa9gZnLi39WKst49WA4dbhk-G7Bi4OrEQB2n9h_wlJ_A41gp7ValL4e31GqEMbsL4K_it0Sh8x7OEwg_S2OTOn3XoRSa2" width="320" /></span></a></div><span style="font-size: medium;"><br />Notice the HTTPS and exclamatory mark and it indicates that there is an SSL certificate but it is not trusted by the browser. </span><p></p><p><span style="font-size: medium;">If the site is taking longer and then times out, check if the HTTP version is working. If the HTTP version is working then you to check if the firewalls are disabled for HTTPS ports. </span></p><p><span style="font-size: medium;">$ <span style="background-color: #1e1e1e; color: #6a9955; font-family: Menlo, Monaco, "Courier New", monospace; white-space: pre;">sudo firewall-cmd --add-service=https</span></span></p><p><span style="font-size: medium;">If the output of this command is "<span style="font-family: Menlo; font-variant-ligatures: no-common-ligatures;">success" </span>Then it was an issue with the firewall. Now the page should load. </span></p><p><span style="font-size: medium;">Inspect the certificate from the browser. </span></p><p><span style="font-size: medium;">Click on the lock icon on the address bar, it says the connection is not secure. Click on more information and it shows the details of the certificate. </span></p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEg-PFBUFfZPy5PZepBnP782a1Dm85o0A8VaQWfHAndztI6Z5b9VRalSay7QYYbOAkgsYUlx3jmySGo6_up46l48JS2n50XeOvbk0Lyy_YQtjUu79v5u95Dc9Z4UKucLDzhVZP5vT3xczTDGQg0RX-rtOATibI6uJExI5kenTDYlNA_2mYW5h8WVprBa" style="margin-left: 1em; margin-right: 1em;"><span style="font-size: medium;"><img alt="" data-original-height="406" data-original-width="1168" height="111" src="https://blogger.googleusercontent.com/img/a/AVvXsEg-PFBUFfZPy5PZepBnP782a1Dm85o0A8VaQWfHAndztI6Z5b9VRalSay7QYYbOAkgsYUlx3jmySGo6_up46l48JS2n50XeOvbk0Lyy_YQtjUu79v5u95Dc9Z4UKucLDzhVZP5vT3xczTDGQg0RX-rtOATibI6uJExI5kenTDYlNA_2mYW5h8WVprBa" width="320" /></span></a></div><span style="font-size: medium;"><br />If you want to see the certificate, click on the view certificate. </span><p></p><p><br /></p>Krishna Shttp://www.blogger.com/profile/17983452641688345252noreply@blogger.com1tag:blogger.com,1999:blog-7570540209369866921.post-52304715643768609622021-10-20T21:40:00.009+05:302022-06-04T18:35:23.778+05:30How to set Python3 as default interpreter for VS Code | or set any version as default interpreter. <p class="MsoNormal" style="color: #222222; font-family: Calibri, sans-serif; font-size: 12pt; margin: 0cm;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: #0d0d0d; font-family: Verdana, sans-serif; font-size: 11.5pt;">Want to run your python code with a particular python version in Microsoft VS Code? And, make it the default version?<br />Read on.<br />Make sure that the required version is installed. You check the available version and the path by running this command.</span><span style="color: black; font-family: Times; font-size: 13.5pt;"></span></p><p class="MsoNormal" style="color: #222222; font-family: Calibri, sans-serif; font-size: 12pt; margin: 0cm;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: #0d0d0d; font-family: Verdana, sans-serif; font-size: 11.5pt;">I am trying this on Apple Mac. It should be similar in Windows Machines as well.</span><span style="color: black; font-family: Times; font-size: 13.5pt;"></span></p><p class="MsoNormal" style="color: #222222; font-family: Calibri, sans-serif; font-size: 12pt; margin: 0cm;"><span style="color: black; font-family: Times; font-size: 13.5pt;"> </span></p><p class="MsoNormal" style="color: #222222; font-family: Calibri, sans-serif; font-size: 12pt; margin: 0cm;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: #0d0d0d; font-family: Verdana, sans-serif; font-size: 11.5pt;">Open the VS Code, at the bottom of the screen you will Terminal. If it is not visible, Go to View and then select Terminal.</span><span style="color: black; font-family: Times; font-size: 13.5pt;"></span></p><p class="MsoNormal" style="color: #222222; font-family: Calibri, sans-serif; font-size: 12pt; margin: 0cm;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: #0d0d0d; font-family: Verdana, sans-serif; font-size: 11.5pt;"><br /><br /></span><span style="color: black; font-family: Times; font-size: 13.5pt;"></span></p><p class="MsoNormal" style="color: #222222; font-family: Calibri, sans-serif; font-size: 12pt; margin: 0cm;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: #0d0d0d; font-family: Verdana, sans-serif; font-size: 11.5pt;">Check the available python versions and their path. </span></p><div><span style="background-color: white; color: #0d0d0d; font-size: 15px; white-space: pre-wrap;"><span style="font-family: courier;"><br /></span></span></div><div><p class="p1" style="font-size: 16px; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span class="s1" style="font-variant-ligatures: no-common-ligatures;"><span style="font-family: courier;"> % which python <span class="Apple-converted-space"> </span></span></span></p><p class="p1" style="font-size: 16px; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span class="s1" style="font-variant-ligatures: no-common-ligatures;"><span style="font-family: courier;">/usr/bin/python</span></span></p><p class="p1" style="font-size: 16px; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br /></p><p class="p1" style="font-size: 16px; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span class="s1" style="font-variant-ligatures: no-common-ligatures;"><span style="font-family: courier;"><br /></span></span></p><p class="p1" style="font-size: 16px; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span class="s1" style="font-variant-ligatures: no-common-ligatures;"><span style="font-family: courier;"> % which python2</span></span></p><p class="p1" style="font-size: 16px; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span class="s1" style="font-variant-ligatures: no-common-ligatures;"><span style="font-family: courier;">/usr/bin/python2</span></span></p><p class="p1" style="font-size: 16px; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span class="s1" style="font-variant-ligatures: no-common-ligatures;"><span style="font-family: courier;"><br /></span></span></p><p class="p1" style="font-size: 16px; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span class="s1" style="font-variant-ligatures: no-common-ligatures;"><span style="font-family: courier;"><br /></span></span></p><p class="p1" style="font-size: 16px; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span class="s1" style="font-variant-ligatures: no-common-ligatures;"><span style="font-family: courier;"> % which python3<span class="Apple-converted-space"> </span></span></span></p><p class="p1" style="font-size: 16px; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span class="s1" style="font-variant-ligatures: no-common-ligatures;"><span style="font-family: courier;">/Library/Frameworks/Python.framework/Versions/3.7/bin/python3</span></span></p><p class="p1" style="font-family: Menlo; font-size: 16px; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span class="s1" style="font-variant-ligatures: no-common-ligatures;"><br /></span></p><p class="p1" style="font-family: Menlo; font-size: 16px; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span class="s1" style="font-variant-ligatures: no-common-ligatures;">Next, </span></p><p class="p1" style="font-family: Menlo; font-size: 16px; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span class="s1" style="font-variant-ligatures: no-common-ligatures;"><br /></span></p><p class="p1" style="font-family: Menlo; font-size: 16px; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span style="font-variant-ligatures: no-common-ligatures;">Run this python code to check what version is used by default. </span></p><p class="p1" style="font-family: Menlo; font-size: 16px; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span style="font-variant-ligatures: no-common-ligatures;"><br /></span></p><div style="background-color: #1e1e1e; color: #d4d4d4; font-family: Menlo, Monaco, "Courier New", monospace; font-size: 12px; line-height: 18px; white-space: pre;"><div><span style="color: #c586c0;">import</span> <span style="color: #4ec9b0;">platform</span></div><div><span style="color: #dcdcaa;">print</span>(<span style="color: #4ec9b0;">platform</span>.<span style="color: #dcdcaa;">python_version_tuple</span>())</div><div><span style="color: #dcdcaa;">print</span>(<span style="color: #4ec9b0;">type</span>(<span style="color: #4ec9b0;">platform</span>.<span style="color: #dcdcaa;">python_version_tuple</span>()))</div><br /></div><p class="p1" style="font-family: Menlo; font-size: 16px; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span class="s1" style="font-variant-ligatures: no-common-ligatures;"><br /></span></p><p class="p1" style="font-family: Menlo; font-size: 16px; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span class="s1" style="font-variant-ligatures: no-common-ligatures;"><br /></span></p><p class="p1" style="font-family: Menlo; font-size: 16px; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span class="s1" style="font-variant-ligatures: no-common-ligatures;">You will get this</span></p><p class="p1" style="font-family: Menlo; font-size: 16px; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span class="s1" style="font-variant-ligatures: no-common-ligatures;"><br /></span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span style="font-family: Menlo;"><span style="font-variant-ligatures: no-common-ligatures;">('2', '7', '16')</span></span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span style="font-variant-ligatures: no-common-ligatures;"><span class="s1" style="font-family: Menlo;"></span></span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span style="font-family: Menlo;"><span style="font-variant-ligatures: no-common-ligatures;"><type 'tuple'></span></span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br /></p><p class="p1" style="font-family: Menlo; font-size: 16px; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span class="s1" style="font-variant-ligatures: no-common-ligatures;"><br /></span></p><div><span style="color: #0d0d0d; font-family: verdana; font-size: 15px; white-space: pre-wrap;">Now you know the path and versions available. </span></div><p class="p1" style="font-family: Menlo; font-size: 16px; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span class="s1" style="font-variant-ligatures: no-common-ligatures;"></span></p><div><span style="font-family: verdana;"><span style="color: #0d0d0d; font-size: 15px; white-space: pre-wrap;">In the VS code, go</span><span style="color: #0d0d0d; font-size: 15px; white-space: pre-wrap;"> to the View menu and select Command Pallete</span></span></div></div><div><br /></div><div><span face="Roboto, Noto, sans-serif" style="background-color: white; color: #0d0d0d; font-size: 15px; white-space: pre-wrap;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhjqScBsQFMZYRgXYTYdsDEttrB5decyE_M5wWI9K4QigJt2rPPkilyYYZcWmFAbCKE1yIJg7jXicC_wfkiolD630NYYx-FCI8INRkzl12Xkdc7U5_zjV18mMSi1j579gP-jnPYt3B19zg/" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="274" data-original-width="652" height="134" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhjqScBsQFMZYRgXYTYdsDEttrB5decyE_M5wWI9K4QigJt2rPPkilyYYZcWmFAbCKE1yIJg7jXicC_wfkiolD630NYYx-FCI8INRkzl12Xkdc7U5_zjV18mMSi1j579gP-jnPYt3B19zg/" width="320" /></a></div><br /><br /></span></div><div><span style="background-color: white; color: #0d0d0d; font-family: verdana; font-size: 15px; white-space: pre-wrap;">Now search for Python and you will get all the installed versions of Python.</span></div><div><span style="background-color: white; color: #0d0d0d; font-family: verdana; font-size: 15px; white-space: pre-wrap;"><br /></span></div><div><span face="Roboto, Noto, sans-serif" style="background-color: white; color: #0d0d0d; font-size: 15px; white-space: pre-wrap;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjL63HuKtI2j496BsaowshjLbVI7XdyAWAe53reV5q837qDx89V_ncYpKTRdFaZdmnle-jHwz_wa9l3bZ2HBUR62FChcumws3Gg3bc0zdeVI7Glul4gnenaPB-T3mYSAXvLK5Jbs3m04V4/" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="398" data-original-width="1272" height="100" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjL63HuKtI2j496BsaowshjLbVI7XdyAWAe53reV5q837qDx89V_ncYpKTRdFaZdmnle-jHwz_wa9l3bZ2HBUR62FChcumws3Gg3bc0zdeVI7Glul4gnenaPB-T3mYSAXvLK5Jbs3m04V4/" width="320" /></a></div><div><span face="Roboto, Noto, sans-serif" style="background-color: white; color: #0d0d0d; font-size: 15px; white-space: pre-wrap;"><br /></span></div></span><div><span face="Roboto, Noto, sans-serif" style="background-color: white; color: #0d0d0d; font-size: 15px; white-space: pre-wrap;"><br /></span></div><div><span style="background-color: white; color: #0d0d0d; font-family: verdana; font-size: 15px; white-space: pre-wrap;">Then select Python: Select interpreter. You will get all the installed versions of python. Select the one you want to be associated with your script. </span></div><div><br /></div></div><div><span face="Roboto, Noto, sans-serif" style="background-color: white; color: #0d0d0d;"><div class="separator" style="clear: both; font-size: 15px; text-align: center; white-space: pre-wrap;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiHBLgho33hTdvS_C4xJq67Jf2sUu4diwkL5_NcnyFJIa5E2CGlDDwcvVUuLJkRqRdpAhQWh2OCZy9SYXD513FMJXzdiHzQthwBuzt8ad3zCKRSbXBD1EO603kG4F0WXwN3umLcMETD9ro/" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="790" data-original-width="1288" height="196" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiHBLgho33hTdvS_C4xJq67Jf2sUu4diwkL5_NcnyFJIa5E2CGlDDwcvVUuLJkRqRdpAhQWh2OCZy9SYXD513FMJXzdiHzQthwBuzt8ad3zCKRSbXBD1EO603kG4F0WXwN3umLcMETD9ro/" width="320" /></a></div><div style="font-size: 15px; white-space: pre-wrap;"><span face="Roboto, Noto, sans-serif" style="background-color: white; color: #0d0d0d; font-size: 15px; white-space: pre-wrap;"><br /></span></div><span style="font-family: verdana; font-size: 15px; white-space: pre-wrap;">Now run the same code again to check what is the default version set. </span></span></div><div><span face="Roboto, Noto, sans-serif" style="background-color: white; color: #0d0d0d;"><span style="font-family: verdana; font-size: 15px; white-space: pre-wrap;"><br /></span></span></div><div><span face="Roboto, Noto, sans-serif" style="background-color: white; color: #0d0d0d;"><span style="font-size: 15px; white-space: pre-wrap;"><br /></span></span></div><div><span face="Roboto, Noto, sans-serif" style="background-color: white; color: #0d0d0d; font-size: 15px; white-space: pre-wrap;"><div style="background-color: #1e1e1e; color: #d4d4d4; font-family: Menlo, Monaco, "Courier New", monospace; font-size: 12px; line-height: 18px; white-space: pre;"><div><span style="color: #c586c0;">import</span> <span style="color: #4ec9b0;">platform</span></div><div><span style="color: #dcdcaa;">print</span>(<span style="color: #4ec9b0;">platform</span>.<span style="color: #dcdcaa;">python_version_tuple</span>())</div><div><span style="color: #dcdcaa;">print</span>(<span style="color: #4ec9b0;">type</span>(<span style="color: #4ec9b0;">platform</span>.<span style="color: #dcdcaa;">python_version_tuple</span>()))</div></div><div style="background-color: #1e1e1e; color: #d4d4d4; font-family: Menlo, Monaco, "Courier New", monospace; font-size: 12px; line-height: 18px; white-space: pre;"><br /></div>
<br /></span></div><div><span face="Roboto, Noto, sans-serif" style="background-color: white; color: #0d0d0d; font-size: 15px; white-space: pre-wrap;">('3', '7', '1')
<class 'tuple'></span></div><div><span face="Roboto, Noto, sans-serif" style="background-color: white; color: #0d0d0d; font-size: 15px; white-space: pre-wrap;"><br /></span></div><div><span face="Roboto, Noto, sans-serif" style="background-color: white; color: #0d0d0d; font-size: 15px; white-space: pre-wrap;"><br /></span></div>Krishna Shttp://www.blogger.com/profile/17983452641688345252noreply@blogger.com0tag:blogger.com,1999:blog-7570540209369866921.post-3455729905092195282021-10-16T22:00:00.001+05:302021-10-16T22:01:23.863+05:30SOLVED | AdSense Approval Issues and Fixes for Site down or Unavailable | How to Fix<p>I tried to get an AdSense approval for my site but I got a mail from AdSense saying the site is down or unavailable. But the site is definitely accessible and all the other requirements are taken care of. The site is also very old and the domain itself is 3 years old. There are no issues with page views as well. </p><p><br /></p><p><b>Here is my situation.</b></p><p>I have an approved AdSense for YouTube. I got my blogger site upgrade to a top-level domain. This is the requirement to link your YouTube AdSense to your blogger site. </p><p>I have taken care of all the requirements to apply for the AdSense for my site. </p><p>1. Clean design and unique content. No issue with the design as I have created it using a blogger theme. </p><p>2. About page, Contact Us Page, Privacy Policy Page. These are mandatory </p><p>3. Top-Leve Domain</p><p><br /></p><p>After applying for the AdSense I got the denial mail and looking at the reason, I was convinced that there must be a small mistake in the Domain setup or blogger settings. </p><p>I tried these things and found the culprit. </p><p>Make sure to check all these steps so that you will not get another denial. </p><p><br /></p><p><b>1. Check if the site is accessible with different combinations of WWW, HTTP and HTTPS </b></p><p><span>krishtalk.com</span></p><p><span>www.krishtalk.com</span></p><p>https://www.krishtalk.com</p><p>http://www.krishtalk.com</p><p>Note that my site still runs without HTTPS, yet to add SSL certificate. </p><p>All these combinations worked. </p><p>So no issues at this point. </p><p><br /></p><p><b>2. Check the DNS settings from your Domain service provider</b>. All CNAME settings were found to be good. Going wrong here has less chances, if there are any issues with your domain settings, your site may not be accessible. </p><p>So no issues found yet. </p><p><br /></p><p><b>3. Check if you have custom rebots.txt enabled </b></p><p>This is one of the major reasons why approval is denied. Check the settings of your blog or you can just check the content of your robots.txt by appending robots.txt to the domain URL</p><p>http://www.krishtalk.com/robots.txt</p><p>Make sure that your robots.txt is not blocking any of the Google servers. </p><p>This is the default robots.txt from my site. </p><pre style="overflow-wrap: break-word; white-space: pre-wrap;">User-agent: Mediapartners-Google
Disallow:
User-agent: *
Disallow: /search
Allow: /
Sitemap: http://www.krishtalk.com/sitemap.xml</pre><p>checked my blogger settings and custom robots was turned off. </p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjpmVFLX9rddRm_rJgy7jLizZUzTOEc4-wnBAMQP78khrJUz6GKA0AEvwNMnVaQTsdIrRKc5PZAZFMHZ2OTJW6WSQ7PH6k02gCWqCgEbEKB8koKOT6Pzo3a3Oc-S5D1A0t2aAyIgy1qMAg/" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="412" data-original-width="1168" height="113" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjpmVFLX9rddRm_rJgy7jLizZUzTOEc4-wnBAMQP78khrJUz6GKA0AEvwNMnVaQTsdIrRKc5PZAZFMHZ2OTJW6WSQ7PH6k02gCWqCgEbEKB8koKOT6Pzo3a3Oc-S5D1A0t2aAyIgy1qMAg/" width="320" /></a></div><br /><br /><p></p><p>This is also clean and not the culprit for my approval denial. </p><p><br /></p><p><b>4. Upload your sitemap to Google. </b></p><p>I had done this before but, I did upload the sitemap.xml again. I am not sure if this will cause any issues. As long as your pages are searched from Google and you are getting the views then this should not be a problem. </p><p>I do not think this was a problem for my site. </p><p><br /></p><p><b>5. While configuring your custom domain you might have selected the option "Redirect Domain". </b></p><p>You will get an option to redirect your non-www site to www site under the Publishing tab of your blogger settings. </p><p>like krishtalk.com to www.krishtalk.com</p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjfjhA21HWA_LiFpVAnUrwOlyiZ4qpPR80PqtUuXQUjLGK-F2ns7nq3DhyphenhyphenxiUHLz5x3xI0i9edUsZMJBye1pVub1PgHhIwXVVRUQ6-kwN2u_LuUigVfHNcLBz_qRU3T01QxqF9RhTpxlO8/" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="632" data-original-width="1072" height="189" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjfjhA21HWA_LiFpVAnUrwOlyiZ4qpPR80PqtUuXQUjLGK-F2ns7nq3DhyphenhyphenxiUHLz5x3xI0i9edUsZMJBye1pVub1PgHhIwXVVRUQ6-kwN2u_LuUigVfHNcLBz_qRU3T01QxqF9RhTpxlO8/" width="320" /></a></div><br />When I checked this setting, it was turned on. I guessed this could be the issue. Since we are providing the non-www site while applying for AdSense. It is better to turn off this redirection. So Google can find and navigate the www site.<p></p><p><b>My guess was right. After changing this setting, I again applied for review. After 2 days, I checked the status and it was approved. :) </b></p><p><br /></p><p>Thank you and let me know what worked for you. </p><p><br /></p><p>Notes: If you are applying for the AdSense for the first time for your blogger site, then you have to do it from within the blogger's earnings tab. </p><p>If you already have an approved AdSense then you have to have a custom domain and from the AdSense account login, you have to apply for the site approval. </p><p><br /></p>Krishna Shttp://www.blogger.com/profile/17983452641688345252noreply@blogger.com0tag:blogger.com,1999:blog-7570540209369866921.post-52985373197634377802021-10-15T01:19:00.002+05:302023-03-15T12:58:16.000+05:30How to create MS Word document automatically with ExcelMS Word and Excel can do wonders together. You can do a lot of automation using these 2 applications for your small and medium businesses. Here is one such solution. <div><br /></div><div>Generating the MS word report automatically using excel as a data source.
Mail merge is used to link data from excel to word. Then a simple macro is used to get the desired record from Excel Spreadsheet.</div><div><br /></div><div>Download the MS word document and Excel spreadsheet used in this video here</div><div><br /></div><div><strike>https://drive.google.com/drive/folders/0B4UB3eOhhY26TVdvQ3RucW1zb1E?resourcekey=0-YcGX4_XdYqt_C_dSztvA_A&usp=sharing
</strike></div><div><br /></div><div>Please use this link</div><div><br /></div><div>https://drive.google.com/drive/folders/0B4UB3eOhhY26TVdvQ3RucW1zb1E?resourcekey=0-YcGX4_XdYqt_C_dSztvA_A&usp=share_link</div><div><br /></div><div>If you are not able to access this link, let me know. The link in my YouTube channel was not working, now I have uploaded the files here. </div>Krishna Shttp://www.blogger.com/profile/17983452641688345252noreply@blogger.com7tag:blogger.com,1999:blog-7570540209369866921.post-2641889041693388652021-09-27T16:15:00.002+05:302021-09-27T16:27:14.234+05:30[SOLVED] MacBook Pro keyboard backlight not working? Check this post<p>Keyboard backlight not working on your MacBook Pro? And your Mac has a touch bar? </p><p>Continue reading...</p><p>MacBook Pro 2020 which comes with a touch bar has moved its Keyboard backlight controls to the touch bar. You will not see any control on the screen under the Keyboard settings. But the setting in the touch bar is also not easy to see and it bit tricky. You most probably, think that there is some issue with Keyboard. Even the support people suggest resetting some of the settings. That is what happened with my case. Luckily I found a way to change the settings. </p><p><br /></p><p>By default, the Keyboard backlight settings are not visible on the touch bar. You have to expand the tra-like stuff on the touch bar and then you will find icons, which look similar to screen brightness control icons. </p><p>On the right side of your touch bar, you will these 4 controls</p><p>1. Screen Brightness control </p><p>2. Volume control </p><p>3. Mute control </p><p>4. Siri button. </p><p>Left these controls there is an arrow button which looks like '<', by clicking on this, you will see more controls. </p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjkv6s37wcJOvZeFr8yYn1cUIs1u7SSPPGVFyPtV1EcSBdYBV4ckP9HaDjGGOMG6OYYNGhBpUDmcfdHDAYTub-Gt3FQy8q5FBlN5UuYwAfgYUioOCYJzBWhh93UuK0Xim7q83BdxpCpFlQ/" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="959" data-original-width="3278" height="94" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjkv6s37wcJOvZeFr8yYn1cUIs1u7SSPPGVFyPtV1EcSBdYBV4ckP9HaDjGGOMG6OYYNGhBpUDmcfdHDAYTub-Gt3FQy8q5FBlN5UuYwAfgYUioOCYJzBWhh93UuK0Xim7q83BdxpCpFlQ/" width="320" /></a></div><br />Once you click on the left arrow mark as shown in the above picture, it will open up more controls. <p></p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiRxlikTpu-uYNC0qlBeiosLSOo7XFsZS8FmhyOdbz3mNOGiUfD3PJOhDYSsuSUnw4QJG_rpD7N67orlZCc138yBXMOqBM3PUhShHL_0L5u2lpKEnd9OX6JaYNJGwPaE_kdrfA_jWwcD3c/" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="1205" data-original-width="2609" height="148" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiRxlikTpu-uYNC0qlBeiosLSOo7XFsZS8FmhyOdbz3mNOGiUfD3PJOhDYSsuSUnw4QJG_rpD7N67orlZCc138yBXMOqBM3PUhShHL_0L5u2lpKEnd9OX6JaYNJGwPaE_kdrfA_jWwcD3c/" width="320" /></a></div><p>At the center of the touch bar, you will see controls that look similar to Screen Brightness controls. You can use these to adjust the backlight of the Keyboard. </p><p></p>Krishna Shttp://www.blogger.com/profile/17983452641688345252noreply@blogger.com0tag:blogger.com,1999:blog-7570540209369866921.post-40341529708257239172021-07-17T13:36:00.005+05:302021-07-17T13:36:42.250+05:30How to create self signed ssl certificate, easy steps<p> These are the ready commands to create self-signed SSL certificate. These certificates can be used for testing purposes. For a production-grade application, you need to contact a CA authority to get the actual SSL certificate. But for your internal testing, you can always use a self-signed SSL certificate. </p><p><br /></p><p>Here are the steps to create a Self Signed SSL certificate using OpenSSL</p><p><br /></p><p># generate self sigend ssl certificate</p><p><br /></p><p>rm -rf *.pem</p><p><br /></p><p>echo "Generating CA certificates"</p><p># Step 1: generate CAs certificate</p><p>openssl req -x509 -newkey rsa:4096 -days 365 -keyout ca-key.pem -out ca-cert.pem -subj "/C=IN/ST=kartnataka/L=bangalore/O=test/OU=test/CN=*.test.com/emailAddress=test@test"</p><p>#inspect the CAs certificate</p><p>#openssl x509 -in ca-cert.pem -noout -text</p><p><br /></p><p># Step 2 generate server sign request certificate which will be used for self signing. Change the subject to as per web server</p><p><br /></p><p>echo "Generating Server certificate sign request"</p><p>openssl req -newkey rsa:4096 -keyout server-key.pem -out server-req.pem -subj "/C=IN/ST=kartnataka/L=bangalore/O=sever/OU=server/CN=*.server.com/emailAddress=server@test"</p><p><br /></p><p>#openssl x509 -in server-req.pem -noout -text</p><p># Step 3 Sign certificate</p><p><br /></p><p>echo "Signing the server certificate with CA"</p><p>openssl x509 -req -in server-req.pem -CA ca-cert.pem -CAkey ca-key.pem -CAcreateserial -out server-signed-cert.pem</p><p><br /></p><p># Inspect the signed certificate</p><p><br /></p><p>#openssl x509 -in server-signed-cert.pem -noout -text</p>Krishna Shttp://www.blogger.com/profile/17983452641688345252noreply@blogger.com0tag:blogger.com,1999:blog-7570540209369866921.post-55966111364086320042020-08-22T20:47:00.010+05:302022-06-05T01:24:52.686+05:30How to Fix ImportError: No module named 'flask' with virtualenv<p class="MsoListParagraphCxSpFirst" style="mso-list: l0 level1 lfo1; text-indent: -0.25in;"></p><p class="MsoNormal"></p><p class="MsoListParagraphCxSpFirst" style="mso-list: l0 level1 lfo1; text-indent: -0.25in;"><!--[if !supportLists]--></p><p class="MsoListParagraphCxSpMiddle"><span style="font-size: large;">Flask is one of the most used frameworks in Python.
If you are new to the flask and if you are getting this error - ImportError: No module named 'flask' on virtualenv, read further.</span></p>
<p class="MsoListParagraphCxSpMiddle"><span style="font-size: large;">You are sure that the Flask module is
already installed. But still, you are getting No module error. This is because you need to install a flask framework inside the virtualenv. <o:p></o:p></span></p>
<p class="MsoListParagraphCxSpMiddle"><span style="font-size: large;">Follow these instructions</span></p><p class="MsoListParagraphCxSpMiddle"><span style="font-size: large;"><o:p></o:p></span></p>
<p class="MsoListParagraphCxSpMiddle"><span style="font-size: large;"><br /></span></p><p class="MsoListParagraphCxSpMiddle"><span style="font-size: large;">Step 1</span></p><p class="MsoListParagraphCxSpMiddle"><span style="font-size: large;"><o:p></o:p></span></p>
<p class="MsoListParagraphCxSpMiddle"><span style="font-size: large;">Create a directory where you will be
installing the virtualenv. <o:p></o:p></span></p>
<p class="MsoListParagraphCxSpMiddle"><o:p><span style="font-size: large;"> </span></o:p></p>
<p class="MsoListParagraphCxSpMiddle" style="margin-left: 1in; mso-add-space: auto;"><span style="font-family: "courier new"; font-size: large;">krishna@osboxes:~$ mkdir testenv<o:p></o:p></span></p>
<p class="MsoListParagraphCxSpMiddle"><o:p><span style="font-size: large;"> </span></o:p></p>
<p class="MsoListParagraphCxSpMiddle"><span style="font-size: large;">Step 2<o:p></o:p></span></p>
<p class="MsoListParagraphCxSpMiddle"><span style="font-size: large;">Get inside the folder and install
virtualenv<o:p></o:p></span></p><p class="MsoListParagraphCxSpFirst" style="margin-left: 1in; mso-add-space: auto;"><span style="font-family: "courier new"; font-size: large;">krishna@osboxes:~$ <b>cd testenv/</b><o:p></o:p></span></p><p class="MsoListParagraphCxSpMiddle" style="margin-left: 1in; mso-add-space: auto;"><span style="font-family: "courier new"; font-size: large;">krishna@osboxes:~/testenv$ <b>pip
install virtualenv</b><o:p></o:p></span></p><p class="MsoListParagraphCxSpMiddle" style="margin-left: 1in; mso-add-space: auto;"><span style="font-family: "courier new"; font-size: large;"><b>or</b></span></p><p class="MsoListParagraphCxSpMiddle" style="margin-left: 1in; mso-add-space: auto;"><span style="font-family: "courier new"; font-size: large;"><b>$ sudo apt-get install virtualenv</b></span></p><p class="MsoListParagraphCxSpMiddle"><o:p><span style="font-size: large;"> </span></o:p></p><p class="MsoListParagraphCxSpMiddle">
</p><p class="MsoListParagraphCxSpLast" style="margin-left: 1in; mso-add-space: auto;"><span style="font-family: "courier new"; font-size: large;">Successfully installed appdirs contextlib2
distlib filelock importlib-metadata importlib-resources pathlib2 scandir
singledispatch six typing unknown unknown virtualenv<o:p></o:p></span></p>
<p class="MsoListParagraphCxSpMiddle" style="margin-left: 1in; mso-add-space: auto;"><span style="font-size: large;"><br /></span></p><p class="MsoListParagraphCxSpMiddle"><span style="font-size: large;"><o:p></o:p></span></p>
<p class="MsoListParagraphCxSpMiddle"><span style="font-size: large;">Step 3<o:p></o:p></span></p>
<p class="MsoListParagraphCxSpMiddle"><span style="font-size: large;">Activate the virtualenv <o:p></o:p></span></p><p class="MsoListParagraphCxSpMiddle"><span color="rgba(0, 0, 0, 0.87)" face="Roboto, Noto, sans-serif" style="background-color: white; font-size: large; white-space: pre-wrap;">This virtual environment requires activation and dedicated installation inside the virtual environment. It has to be installed after creating a virtual environment. The libraries will reside inside the folder created for the virtual environment. </span></p>
<p class="MsoListParagraphCxSpMiddle"><o:p><span style="font-size: large;"> </span></o:p></p>
<p class="MsoListParagraphCxSpMiddle" style="margin-left: 1in; mso-add-space: auto;"><span style="font-family: "courier new"; font-size: large;">krishna@osboxes:/tmp/tempenv$<b> source
tempenv/bin/activate</b><o:p></o:p></span></p>
<p class="MsoListParagraphCxSpMiddle" style="margin-left: 1in; mso-add-space: auto;"><span style="font-family: "courier new"; font-size: large;">(tempenv) krishna@osboxes:/tmp/tempenv$<o:p></o:p></span></p>
<p class="MsoListParagraphCxSpMiddle"><o:p><span style="font-size: large;"> </span></o:p></p>
<p class="MsoListParagraphCxSpMiddle"><span style="font-size: large;">Please note that you are inside the virtual
environment. <o:p></o:p></span></p>
<p class="MsoListParagraphCxSpMiddle"><span style="font-size: large;">Step 4</span></p><p class="MsoListParagraphCxSpMiddle"><span style="font-size: large;"><o:p></o:p></span></p>
<p class="MsoListParagraphCxSpMiddle"><span style="font-size: large;">Install Flask inside the virtual
environment</span></p><p class="MsoListParagraphCxSpMiddle"><span style="font-size: large;"><o:p></o:p></span></p>
<p class="MsoListParagraphCxSpMiddle" style="margin-left: 1in; mso-add-space: auto;"><span style="font-family: "courier new"; font-size: large;">(tempenv)
krishna@osboxes:/tmp/tempenv$ <b>pip install Flask</b><o:p></o:p></span></p>
<p class="MsoListParagraphCxSpMiddle" style="margin-left: 1in; mso-add-space: auto;"><span style="font-family: "courier new"; font-size: large;">Successfully installed
Flask-1.1.2 Jinja2-2.11.2 MarkupSafe-1.1.1 Werkzeug-1.0.1 click-7.1.2
itsdangerous-1.1.0<o:p></o:p></span></p>
<p class="MsoListParagraphCxSpMiddle"><span style="font-size: large;">Make sure that there is Flask package available
inside the virtual env folder</span></p><p class="MsoListParagraphCxSpMiddle"><span style="font-size: large;"><o:p></o:p></span></p>
<p class="MsoListParagraphCxSpMiddle"><o:p><span style="font-size: large;"> </span></o:p></p>
<p class="MsoListParagraphCxSpMiddle" style="margin-left: 1in; mso-add-space: auto;"><span style="font-family: "courier new"; font-size: large;">(tempenv)
krishna@osboxes:/tmp/tempenv$ <b>ls -ltr tempenv/lib/python3.5/site-packages/ |
grep -i flask</b><o:p></o:p></span></p>
<p class="MsoListParagraphCxSpMiddle" style="margin-left: 1in; mso-add-space: auto;"><span style="font-family: "courier new"; font-size: large;">drwxrwxr-x 4 krishna krishna 4096
Aug 22 11:05 flask<o:p></o:p></span></p>
<p class="MsoListParagraphCxSpMiddle" style="margin-left: 1in; mso-add-space: auto;"><span style="font-family: "courier new"; font-size: large;">drwxrwxr-x 2 krishna krishna 4096
Aug 22 11:05 Flask-1.1.2.dist-info<o:p></o:p></span></p>
<p class="MsoListParagraphCxSpMiddle"><o:p><span style="font-size: large;"> </span></o:p></p>
<p class="MsoListParagraphCxSpMiddle"><span style="font-size: large;">Step 6 <o:p></o:p></span></p>
<p class="MsoListParagraphCxSpMiddle"><span style="font-size: large;">Create a generic test.py basic python
script to test the Flask <o:p></o:p></span></p>
<p class="MsoListParagraphCxSpMiddle"><o:p><span style="font-size: large;"> </span></o:p></p>
<p class="MsoListParagraphCxSpMiddle" style="margin-left: 1in; mso-add-space: auto;"><span style="font-family: "courier new"; font-size: large;">from flask import Flask<o:p></o:p></span></p>
<p class="MsoListParagraphCxSpMiddle" style="margin-left: 1in; mso-add-space: auto;"><span style="font-family: "courier new"; font-size: large;">app = Flask(__name__)<o:p></o:p></span></p>
<p class="MsoListParagraphCxSpMiddle" style="margin-left: 1in; mso-add-space: auto;"><span style="font-family: "courier new"; font-size: large;">@app.route('/')</span></p>
<p class="MsoListParagraphCxSpMiddle" style="margin-left: 1in; mso-add-space: auto;"><span style="font-family: "courier new"; font-size: large;">def hello_world():<o:p></o:p></span></p>
<p class="MsoListParagraphCxSpMiddle" style="margin-left: 1in; mso-add-space: auto;"><span style="font-family: "courier new"; font-size: large;"><span style="mso-spacerun: yes;"> </span>return 'Hello World!'<o:p></o:p></span></p>
<p class="MsoListParagraphCxSpMiddle" style="margin-left: 1in; mso-add-space: auto;"><span style="font-family: "courier new"; font-size: large;">if __name__ == '__main__':</span></p>
<p class="MsoListParagraphCxSpMiddle" style="margin-left: 1in; mso-add-space: auto;"><span style="font-family: "courier new"; font-size: large;"><span style="mso-spacerun: yes;"> </span>app.run()<o:p></o:p></span></p>
<p class="MsoListParagraphCxSpMiddle"><o:p><span style="font-size: large;"> </span></o:p></p>
<p class="MsoListParagraphCxSpMiddle"><span style="font-size: large;">Execute the python script <o:p></o:p></span></p>
<p class="MsoListParagraphCxSpMiddle" style="margin-left: 1in; mso-add-space: auto;"><span style="font-family: "courier new"; font-size: large;">(tempenv)
krishna@osboxes:/tmp/tempenv$ <b>python test.py</b><o:p></o:p></span></p>
<p class="MsoListParagraphCxSpMiddle" style="margin-left: 1in; mso-add-space: auto;"><span style="font-family: "courier new"; font-size: large;"><span style="mso-spacerun: yes;"> </span>* Serving Flask app "test" (lazy
loading)<o:p></o:p></span></p>
<p class="MsoListParagraphCxSpMiddle" style="margin-left: 1in; mso-add-space: auto;"><span style="font-family: "courier new"; font-size: large;"><span style="mso-spacerun: yes;"> </span>* Environment: production<o:p></o:p></span></p>
<p class="MsoListParagraphCxSpMiddle" style="margin-left: 1in; mso-add-space: auto;"><span style="font-family: "courier new"; font-size: large;"><span style="mso-spacerun: yes;">
</span>WARNING: This is a development server. Do not use it in a production
deployment.<o:p></o:p></span></p>
<p class="MsoListParagraphCxSpMiddle" style="margin-left: 1in; mso-add-space: auto;"><span style="font-family: "courier new"; font-size: large;"><span style="mso-spacerun: yes;">
</span>Use a production WSGI server instead.<o:p></o:p></span></p>
<p class="MsoListParagraphCxSpMiddle" style="margin-left: 1in; mso-add-space: auto;"><span style="font-family: "courier new"; font-size: large;"><span style="mso-spacerun: yes;"> </span>* Debug mode: off<o:p></o:p></span></p>
<p class="MsoListParagraphCxSpLast" style="margin-left: 1in; mso-add-space: auto;"><span style="font-family: "courier new"; font-size: large;"><span style="mso-spacerun: yes;"> </span>*
Running on http://127.0.0.1:5000/ (Press CTRL+C to quit)<o:p></o:p></span></p><br /><p></p><p></p>Krishna Shttp://www.blogger.com/profile/17983452641688345252noreply@blogger.com3tag:blogger.com,1999:blog-7570540209369866921.post-12079419530612249232020-03-11T14:43:00.002+05:302020-08-15T15:23:16.272+05:30Looking to move from development to DevOps? Here is what you should knowDevOps is a buzz word in the last decade, everybody wants to try their luck in DevOps. DevOps is the most generic area of software engineering. Every team and every company wants to have a team called DevOps. Nowadays, everybody wants a DevOps tag name attached to them irrespective of what they do. This works for most people. Because of the generous nature of the DevOps skillsets. That means, the pool of DevOps skillsets so huge, even if you know a few of them, it will work for you.<br />
<br />
If you are a developer or QA and want to change over to DevOps, here is the list of bare minimum technologies and skills you should learn. These are the mandatory skillsets you should possess before you try your luck in DevOps.<br />
<br />
<b>1. Expert Knowledge on Linux Environment</b><br />
<br />
You take any software development, without a Linux flavor it will be incomplete. You should have a thorough knowledge of how to work in a Linux environment. If you have system administration knowledge, then it's a huge advantage. Most of the DevOps activities involve manipulating the environments manually or through automation. You may have to analyze the system for performance, failures, and/or improve the performance of the existing systems. Create or add new features to your existing systems. You should be well versed with basic to advanced Linux commands. You should know multiple flavors of Linux distributions. Distributions like RHEL, CentOS, and Ubuntu are must, these are the most used distributions.<br />
<br />
CentOS, Ubuntu, RHEL<br />
<br />
<b>2. Scripting Languages </b><br />
<br />
As a DevOps engineer, you should give more importance to Scripting languages rather than programming languages. You should know more than one scripting language. In the current trend, Python and Shell scripting are must have skillsets. These two scripts are most used in DevOps automation. Shell scripting is a building block while you are working in a Linux environment. You can build simple to complex tools and utilities using Shell scripts. While you can use Python in just about any area. In fact, python is replacing most of the mainstream programming languages, because of its conciseness and narrow learning curve. In addition to these two, you can have PHP, JavaScript, Ruby, etc, which will boost your chances.<br />
<br />
Python, Shell Scripting, PHP<br />
<br />
<b>3. Programming Languages</b><br />
<b><br /></b>
Apart from scripting, you should also know multiple programming languages. It is ok even if you know a basic understanding of the constructs of the language. There are fewer chances that you end up writing programs in these high-level programming languages like C, C++, C#, or Java. But you may be involved in products or solutions which are written in these languages. You will have to support the production and release of these software packages. So, you must know how the code is built, how the binary packages created, how they are distributed. After all the DevOps is derived from Build and Release Management.<br />
<br />
C, C++, Java, C#<br />
<br />
<b>4. Version control tools</b><br />
<br />
Being in the software industry you must know one of the version control tools. It does not matter whether you are a developer, QA, or Program Manager. But for a DevOps person, you must have expert level knowledge on more than one version control tool. Being a DevOps guy, you own the entire codebase that the developer has put in. So, you take the pride saying that. You should be thoroughly knowledgeable about codebase management. You should be capable of introducing the best practices to the developer so that their time is better utilized. You take all the burden of the operations in the version control and give the developer his time to develop the application. You take the responsibility of giving him feedback about the quality of his code. This is where the automation comes into the picture. This is where you can excel, you have a better scope for showing your talent.<br />
<br />
GIT, Bitbucket, SVN, TFS<br />
<br />
<b>5. CI/CD and CM tools</b><br />
<br />
This is a major area where the scope differs from one company to another. Different companies use different sets of tools. Being a DevOps engineer you have the authority to choose and suggest in the interest of productivity. Every now and then there are new tools coming up. You should thrive learning new tools, analyze and choose the best-suited tool for your environment. Jenkins is a must-have tool for CI and CD. It is the most used CI/CD tool, thanks to its extendibility feature. You will get plenty of plugins to suit your needs. There are other options like Bamboo, TeamCity, Gitlab CI, CruiseControl, UrbanCode, etc.<br />
<br />
Jenkins, Jenkins pipeline<br />
<br />
CM tools are configuration management tools used in provisioning the systems. When you have to install and configure the environments more frequently, you can go for one of these automation tools. These tools are also called orchestration tools. This works on the concept of write once deploy many times. You keep your configuration items as code when you need to create or update an environment, just deploy/run your codebase which contains your configured items<br />
<br />
Chef, Puppet, Ansible, Saltstack are major tools available. If you know anyone of these should fine.<br />
<br />
<b> 6. Virtualization and Container Technologies</b><br />
<b><br /></b>
Without Docker and Kubernetes, you will not be considered fit for the DevOps. How much it is being used in many companies is a different argument. As an entry point, you should know virtualization and containerization. Virtualization is key in the areas of Cloud, Containers, Orchestration, and Configuration Management. People are moving towards software-defined hardware. What that means is, instead of going with physical hardware, a software abstraction is used to achieve the functionality of that hardware. A major breakthrough is a software-defined network. It is gaining momentum in Software-defined storage, software-defined data center, software-defined infrastructure, etc.<br />
<br />
It gives you a jump start to your career if you are familiar with enterprise-level virtualization tools and concepts from VMWare, Citrix, NetApps, and EMC2, etc.<br />
<br />
You should be familiar with Container and container management tools. Docker, Kubernetes, Docker Enterprise, Docker Swarm, Redhat OpenShift, AWS ECS, Google Container Engine are the major stakeholders in the containers management field. If you know at least 3 of them, then you are better placed than others.<br />
<br />
<b><br /></b>
<b>7. Cloud concepts</b><br />
<br />
AWS is an undisputed king in the cloud technologies until a few years. And in recent years, Microsoft Azure is not far behind, they are catching up. Google cloud is the next promising player in the cloud business. It is very important to have either AWS or MS Azure experience in your resume. If you do not have one, it's better to start today, as you will get plenty of free tiers cloud accounts. You can try as much as you want and get familiar with cloud technologies.<br />
<br />
Amazon AWS, MS Azure<br />
<br />
<b>8. Build tools </b><br />
<br />
This is an old technological area, it takes the least position but it is important to know if you are aspiring a DevOps role. The build tool is used for building a software component out of the source code. There are different tools for different programming languages. For Java, you have Ant, Maven, and Gradle. For C/C++ you have GNU make and CMake. For C# you have MSBuild.<br />
<br />
Make, Ant, Maven, MSBuild<br />
<br />
<b>9. Build and Release Management </b><br />
<br />
After all, DevOps is derived from BnR a.k.a, Build and Release Management. There are companies still stuck with this BnR or B&R and many other companies renamed their teams as DevOps, but under the hood, they are still doing somewhat similar tasks. The only difference is that they have upgraded with the tools they use. Having prior knowledge of Build and Release management gives you an unmatched advantage to start your DevOps job.<br />
<br />
<b>Conclusion</b><br />
<br />
These are the minimum requirements to start with DevOps. As you start working in DevOps, you should be capable of continuously adapting to changes as and when required. That is one major quality a DevOps engineer should possess. Since the tools and technologies vary hugely from company to company, you should be a quick learner to make yourself capable of the tools used in your newer job. <br />
<br />
<br />Krishna Shttp://www.blogger.com/profile/17983452641688345252noreply@blogger.com0tag:blogger.com,1999:blog-7570540209369866921.post-27564533591251195632019-12-14T20:06:00.002+05:302020-08-15T15:25:58.741+05:30Xiaomi Redmi Note 8 Pro new featuresRedmi Note 8 Pro is among the most anticipated flagship phone in the last few months in India. Now that it is available and I wanted to list out some of the features that new on this device and interesting.<br />
<br />
<b>You can connect to Wifi and enable Hotspot at the same time</b><br />
<br />
This is a very interesting feature. You can enable WiFi and at the same time, you can enable the hotspot. Earlier, either you could enable WiFi or enable Hotspot at the same time. That means you could use mobile data in the hotspot. Once you enable the hotspot, your WiFi would be disabled and when you enable the WiFi, your hotspot would be disabled. With this, you could only share your mobile data through the hotspot. Now you can use the WiFi data to share the internet through the hotspot.<br />
<br />
With this, you can use your mobile as a WiFi repeater, as a WiFi relay. If your WiFi router is a bit far from your TV, for example, and if the strength of the Wifi signal is weak, then you can keep your mobile phone in between these 2 devices. This works perfectly.<br />
<br />
You can share your WiFi internet with other devices.<br />
<br />
<b>Built-in screen recorder with audio recording feature</b><br />
<br />
The earlier versions did not support the audio recordings. Now, this is been enabled. But it is not enabled by default. You need to enable it in the App setting. Under settings, within the App, go to "Sound Source". You will get three options. Mute, Mic, and System sound.<br />
<br />
<b>Disable or edit the Camera watermark</b><br />
<b><br /></b>
You will see a watermark on the photos taken with many flagship phones where the camera is one the feature to be showcased or a selling point for the manufacturers. But not all of like it for obvious reasons. If you do not like it, well, there is an option to disable it. Wait, what if you do not like the default watermark and you want to have your own watermark, well, there is an option.<br />
<br />
Open the camera, go to Settings, then select the Watermark option under the Camera section. You can toggle the default watermark or you can make a custom watermark as per your likings. Note there that, you can also add the timestamp<br />
<br />
<b>Game turbo mode</b>.<br />
<br />
It enables smooth graphics and speed. The enhancement includes faster Frames Per Second graphics, WiFi and 4G enhancement, and sound quality optimization. Overall the gaming experience will improve a lot better.<br />
<br />
<br />Krishna Shttp://www.blogger.com/profile/17983452641688345252noreply@blogger.com13tag:blogger.com,1999:blog-7570540209369866921.post-16484146647581147332019-08-25T23:42:00.001+05:302020-08-15T15:27:54.655+05:30MS word drop down control with Excel dataThere are lots of things you can do with MS Excel and MS word. Word and Excel can be connected easily to build sophisticated use cases. Mail merge is one of the great tool, where you can leverage the data power of excel and reporting feature of the MS Word. With a bit of VBA knowledge, you can extend the mail merge for usages.<br />
<div>
<br /></div>
<div>
Below I explain how to use the Excel data to populate the DropDown control in the MS word. And it is dynamic, when the data in Excel got changed, the items in the drop-down will also be changed. </div>
<div>
<br /></div>
<div>
First, I have this sample data in Excel.</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEieW-pmwi8tZEKaKW-xvh5gh145wtTYEGjPTJ0t0pxxgs4JhKSYwWOQtu7V-sOTxclrohjVIwFlA_MFFUV7La9_GjC63_VAhPYZ0r1c6bLyHf2JVZGj5Cnz54Zj0Ku6cespsrenngXG2Vo/s1600/1.JPG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="345" data-original-width="834" height="165" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEieW-pmwi8tZEKaKW-xvh5gh145wtTYEGjPTJ0t0pxxgs4JhKSYwWOQtu7V-sOTxclrohjVIwFlA_MFFUV7La9_GjC63_VAhPYZ0r1c6bLyHf2JVZGj5Cnz54Zj0Ku6cespsrenngXG2Vo/s400/1.JPG" width="400" /></a></div>
<div>
<br /></div>
<div>
I want this data to be displayed in a dropdown control in the MS Word. For this I am making use of this macro or VBA code.</div>
<div>
<br /></div>
<div>
<span face="" style="color: blue; font-family: "courier new", courier, monospace;"><b>Private Sub ld_Click()</b></span></div>
<div>
<span face="" style="color: blue; font-family: "courier new", courier, monospace;"><b>Dim objExcel As New Excel.Application</b></span></div>
<div>
<span face="" style="color: blue; font-family: "courier new", courier, monospace;"><b>Dim objWB As Excel.Workbook</b></span></div>
<div>
<span face="" style="color: blue; font-family: "courier new", courier, monospace;"><b>Set objWB = objExcel.Workbooks.Open("C:\Users\krsanjee\Documents\krishtalk.com\book1.xlsx")</b></span></div>
<div>
<span face="" style="color: blue; font-family: "courier new", courier, monospace;"><b>'Me.drop.RemoveItem (0)</b></span></div>
<div>
<span face="" style="color: blue; font-family: "courier new", courier, monospace;"><b>Me.drop.Clear</b></span></div>
<div>
<span face="" style="color: blue; font-family: "courier new", courier, monospace;"><b><br /></b></span></div>
<div>
<span face="" style="color: blue; font-family: "courier new", courier, monospace;"><b>total_rows = Cells(Rows.Count, 1).End(xlUp).Row</b></span></div>
<div>
<span face="" style="color: blue; font-family: "courier new", courier, monospace;"><b>total_columns = Cells(1, Columns.Count).End(xlToLeft).Column</b></span></div>
<div>
<span face="" style="color: blue; font-family: "courier new", courier, monospace;"><b><br /></b></span></div>
<div>
<span face="" style="color: blue; font-family: "courier new", courier, monospace;"><b> With Me.drop</b></span></div>
<div>
<span face="" style="color: blue; font-family: "courier new", courier, monospace;"><b> For r = 2 To total_rows Step 1</b></span></div>
<div>
<span face="" style="color: blue; font-family: "courier new", courier, monospace;"><b> .AddItem objWB.Sheets(1).Cells(r, 1)</b></span></div>
<div>
<span face="" style="color: blue; font-family: "courier new", courier, monospace;"><b> Next r</b></span></div>
<div>
<span face="" style="color: blue; font-family: "courier new", courier, monospace;"><b> End With</b></span></div>
<div>
<span face="" style="color: blue; font-family: "courier new", courier, monospace;"><b><br /></b></span></div>
<div>
<span face="" style="color: blue; font-family: "courier new", courier, monospace;"><b>objWB.Close</b></span></div>
<div>
<span style="color: blue;"><span face="" style="font-family: "courier new", courier, monospace;"><b>End Sub</b></span> </span></div>
<div>
<br /></div>
<div>
<span style="font-family: inherit;">Note the </span><b style="color: blue; font-family: "courier new", courier, monospace;">Sub ld_Click()</b><span style="font-family: inherit;"> Subroutine, it has been assigned to a button control. Clicking on this button you can load the dropdown control with the data from Excel</span></div>
<div>
<span style="font-family: inherit;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhlyea2TCujpJPnJVpn0iN1MRBPS4zj9YdulXoZYQwt3h6CPbIdh3ydvCDOrpGwttrMZF9-ttfqp88X5IGRRg5eE_Ez-R6V2E9UPgaO3IqYr4QPoRduGkIfJCGRKLooqCg-AXz037QDghI/s1600/2.JPG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="463" data-original-width="749" height="246" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhlyea2TCujpJPnJVpn0iN1MRBPS4zj9YdulXoZYQwt3h6CPbIdh3ydvCDOrpGwttrMZF9-ttfqp88X5IGRRg5eE_Ez-R6V2E9UPgaO3IqYr4QPoRduGkIfJCGRKLooqCg-AXz037QDghI/s400/2.JPG" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhwIYNuyFD1VUmhc5Fen7fIetgVnBlaN7eUcxYjYQBe4_INQ0bCKlq_16DANd6SKFCBIkDChss9DWAbuyqLLo21hOHdmExkC0AY1g0nKh0Ywzx-AXpaEXSO6VD9J2GBnCo4C8jJn_fuQ34/s1600/3.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="501" data-original-width="1250" height="256" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhwIYNuyFD1VUmhc5Fen7fIetgVnBlaN7eUcxYjYQBe4_INQ0bCKlq_16DANd6SKFCBIkDChss9DWAbuyqLLo21hOHdmExkC0AY1g0nKh0Ywzx-AXpaEXSO6VD9J2GBnCo4C8jJn_fuQ34/s640/3.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div>
<span style="font-family: inherit;"><br /></span></div>
<div>
Compare the value in the first column in the Excel and the values in the MS Word dropdown. I am using the first column that is a serial number because I needed the Sl No. to manipulate the data to extract other columns from the Excel for reporting purpose. </div>
<div>
<br /></div>
<div>
The code has been changed slightly to get the EMP Name in the dropdown. </div>
<div>
<br /></div>
<div>
<div>
<span face="" style="color: blue; font-family: "courier new", courier, monospace;"><b> With Me.drop</b></span></div>
<div>
<span face="" style="color: blue; font-family: "courier new", courier, monospace;"><b> For r = 2 To total_rows Step 1</b></span></div>
<div>
<span face="" style="color: blue; font-family: "courier new", courier, monospace;"><b> .AddItem objWB.Sheets(1).Cells(r, 2)</b></span></div>
<div>
<span face="" style="color: blue; font-family: "courier new", courier, monospace;"><b> Next r</b></span></div>
<div>
<span face="" style="color: blue; font-family: "courier new", courier, monospace;"><b> End With</b></span></div>
</div>
<div>
<br /></div>
<div>
In the <b style="color: blue; font-family: "courier new", courier, monospace;">.AddItem objWB.Sheets(1).Cells(r, 2)</b> line I have changed the column number to 2 so that the EMP Name will be populated in the dropdown.</div>
<div>
<br /></div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEirgceYD95aQAn1Mlq4zss_If2JxoSo-khGotmXMIpY6iwv36OyQ5o74dSO7OOPlv4LldXLAtrmuqRmnXmyOyY-o9F8l_cQfFO4gZ2jYAkQ4LxnEBnz9VGdP1YQ5PsiMXM3KAZ4doJOpsY/s1600/4.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="713" data-original-width="1218" height="374" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEirgceYD95aQAn1Mlq4zss_If2JxoSo-khGotmXMIpY6iwv36OyQ5o74dSO7OOPlv4LldXLAtrmuqRmnXmyOyY-o9F8l_cQfFO4gZ2jYAkQ4LxnEBnz9VGdP1YQ5PsiMXM3KAZ4doJOpsY/s640/4.jpg" width="640" /></a></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
Watch the step by step tutorial in my YouTube channel </div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<iframe allowfullscreen="" class="YOUTUBE-iframe-video" data-thumbnail-src="https://i.ytimg.com/vi/oxs42t1Vrz8/0.jpg" frameborder="0" height="266" src="https://www.youtube.com/embed/oxs42t1Vrz8?feature=player_embedded" width="320"></iframe></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br />
<br />
<br />
<br /></div>
Krishna Shttp://www.blogger.com/profile/17983452641688345252noreply@blogger.com5tag:blogger.com,1999:blog-7570540209369866921.post-51780381848971494012019-06-23T12:58:00.001+05:302019-06-23T12:58:11.409+05:30VirtualBox and Hyper V conflicts on Windows 10If you are using Windows 10 operating system and want to use virtualization on it, then you have 2 options.<br />
<br />
1. Using Virtual Box<br />
2. Using Hyper V<br />
<br />
This post is not about how to use them but instead, what are the issues you will encounter if you have them together.<br />
<br />
One scenario is, you have VirtualBox installed and working fine. Then you realize you want to use a docker on your Windows machine. The VirtualBox working fine until will start comparing about virtualization and acceleration. After installing the docker on your windows, all your VMs on the VirtualBox cannot start. It complains about the availability of virtualization.<br />
<br />
"<span style="font-family: "courier new" , "courier" , monospace;">The hardware virtualization is enabled in the Acceleration section of the system although it is not supported from the host system. It should be disabled in order to start the visual system</span>"<br />
<br />
The error message not straight forward to understand. As you know virtualization is required for docker or VirtualBox to work. First thing you will do now is, check whether your system is virtualization enabled or not. There are multiple options to check if your system is enabled with virtualization or not. Check the BIOS settings, check the CPU section of the Task manager. <br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEixc0a-Qw1bTGwX_z9NTkd1ibGf5Fe2kdvJJD3ndFJQizixD9CiEmMga27I_XT0ErceaPMwBPxP0K_rZnIOtxVZpZLwfj9cx3qjy5M32dgCCC8qEhf4LYRfmxWm5yVaky8Nx_ms44z6NlM/s1600/check_virtualization.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="603" data-original-width="845" height="456" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEixc0a-Qw1bTGwX_z9NTkd1ibGf5Fe2kdvJJD3ndFJQizixD9CiEmMga27I_XT0ErceaPMwBPxP0K_rZnIOtxVZpZLwfj9cx3qjy5M32dgCCC8qEhf4LYRfmxWm5yVaky8Nx_ms44z6NlM/s640/check_virtualization.JPG" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Now you start worrying more about the possible reason for VirtualBox failure. The reason for this failure is having VirtualBox and Docker running on your system. </div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
If you install docker in your Windows, it will enable the Hyper V and install a virtual machine for docker to work. This is the requirement for docker on Windows. Both VirtualBox and Hyper V cannot run together. However, the later versions of docker might come up with some capability to have them together. </div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg4k-MCO1bLLGV7BxWYGkOXJp3UtDzv8Yd8rCUalmqKafeAVZpOhX6frXTfQBpzaIwThLymQ52qovtkVp_cJFy82eT3PyEezVNWpqE3qcRlRjIbZOq3DcvuBrX-GrgdV5JbHkNba_Wy2xc/s1600/invalid+settings.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="578" data-original-width="708" height="326" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg4k-MCO1bLLGV7BxWYGkOXJp3UtDzv8Yd8rCUalmqKafeAVZpOhX6frXTfQBpzaIwThLymQ52qovtkVp_cJFy82eT3PyEezVNWpqE3qcRlRjIbZOq3DcvuBrX-GrgdV5JbHkNba_Wy2xc/s400/invalid+settings.png" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
To solve this problem, disable the Hyper V from optional features on your Windows 10. Remember, disabling the Hyper V will stop your docker. Only if you are not interested in docker and only need VirtualBox to run, then do this. </div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Open the optional feature dialog box by entering the <b>optionalfeatures</b> in the run box</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgixfJcnjZMUeaIa0IEUzFohBCP9_awIzfqxo1jbM33wBoyHIbP8bDvyW-wPGPrcy0LpsKxzoQjgm-uBoFx2eFffbzxUdlfhVtULxm6aZFi-4mH9D1lTKy4QKxEFq8g72ye26J7FL-GPyo/s1600/optional_features.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="281" data-original-width="476" height="188" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgixfJcnjZMUeaIa0IEUzFohBCP9_awIzfqxo1jbM33wBoyHIbP8bDvyW-wPGPrcy0LpsKxzoQjgm-uBoFx2eFffbzxUdlfhVtULxm6aZFi-4mH9D1lTKy4QKxEFq8g72ye26J7FL-GPyo/s320/optional_features.JPG" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Then uncheck the box Hyper-V, it requires a restart. After a restart, your VirtualBox should work as before. </div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjPYMk0xrVx_eIQO7qZYJMms36T-q8e5fnVIpkALHow_atEcdCuJVheGFZb3h3-z0ThdgXYMkMlHkUCI-Kel8Ui2VbS_BI9VfyTIuvHSnMGm1Zj3qGqlBU_s14X4KtoIQP3x1tiSdvHHFo/s1600/disableHyperv.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="498" data-original-width="560" height="355" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjPYMk0xrVx_eIQO7qZYJMms36T-q8e5fnVIpkALHow_atEcdCuJVheGFZb3h3-z0ThdgXYMkMlHkUCI-Kel8Ui2VbS_BI9VfyTIuvHSnMGm1Zj3qGqlBU_s14X4KtoIQP3x1tiSdvHHFo/s400/disableHyperv.JPG" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Error messages:</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
1. Invalid settings detected. </div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
2. System acceleration page:</div>
<div class="separator" style="clear: both; text-align: left;">
The hardware virtualization is enabled in the Acceleration section of the System page although it is not supported by the host machine. It should be disabled in order to start the virtual system.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
3. General Basic page:</div>
<div class="separator" style="clear: both; text-align: left;">
The virtual machine operating hint is set to 64-bit type. 64-bit guest system require hardware virtualization, so this will be enabled automatically if you confirm the changes. </div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
4. Raw-mode is unavailable courtesy of Hyper-V. (VERR_SUPDRV_NO_RAW_MODE_HYPER_V_ROOT</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
Failed to open a session for the virtual machine <b>ubantu</b>.<br />
Raw-mode is unavailable courtesy of Hyper-V. (VERR_SUPDRV_NO_RAW_MODE_HYPER_V_ROOT).<br />
<table bgcolor="#EEEEEE" border="0" cellpadding="0" cellspacing="5" style="width: 100%px;"><tbody>
<tr><td>Result Code: </td><td><tt>E_FAIL (0x80004005)</tt></td></tr>
<tr><td>Component: </td><td>ConsoleWrap</td></tr>
<tr><td>Interface: </td><td>IConsole {872da645-4a9b-1727-bee2-5585105b9eed}</td></tr>
</tbody></table>
<div class="separator" style="clear: both;">
<qt><br /></qt></div>
<div class="separator" style="clear: both;">
<qt><br /></qt></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<br />Krishna Shttp://www.blogger.com/profile/17983452641688345252noreply@blogger.com11tag:blogger.com,1999:blog-7570540209369866921.post-43894922025506940232019-05-19T10:54:00.003+05:302019-06-16T12:13:56.067+05:30Excel conditional formatting based on multiple cell values<span style="font-size: large;">1. Condition on a single cell</span><br />
<br />
To apply conditional formatting based on the values of multiple cells. In other words, format a cell where formatting cell is different and conditional cell is different.<br />
<br />
Click on the cell where you want to apply for formatting<br />
<br />
Go to Home on the Ribbon, then select Conditional Formatting.<br />
<br />
Then select the last option, "Use a formula to select which cell to format"<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgNuaQaWnHbXKxkZOCtVHD_aMhF_Kh_VudEghqbqriu9sjRlMYv6LAFAPaeDIp5ua6qT5JHnHy-mJ8UmG3KZA4Q0Jb8ZJreXoJ4gMP0lcViTigRkLxxnSNgMkcw6pn9BtX_DEfp4GijMow/s1600/conditional_formatting.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="464" data-original-width="462" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgNuaQaWnHbXKxkZOCtVHD_aMhF_Kh_VudEghqbqriu9sjRlMYv6LAFAPaeDIp5ua6qT5JHnHy-mJ8UmG3KZA4Q0Jb8ZJreXoJ4gMP0lcViTigRkLxxnSNgMkcw6pn9BtX_DEfp4GijMow/s320/conditional_formatting.JPG" width="318" /></a></div>
<br />
Now, in the formula cell write the formula for your condition<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;"><b>=$C$6=10</b></span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjuTkDCdZSkT7nD_F827X9MUGJATJoy1PUpy2r9IJLznSguPj7WrhODbdeHYWAjuorxy4uoY4w5crFps0UaMT5Gv7sZOTNwp7_lhSNCNYfcFVAKH6DsbCtS_Myg-hQJFV93ZNfIPiyZkzQ/s1600/2.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="502" data-original-width="893" height="358" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjuTkDCdZSkT7nD_F827X9MUGJATJoy1PUpy2r9IJLznSguPj7WrhODbdeHYWAjuorxy4uoY4w5crFps0UaMT5Gv7sZOTNwp7_lhSNCNYfcFVAKH6DsbCtS_Myg-hQJFV93ZNfIPiyZkzQ/s640/2.JPG" width="640" /></a></div>
<br />
<br />
<span style="font-size: large;">2. Conditions on multiple cells</span><br />
<span style="font-size: large;"><br /></span>
What if you have to consider multiple cells for the condition?<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;"><b>Condition cell 1: Value equal to 10</b></span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><b>Condition cell 2: Value equal to "Krishna" </b></span><br />
<br />
You need to build a formula with multiple conditions using AND operator. Based on your requirement, you can also use OR operator. Not just AND and OR, you can use any of the available functions in the MS Excel. The overall result should be TRUE or FALSE. It does not matter how complex your formula is, what does matter is the return value.<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjp4dog3KbwV6hX0K6WWf0JMSwmmysTOHVhtgJ2JKMYT5p0rbIUXlYq7zTq-dPZftoGAAqgJtHHFekWAdKo91jl-l6OJfsZCTodqFQXqCAriNMOCby_WqJ-d8WYXGTP-jxPYVAYNjK_Jd4/s1600/4.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="277" data-original-width="576" height="191" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjp4dog3KbwV6hX0K6WWf0JMSwmmysTOHVhtgJ2JKMYT5p0rbIUXlYq7zTq-dPZftoGAAqgJtHHFekWAdKo91jl-l6OJfsZCTodqFQXqCAriNMOCby_WqJ-d8WYXGTP-jxPYVAYNjK_Jd4/s400/4.JPG" width="400" /></a></div>
<br />
Now, build this formula and paste it in the conditional formatting formula bar.<br />
<span style="font-size: large;"><span style="background-color: white; color: #222222; font-family: "Courier New", Courier, monospace; font-size: 13.2px;"><b><br /></b></span></span>
<span style="font-size: large;"><span style="background-color: white; color: #222222; font-family: "Courier New", Courier, monospace; font-size: 13.2px;"><b>=AND($B$6=10, $C$6="Krishna")</b></span></span><br />
<span style="font-size: large;"><span style="background-color: white; color: #222222; font-family: "Courier New", Courier, monospace; font-size: 13.2px;"><b><br /></b></span></span>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgL3Mb4Mn4z5RhISpCxHewqjkSmCKbNh0it9kYQ85iOi0l0la4aG7f-h7gau1be2lD1VBjykGLE6zC1B2dtYGfRzZzFzp0lH6T7qTkfPSVUmsrZMBid2Kjv02Nz5xgGqYKjQ-mMM62bd_o/s1600/conditional_formatting1.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="759" data-original-width="1046" height="464" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgL3Mb4Mn4z5RhISpCxHewqjkSmCKbNh0it9kYQ85iOi0l0la4aG7f-h7gau1be2lD1VBjykGLE6zC1B2dtYGfRzZzFzp0lH6T7qTkfPSVUmsrZMBid2Kjv02Nz5xgGqYKjQ-mMM62bd_o/s640/conditional_formatting1.JPG" width="640" /></a></div>
<span style="font-size: large;"><span style="background-color: white; color: #222222; font-family: "Courier New", Courier, monospace; font-size: 13.2px;"><b><br /></b></span></span>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
Krishna Shttp://www.blogger.com/profile/17983452641688345252noreply@blogger.com2tag:blogger.com,1999:blog-7570540209369866921.post-52610643612863663482019-05-13T20:12:00.000+05:302019-05-13T20:21:18.283+05:30How to control your Android Phone from PC | How to make a phone call from Laptop Have you ever thought of using your Laptop or in general a PC to make a call? It would be easier if there is an option available to make a call or receive a call. Yes, there is an option to use your PC to make a call.<br />
<div>
<br /></div>
<div>
In other words, connect your mobile phone to your PC and control it. Without touching your phone you can make a call, send an SMS or transfer a file between your PC and your Mobile. You can even open a link in the Mobile browser. </div>
<div>
<br /></div>
<div>
There is a utility called AirDroid, using this you can connect your phone to PC. Then, from within your PC dial a number or send an SMS, etc. This is very handy if you are using the phone most frequently when you are in front of your Laptop.</div>
<div>
<br /></div>
<div>
How to setup.</div>
<div>
<br /></div>
<div>
On your Android mobile, download and install the Airdroid mobile app. On your PC, you have an option either install a desktop application or you can use the web application. </div>
<div>
<br /></div>
<div>
If you don't want to install any application on your desktop, then go to <a href="http://web.airdroid.com/">http://web.airdroid.com/</a>. It will give you 2 options. One, you can register yourself in an existing email. Second, you can just scan the QR code and connect instantly.<br />
<br /></div>
<div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzkcGscfrjtcqg6lEJRBllQskT3k6vG7pY1vvaixa3nNbp0AgQ7XhaKOrRAWnUuEBN7wyYfIlNMIP9SepLJUbiu6mPM8TJMAh_AvqvlLOdeFwKlMeuD6lFaw1bZ7ZVuG-nOwR8xZiydR8/s1600/Capture.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="507" data-original-width="706" height="286" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzkcGscfrjtcqg6lEJRBllQskT3k6vG7pY1vvaixa3nNbp0AgQ7XhaKOrRAWnUuEBN7wyYfIlNMIP9SepLJUbiu6mPM8TJMAh_AvqvlLOdeFwKlMeuD6lFaw1bZ7ZVuG-nOwR8xZiydR8/s400/Capture.JPG" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
In your mobile phone, Open the AirDroid app. Scan the QR code and click on SIGN IN. That's all, you are ready to control your phone from within the Laptop<br />
<br />
You will see this below screen. This is the main screen, you will get all the option on this page.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgxCxNkUJujx5AeTDypeEwINGPdixTtHPuyoNeRyMPTvywwgkCYwvO8iZzOZJ8EHtNdNE8Qw1L3mro4NkAvdEHkN3VQt7mmIHYqKBzE8B4O4WKIVfKMK6LQjv4cW6nMs9meM37eV5HQ6c8/s1600/Capture1.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="826" data-original-width="1600" height="330" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgxCxNkUJujx5AeTDypeEwINGPdixTtHPuyoNeRyMPTvywwgkCYwvO8iZzOZJ8EHtNdNE8Qw1L3mro4NkAvdEHkN3VQt7mmIHYqKBzE8B4O4WKIVfKMK6LQjv4cW6nMs9meM37eV5HQ6c8/s640/Capture1.JPG" width="640" /></a></div>
</div>
<div>
<br /></div>
<div>
You can do below tasks from your Laptop<br />
<br />
<br />
<ul>
<li>Make a Call or receive a call</li>
<li>See the notifications</li>
<li>Send an SMS</li>
<li>Play music or video which is present in your phone. You don't have to copy them to your PC</li>
<li>Open the camera and take photos or record a video</li>
<li>Find my Phone</li>
<li>See the call logs</li>
<li>Status bar, which shows battery percentage, network connectivity</li>
</ul>
<br />
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
Krishna Shttp://www.blogger.com/profile/17983452641688345252noreply@blogger.com5tag:blogger.com,1999:blog-7570540209369866921.post-20961384068281073032019-03-16T21:48:00.005+05:302019-03-16T21:51:33.634+05:30How to connect wireless Bluetooth headphones to your old TV or non smart TVHave you ever thought of making your old TV wireless audio capable? Wanted to connect your wireless headphone or a wireless speaker to your TV which does not support wireless audio streaming? You can do that with an additional device.<br />
<br />
Smart TVs have the capability to stream your TV audio onto your wireless speakers or headphones. But non-smart TVs does not support this feature. Just for the wireless purpose, you cannot change your TV, right?. Instead, make your TV wireless capable and connect any wireless audio receivers to your TV and increase your TV viewing experience.<br />
<br />
I will explain to you what are the devices you need to make your TV wireless audio capable, but before that, you should understand some terminologies.<br />
<br />
<b>Bluetooth audio transmitters and Bluetooth audio receivers </b><br />
<br />
Most of the Bluetooth wireless audio devices like Headphones, Wireless Speakers are audio receivers. They are capable of receiving the audio signals through from a transmitter and they cannot transmit back. The audio sources like Smart TV, Computer, Mobiles, Audio Players are the Bluetooth audio transmitters. So, you are going to make your TV which a Bluetooth audio transmitter. For that, you need additional hardware.<br />
<br />
There are plenty in of them in the online market. Before getting one, check the specifications.<br />
<br />
<b>The connectivity options </b><br />
<br />
The first thing you need to check is that its a transmitter and not a receiver. For example, if you want to play your mobile content on your car stereo, or any other AUX enabled speakers, you need to look for Bluetooth audio receivers. But in our case, we want to transmit the TV audio.<br />
<br />
Second, check for input and out options. That means, the device's input should match with your TV audio output. If your TV has 3.5 mm audio output jack, then your transmitter should have 3.5mm audio input options. If your TV does not support the 3.5mm audio out then you have to go for the transmitter which has AV input. Every TV should have AV out and AV in. If your audio source is an STB, then you have to connect STB audio out to transmitter audio input.<br />
<br />
Then, the options available for the power supply. The device needs an external power supply. Generally, it will be a USB cable. If your TV has a USB port, use it for power supply. Otherwise, you can use any mobile wall charger which has a USB port.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiSAWCHuBAh1wAN0wKdurcI-9-NTUEwwmtofrM2Z3qZlEUxSRNvIBLbWLzSG9p5nIV6zpeU0ifNOECpP7KaaBcAYWsnF1p2rDxhoJj-4Q4Boo35OM0L2AmBtDkaGhFoIyajyp4sXtOrFxA/s1600/IMG_20190316_212342.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1368" data-original-width="1600" height="273" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiSAWCHuBAh1wAN0wKdurcI-9-NTUEwwmtofrM2Z3qZlEUxSRNvIBLbWLzSG9p5nIV6zpeU0ifNOECpP7KaaBcAYWsnF1p2rDxhoJj-4Q4Boo35OM0L2AmBtDkaGhFoIyajyp4sXtOrFxA/s320/IMG_20190316_212342.jpg" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<b>Bluetooth Transmitter</b></div>
<br />
<div class="" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhu9Zd1ZlCe2Sf06NachXNcJDG9mXlJfhjb_hyBrRa_hDKeW894GkrWxpKxR4F3uHCXaEn-BuJSWI4cZa6Up2lz0lE6HuJ_y92ZODPvK5VD-kHX8Q-lM7cwVRJhxY6BiIV2qVhIsnbwfHc/s1600/IMG_20190316_212439.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1011" data-original-width="1600" height="202" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhu9Zd1ZlCe2Sf06NachXNcJDG9mXlJfhjb_hyBrRa_hDKeW894GkrWxpKxR4F3uHCXaEn-BuJSWI4cZa6Up2lz0lE6HuJ_y92ZODPvK5VD-kHX8Q-lM7cwVRJhxY6BiIV2qVhIsnbwfHc/s320/IMG_20190316_212439.jpg" width="320" /></a></div>
<div class="" style="clear: both; text-align: center;">
<b>AV to 3.5mm Audio converter</b></div>
<div class="" style="clear: both; text-align: center;">
<b><br /></b></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjBTOCXFFdy0c0LGXFpHX0d9BFpJePS9Dn7ltcuSrlhD1h1DTI3QxQKrpOK8scnDU-HZKr0Aoyui47JTEn-rgalufPW1u6LhVm50pUQKv_lYe0wTOvrwY2P-_uOSR8x_hclyeiCaAm9giA/s1600/IMG_20190316_213329.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1600" data-original-width="1220" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjBTOCXFFdy0c0LGXFpHX0d9BFpJePS9Dn7ltcuSrlhD1h1DTI3QxQKrpOK8scnDU-HZKr0Aoyui47JTEn-rgalufPW1u6LhVm50pUQKv_lYe0wTOvrwY2P-_uOSR8x_hclyeiCaAm9giA/s320/IMG_20190316_213329.jpg" width="244" /></a></div>
<div class="" style="clear: both; text-align: center;">
<b>AUX 3.5mm input output male to male cable</b></div>
<br />
<br />
I just purchased one of the basic Bluetooth transmitters. It does not come with a brand name, though it does the job pretty well. I even tried with the Laptop and it works perfectly. It has all the options like USB power, 3.5mm audio input and AV to 3.5mm audio converter.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhGkh7NHBsRaeMZ__rrEDmr5Vuimy_XSbjRX5TewP5yfzT17lLTAv2nX3-VTkiyDUX0jrhY42SQ1aObSylkxJoG-V4xzoMpf4Dy76iYmHARkr0FzaBGkyzQDamO2A3vcOvsK3R4LZjido4/s1600/IMG_20190316_212355.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1464" data-original-width="1600" height="292" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhGkh7NHBsRaeMZ__rrEDmr5Vuimy_XSbjRX5TewP5yfzT17lLTAv2nX3-VTkiyDUX0jrhY42SQ1aObSylkxJoG-V4xzoMpf4Dy76iYmHARkr0FzaBGkyzQDamO2A3vcOvsK3R4LZjido4/s320/IMG_20190316_212355.jpg" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<b>USB Power Source</b></div>
<div class="separator" style="clear: both; text-align: center;">
<b><br /></b></div>
My TV has USB and 3.5mm audio out close to each other. Used TV USB for power supply. Connected TV audio out from 3.5mm to audio input of the transmitter. <br />
<br />
<br />Krishna Shttp://www.blogger.com/profile/17983452641688345252noreply@blogger.com4tag:blogger.com,1999:blog-7570540209369866921.post-7151494177505950042019-01-22T15:23:00.003+05:302020-08-15T15:33:45.482+05:30Amazing tricks in MS Word which are less known <div class="MsoNormal">
<span style="font-size: large;">Write Anywhere<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
How to write anywhere in the MS word document<o:p></o:p></div>
<div class="MsoNormal">
You can only move the cursor to the location you have already visited. But what if you want to type anywhere in the document? You can do so by double-clicking your mouse left button. Once you double click, the cursor will move to that location and you can start typing. <o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjn1yitwJwXgPZcjEucaoPWNAbKSRkSFt_oWTbr47K-q9BU1a1dIOpgDf4qgF8Wix9tSdFV8wQdTTtrbv2_4eU-A-RAOlJoLvkbxp4wEhsFSDsJ_Lfxdy0FwQJOAx-KvrsZ9LaLdoXTua0/s1600/wtite+anywhere.gif" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="559" data-original-width="1193" height="186" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjn1yitwJwXgPZcjEucaoPWNAbKSRkSFt_oWTbr47K-q9BU1a1dIOpgDf4qgF8Wix9tSdFV8wQdTTtrbv2_4eU-A-RAOlJoLvkbxp4wEhsFSDsJ_Lfxdy0FwQJOAx-KvrsZ9LaLdoXTua0/s400/wtite+anywhere.gif" width="400" /></a></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-size: large;">Quick calculate<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-size: large;"><br /></span></div>
<div class="MsoNormal">
How to quickly calculate big numbers in MS word<o:p></o:p></div>
<div class="MsoNormal">
Think about you have a big number and you want to know the total sum or the difference of the numbers quickly. You may copy the values into excel and find a sum or difference. Or you can use your calculator to do the math. What if you have the Excel-like option within the MS word to quickly calculate the sum or difference? Yes, you have that option in the quick access menu. That’s at the top left corner. First, enable the Formula item in the quick access menu, then to find the sum select the numbers and press on the formula button. You can see the result in the status bar. <o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgNIKM8A02e4W55iqiidjV09a2C99JBfWXE3YlwPkIisgBiEmXaaWwUJZ1wfpUXFyEPTlU0AWwed-EBZgC3XZfoAItkKkzzTZSTzkwjgIzn8Bkyu7Brq-xi2FTH12288xNLSCjtdOCT2UM/s1600/quick+calculate.gif" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="635" data-original-width="308" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgNIKM8A02e4W55iqiidjV09a2C99JBfWXE3YlwPkIisgBiEmXaaWwUJZ1wfpUXFyEPTlU0AWwed-EBZgC3XZfoAItkKkzzTZSTzkwjgIzn8Bkyu7Brq-xi2FTH12288xNLSCjtdOCT2UM/s320/quick+calculate.gif" width="155" /></a></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-size: large;">How to clear the formatting at once.</span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Select the portion of the text, then press CTRL + Space. All the formatting will be cleared. <o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-size: large;">How to delete an entire word at once<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-size: large;"><br /></span></div>
<div class="MsoNormal">
Move to the location where you want to delete. Hold the CTRL key and press Backspace and Del one after the other. Backspace will delete the left portion from the cursor and Del will delete the right portion of the text from the cursor. <o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-size: large;">How to change upper case to lower case, Lower case to upper or camel case </span><o:p></o:p></div>
<div class="MsoNormal">
<span style="font-size: large;"><br /></span></div>
<div class="MsoNormal">
Select the portion of the text, which you want to change the case. Hold the SHIFT key and then press function key F3. Remember, if the function keys are associated with multimedia other functions, then you have to hold the Fn key along with the SHIFT key. <o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-size: large;">Replace all the images in the document at once</span><o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Keep the to-be-replaced image in the clipboard. For that, open the image in an image viewer and copy. Go to MS word document. Press CTRL + H to replace all the images. In the search box enter ^g, this means all the graphics content. In the replace box type ^c, this means the content of the clipboard. Then press replace all. You will see all the old images replaced with newer ones. <o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEigwFbyb-vB43DA0zlGxVXbz-2sKlqQrs6dx5hHNqcMzYzxdrNztddC-VLNBuXsw80Qvm51PpUdX26PpkrGAiXWzGnHRSbm3I3oHb7WomeJ_h0egHngmrKScrhRC83npwGp3MdCQW1oBNQ/s1600/repalce_images.JPG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="294" data-original-width="812" height="115" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEigwFbyb-vB43DA0zlGxVXbz-2sKlqQrs6dx5hHNqcMzYzxdrNztddC-VLNBuXsw80Qvm51PpUdX26PpkrGAiXWzGnHRSbm3I3oHb7WomeJ_h0egHngmrKScrhRC83npwGp3MdCQW1oBNQ/s320/repalce_images.JPG" width="320" /></a></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
<br />
<div class="MsoNormal">
<br /></div>
Krishna Shttp://www.blogger.com/profile/17983452641688345252noreply@blogger.com0tag:blogger.com,1999:blog-7570540209369866921.post-14628291580239749662019-01-03T15:01:00.003+05:302020-08-15T15:35:19.694+05:304 Amazing WhatsApp features you should knowA lot of new features have been introduced in the WhatsApp recently. You may know some of them or all of them. I am listing here 4 new features that are very handy.<br />
<br />
<b>1. Sending messages to multiple people - Message broadcasting</b><br />
<br />
Earlier there was no option to send messages more than one person at a time, without creating a group. But now, WhatsApp has added this new feature where you can send a message to multiple persons. Remember, you cannot forward a message to multiple people. It has to be your own message and not the forwarded message. If you want to broadcast a forwarded message, then you can copy and send it to multiple persons<br />
<br />
Follow these steps to broadcast a message to multiple persons.<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjXWCFva_ana683hJ7DyGSrO0-wlJo-VLqi8jOW4qisXo7nXVt5Nq5YJURnEA_9FmELNV5aHSarulRfjRHtjbYx_-TDdUmTDQrW_COTQM2iGjzhMUJ1meWd9FB0wPOBIn-MftbEoy4V_gA/s1600/1.1.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="379" data-original-width="1080" height="112" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjXWCFva_ana683hJ7DyGSrO0-wlJo-VLqi8jOW4qisXo7nXVt5Nq5YJURnEA_9FmELNV5aHSarulRfjRHtjbYx_-TDdUmTDQrW_COTQM2iGjzhMUJ1meWd9FB0wPOBIn-MftbEoy4V_gA/s320/1.1.jpg" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
On the home screen of WhatsApp, select CHATS, then click on 3 dots, which will display a menu.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiVYnrtBuMoaJL16ITWXqT7Onf6G62lgyCoP1oEV4Ee_xXQLL8CHRPn4OdTGTD0goTTwaTfOD53NycXfDPwLO7rmlkN7KVXKjphXXRKUE6PlTaHfcKrA-2i7bBUY9yFSUXsRBRqOgykD6Y/s1600/2.2.jpg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="771" data-original-width="1077" height="229" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiVYnrtBuMoaJL16ITWXqT7Onf6G62lgyCoP1oEV4Ee_xXQLL8CHRPn4OdTGTD0goTTwaTfOD53NycXfDPwLO7rmlkN7KVXKjphXXRKUE6PlTaHfcKrA-2i7bBUY9yFSUXsRBRqOgykD6Y/s320/2.2.jpg" width="320" /></a></div>
<br />
You will find a menu item called <b>New Broadcast, </b>click on it. In the next screen, you will get an option to chose the contacts to which you want to broadcast a message. Select the contacts and click on the tick mark at the bottom to go the compose screen.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhgVHCLOQcr9r8WZ4Yixmgvb0RwPRsFvpPMS1aoMQhmPm0ecS4xwfz8oQUhbjHwrbkufeM7kL79t8h_a38QnE4vvvExzl3gZ5VtRIRFNHyE7gI1UNmnqobA4JLlpKkHhAvT7y8umg25VpE/s1600/3.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1600" data-original-width="800" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhgVHCLOQcr9r8WZ4Yixmgvb0RwPRsFvpPMS1aoMQhmPm0ecS4xwfz8oQUhbjHwrbkufeM7kL79t8h_a38QnE4vvvExzl3gZ5VtRIRFNHyE7gI1UNmnqobA4JLlpKkHhAvT7y8umg25VpE/s640/3.png" width="320" /></a></div>
<br />
<b>2. Change the way ENTER key works</b><br />
<br />
In most cases when you press the ENTER key on your keyboard, it takes you to the new line. But in WhatsApp, you can configure it to act as a SEND button. That means, whenever you press the ENTER key, the message, which is already composed will be sent. You can configure this in the CHAT settings.<br />
<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjXWCFva_ana683hJ7DyGSrO0-wlJo-VLqi8jOW4qisXo7nXVt5Nq5YJURnEA_9FmELNV5aHSarulRfjRHtjbYx_-TDdUmTDQrW_COTQM2iGjzhMUJ1meWd9FB0wPOBIn-MftbEoy4V_gA/s1600/1.1.jpg" style="margin-left: 1em; margin-right: 1em; text-align: center;"><img border="0" data-original-height="379" data-original-width="1080" height="112" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjXWCFva_ana683hJ7DyGSrO0-wlJo-VLqi8jOW4qisXo7nXVt5Nq5YJURnEA_9FmELNV5aHSarulRfjRHtjbYx_-TDdUmTDQrW_COTQM2iGjzhMUJ1meWd9FB0wPOBIn-MftbEoy4V_gA/s320/1.1.jpg" width="320" /></a><br />
<br />
Under the CHATS menu, click on the 3 dots. Then select Chats<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgu9d60U62LseFiskg-WbYtpP_viLnGjuZKnX3BvSrT2dIVe_ZppsicUIBDnxaLjpCtlBW8_ghoLaXKHkQiIsBi_XFhVJnl3eEuzXGTZDnX_ObnnUgsjUHJwoXkhjqaw-DEQseMUTgw8rg/s1600/1.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1600" data-original-width="800" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgu9d60U62LseFiskg-WbYtpP_viLnGjuZKnX3BvSrT2dIVe_ZppsicUIBDnxaLjpCtlBW8_ghoLaXKHkQiIsBi_XFhVJnl3eEuzXGTZDnX_ObnnUgsjUHJwoXkhjqaw-DEQseMUTgw8rg/s640/1.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
In the next screen, under Chat Settings if the box is checked, then ENTER will act as a send key. If you uncheck this box, then ENTER key will take you to the new line while composing a message. </div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<b>3. Check who has read or received your message in a WhatsApp group</b></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
If you are sending a message to a single contact then you can easily see whether the message has delivered or not. You can also see if the recipient has read your message or not, provided, the recipient has enabled the REAS RECEIPTS. </div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
What if you are sending a message to a group, you still have an option to check who has read your message and whom and all the message has delivered. </div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Follow the below steps.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Select the message you have sent to a group by long pressing on it. </div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiw6JkER-BQaVl8i1kYMbVvPFyi2b5QMudHBuCXPVqekmtTsUjy6m7U_xqUUd_aXymqzuyxFueHqOVg9mraIDopmBB50i7dOs5crJzvxI5AshvYxvlO-tHO6d2WL8w4_Vg-PAuNztdPvB0/s1600/3.3.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="416" data-original-width="1076" height="153" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiw6JkER-BQaVl8i1kYMbVvPFyi2b5QMudHBuCXPVqekmtTsUjy6m7U_xqUUd_aXymqzuyxFueHqOVg9mraIDopmBB50i7dOs5crJzvxI5AshvYxvlO-tHO6d2WL8w4_Vg-PAuNztdPvB0/s400/3.3.png" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
Then click on the 3 dots at the top right corner, select Info.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiYXbHT-ZmSEv9PdaW_JlU8GDcjk7AfkaI6-ZKIhkpsJ5NVUpuQ2NwkQYnR8B8NW3kFI6gjPnp9EHY479m_3O2UV3b02Wma4ulFnfa3P-1R4UXGmmlpA6quOjpI-9Qbb8tTmDwmKOZHMog/s1600/3.4.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="421" data-original-width="1076" height="155" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiYXbHT-ZmSEv9PdaW_JlU8GDcjk7AfkaI6-ZKIhkpsJ5NVUpuQ2NwkQYnR8B8NW3kFI6gjPnp9EHY479m_3O2UV3b02Wma4ulFnfa3P-1R4UXGmmlpA6quOjpI-9Qbb8tTmDwmKOZHMog/s400/3.4.png" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
You will see 2 sections, one Read By and second Delivered to. This shows who has read your message and to whom all the message has delivered. </div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhpy12JRUxDCrP2kkuHGI0SHNuEo5AhAdRdywIqNzXkKvvJrUVqmlunf8Xt6H9SHojX0pNXZwHcW_Ba5KD2C6dFoBbieztUi6jaMNydeW6GvRaMavPzeRZLpmXE9fQenLh8hCbCNJEWKmE/s1600/10_1.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1600" data-original-width="801" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhpy12JRUxDCrP2kkuHGI0SHNuEo5AhAdRdywIqNzXkKvvJrUVqmlunf8Xt6H9SHojX0pNXZwHcW_Ba5KD2C6dFoBbieztUi6jaMNydeW6GvRaMavPzeRZLpmXE9fQenLh8hCbCNJEWKmE/s640/10_1.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<b>4. Change the FONT in the WhatsApp - More Options </b></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
This feature was introduced a little while ago, check out more options below </div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEio3bKiZLuq-E-oPWJntXxi6J9I0BuW48JkSSQ5mFDJujdvZq9eBQRHRL2iuZxCgwWNAlj1WFJ-jKDWJuAU2wiuqHAf7nB8H4L5hupefNpi1-z1ScFyon6RFnIJOVoXc3Znx8vrzxjvIZg/s1600/font.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="424" data-original-width="812" height="208" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEio3bKiZLuq-E-oPWJntXxi6J9I0BuW48JkSSQ5mFDJujdvZq9eBQRHRL2iuZxCgwWNAlj1WFJ-jKDWJuAU2wiuqHAf7nB8H4L5hupefNpi1-z1ScFyon6RFnIJOVoXc3Znx8vrzxjvIZg/s400/font.png" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<br />Krishna Shttp://www.blogger.com/profile/17983452641688345252noreply@blogger.com0tag:blogger.com,1999:blog-7570540209369866921.post-14479730225894849552018-12-21T23:25:00.004+05:302020-08-15T15:36:15.574+05:30Parsing a text file and importing values to MS ExcelWe can use Excel VBA script to analyze a text file for a specific purpose. In this example, I have a text file that contains a list of files and their details like name, date created, file size, etc. This is an extraction of DIR command in the dos prompt. I am trying to find a text string "CNT" in each line and if it is present then I will extract the name of the file and store that in the Excel Cell along with the date the is created. <div>
<br /></div>
<div>
I have hardcoded the column here, you can modify it however you want. The first column will keep the name of the file and the second column will keep the file creation date. </div>
<div>
<br /></div>
<div>
<div>
<span face="" style="font-family: "courier new", courier, monospace;"><b>Sub import_text_file_to_excel()</b></span></div>
<div>
<span face="" style="font-family: "courier new", courier, monospace;"><b>Dim fileHandle As Integer</b></span></div>
<div>
<span face="" style="font-family: "courier new", courier, monospace;"><b>Dim fileLine As String</b></span></div>
<div>
<span face="" style="font-family: "courier new", courier, monospace;"><b><br /></b></span></div>
<div>
<span face="" style="font-family: "courier new", courier, monospace;"><b>Dim r As Integer</b></span></div>
<div>
<span face="" style="font-family: "courier new", courier, monospace;"><b>Dim c As Integer</b></span></div>
<div>
<span face="" style="font-family: "courier new", courier, monospace;"><b>r = 1</b></span></div>
<div>
<span face="" style="font-family: "courier new", courier, monospace;"><b>c = 1</b></span></div>
<div>
<span face="" style="font-family: "courier new", courier, monospace;"><b><br /></b></span></div>
<div>
<span face="" style="font-family: "courier new", courier, monospace;"><b>Open "C:\\Users\\krsanjee\\files.txt" For Input As #1</b></span></div>
<div>
<span face="" style="font-family: "courier new", courier, monospace;"><b><br /></b></span></div>
<div>
<span face="" style="font-family: "courier new", courier, monospace;"><b>While Not EOF(1)</b></span></div>
<div>
<span face="" style="font-family: "courier new", courier, monospace;"><b>Line Input #1, myline</b></span></div>
<div>
<br /></div>
<div>
<span face="" style="font-family: "courier new", courier, monospace;"><b><br /></b></span></div>
<div>
<span face="" style="font-family: "courier new", courier, monospace;"><b>On Error Resume Next</b></span></div>
<div>
<span face="" style="font-family: "courier new", courier, monospace;"><b>line_first_part = Left(myline, 40)</b></span></div>
<div>
<span face="" style="font-family: "courier new", courier, monospace;"><b>line_second_part = Right(myline, Len(myline) - 40)</b></span></div>
<div>
<span face="" style="font-family: "courier new", courier, monospace;"><b>If InStr(line_second_part, "CNT") Then</b></span></div>
<div>
<span face="" style="font-family: "courier new", courier, monospace;"><b>arr = Split(line_first_part, " ")</b></span></div>
<div>
<span face="" style="font-family: "courier new", courier, monospace;"><b>ActiveSheet.Cells(r, c).Value = arr(0)</b></span></div>
<div>
<span face="" style="font-family: "courier new", courier, monospace;"><b>ActiveSheet.Cells(r, c + 1).Value = line_second_part</b></span></div>
<div>
<span face="" style="font-family: "courier new", courier, monospace;"><b>r = r + 1</b></span></div>
<div>
<span face="" style="font-family: "courier new", courier, monospace;"><b>End If</b></span></div>
<div>
<span face="" style="font-family: "courier new", courier, monospace;"><b>Wend</b></span></div>
<div>
<span face="" style="font-family: "courier new", courier, monospace;"><b>Close #1</b></span></div>
<div>
<span face="" style="font-family: "courier new", courier, monospace;"><b>End Sub</b></span></div>
</div>
<div>
<br /></div>
Krishna Shttp://www.blogger.com/profile/17983452641688345252noreply@blogger.com0tag:blogger.com,1999:blog-7570540209369866921.post-91648956804711827542018-12-16T09:18:00.002+05:302020-08-15T15:42:31.796+05:30You should know these MS Excel skills if you are using MS excelIf you are using excel for whatever reason then knowing these skills will definitely increase your productivity. These are not advanced but moderate skills. You can manage with excel without knowing these skills but knowing these skills will improve the proficiency in Excel.<br /><br />
<span style="font-size: large;">1. Defining named tables.</span><br />
<br />
You can define a range as a table and give a name to it. Later you can use this name in a formula. You can also use these named tables in references.<br />
<br />
Under formulas menu bar, select Define names. Then give a name to the tables, for example, Sales. Then select the range and select ok. After you create the named table, you can use the table name 'Sales' in the formula to calculate the sum.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhEAp9tUjfaYiDa-ufxKi0zCP21Mt5D9d5JjvATiVoHY1UXYst_oKpBk7igkON3HlrpLTi9GdEJduaoURfyEshH-c89e50lFP4KpCF0aERKBn5yfMxtnB-6P8wAabdmfxgkdNf7V98SYwA/s1600/sales_table.JPG" style="margin-left: 1em; margin-right: 1em;"><b><img border="0" data-original-height="320" data-original-width="230" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhEAp9tUjfaYiDa-ufxKi0zCP21Mt5D9d5JjvATiVoHY1UXYst_oKpBk7igkON3HlrpLTi9GdEJduaoURfyEshH-c89e50lFP4KpCF0aERKBn5yfMxtnB-6P8wAabdmfxgkdNf7V98SYwA/s1600/sales_table.JPG" /></b></a></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-size: large;">2. Autofill </span></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Using autofill you can complete the series based on the current values. For example, if you want to fill multiple of a number, you can enter the first 2 numbers of the series and click on the autofill icon and drag down the column. In the newer version of the office, you will get multiple options. </div>
<div class="separator" style="clear: both; text-align: left;">
The options are</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
copy cells</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
This will repeat the already available items, instead of completing the series with the next numbers. </div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Fill series</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Completes the series with the next values. </div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Fill format only</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
It will ignore the values. Just copies the format of the existing cells. It can alternate the colors based on the selection.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Fill without formatting</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
This will ignore the format but copies the value of the series. </div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgKy57aWoH1pl4EBi7KgB3iyYfROWTGVLDfOMPDTc5H_4KABEovBSXwq7vp-_Qa1ggRMZIFOb0Jm-PUnRwPTCMuM3lSgTMnydsGRYsn29Mwtdr_pklDlG5DpDuVMmGVkwwxEejkJ53Zoek/s1600/fill.JPG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="502" data-original-width="303" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgKy57aWoH1pl4EBi7KgB3iyYfROWTGVLDfOMPDTc5H_4KABEovBSXwq7vp-_Qa1ggRMZIFOb0Jm-PUnRwPTCMuM3lSgTMnydsGRYsn29Mwtdr_pklDlG5DpDuVMmGVkwwxEejkJ53Zoek/s320/fill.JPG" width="193" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-size: large;">3. How to get to the new line within a cell</span></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
As you know Excel is a spreadsheet tool, the basic building block is a cell. Unlike any word processing tool, if you press 'Enter Key' it will take you to the next cell. What if you want to enter a new line within the cell. You can do that by holding 'ALT' key and pressing 'ENTER' key. </div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-size: large;">4. Data bars</span></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
If you want to represent your data visually amazing, you can use data bars. It is very simple and it looks very attractive. The data needs to be numerical values. This is one kind of conditional formatting. You can create a bar horizontal graph just by applying a conditional format. </div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Select the range you want to apply the format. Go to Conditional formatting and select Data Bars. Under the gradient fill, select whatever color you want. </div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEihJKJHktFj-AGCBM0CLujAdf55WA9OWmfJ2ONu8zVw5MAGKnYWRpcJlaZRUaQORbEKFcT9P-AMWL882P2ntNgA7aUS7W062PI5-iOGlh3jLPpYhwPvmkfTWRkrHrrPdsO7nqAIF3fVhYM/s1600/data+bars.JPG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="665" data-original-width="446" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEihJKJHktFj-AGCBM0CLujAdf55WA9OWmfJ2ONu8zVw5MAGKnYWRpcJlaZRUaQORbEKFcT9P-AMWL882P2ntNgA7aUS7W062PI5-iOGlh3jLPpYhwPvmkfTWRkrHrrPdsO7nqAIF3fVhYM/s320/data+bars.JPG" width="214" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
If you want the Data Bars in a separate column, then copy the values in the next column. Apply the conditional format on the new column but not on the old column. Edit the condition formula and check the box 'Show Only Bars'</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjONwmYwn4XlE8UIvSSW2V-Ir1AA9-vKQf4MSjUna3B1lr8DCIvlcV0FgCdI95O0DWiQp_CSf7eefuIatX5JsqnnxLJfXZi5GuyBXgabzrTLJS05mgm6lkKiefHBBwnKl_eyVrLY4NxvI8/s1600/only+bars.JPG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="628" data-original-width="447" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjONwmYwn4XlE8UIvSSW2V-Ir1AA9-vKQf4MSjUna3B1lr8DCIvlcV0FgCdI95O0DWiQp_CSf7eefuIatX5JsqnnxLJfXZi5GuyBXgabzrTLJS05mgm6lkKiefHBBwnKl_eyVrLY4NxvI8/s320/only+bars.JPG" width="227" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-size: large;">5. Paste special </span></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Under the paste special you have options like transposing a the selected. That means you converted a row into column and column to row. </div>
<div class="separator" style="clear: both; text-align: left;">
You can paste only the value if the source is a formula</div>
<div class="separator" style="clear: both; text-align: left;">
You can paste only the formula, not the values. </div>
<div class="separator" style="clear: both; text-align: left;">
You can also link destination cells to the source cell. By this, you can make sure that any change in the source will also get affected by the destination. </div>
<br />
<span style="font-size: large;">6. Text to column</span><br />
<br />
<br />
Suppose you have a bank statement in PDF format. If you bring the statement into excel you will have a lot of options to analyze it. One example would be a BRS, Bank Reconciliation Statement. Import the statement to excel and apply formulas on the imported statement.<br />
<br />
You can split the single line into multiple columns based on a specific delimiter. In the below example list folders and files have been extracted to a text file and it contains columns like date and time, type of the file, and the name of the file and folder. Each of these columns can be split into separate columns in the excel. Once the data is in tabular form, it can be used for various analyses. In this case, you can sort the files based on their creation date or based on the names, etc.<br />
<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg0PyZniX0W999Ew9MiLDd07-u2Nus1zEyUAPZBVLVAHr-8DxEHjsrmClpDZehcHs6WIA14ih8pxmS3_oNTov4YH8QHz41DZJPX3_HYyz87FC_bDtRZ7P2vFK1mmL13j24yd8mxtv5d4Hg/s1600/text-to-column.JPG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="672" data-original-width="1453" height="147" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg0PyZniX0W999Ew9MiLDd07-u2Nus1zEyUAPZBVLVAHr-8DxEHjsrmClpDZehcHs6WIA14ih8pxmS3_oNTov4YH8QHz41DZJPX3_HYyz87FC_bDtRZ7P2vFK1mmL13j24yd8mxtv5d4Hg/s320/text-to-column.JPG" width="320" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
After converting a single line into multi-column </div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgdsfwVMaOfFftNevD6ZhjXzYDB8vUpU7kdb0yLFAFxjFGb1LlQsLhExBFYg1FC3O_Xz1bI0KI01GxE_qOiYJ87UJQRJkbSp7NzG9WnANcI1cNfZPQM8WDH2TykSmunTsqAhy60rgfXS6s/s1600/text-to-column1.JPG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="661" data-original-width="1243" height="170" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgdsfwVMaOfFftNevD6ZhjXzYDB8vUpU7kdb0yLFAFxjFGb1LlQsLhExBFYg1FC3O_Xz1bI0KI01GxE_qOiYJ87UJQRJkbSp7NzG9WnANcI1cNfZPQM8WDH2TykSmunTsqAhy60rgfXS6s/s320/text-to-column1.JPG" width="320" /></a></div>
<br />
<br />
<span style="font-size: large;">7. Auto-sizing a column width or row height. </span><br />
<br />
By clicking on the line between 2 rows or a column, you can auto adjust the width of the column or height of a row based on the content of that column or row.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj8wpGA30g2iL2nP4uKLxqrtCEXE8Rc17Ow1ymX6qp8-Le3EOuYWHlVysG1PhTfhpqKvoojTCBHxEvQKKHsXz312VlA4nMzauSPieiFKg6nX83NoQqxedg-hIt7BrDFDy-zt_u19GiXn_c/s1600/autosize+column+or+row.gif" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="482" data-original-width="600" height="257" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj8wpGA30g2iL2nP4uKLxqrtCEXE8Rc17Ow1ymX6qp8-Le3EOuYWHlVysG1PhTfhpqKvoojTCBHxEvQKKHsXz312VlA4nMzauSPieiFKg6nX83NoQqxedg-hIt7BrDFDy-zt_u19GiXn_c/s320/autosize+column+or+row.gif" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-size: large;">8. Row and column selection shortcuts </span></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-size: large;"><br /></span></div>
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse; width: 928px;">
<colgroup><col style="mso-width-alt: 7452; mso-width-source: userset; width: 157pt;" width="210"></col> <col style="mso-width-alt: 25543; mso-width-source: userset; width: 539pt;" width="718"></col> </colgroup><tbody>
<tr height="53" style="height: 39.6pt; mso-height-source: userset;">
<td class="xl63" height="53" style="height: 39.6pt; width: 157pt;" width="210">Ctrl + Space </td>
<td class="xl64" style="width: 539pt;" width="718">To select the entire column</td>
</tr>
<tr height="53" style="height: 39.6pt; mso-height-source: userset;">
<td class="xl63" height="53" style="border-top: none; height: 39.6pt; width: 157pt;" width="210">Shift + space </td>
<td class="xl64" style="border-top: none;">To select an entire row</td>
</tr>
<tr height="53" style="height: 39.6pt; mso-height-source: userset;">
<td class="xl63" height="53" style="border-top: none; height: 39.6pt; width: 157pt;" width="210">Ctrl + Shift + Space:</td>
<td class="xl64" style="border-top: none;"> The First click will select the filled range surrounded by the current cell. Second, the space press selects the entire worksheet. </td>
</tr>
<tr height="53" style="height: 39.6pt; mso-height-source: userset;">
<td class="xl63" height="53" style="border-top: none; height: 39.6pt; width: 157pt;" width="210">Ctrl + shift + <span class="font5">+</span></td>
<td class="xl64" style="border-top: none;">Insert a new row or a column</td>
</tr>
<tr height="53" style="height: 39.6pt; mso-height-source: userset;">
<td class="xl63" height="53" style="border-top: none; height: 39.6pt; width: 157pt;" width="210">Ctrl + <span class="font6">-</span></td>
<td class="xl64" style="border-top: none;">To delete a row or a column</td>
</tr>
</tbody></table>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-size: large;"> </span></div>
Krishna Shttp://www.blogger.com/profile/17983452641688345252noreply@blogger.com1tag:blogger.com,1999:blog-7570540209369866921.post-31266328800310498812018-11-08T08:36:00.002+05:302018-11-08T08:41:11.843+05:30How to swap 2 excel ranges | swap with multiple cells selected I had created a macro to swap 2 cells values in MS Excel. By just clicking a button a macro could interchange the content of 2 excel cells. There was a limitation on that macro. It could only work only if the selection is just 2 cells. It could interchange the value of just 2 cells but not the ranges. I used to get a lot of request from my viewers to extend this macro for more than 1 cell. Due to time constraint, I could not work on it.<br />
<br />
<h3 class="post-title entry-title" itemprop="name" style="background-color: white; font-family: arial, tahoma, helvetica, freesans, sans-serif; font-size: 22px; font-stretch: normal; font-weight: normal; line-height: normal; margin: 0.75em 0px 0px; position: relative;">
<a href="http://www.krishtalk.com/2013/11/excel-vba-macro-to-swap-2-values-in.html"><span style="color: #0b5394;">Excel VBA macro to swap 2 values in cells</span></a></h3>
<div>
<br /></div>
<br />
Now, I have written another macro which will work on the Excel ranges. This macro is completely different. My original macro was based on area selection. But the new macro is based on the ranges. It is very small and easy to understand, unlike another macro, which is comparatively bigger.<br />
<br />
I will explain each of the lines in the macro.<br />
<br />
Check out my youtube channel for more amazing excel and office automation videos<br />
<br />
<h3>
<span style="color: #6fa8dc; font-size: large;"><a href="https://www.youtube.com/user/krishnayess">My youtube channel</a></span></h3>
<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;"><b style="background-color: white;">1. Sub swap_2_ranges()</b></span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><b style="background-color: white;"><br /></b></span> <span style="font-family: "courier new" , "courier" , monospace;"><b style="background-color: white;">2. string_all_selected_area = (Selection.Address(ReferenceStyle:=xlA1, RowAbsolute:=False, ColumnAbsolute:=False))</b></span><br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span> The first line is the subroutine name. The second line gets the string of value of all the ranges selected. The output will be "I23,H25,G22" format. It says 3 ranges are selected. Each range is separated by a comma. I will use this string later to get the ranges.<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;"><b style="background-color: white;">3. array_selected_area = Split(string_all_selected_area, ",")</b></span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><b style="background-color: #999999;"><br /></b></span> <span style="background-color: white; font-family: inherit;">The above line splits the multiple ranges into one range and stores them in an array</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><b style="background-color: #999999;"><br /></b></span> <span style="font-family: "courier new" , "courier" , monospace;"><b>4. If (UBound(array_selected_area) = 1) Then</b></span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><b>5. area1 = array_selected_area(0)</b></span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><b><span style="background-color: #999999;"></span></b></span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><b>6. area2 = array_selected_area(1)</b></span><br />
<br />
Then, there is an if condition to check if the selection is suitable for our swap operation. It will not work as expected if we select more than 2 ranges. or less than 2 ranges. Our purpose is to swap or interchange the content of 2 different ranges. So the selection should be exactly 2 ranges.<br />
<br />
After this check, the selected ranges are stored in 2 temporary variables.<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;"><b>7. selection_1 = Range(area1)</b></span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><b>8. selection_2 = Range(area2)</b></span><br />
<br />
The variables are converted to Excel ranges. This is very important, as we are not getting selection directly in the Excel range format. Instead, the selection is in text format. The string values should be converted into an Excel range object so that we can apply some Excel VBA functionalities.<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;"><b>9. If Range(area1).Count = Range(area2).Count Then</b></span><br />
<br />
Then comes one more if condition to check if the selected ranges have the same number of cells. It is obvious that we should have the same number of cells on each selection so that we can interchange their values.<br />
<br />
<b><span style="font-family: "courier new" , "courier" , monospace;">10. Sheets(1).Range(area2) = selection_1</span></b><br />
<b><span style="font-family: "courier new" , "courier" , monospace;">11. Sheets(1).Range(area1) = selection_2</span></b><br />
<br />
This is actual swapping code. This is achieved through the temporary variables.<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;"><b>12. End If</b></span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><b>13. End If</b></span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><b><br /></b></span> <span style="font-family: "courier new" , "courier" , monospace;"><b>14. End Sub</b></span><br />
<br />
Finally ending the if conditions and the Subroutine.<br />
<br />
<br />
<br />Krishna Shttp://www.blogger.com/profile/17983452641688345252noreply@blogger.com2tag:blogger.com,1999:blog-7570540209369866921.post-3312359749858510182018-10-26T12:03:00.002+05:302018-10-29T12:12:45.294+05:30Practical Text Manipulation using MID LEFT RIGHT FIND Functions in MS ExcelText manipulation is one of the great features of MS Excel. It has a lot of built-in functions to extract desired values from a text. By using different combinations of these functions we can create a complex formula.<br />
<br />
I have listed a few practical examples which are very useful.<br />
<br />
<span style="font-size: large;">Extracting a Door number from an address text. </span><br />
<span style="font-size: large;"><br /></span>
<br />
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse; width: 311px;">
<colgroup><col style="mso-width-alt: 11064; mso-width-source: userset; width: 233pt;" width="311"></col> </colgroup><tbody>
<tr height="19" style="height: 14.4pt;">
<td class="xl65" height="19" style="height: 14.4pt; width: 233pt;" width="311"><span style="font-family: "courier new" , "courier" , monospace;"><b>8314 W. Galvin Lane West Des Moines, IA 50265</b></span></td>
</tr>
<tr height="19" style="height: 14.4pt;">
<td class="xl65" height="19" style="border-top: none; height: 14.4pt;"><span style="font-family: "courier new" , "courier" , monospace;"><b>68 4th Ave. Neptune, NJ 07753</b></span></td>
</tr>
<tr height="19" style="height: 14.4pt;">
<td class="xl65" height="19" style="border-top: none; height: 14.4pt;"><span style="font-family: "courier new" , "courier" , monospace;"><b>8 Oklahoma St. Ladson, SC 29456</b></span></td>
</tr>
</tbody></table>
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse; width: 294px;"><colgroup><col style="mso-width-alt: 10467; mso-width-source: userset; width: 221pt;" width="294"></col></colgroup><tbody></tbody></table>
<br />
<div>
I will use the LEFT and FIND functions to extract the Door number of an address. </div>
<div>
<br /></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;"><b>=LEFT(B4,FIND(" ",B4))</b></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;"><b><br /></b></span></div>
<div>
<span style="font-family: "arial" , "helvetica" , sans-serif;">Here I am using the delimiter space (" "). The formula first finds a space character which appears after the door number. Then extracts the value whatever if at the left side of the first space. </span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;"><b><br /></b></span></div>
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiNw1w1p_RTZIyi1zMgVdDi3L4tah65XE-Kw4yjNZFhkM-3Wu4Zs4ebSMh-hz-0SvxrTsDZjjbecRu9dBi8Kzf-YQDbOVgHLQJzGZR9FPo6yqtxvQm5lC_F-edhshbh3lTRe9Cv1Qmzk3M/s1600/door_numner_from_adress.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="196" data-original-width="972" height="80" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiNw1w1p_RTZIyi1zMgVdDi3L4tah65XE-Kw4yjNZFhkM-3Wu4Zs4ebSMh-hz-0SvxrTsDZjjbecRu9dBi8Kzf-YQDbOVgHLQJzGZR9FPo6yqtxvQm5lC_F-edhshbh3lTRe9Cv1Qmzk3M/s400/door_numner_from_adress.JPG" width="400" /></a></div>
<div>
<br /></div>
<div>
<span style="font-size: large;">Extracting a ZIP code from an address </span></div>
<div>
<span style="font-size: large;"><br /></span></div>
<div>
Generally, the ZIP code will at the end of the address. Using the <span style="font-family: "courier new" , "courier" , monospace;"><b>RIGHT </b></span>function, we can extract the ZIP code </div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;"><b><br /></b></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;"><b>=RIGHT(B9,5)</b></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;"><b><br /></b></span></div>
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEghKBM_1EL1IaD-fKiioYgSZUt3Jo9YtDlk-hK0i6wtxXH8DhN1Um6zr-3q5IN1vNjtq4vgzzwIF68S_GmEIe1IZ2OHSAOJz42bK9_mwN5dOTFaba3Vy_8ajo8gDXQFKAgz9Ev0zyjJ_pU/s1600/extract+zip+code.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="144" data-original-width="944" height="94" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEghKBM_1EL1IaD-fKiioYgSZUt3Jo9YtDlk-hK0i6wtxXH8DhN1Um6zr-3q5IN1vNjtq4vgzzwIF68S_GmEIe1IZ2OHSAOJz42bK9_mwN5dOTFaba3Vy_8ajo8gDXQFKAgz9Ev0zyjJ_pU/s640/extract+zip+code.JPG" width="640" /></a></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;"><b><br /></b></span> <span style="font-family: inherit; font-size: large;">Extracting a specific number from anywhere in the text </span><br />
<span style="font-family: inherit; font-size: large;"><br /></span> <span style="font-family: inherit;"><span style="font-family: inherit;">Suppose, from the below text I need to extract the dollar amount Which may be present anywhere in the text. For this, I will use multiple functions. For better understanding and for clear visibility, I have separated the functions. If you understand them clearly then you can create your own complex formula. </span></span><br />
<span style="font-family: inherit;"><span style="font-family: inherit;"><br /></span></span> <span style="font-family: "courier new" , "courier" , monospace;"><b>I had <span style="color: #e06666;">$100</span> in my pocket</b></span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><b>bought 2 pens for <span style="color: #e06666;">$1</span> each</b></span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><b>Spent <span style="color: #e06666;">$50</span> for snacks. </b></span><br />
<b style="font-family: "courier new", courier, monospace;">Now I have <span style="color: #e06666;">$48</span> left</b><br />
<div>
<br /></div>
First, identify the starting position of the Dollar symbol.<br />
<br />
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse; width: 155px;"><tbody>
<tr height="19" style="height: 14.4pt;">
<td height="19" style="height: 14.4pt; width: 116pt;" width="155"><span style="font-family: "courier new" , "courier" , monospace;"><b>=FIND("$",B10)</b></span><br />
<br />
Then identify the next </td></tr>
</tbody></table>
Then identify the next space after the Dollar symbol and the digits using the below functions</div>
<div>
<br /></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;"><b>=FIND(" ",B10,FIND("$",B10))</b></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;"><b><br /></b></span></div>
<div>
<span style="font-family: inherit;">Finally, extract the context between the first index, tha is a dollar sign and the space character after the amount. For this, I am using the mid function</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;"><b><br /></b></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;"><b>=MID(B10,C10,D10-C10)</b></span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;"><b><br /></b></span></div>
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhC_-P-XtmHr-vF4mpJ9DYurAWe1UUA74AWbyh7f23V80dQHfLtdJJF0zIDdRB0ROWgW81n71z44yrUt9CUhM9zOl2mw875vx-bULYBGBT4yuQTwFezgIUbGaNZIISJeXjwXtx-9fopaLI/s1600/find_text_from_anywhere.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="215" data-original-width="1090" height="126" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhC_-P-XtmHr-vF4mpJ9DYurAWe1UUA74AWbyh7f23V80dQHfLtdJJF0zIDdRB0ROWgW81n71z44yrUt9CUhM9zOl2mw875vx-bULYBGBT4yuQTwFezgIUbGaNZIISJeXjwXtx-9fopaLI/s640/find_text_from_anywhere.JPG" width="640" /></a></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;"><b><br /></b></span>
<span style="font-family: inherit; font-size: large;"><br /></span>
<span style="font-family: inherit; font-size: large;">Extracting the names from a text </span><br />
<span style="font-family: inherit; font-size: large;"><br /></span>
<span style="font-family: inherit;">The below data has names mixed with some other text. The left part does not have any unwanted text. So we don't have to worry about the left part. But the right portion has other text and we want to remove that. The text marked in red are to be removed. </span><br />
<span style="font-size: large;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;"><b>John Smit <span style="color: #e06666;">(IT Dept)</span></b></span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><b>Krishh J <span style="color: #e06666;">(Sales)</span></b></span><br />
<span style="font-family: "courier new" , "courier" , monospace;"><b>Priore Priore <span style="color: #e06666;">(Software)</span></b></span><br />
<b><span style="font-family: "courier new" , "courier" , monospace;"></span></b><br />
<span style="font-family: "courier new" , "courier" , monospace;"><b>Lucy S <span style="color: #e06666;">(Sales)</span></b></span><br />
<span style="font-family: "courier new" , "courier" , monospace; font-size: large;"><b><span style="color: #e06666;"><br /></span></b></span>
<span style="font-family: inherit;">It is very clear that we need to extract the text up to the first "(" symbol. This is can be done using a </span><span style="font-family: "courier new" , "courier" , monospace;"><b>FIND</b></span><span style="font-family: inherit;"> and </span><span style="font-family: "courier new" , "courier" , monospace;"><b>MID</b></span><span style="font-family: inherit;"> functions. Note the -1 at the end of the formula. This is to exclude the "(" character. Because the FIND function will return the position of the "(". But we don't need it. </span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;"><b>=LEFT(B17,FIND("(",B17)-1)</b></span><br />
<br />
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjAkMhfl1OyFRamJn4As0FxLucDNABFGfY2-7OyGsyoY34RGl1oRpZ87fE8AZPaHuc9sqmAxYNeBw57ksrh5EtAjwSiX_Dk7j3YTSTwuElTnBHlFflpzIrMuu-shLZpqJ4LKL6HKAAWfzU/s1600/extract_names.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="190" data-original-width="797" height="151" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjAkMhfl1OyFRamJn4As0FxLucDNABFGfY2-7OyGsyoY34RGl1oRpZ87fE8AZPaHuc9sqmAxYNeBw57ksrh5EtAjwSiX_Dk7j3YTSTwuElTnBHlFflpzIrMuu-shLZpqJ4LKL6HKAAWfzU/s640/extract_names.JPG" width="640" /></a></div>
<br /></div>
Krishna Shttp://www.blogger.com/profile/17983452641688345252noreply@blogger.com9tag:blogger.com,1999:blog-7570540209369866921.post-71021080919956215602018-10-21T12:46:00.002+05:302020-08-15T15:47:37.769+05:30Practical examples on MS Excel date functions <div class="MsoNormal">
MS Excel has an amazing set of Date and Time functions. By tweaking these functions, we can create a complex and useful formula. I am trying to list down a few practical examples particularly on the Date part, which will be very useful for a moderate to advanced Excel user. </div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-size: large;">How to find the end of the month for a given date</span><br />
<span style="font-size: large;"><br /></span>
Note that <o:p></o:p><span face="" style="font-family: "courier new", courier, monospace; font-weight: bold;">EOMONTH </span><span style="font-family: inherit;">will return a serial number. You have to change the format to date format. </span><br />
<br />
<b><span face="" style="font-family: "courier new", courier, monospace;">=EOMONTH(TODAY(),0)</span></b><br />
<span face="" style="font-family: "courier new", courier, monospace;"><br /></span>
<b><span face="" style="font-family: "courier new", courier, monospace;">=EOMONTH(DATE(2018,10,10),0)</span></b></div>
<div class="MsoNormal">
<br />
<br /></div>
<div class="MsoNormal">
<span style="font-size: large;">How to find the number of days remaining in a month</span><br />
<br />
This is useful for interest calculation on first and last month on any type of loan repayment schedule. <o:p></o:p><br />
<br />
Use <b><span face="" style="font-family: "courier new", courier, monospace;">DAYS</span></b>, <b><span face="" style="font-family: "courier new", courier, monospace;">EOMONTH, </span></b>and <b><span face="" style="font-family: "courier new", courier, monospace;">DATE </span></b>functions.<br />
<br />
<span face="" style="font-family: "courier new", courier, monospace;"><b>=DAYS(EOMONTH(TODAY(),0),DATE(2018,10,10))</b></span><br />
<span face="" style="font-family: "courier new", courier, monospace;"><b><br /></b></span>
<span face="" style="font-family: "courier new", courier, monospace;"><b><br /></b></span></div>
<div class="MsoNormal">
Number of days remaining in this month or current month<br />
<br />
<span face="" style="font-family: "courier new", courier, monospace;"><b>=DAYS(EOMONTH(TODAY(),0),TODAY())</b></span><br />
<br /></div>
<div class="MsoNormal">
<br />
<span style="font-size: large;">Find the number of months between two dates</span><br />
<br />
In other words, find the date difference in terms of months. <b><span face="" style="font-family: "courier new", courier, monospace;">DATEDIF</span></b> is one of the very useful formulas in MS Excel. It will take 2 parameters, the first parameter is the date and the second parameter is the unit. A unit may be Y, M, or D each constitutes Years Months and Days.<br />
<o:p></o:p></div>
<div class="MsoNormal">
<br />
<span face="" style="font-family: "courier new", courier, monospace;"><b>=DATEDIF(TODAY(), DATE(2019,1,10), "M")</b></span><br />
<span face="" style="font-family: "courier new", courier, monospace;"><b>=DATEDIF(TODAY(), DATE(2019,1,10), "Y")</b></span><br />
<span face="" style="font-family: "courier new", courier, monospace;"><b>=DATEDIF(TODAY(), DATE(2019,1,10), "D")</b></span><br />
<br />
If you want to calculate the non-cumulative difference use these options.<br />
<br />
"MD" - Ignores the Year and Month.<br />
"YD" - Ignores the Year<br />
"YM" - Ignores the Year<br />
<br /></div>
<div class="MsoNormal">
<span style="font-size: large;">How to construct a date using Day, Month and Year</span><o:p></o:p><br />
<br />
<span face="" style="font-family: "courier new", courier, monospace;"><b>DATE</b></span> formula returns a date value. Takes 3 parameters - day, month, and year. This formula is useful whenever you want to construct a date from different formats or from a text value. Before using this formula to extract date value from a text, you need to parse and get the individual DAY, MONTH, and YEAR values. I will discuss more on this later<br />
<br />
<span face="" style="font-family: "courier new", courier, monospace;"><b>Syntax: </b></span><br />
<span face="" style="font-family: "courier new", courier, monospace;"><b><br /></b></span>
<span face="" style="font-family: "courier new", courier, monospace;"><b>=DATE(YEAR, MONTH, DAY)</b></span><br />
<span face="" style="font-family: "courier new", courier, monospace;"><b><br /></b></span>
<span face="" style="font-family: "courier new", courier, monospace;"><b>=DATE(2018,10,10)</b></span><br />
<br />
A practical scenario where the DATE function is useful. You have the date but you only want to change the Month. </div>
<div class="MsoNormal">
<br />
<span face="" style="font-family: "courier new", courier, monospace;"><b>=DATE(YEAR(B2), 12, DAY(B2))</b></span><br />
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgTQlTL5-W-sBe4tuKbiOWUs2hoO-LM7Hg74SVWxHETN38ep1TWMYnEqZ4zXDmYuMvsGxhhksDY6AllQncM-q3XIimL08tsFLv2bn9wBiTq88FGjD3V8-1zd2mQDISKeTBxuOTpycP76pI/s1600/datediff.PNG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="217" data-original-width="591" height="146" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgTQlTL5-W-sBe4tuKbiOWUs2hoO-LM7Hg74SVWxHETN38ep1TWMYnEqZ4zXDmYuMvsGxhhksDY6AllQncM-q3XIimL08tsFLv2bn9wBiTq88FGjD3V8-1zd2mQDISKeTBxuOTpycP76pI/s400/datediff.PNG" width="400" /></a></div>
<span face="" style="font-family: "courier new", courier, monospace;"><b><br /></b></span>
<br />
<span style="font-size: large;">How to get the first and last day of a month</span></div>
<div class="MsoNormal">
<br />
First day:<br />
<br />
<span face="" style="font-family: "courier new", courier, monospace;"><b>=DAY(EOMONTH(TODAY(),-1)+1) </b></span><br />
<br />
The above formula finds the end of the month of the previous month and adds one day to the date and then returns the day of the date value.<br />
<br />
Last Day:<br />
<br />
It is very simple to find the last day of a month using <span face="" style="font-family: "courier new", courier, monospace;"><b>EOMONTH</b></span> function<br />
<br />
<span face="" style="font-family: "courier new", courier, monospace;"><b>=DAY(EOMONTH(TODAY(),-1))</b></span><br />
<br />
EOMONTH takes 2 parameters, Date and Month. The month value will be positive for future dates and negative for past dates, 0 for the current month.<br />
<br />
0 - for the current month<br />
-1 - previous month<br />
3 - the 3rd month from now<br />
<br />
<br /></div>
<div class="MsoNormal">
<span style="font-size: large;">How to convert month number to month name<o:p></o:p></span><br />
<br />
<span style="font-family: inherit;">Using the </span><span face="" style="font-family: "courier new", courier, monospace;"><b>TEXT</b></span><span style="font-family: inherit;"> function, the month name can be extracted from a date value. Note the different formatting options and outputs. </span><br />
<br />
<span face="" style="font-family: "courier new", courier, monospace;"><b>=TEXT(TODAY(),"mmmm")</b></span><br />
<span face="" style="font-family: "courier new", courier, monospace;"><b>=TEXT(TODAY(),"mm")</b></span><br />
<span face="" style="font-family: "courier new", courier, monospace;"><b>=TEXT(DATE(2018,1,1),"m")</b></span><br />
<span face="" style="font-family: "courier new", courier, monospace;"><b>=TEXT(DATE(2018,1,1),"mm")</b></span><br />
<span face="" style="font-family: "courier new", courier, monospace;"><b>=TEXT(DATE(2018,1,1),"mmm")</b></span><br />
<span face="" style="font-family: "courier new", courier, monospace;"><b>=TEXT(DATE(2018,1,1),"mmm")</b></span><br />
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMHKS6jXCBPE4QyjUNwDxUuY_9mp_wod3t71evBlmMOJ999JtT6GNV_tQMoqZ2OJQyCQIESz0_EEZ456xJbQrddCy1VpQfKJEQzKjtxTHhPKYGnx8Jnpzo4HFirQ3qVuhQxUmW58Y6bBY/s1600/convert_number_month_value.PNG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="247" data-original-width="687" height="143" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMHKS6jXCBPE4QyjUNwDxUuY_9mp_wod3t71evBlmMOJ999JtT6GNV_tQMoqZ2OJQyCQIESz0_EEZ456xJbQrddCy1VpQfKJEQzKjtxTHhPKYGnx8Jnpzo4HFirQ3qVuhQxUmW58Y6bBY/s400/convert_number_month_value.PNG" width="400" /></a></div>
<div>
<br /></div>
</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-size: large;">How to get the numerical value of a month text</span><br />
<span style="font-size: large;"><br /></span>
<span style="font-family: inherit;">Suppose I have a month value in the string format, say 'October', and I want this in numerical equivalent value 10. There is no readily available excel formula to get the numerical value of this string. We can use the below trick to get the numerical month value</span><br />
<span style="font-size: large;"><br /></span>
In excel there is a shorthand to enter a date. By entering 1oct and then by pressing the enter key, we can get the date 1st October of the current year. Using this technique I will construct a formula to get the numerical value of text month value<br />
<span style="font-size: large;"><br /></span>
<br />
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse; width: 376px;"><tbody>
<tr height="19" style="height: 14.4pt;">
<td class="xl65" height="19" style="height: 14.4pt; width: 282pt;" width="376"><span face="" style="font-family: "courier new", courier, monospace;"><b>=MONTH(1&LEFT(B24,3))</b></span><br />
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse; width: 376px;"><tbody>
<tr height="19" style="height: 14.4pt;">
<td class="xl65" height="19" style="height: 14.4pt; width: 282pt;" width="376"><span face="" style="font-family: "courier new", courier, monospace;"><b>=MONTH(1&B25)</b></span><br />
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse; width: 376px;"><tbody>
<tr height="19" style="height: 14.4pt;">
<td class="xl65" height="19" style="height: 14.4pt; width: 282pt;" width="376"><span face="" style="font-family: "courier new", courier, monospace;"><b>=MONTH(1&MID(B26,6,3))</b></span></td></tr>
</tbody></table>
</td></tr>
</tbody></table>
</td></tr>
</tbody></table>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgTsNMaFEI1QLpUV4w1Rk6VpfN988Q_vC-MNfaUvPhNc61B3UTz1a8_dwamgvvsGfw-kcTHR5MBqccq3KcPaGh0QHQenWLlgrNvtxr0NTk3DN_Ou5i97N0moMltuNgXESnfCf9G2Yzvxwg/s1600/nerical_month_from_string.JPG" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="245" data-original-width="1077" height="90" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgTsNMaFEI1QLpUV4w1Rk6VpfN988Q_vC-MNfaUvPhNc61B3UTz1a8_dwamgvvsGfw-kcTHR5MBqccq3KcPaGh0QHQenWLlgrNvtxr0NTk3DN_Ou5i97N0moMltuNgXESnfCf9G2Yzvxwg/s400/nerical_month_from_string.JPG" width="400" /></a><span style="font-size: large;"><br /></span><br />
<span style="font-size: large;"><br /></span>
<span style="font-size: large;"><br /></span>
<span style="font-size: large;"><br /></span>
<span style="font-size: large;"><br /></span>
<span style="font-size: large;">How to get a Day, Month or a Year of a given date<o:p></o:p></span></div>
<div class="MsoNormal">
<br />
It is very simple to extract a day, month, or a year from the date value. Just use the built-in Excel functions as shown below. These functions alone may not be that useful, but whenever you need to write a complex formula, these functions will be very helpful<br />
<br />
<span face="" style="font-family: "courier new", courier, monospace;"><b>=MONTH(TODAY())</b></span><br />
<span face="" style="font-family: "courier new", courier, monospace;"><b>=YEAR(TODAY())</b></span><br />
<span face="" style="font-family: "courier new", courier, monospace;"><b>=DAY(TODAY())</b></span><br />
<br /></div>
<br />
<div class="MsoNormal">
<span style="font-size: large;">Convert any text into date format</span><br />
<br />
If you have a date in a different format or the date is represented as text then is difficult to use that for any calculation purpose. First, you need to convert them into proper date format. You have to use different methods or techniques in different situations. I am giving a few examples here.<o:p></o:p><br />
<br />
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-2oloBsuO934S8do94J-gzhWOocA8aOqVxhcIm3H_YoCcJPIGJyYd1eZYQkcHCp5nuj8Ut9Bz2odB4tE5nqPI7OqfD03gORPISKUbB4ax0AnRFF-fil2aGx8awPLAtLtgJM4tU4Uiydc/s1600/text_to_date_convertion.PNG" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="171" data-original-width="1026" height="66" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-2oloBsuO934S8do94J-gzhWOocA8aOqVxhcIm3H_YoCcJPIGJyYd1eZYQkcHCp5nuj8Ut9Bz2odB4tE5nqPI7OqfD03gORPISKUbB4ax0AnRFF-fil2aGx8awPLAtLtgJM4tU4Uiydc/s400/text_to_date_convertion.PNG" width="400" /></a></div>
<br />
<br /></div>
Krishna Shttp://www.blogger.com/profile/17983452641688345252noreply@blogger.com1tag:blogger.com,1999:blog-7570540209369866921.post-5526115519548414382018-09-30T23:40:00.001+05:302018-09-30T23:40:08.943+05:30VBA Code to find a record in the MS word mail mergeMS Word mail merge has an option to navigate through the records. The below VBA code will help you to type and search the record within the MS word document area, you do not have to go to the menu bar.<br />
<br />
This code first resets the document to the first record. This is required as the mail merge navigation is only forward. That means the VBA code cannot traverse backward.<br />
<br />
Reads a text from TextBox which is placed within the document. If the keyword entered and the current record is matched, then the Active document is activated with the current record.<br />
<br />
<br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">With ActiveDocument.MailMerge</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;"> .DataSource.ActiveRecord = wdFirstRecord</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">End With</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;"><br /></span>
<span style="color: blue; font-family: Courier New, Courier, monospace;">Dim numRecord As Integer</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">Dim searchText As String</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">searchText = TextBox1.Text</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;"><br /></span>
<span style="color: blue; font-family: Courier New, Courier, monospace;">Set dsMain = ActiveDocument.MailMerge.DataSource</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">If dsMain.FindRecord(FindText:=searchText, Field:="Name") = True Then</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">numRecord = dsMain.ActiveRecord</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">End If</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;">End Sub</span><br />
<span style="color: blue; font-family: Courier New, Courier, monospace;"><br /></span>
<br />
Watch the complete step by step tutorial<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<iframe width="320" height="266" class="YOUTUBE-iframe-video" data-thumbnail-src="https://i.ytimg.com/vi/I5je04j2CMQ/0.jpg" src="https://www.youtube.com/embed/I5je04j2CMQ?feature=player_embedded" frameborder="0" allowfullscreen></iframe></div>
<br />
Download the Excel file and the word document from this link below<br />
<br />
<a href="https://drive.google.com/drive/folders/1DCfpFkn5UpGs-8lPuROJRl5eyMK-wFzy?usp=sharing">Download</a>Krishna Shttp://www.blogger.com/profile/17983452641688345252noreply@blogger.com22tag:blogger.com,1999:blog-7570540209369866921.post-63983261786750406322018-08-12T22:47:00.001+05:302018-08-12T22:47:24.904+05:30VBA Code to copy and paste the Content from excel to wordThere are situations where you may have to copy and paste some content from excel to word. And save the newly created document into disk. The below VBA code or a macro will help you to do that. It is a very simple code, it is being executed from MS Excel. The MS Word object Library should included. This library has to functionality to interact with MS word object. <div>
<br /></div>
<div>
To include MS word object library, go to Tools and then select References. Select the required object library as shown in the picture below.</div>
<div>
<div>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhxoLZ5n491sG7AfB4m47MAONA2cIVAi6Wu5BqV5kyGTg0f9HJWZNex8JZ4MIz1gGlI3sRUbXi8aeZzImQ9N1RIbzbn24hrbGsOrjSMP8FiE4DKXyKObJqWvs7wwyRq88I-PTouQCan9Zk/s1600/adsfadsfasdf.png" imageanchor="1"><img border="0" height="260" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhxoLZ5n491sG7AfB4m47MAONA2cIVAi6Wu5BqV5kyGTg0f9HJWZNex8JZ4MIz1gGlI3sRUbXi8aeZzImQ9N1RIbzbn24hrbGsOrjSMP8FiE4DKXyKObJqWvs7wwyRq88I-PTouQCan9Zk/s320/adsfadsfasdf.png" width="320" /></a></div>
</div>
<div>
<br /></div>
<div>
To copy the VBA code follow the below steps</div>
<div>
<br /></div>
<div>
Open excel and make your table ready. This steps is as per your requirement. </div>
<div>
<br /></div>
<div>
Press Ctrl + F11, this will open a Visual Basic editor. </div>
<div>
<br /></div>
<div>
Click on insert and then module. </div>
<div>
<br /></div>
<div>
Module1 will appear if do not have any module. Paste the below code into this module </div>
<div>
<br /></div>
<div>
Go back to excel and your macro will appear under macros list. Select and click on Run to execute the macro. </div>
<div>
<br /></div>
<div>
Watch this video for more details explanation</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<iframe width="320" height="266" class="YOUTUBE-iframe-video" data-thumbnail-src="https://i.ytimg.com/vi/Yr81fp4iUlE/0.jpg" src="https://www.youtube.com/embed/Yr81fp4iUlE?feature=player_embedded" frameborder="0" allowfullscreen></iframe></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
Sub CreateWord()</div>
<div>
<div>
</div>
<div>
Dim w</div>
<div>
Dim d</div>
<div>
Dim filename As String</div>
<div>
'filename = "D:\\krishna\\youtube vidoes upload\\Marks Card " & Format(Date, "ddmmyy") & "_" & Second(Now())</div>
<div>
filename = "D:\\krishna\\youtube vidoes upload\\Marks Card " & Sheets("marks_sheet").Range("G7:G7") & "_" & Sheets("marks_sheet").Range("G8:G8")</div>
<div>
</div>
<div>
Sheets("marks_sheet").Range("a4:k25").Copy</div>
<div>
</div>
<div>
Set d = CreateObject("Word.Application")</div>
<div>
'If w Is Nothing Then</div>
<div>
Set w = d.Documents.Add</div>
<div>
</div>
<div>
d.Selection.Paste</div>
<div>
d.Visible = True</div>
<div>
'd.Selection.Paste ("new content")</div>
<div>
Application.CutCopyMode = False</div>
<div>
'ActiveDocument.Sections.Paste ("adsf")</div>
<div>
d.ActiveDocument.SaveAs filename</div>
<div>
</div>
<div>
Application.ScreenUpdating = True</div>
<div>
Application.EnableEvents = True</div>
<div>
</div>
<div>
Application.CutCopyMode = False</div>
<div>
Set w = Nothing</div>
<div>
Set d = Nothing</div>
<div>
<br /></div>
<div>
End Sub</div>
</div>
<div>
<br /></div>
Krishna Shttp://www.blogger.com/profile/17983452641688345252noreply@blogger.com5