Showing posts with label Visual. Show all posts
Showing posts with label Visual. Show all posts

Friday, June 28, 2024

How to add a drop down data set in excel

I wanted to add a dropdown data set which can be picked for a cell instead of the manual entry. This can be achieved in excel very easily. Here's how to do it:

Method 1: Using a Comma-Separated List

  1. Select the cell or range of cells where you want the drop-down list to appear.
  2. Go to the Data tab on the ribbon.
  3. In the Data Tools group, click Data Validation.
  4. In the Data Validation dialog box, go to the Settings tab.
  5. In the Allow box, select List.
  6. In the Source box, type the items you want to appear in the drop-down list, separated by commas (e.g., Apple, Banana, Cherry).
  7. Click OK.

Method 2: Using a Range of Cells

  1. Enter your list of items in a column or row in your worksheet. For example, enter the items Apple, Banana, and Cherry in cells A1 to A3.
  2. Select the cell or range of cells where you want the drop-down list to appear.
  3. Go to the Data tab on the ribbon.
  4. In the Data Tools group, click Data Validation.
  5. In the Data Validation dialog box, go to the Settings tab.
  6. In the Allow box, select List.
  7. In the Source box, click the range selector button and select the range of cells that contain your list of items (e.g., =$A$1:$A$3).
  8. Click OK.

Method 3: Using a Named Range

  1. Enter your list of items in a column or row in your worksheet. For example, enter the items Apple, Banana, and Cherry in cells A1 to A3.
  2. Select the range of cells containing the list of items.
  3. Go to the Formulas tab on the ribbon.
  4. In the Defined Names group, click Define Name.
  5. In the New Name dialog box, enter a name for your list (e.g., FruitList), and click OK.
  6. Select the cell or range of cells where you want the drop-down list to appear.
  7. Go to the Data tab on the ribbon.
  8. In the Data Tools group, click Data Validation.
  9. In the Data Validation dialog box, go to the Settings tab.
  10. In the Allow box, select List.
  11. In the Source box, type the name of your list preceded by an equal sign (e.g., =FruitList).
  12. Click OK.

These methods will create a drop-down list in your selected cells, allowing you to choose from predefined options.




Sunday, July 9, 2023

How to Embed PDF in Word file

To embed a PDF file within a Word document, we can follow these steps:

 

  • Open Microsoft Word and create a new or existing document where you want to embed the PDF file.
  • Click on the "Insert" tab in the ribbon at the top of the Word window.
  • In the "Text" section of the ribbon, click on the "Object" button. This will open the "Object" dialog box.
  • In the "Object" dialog box, click on the "Create from File" tab.
  • Click on the "Browse" button and navigate to the location where your PDF file is saved. Select the PDF file and click "OK".
  • In the "Object" dialog box, make sure the checkbox next to "Link to file" is unchecked. If it's checked, the PDF file will be linked to the Word document instead of being embedded.
  • Optionally, you can check the checkbox next to "Display as icon" if you want the PDF file to appear as an icon in your Word document. If you leave it unchecked, the PDF pages will be displayed directly in the Word document.
  • Click on the "OK" button to embed the PDF file into your Word document.

The PDF file should now be embedded within your Word document. You can resize and reposition it as needed. When you save the Word document, the embedded PDF file will be included and accessible whenever you open the document on a device with a compatible PDF viewer.

Saturday, March 18, 2023

Formula to count the blank cells in excel

When I was working on some data calculations in a excel sheet, we had a situation to count the blank number of cells in a range of rows. There are lot of statistical formulas available in excel

Here is the one formula which can be used to achieve this. and We can see how can use this.
We can use COUNTBLANK function, one of the Statistical functions, to count the number of empty cells in a range of cells.

Syntax
COUNTBLANK(Range)
 
The COUNTBLANK function syntax has the following arguments:
 
Range    Required. The range from which you want to count the blank cells.
 
Remark
Cells with formulas that return "" (empty text) are also counted. Cells with zero values are not counted.

Example:




Hope this helps!

Monday, January 23, 2023

Adding Apps And Websites To Microsoft Teams

We can use this Microsoft Office and its tools for its various applications, including communications, collaboration and file storages.  However, many users in the organizations fail to take advantage of one of the platform's most important features - the ability to customize their environment with the apps and websites of the organization. 

When you have a app or web application you want to share or use by your team. Then you can add this to your Microsoft office 365. Login to office by going to  https://www.office.com/ 
You will see the below screen:




Click on the teams from the left menu icon:



You can create your office staff Team:  by clicking on the create teams icon on the right hand side of the panel:



Click on create Team in the below panel:






I click on staff:





Microsoft enables you to add a wide variety of apps or website to your teams environment. To add the app or website, you need to select the team and the channel where you want to access this app. Now you can add apps or website by clicking on "+" icon of the window. Upon doing so, the service displays the Add Tab dialog as below:












You can select the existing app or click on the website, if you have awebsite to be shaed ith the collaborators:








Once you click on the Save button, you can see the website is opened up. And this tab can be accessed by all the collaborators of the Team:



On its own, Teams is a marvelous tool for increasing communication, collaboration, and productivity. However, as is often the case, some of the best benefits of this tool are realized when we integrate it with other applications or websites. Fortunately, as you see, this is a feature that is exceptionally easy to configure and use. Therefore, if you use Teams, consider how you can take advantage of the opportunity to add apps and websites to increase your productivity. Thank you!













Saturday, March 26, 2016

Access an IIS-hosted Site from Another PC (IIS Express + Windows Firewall)

I was working on a Intranet project for one of my client. Earlier It had to be developed with Apache web server. But later they wanted it to be developed with IIS (Internet Information Service) server. So we wanted the site i.e web application to be accessed by other PCs on the local network which's hosted on IIS Server.

Overview

This post shows the steps I used to let other PCs on the local network access a site hosted on IIS / IIS Express running on my development machine. The procedure covers:

  • Opening the server port in Windows Firewall
  • Updating the IIS/IIS Express binding (applicationhost.config)
  • Reserving the URL with netsh

Follow the steps below and replace the example port (6060) with your site’s port.

1) Confirm the site port

First you should know the port on which your site can be accessed. For example it's 6060 in my case. You can get it through WebMatrix or the IIS Manager. It looks like this in WebMatrix:

2) Open the port in Windows Firewall (Inbound rule)

Open the Control Panel -> Windows Firewall -> Advanced Settings and add a new inbound rule for the port your site is using.

Steps (high level):

  • Open Control Panel
  • Open Windows Firewall
  • Click Advanced Settings
  • Select Inbound Rules
  • Select New Rule

Choose the Port option for the rule type:

When asked for protocol/port select TCP and enter your port (6060 in my example):

Allow connections and continue:

Select the profiles to which the rule applies (Public, Private, Domain):

Save the rule with a descriptive name (for example: "IIS Site Port 6060"). That's it for opening a port.

3) Update IIS / IIS Express bindings

If you are using IIS Express (or IIS), you need to make the binding accept requests from other machines. Go to the IIS Express installation folder and open the config directory. Open applicationhost.config for edit and find the <bindings> for your site.

Add or update the binding with a line like this (use your port):

<binding protocol="http" bindingInformation=":*6060:*" />

(Or, as shown in my example — same content formatted in the original post):

<binding protocol="http" bindingInformation="*:6060:*" />
</bindings>

Save the file and stop the site in IIS/WebMatrix before restarting.

4) Reserve the URL with netsh (run as Administrator)

Open an elevated command prompt (Right click -> Run as administrator) and run:

netsh http add urlacl url=http://*:6060/ user=Everyone

You can change the port to match yours. Mine is 6060 hence I mentioned 6060.

Security note

Using user=Everyone is convenient for development but broad. That grants any user the right to register and use the reserved URL namespace. For production or shared machines, prefer reserving the URL to a specific user or group. Example alternatives (replace DOMAIN\User or a local account):

netsh http add urlacl url=http://*:6060/ user="DOMAIN\User"

Always evaluate security implications before opening ports and granting broad URL reservations.

5) Restart IIS and test from another PC

Restart the IIS server (or IIS Express instance) and try to access the application from another PC on the LAN using:

http://<IP Address of the Server>:6060

Replace <IP Address of the Server> and 6060 with your server IP and port.

Quick troubleshooting checklist

  • Can you ping the server IP from the client machine?
  • Is the service listening on the expected port? (Run netstat -ano | findstr 6060 on the server.)
  • Is the inbound firewall rule enabled and targeting the correct port and profiles?
  • Confirm the binding in applicationhost.config includes the correct port and wildcard/host as needed.

That's it. Happy Coding.

Sunday, March 13, 2016

Configure PHP & MySQL on IIS (Windows) — Step‑by‑Step Guide

This blog describes the installation and configuration of PHP, MySQL and IIS (Internet Information Services) server for running web applications on Windows.

Table of contents

  • Overview
  • Prerequisites
  • IIS installation (screenshots)
  • MySQL installation
  • PHP installation and php.ini changes
  • Configuring PHP with IIS (FastCGI)
  • Verifying PHP + MySQL (phpinfo)
  • Installing Joomla (optional)
  • Firewall and external access
  • Troubleshooting

Overview

Internet Information Services (IIS) is a web server from Microsoft. Modern IIS can host PHP and MySQL-backed applications, but unlike WAMP you install and configure each component separately. This guide walks through installing IIS, MySQL, and PHP, and configuring them to work together.

Prerequisites

  • Administrative access on the Windows machine.
  • IIS available on your Windows edition (note: IIS is not supported in Starter, Home Basic, or Home Premium editions). IIS is available in Windows 7 Professional/Ultimate, Windows Server editions, and above.
  • Internet access to download installers and runtimes.

IIS Installation

If IIS is already installed, skip this section. Otherwise enable IIS from Windows Features (or use Server Manager on Windows Server).

STEPS TO CHECK WHETHER IIS IS ALREADY INSTALLED ON YOUR COMPUTER:

  1. Open Control Panel → Programs and Features → Turn Windows features on or off and check Internet Information Services.

To install IIS, follow the Windows feature dialogs. The article screenshots below show the Windows feature dialogs and options during installation.

IIS Check / Step 1

IIS Install Step 2

IIS Install Step 3

IIS Install Step 4

IIS Install Step 5

IIS Install Step 6

IIS Install Step 7

IIS Install Step 8

Note: You can also use the Windows PPI/launcher to install packages: https://sites.google.com/site/karjolblogs/downloads/wpilauncher.zip?attredirects=0&d=1

MySQL Installation

There are a couple of options for installing MySQL on Windows: extract the zip and run manually, or use the MySQL Installer to install as a Windows service. Downloads are available at: http://dev.mysql.com/downloads/mysql/

I chose the MySQL Installer 5.6 for Windows in my setup and used the Custom setup type to install the server, MySQL Workbench and the Notifier. If you install Workbench, you may need the Microsoft Visual C++ 2013 Runtime:

MySQL Installer

After installation, verify you can connect using MySQL Workbench or the MySQL Command Line Prompt.

MySQL Workbench / verify login

You can create the required database using the commands mentioned in the MySQL User Manual.

PHP Installation

Download the Windows PHP build from http://windows.php.net/download. For IIS you should use the non-thread safe (NTS) build and extract it to a folder of your choice (for example C:\php). In this article I used PHP 7.0.

After unzipping, copy and edit the php.ini-production file (rename to php.ini) and place it in C:\Windows (or keep it in your PHP directory and point IIS to it using environment variables — but the simplest approach is to copy to C:\Windows as shown below).

Open php.ini in a text editor and make these minimum changes (uncomment and set values):

cgi.force_redirect = 0
fastcgi.impersonate = 1
; set extension_dir to the ext folder in your PHP install:
extension_dir = "C:\php\ext"
; set timezone
date.timezone = "Your/Timezone"

Set date.timezone to your server timezone (example: America/New_York). See the PHP timezone list at: http://php.net/manual/en/timezones.php

PHP extensions for MySQL

In order for PHP to work with MySQL, enable the appropriate Windows extensions. Note: if you are using PHP 7 or newer, the old php_mysql.dll extension was removed. Use mysqli or PDO instead. Example lines to enable:

extension=php_curl.dll
extension=php_mbstring.dll
extension=php_mysqli.dll
extension=php_pdo_mysql.dll  ; optional, for PDO

The php_curl.dll extension is often required by applications (for example WordPress) to fetch remote updates.

You can also use the wpilauncher.exe mentioned above to help automate installation.

Configuring PHP and MySQL with IIS (FastCGI)

After setting up PHP, configure IIS to run PHP via FastCGI.

  1. Ensure the CGI feature is installed for IIS (Server Manager → Add Roles/Features → Web Server (IIS) → Application Development → CGI).
  2. In IIS Manager, add a Handler Mapping for PHP with these values:
  • Request path: *.php
  • Module: FastCgiModule
  • Executable: full path to php-cgi.exe (for example C:\php\php-cgi.exe)
  • Name: PHP
  1. Restart IIS after the handler mapping change.

Verifying PHP + MySQL

Create a file phpinfo.php in your site root (wwwroot) with the following content:

<?php phpinfo(); ?>

Browse to http://localhost/phpinfo.php. The PHP settings page should load; search the page for MySQL / mysqli / PDO sections to confirm that PHP has the MySQL extensions loaded.

phpinfo MySQL section

Installing Joomla (optional)

The author includes a packaged Joomla.exe for quick setup. If you use it, the steps below summarize how I proceeded:

Joomla in WebMatrix

Find the port assigned to the site in WebMatrix (note it for external access).

Find port in WebMatrix

Click on the site in WebMatrix to open it (screenshot):

Open site

After updating configuration.php with the DB credentials and restarting IIS, the Joomla site should run.

Firewall and external access

To let other hosts access your site you must allow incoming connections on the port used by the site (commonly TCP 80 for HTTP). Example steps for Windows Firewall with Advanced Security:

  1. Open Windows Firewall with Advanced Security.
  2. Inbound Rules → Action → New Rule
  3. Choose Predefined and select: World Wide Web Services (HTTP)
  4. Allow the connection and finish the wizard.

You can then access the site with:

http://<server-ip>:<port>

Troubleshooting

  • If phpinfo() does not show MySQL sections, confirm the extension_dir path and that the listed extensions are present in C:\php\ext.
  • If you get 500 errors, check the Windows Event Viewer (Application) and the IIS log files for FastCGI/PHP errors.
  • Permission issues: ensure the IIS user (e.g. IIS_IUSRS) has read access to the PHP folder and to your site files.
  • If using PHP 7+, do not enable php_mysql.dll — use php_mysqli.dll and php_pdo_mysql.dll.

Notes and further reading

That's it — Happy Coding.

Sunday, February 7, 2016

VBA Macro: Copy Data from One Workbook to Another (without manual open)

One of my friend was trying to copy the data from one work book to other workbook. We can automate this using the VB macro. Here is the solution for it.

Overview

This post shows a simple VBA macro that opens two example workbooks, copies a range from each, and pastes the data into a report workbook. The example keeps the author's original code and example files so you can reproduce the result.

Prerequisites

  • Excel with VBA support (desktop Excel). The report workbook must be saved as a macro-enabled workbook (Report.xlsm) to store the macro.
  • The macro uses explicit file paths in the example; update the paths to match your system.

Example files and screenshots

Original workbook (test1.xlsx) contents:

test1 screenshot

You can download it from here: test1.xlsx

Second workbook (test2.xlsx) contents:

test2 screenshot

You can download it from here: test2.xlsx

Report workbook (expected result):

report screenshot

You can download the report (contains the macro) from here: report.xlsm

VBA macro (copy and paste into a module named test)

Use the macro below exactly as provided by the author. Update the file paths if needed.

Sub test()

Application.ScreenUpdating = False
Workbooks.Open Filename:="F:\Projects\freelancer\vba\test1.xlsx"
'For I = 1 To 50
 Workbooks("test1.xlsx").Sheets("Sheet1").Range("B2:B15").Copy
 Workbooks("Report.xlsm").Sheets("Sheet1").Range("B2").PasteSpecial
    'Workbooks("Report.xlsm").ActiveSheet.Range("B2:B15").Value = Workbooks("test1.xlsx").Sheets("Sheet1").Range("B2:B15")
'Next I
Windows("test1.xlsx").Close

 Workbooks.Open Filename:="F:\Projects\freelancer\vba\test2.xlsx"
 Workbooks("test2.xlsx").Sheets("Sheet1").Range("B2:B15").Copy
 Workbooks("Report.xlsm").Sheets("Sheet1").Range("C2").PasteSpecial
Windows("test2.xlsx").Close

Application.ScreenUpdating = True
 Workbooks("Report.xlsm").Save
End Sub

How it works (brief)

  • The macro turns off ScreenUpdating to speed execution.
  • It opens test1.xlsx, copies B2:B15 from Sheet1, and pastes into B2 of Report.xlsm.
  • It closes test1.xlsx, then opens test2.xlsx and copies B2:B15 into C2 of Report.xlsm.
  • Finally it re-enables ScreenUpdating and saves Report.xlsm.

The author commented an alternative approach that assigns values directly instead of Copy/PasteSpecial:

'Workbooks("Report.xlsm").ActiveSheet.Range("B2:B15").Value = Workbooks("test1.xlsx").Sheets("Sheet1").Range("B2:B15")

That approach avoids the clipboard and is generally faster for value-only transfers.

Notes, fixes, and recommendations

  • Typos in the original text: "VB Micro" should read "VBA macro"; "xlx" appears where "xlsx" or "xls" is intended. Fix these for clarity.
  • Paths: replace the example paths (F:\Projects...) with your actual file locations. Use double backslashes in VBA string literals or single forward slashes.
  • Use explicit workbook variables for safer code. For example:
Dim wbSrc As Workbook, wbReport As Workbook
Set wbReport = Workbooks("Report.xlsm")
Set wbSrc = Workbooks.Open("C:\path\test1.xlsx")
wbSrc.Sheets("Sheet1").Range("B2:B15").Copy wbReport.Sheets("Sheet1").Range("B2")
wbSrc.Close False
  • Avoid Windows("name").Close when workbooks may have different window captions; prefer wb.Close.
  • Consider using PasteSpecial arguments, e.g., PasteSpecial xlPasteValues if you only need values.
  • Add basic error handling around file opens and saves to catch missing files or permission issues.

Troubleshooting

  • "Subscript out of range": usually the workbook or sheet name is wrong. Confirm filenames and sheet names match exactly.
  • "Path not found" or "File not found": update the Filename path to the correct location.
  • Macro not running: ensure macros are enabled and Report.xlsm is macro-enabled.

That's it. Happy coding.