Étiquette : MS_Excel _Internet

What is ActiveX Technology with Excel VBA
As you know, each of the MS Office applications is a very flexible product, providing the user with extensive capabilities. By using these capabilities, it is possible to create more efficient and technologically advanced applications. ActiveX technology enables interaction between different MS Office applications, as well as with any programs that support this technology, for example, Visual Basic, through the Component Object Model (COM). This technology was previously called OLE (Object Linking and Embedding). Currently, OLE is part of ActiveX technology.

ActiveX technology is designed for simple and efficient interaction between different products. For example, MS Word or MS Excel can provide their objects to each other. This makes it possible to create documents consisting of elements developed in different applications. ActiveX provides the developer with two powerful tools for building applications:
- OLE (Object Linking and Embedding) — linking and embedding objects from different applications, which allows editing objects created by other applications directly within a document.
- Automation — a technology that enables programmatic control of both embedded objects and the objects of other applications. This significantly reduces development time, since there is no need to reimplement features that are not built-in but are available in other specialized applications.
NOTE
ActiveX technology assumes that the integrated applications, whose objects are used in a document, are installed and registered in the system registry. As a rule, application registration takes place automatically during installation.
octobre 13, 2025
How to Transfer Data from Client to Server with Excel VBA
To transfer data from the client to the server, forms are often used, which group several controls together.

In the example below, the form contains two fields — Name and E-Mail — and two buttons — Submit and Reset.
- Clicking the Submit button initiates the execution of the ASP file specified in the action attribute of the <form> tag (in this case, Answer.asp).
- The form property of the server-side Request object passes the values of the input elements within the form to this file.
- The identification of tags is performed using the values of their name attributes.
After filling out the form, the user clicks Submit, which sends the data to the server and processes it with the program Answer.asp, confirming that the data has been received.

Clicking the Reset button clears the form values, restoring their default values.

Steps to Run This Example
1. In Notepad, type the following codeand save the file as ask.htm.
Transferring data from client to server. File ask.htm
```
<html>
<body>
<h1>Enter Your Data</h1>
<form id="frmName" method="post" action="http://localhost/answer.asp">
Name: <input type="text" name="txtName">
<br>
E-Mail: <input type="text" name="txtEMail">
<br>
<input type="submit" value="Submit">
<input type="reset" value="Reset">
</form>
</body>
</html>
```
- Then type the following code in Notepad and save the file as answer.asp.
Transferring data from client to server. File answer.asp
```
<%@ Language=VBScript %>
<html>
<body>
<%
Response.Write "<H2>Received Data</H2>"
Response.Write "Name: " & Request.Form("txtName")
Response.Write "<BR>"
Response.Write "E-Mail: " & Request.Form("txtEMail")
%>
</body>
</html>
```
- Place the created files in the directory C:\Inetpub\wwwroot on your computer.
- Launch the file iisstart.htm, located in the same directory, and in the browser’s address bar enter:
http://localhost/ask.htm
- Click the Submit button in the browser window and check the ASP request execution. Also, test the Reset button.
Our Results
- Forms are the main way to transfer data from the client to the server.
- The ASP Request.Form collection is used to retrieve form data.
- The Response.Write method confirms or processes the submitted data.
octobre 13, 2025
How to Create Server-Side Web Scripts with Excel VBA
Microsoft Active Server Pages (ASP) scripts, written in VBScript, allow you to create programs that are executed on the server side. From all the output generated, ASP scripts send only text and HTML tags to the client’s computer, where they are displayed in the browser window.

Using the ADO (ActiveX Data Objects) model, ASP scripts can access databases stored on the server, and using FSO (File System Object) they can work with text files stored there as well. This makes it easy to create various web applications such as virtual catalogs. In simplified terms, ADO can be described as a data access model optimized for web projects.

To use ASP, you need to install Microsoft’s Internet Information Services (IIS) web server to work with operating systems such as Windows XP, Windows Vista, or Windows 7.

Windows provides the ability to run server applications on the client’s computer. To do this, the server application file must be placed in the directory C:\Inetpub\wwwroot on the client’s computer. This is the root directory of the server, created by default during installation.

Clients connected to your computer via the HTTP protocol automatically access this directory. The directory contains the file iisstart.htm, which opens in the client’s browser when connecting to the server without specifying a file name.

To open an ASP file from your computer in a browser, you must enter:

http://127.0.0.1/filename.asp

or

http://localhost/filename.asp

where http://127.0.0.1 or http://localhost is a reserved IP address for connecting to a server running on the same computer from which the request originates.

Example 1: Greeting Based on Time of Day

As an example, let’s create the following code, which displays different greetings in the browser depending on the time the document is loaded.

To let the computer know which part of the ASP file should be executed on the server, you must use the <% statements %> tag, where statements are the server-side commands. To determine the document load time, the Time() function is used.

Steps:
- Verify that IIS is installed on your computer. If not, install it. For example, on Windows 7: go to Control Panel → Programs and Features → Turn Windows features on or off, and enable IIS Services.
- In Notepad, type the code and save it with the .asp extension, for example, 6-Hello.asp.
- Place the file 6-Hello.asp in C:\Inetpub\wwwroot.
- Launch iisstart.htm from C:\Inetpub\wwwroot, then in the browser’s address bar enter:
- http://127.0.0.1/6-hello.asp
- The browser window will display a greeting depending on the time of day).
- Right-click in the browser window, choose View HTML Source, and you will see the code that the client receives from the server.
Greeting. File 6-Hello.asp
```
<html>
<body>
<h1>Greeting</h1>
<% If Time>= #04:00:00# And Time < #12:00:00# Then %>
    <h2>Good Morning!!!</h2>
<% ElseIf Time>= #12:00:00# And Time < #18:00:00# Then %>
    <h2>Good Afternoon!!!</h2>
<% ElseIf Time>= #18:00:00# And Time < #23:00:00# Then %>
    <h2>Good Evening!!!</h2>
<% Else %>
    <h2>Good Night!!!</h2>
<% End If %>
</body>
</html>
```
Greeting. Code returned to the client
```
<html>
<body>
<h1>Greeting</h1>
<h2>Good Afternoon!!!</h2>
</body>
</html>
```
Example 2: Greeting Based on the Day of the Week

The following code displays greetings depending on the day of the week when the document is loaded.

The <% statements %> tag again marks the server-side code. The equal sign in <%=Variable%> is shorthand for the Response.Write method, which sends information from the server to the client.

Your first server-side script. File day1.asp
```
<%@ Language=VBScript %>
<html>
<h1>Today <%= Now %></h1>
<% If Weekday(Now) = vbFriday Then %>
    <h2>Tomorrow the weekend begins!!!!</h2>
<% ElseIf Weekday(Now) = vbSaturday Or Weekday(Now) = vbSunday Then %>
    <h2>Weekend!!!!</h2>
<% Else %>
    <h2>Still have to work</h2>
<% End If %>
</html>
```
Example 3: Using Response.Write Explicitly

Here is the same script, rewritten using the Response object and its Write method.

Your first server-side script. File day2.asp
```
<%@ Language=VBScript %>
<html>
<h1>Today <% Response.Write Now %></h1>
<%
If Weekday(Now) = vbFriday Then
    Response.Write "<h2>Tomorrow the weekend begins!!!!</h2>"
ElseIf Weekday(Now) = vbSaturday Or Weekday(Now) = vbSunday Then
    Response.Write "<h2>Weekend!!!!</h2>"
Else
    Response.Write "<h2>Still have to work</h2>"
End If
%>
</html>
```
octobre 13, 2025
How to Create Client-Side Web Scripts with Excel VBA
Using VBScript, you can write code directly inside HTML files, and this code will be executed when such a file is loaded in a browser — i.e., on the client side. This code must be placed inside the paired <script> tag, whose language attribute specifies the scripting language.

As an example, The code shows that displays a greeting window when you click on the text “Hello, World!”.
```
<html>
<head>
<script language="VBScript">
Sub hello()
    Msgbox "Hello, World, again!"
End Sub
</script>
</head>
<body>
    <h1 onclick="hello">Hello, World!</h1>
</body>
</html>
```
To create and run this example, do the following:
- Open the standard Windows application Notepad.
- In the window that opens, type the suggested code.
- Save the code in a file with the .html extension, for example, Hello-world.html in the root directory of drive D:.
- Open the file you created, either by double-clicking it with the left mouse button or pressing Enter.
- In the browser window, the greeting you wrote (“Hello, World!”) will be displayed. If your browser blocks the execution of scripts or ActiveX elements, click the message that appears in the browser window and allow the blocked content to run.
- Click the “Hello, World!” text in the browser window and verify that the script executes.
octobre 13, 2025
Web Query and Retrieving Data from a Web Page with Excel VBA
Web pages often contain information needed for analysis in Microsoft Office Excel. For example, in MS Excel you can analyze stock quotes using data that comes directly from a web page, or a sales table from an organization’s private webpage.

If necessary, you can extract refreshable data (in which case it can be updated directly in MS Excel to reflect the latest changes on the web page) or static data from a web page to store on a worksheet.

Using a Web Query lets you obtain data from a web page—such as a single table, several tables, or all text—and analyze it with Excel’s tools.

To create a web query, do the following:
- Place the file Data.htm on the D: drive of your computer.
- Open a new workbook, go to the Data tab on the ribbon, and in the Get External Data group choose From Web.
- In the New Web Query window, in the address field enter a link to the file you placed: …/Data.htm.
NOTE
The Address field is for entering the URL of the required web page. For example, if Internet Information Services (IIS) is installed on your computer (described later), its root directory is C:\Inetpub\wwwroot. You can place in this directory, for instance, a file with the .asp extension (a web page that also contains data). In that case, you should enter the following link in the Address field:
http://localhost/filename.asp.
You must not specify a link in the form C:\Inetpub\wwwroot\filename.asp in this window.
- Using the arrow buttons, select the desired table on the web page.
- Click Import.
- In the Import Data window, set Where do you want to put the data? to Existing worksheet, and in the corresponding field enter the cell reference (for example, A1) where the upper-left corner of the imported table will be placed.
- Click Properties to specify query parameters.
- In the External Data Range Properties window, the Name field sets the query name. The Query definition group is used to save the password and the query definition. The Refresh control group specifies how the imported data will be refreshed. The Formatting and layout group sets formatting parameters. The If the number of rows in the range changes group specifies how to handle changes in the size of the range. The Fill down formulas in columns adjacent to data checkbox determines whether explanatory formulas should be filled along with the data. Leave all default settings in the External Data Range Properties window and click OK.
- The External Data Range Properties window will close. Click OK in the Import Data window.
- The data from the selected table on the web page will be imported to the worksheet.
octobre 13, 2025
Navigating Through Hyperlinks from a List with Excel VBA
Hyperlinks can also be used when organizing data on a worksheet. For example, you can use a list directly linked to hyperlinks in worksheet cells. As an example, consider preparing a statement called Examples of Laboratory Work), which uses a ListBox control that allows you to navigate to the required example file).

To prepare this example, do the following:
- Prepare the necessary example files with the .xlsm extension that will be used for hyperlink navigation, and place them in the folder.
- Open a new workbook and prepare a statement on the worksheet according. In the range C4:C8, insert hyperlinks to the corresponding prepared example files. Save the prepared file in the folder mentioned above under the name.
- In each of the files prepared in step 1, create a backward hyperlink to the file using the Add method to insert the hyperlink.
Returning to the original file via hyperlink. ThisWorkbook module
```
Private Sub Workbook_Open()
    Dim i As Integer
    For i = 1 To 3
        With Worksheets(i)
            .Hyperlinks.Add Anchor:=.Range("A1"), _
            Address:="D:\3-Example of using a list and hyperlinks\" & _
            "1-Examples of Laboratory Work.xlsm", _
            ScreenTip:="Return to the initial file", TextToDisplay:="BACK"
        End With
    Next
End Sub
```
- Place a ListBox control over the range C4:C8 by using the Insert button located on the Developer tab in the Controls group.
- Click the ListBox control and, in the Sheet1 module, enter the code from, which uses the Follow method to navigate via the specified hyperlink.
Navigating via hyperlink from a list. Sheet1 module
```
Private Sub ListBox1_Click()
    Hyperlinks(ListBox1.ListIndex + 1).Follow
End Sub
```
- In the ThisWorkbook module, enter the code , which fills the list and creates the corresponding Hyperlink objects.
Navigating via hyperlink from a list. ThisWorkbook module
```
Private Sub Workbook_Open()
    Worksheets(1).ListBox1.ColumnCount = 2
    Worksheets(1).ListBox1.ColumnWidths = "100;0"
    Worksheets(1).ListBox1.Clear  
    Dim lst(4, 1) As String
    lst(0, 0) = "Statements" : lst(0, 1) = "2-Statements.xlsm"
    lst(1, 0) = "Charts, Surfaces, Diagrams"
    lst(1, 1) = "3-Charts_Surfaces_Diagrams.xlsm"
    lst(2, 0) = "Arrays" : lst(2, 1) = "4-Arrays.xlsm"
    lst(3, 0) = "Text Functions"
    lst(3, 1) = "5-Text Functions.xlsm"
    lst(4, 0) = "Lists" : lst(4, 1) = "6-Lists.xlsm"   
    Worksheets(1).ListBox1.List = lst   
    Dim i As Integer
    Dim r As Hyperlink  
    For Each r In Worksheets(1).Hyperlinks
        r.Delete
    Next   
    For i = 0 To 4
        Worksheets(1).Hyperlinks.Add Anchor:=Worksheets(1).Cells(i + 4, 3), _
        Address:=lst(i, 1), TextToDisplay:=lst(i, 0)
    Next
End Sub
```
- Place the prepared folder with files on your computer’s D: drive and test that your hyperlinks work correctly.
octobre 13, 2025

The Hyperlink Object and the Hyperlinks Collection with Excel

Hyperlinks in VBA are represented by the Hyperlink object, which is an element of the Hyperlinks collection consisting of all the hyperlinks of a worksheet or range.

This collection has two methods: Add (to add a new hyperlink to the collection) and Delete (to delete all hyperlinks from the collection).

Add(Anchor, Address, SubAddress, ScreenTip, TextToDisplay)

Anchor — specifies the location of the hyperlink. It can be either a Range object or a Shape object.
Address — the hyperlink address.
SubAddress — the area in the document (for example, a cell range or a bookmark) to which the hyperlink points.
ScreenTip — the text of the tooltip.
TextToDisplay — the text displayed as the hyperlink.

In Table 1, the properties of the Hyperlink object are listed, and in Table 2, its methods are given.

Table 1. Properties of the Hyperlink Object

Property	Description
Address	The address of the hyperlink
EmailSubject	The text string passed as the email subject
Range	Returns the Range object to which the hyperlink is assigned
ScreenTip	The text of the tooltip
Shape	Returns the Shape object to which the hyperlink is assigned
SubAddress	The area in the document to which the hyperlink points
TextToDisplay	The text displayed as the hyperlink
Type	Returns the object type to which the hyperlink is attached. Possible values: msoHyperlinkInlineShape, msoHyperlinkRange, msoHyperlinkShape

Table 2. Methods of the Hyperlink Object

Method	Description
AddToFavorites	Add to the list of favorite links
CreateNewDocument	Create a new document associated with the specified hyperlink
Delete	Delete the hyperlink
Follow	Follow the specified hyperlink

Example: Programmatically Creating a Hyperlink

First, prepare two files (save them in a macro-enabled format):
- 1-Staff Positions and Tariff Rates.xlsm
- 2-Salary Statement.xlsm.

In the ThisWorkbook module of the file , enter the following code.

Creating a new hyperlink. ThisWorkbook module

Private Sub Workbook_Open()
    With Worksheets(1)
        .Hyperlinks.Add Anchor:=.Range("A1"), _
        Address:="D:\2-Example of using a hyperlink\" & _
        "2-Salary Statement.xlsm", _
        ScreenTip:="NPO Alpha", TextToDisplay:="Employee Salary Calculation"
    End With
End Sub

In the ThisWorkbook module of the file, enter the following code .

Creating a new hyperlink. ThisWorkbook module

Private Sub Workbook_Open()
    With Worksheets(1)
        .Hyperlinks.Add Anchor:=.Range("D1"), _
        Address:="D:\2-Example of using a hyperlink\" & _
        "1-Staff Positions and Tariff Rates.xlsm", _
        ScreenTip:="Staff Salaries NPO Alpha", _
        TextToDisplay:="GO BACK"
    End With
End Sub

Make sure that when opening the file appears in cell A1

which leads to the file.

octobre 13, 2025

What is a Conditional Hyperlink
To make a hyperlink enabled or disabled depending on a certain condition, use it together with the IF() function. For example:
```
=IF(C1="May",HYPERLINK("[D:\Sales.xlsx]May!A1","Sales"),""
```
Automatic changes of a hyperlink depending on the value in a cell can easily be implemented using the IF() or CHOOSE() function. For example, do the following:
- Create and save on drive D: the following Microsoft Excel workbooks: May.xlsx, June.xlsx, July.xlsx.
- Open a new workbook and enter the following formula in cell A1:
```
=CHOOSE(1, HYPERLINK("D:\May.xlsx","May"), HYPERLINK("D:\June.xlsx","June"), HYPERLINK("D:\July.xlsx","July"))
```
- Make sure that cell A1 displays the hyperlink May. When you click it, the corresponding file located on drive D: will open.
- By changing the index (the first argument) in the CHOOSE() function to 2 or 3, check the transition to the hyperlinks for the files June.xlsx or July.xlsx, respectively.
octobre 13, 2025
How to Create a Hyperlink with a Worksheet Formula with Excel VBA
A hyperlink can also be created using the worksheet function HYPERLINK():
```
HYPERLINK(link_location, [friendly_name])
```
- link_location — the path and file name of the document to be opened. The address can refer to a place within a document, for example, a specific cell or a named range in an MS Excel workbook, or a bookmark in an MS Word document. The path may be a file path on a hard disk, a UNC server address (in MS Excel for Windows), or a URL address on the Internet or intranet.
- friendly_name — the text to display for the link or a numeric value shown in the cell. The name appears in blue and underlined. If this argument is omitted, the cell displays the link_location as the link text.
NOTE
The arguments of the HYPERLINK() function can be either text expressions or cell references.

For example, if in the workbook Department.xlsx you need to go from cell B3 to cell A20 of the worksheet Report (cell B3 may itself be on the Report sheet, so movement is possible both within one sheet and between sheets), then you would enter the following hyperlink in cell B3:
```
=HYPERLINK("[Department.xlsx]Report!A20","Go to report")
```
or the formula:
```
=HYPERLINK(A1,B1)
```
where cell A1 contains [Department.xlsx]Report!A20, and cell B1 contains Go to report.

The first of the following two hyperlinks opens the workbook Sales.xlsx located in the root directory of drive D:, while the second opens the workbook and activates the worksheet May:
```
=HYPERLINK("D:\Sales.xlsx","Sales")
=HYPERLINK("[D:\Sales.xlsx]May!A1","Sales")
```
A hyperlink to the Russian-language Microsoft webpage can be created as follows:
```
=HYPERLINK("http://www.microsoft.com/ru/ru/","Microsoft")
```
octobre 13, 2025
How to Add Hyperlinks to MS Office Documents
When working with a Microsoft Excel workbook, you often need to use documents prepared in various MS Office applications. For example, by adding hyperlinks to worksheets, you can move between these documents with a single mouse click.

To create a hyperlink to another MS Office document (including the current workbook, but to a different cell range or another worksheet) in an MS Excel worksheet:
- Select the range or shape to which the hyperlink will be attached.
- Go to the Insert tab on the ribbon and, in the Links group, click the Hyperlink button.
- In the Insert Hyperlink window, set the necessary options. The switches File, Web Page, Place in This Document, Create New Document, Email Address in the Link to group specify the document that the hyperlink will point to. The Text to Display field specifies the hyperlink text. In the Look in field, choose the folder that contains the desired document. From the lists Current Folder, Browsed Pages, and Recent Files, you can select the required file. In the Address drop-down list, you can specify the document’s URL. The ScreenTip button allows you to add text to the hyperlink’s tooltip, and the Bookmark button lets you choose a specific location to which the hyperlink will jump.
- After setting the required parameters, click OK.
You can edit a hyperlink by right-clicking on it. A context menu will appear with three commands for working with hyperlinks:
- Edit Hyperlink — opens the Edit Hyperlink window, where you can change the path to the linked file and its name, as well as add an explanatory note to the hyperlink;
- Open Hyperlink — opens the linked document, just as if you clicked the hyperlink;
- Remove Hyperlink — deletes the hyperlink.
octobre 13, 2025