Catégorie : Excel VBA Course

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.

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")

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.

The convenient interface of the World Wide Web (WWW) links the vast number of resources available on the Internet. Using the WWW, you can move among thousands of computer nodes, system applications, files, and documents. The ease of moving between documents and the ability to read them on any computer system has enabled organizations to adopt web technology. Many corporations and enterprises develop their own networks based on Internet technologies (intranets) to host internal information intended only for employees.

Thus, when accessing resources located on other computers, we are dealing with resources of a computer network. A computer network is two or more computers connected by a shared data transmission channel. By territorial and organizational characteristics, computer networks are divided into:

• Local Area Networks (LANs) — cover an organization, a group of organizations, or a district and use a single high-speed data transmission channel;
• Wide Area Networks (WANs) — operate worldwide and use all possible communication channels (including, for example, satellite links).

A local network is usually called an intranet, whereas the global world-wide network is called the Internet. As a rule, the architectural principle for building networks is the client–server model.

Today, the Internet is a virtual space consisting of software, networks of various tiers, computers, and terminals (for data input and display) that is constantly growing and being updated to meet the new needs of modern society. The many local computer networks that make up the Internet are interconnected by high-speed communication channels at the continental level.

Given the enormous number of networks that form the Internet, to get to the right place you need to know the address formats in use. The numbers used to identify a computer on the Internet are called IP addresses. Every computer on the Internet has a unique IP address consisting of a combination of four groups of digits, each not exceeding 255 in decimal notation. Because an IP address is not very convenient for users, every computer on the Internet also has a DNS address (Domain Name Service), for example, www.domainname.org. Such a name is called a domain name.

To access a particular type of resource available on the Internet—for example, to view information published on a page—you must enter the address of that page on the Internet. This address is called a Uniform Resource Locator (URL). Depending on how the document must be accessed (through a local disk, a local network, a web site, or a file archive), the URL may look different (even for the same document). A URL consists of two parts: a protocol specifier for accessing the given resource and a location specifier for the resource itself. For example:

• file://c:\sales\sales.htm — a file on the local computer;
• file://brig\sales\sales.htm — a file on a computer in the local network;
• http://brig/sales/sales.htm — a file on a web server in an intranet;
• http://brig.boreas.ru/sales/sales.htm — a file on a remote web server on the Internet;
• ftp://brig.boreas.ru/sales/sales.htm — a file on a remote FTP server on the Internet.

If a specific file is not indicated in the URL, the web page configured as the default for that web server opens.

The term “web server” (web site) can be interpreted in several ways. On the one hand, it is a set of documents connected by hyperlinks (in this case the web server has a main page from which all other pages are reachable in one or more steps). On the other hand, the term “web server” can mean the computer on which this set of documents is hosted and made accessible over a local or global network. Finally, the term may also refer to the software intended to provide access to the set of documents over a local or global network. Throughout this chapter, unless noted otherwise, we mean the first definition of “web server.”

A web page (or Internet page, or HTML document) is a text file that contains special document markup commands. If you open a web page in a plain text editor (for example, Notepad), you will see exactly these commands. However, when opened with a web browser such as Internet Explorer, Mosaic, or Netscape, the web page can display text, graphics, hyperlinks to other documents, and also controls. The secret is that a web browser contains an interpreter of HTML commands embedded in the web page file.

HTML (Hypertext Markup Language) is a document markup system for subsequent publication on the World Wide Web. Documents prepared in HTML format include images and links as well as formatting commands. To view these documents, a web browser (for example, Microsoft Internet Explorer) is used.

A hyperlink is text highlighted in blue or underlined (or otherwise styled as defined by the user), or a graphical image. Clicking a hyperlink navigates to a file, to a specific location within a file, to an HTML page on the World Wide Web, or to an HTML page in an intranet. Hyperlinks can also point, for example, to a terminal emulation protocol (Telnet), to newsgroups, or to FTP sites. As you move between pages via hyperlinks, a browsing history of all pages is created and stored. Web browsers such as Internet Explorer have navigation buttons on their toolbars that allow you to move forward or back from one viewed page to another.

Publishing is the process of outputting tables, forms, and reports in static or dynamic HTML format and then deploying all related files as World Wide Web applications to a web server, for example, Microsoft Internet Information Server or Microsoft Personal Web Server.

To view information published on the Internet, special programs called browsers (web browsers) are used. Browsers provide users with access to information, convenient tools for viewing it, and tools for creating their own web pages.

One well-known browser used as a convenient and reliable means of navigating Internet resources is Microsoft Internet Explorer.

With Internet Explorer you can not only view web pages, but also work with Microsoft Office Word documents, Excel worksheets, and PowerPoint presentations regardless of whether the document was saved as a web page or in the application’s standard format. When opening documents saved in the standard format of the application that created them, the corresponding application’s menus and toolbars appear inside Internet Explorer, allowing you to edit the document directly in Internet Explorer. This has become possible thanks to ActiveX technology.

Main Internet Services

• WWW (World Wide Web) — a tool for working with hypertexts, allowing the retrieval and storage of different types of information (text, graphics, video, audio, etc.); hypertext documents are hosted on web servers that are part of the Internet.
• FTP (File Transfer Protocol) — a method of transferring files between computers in the network regardless of their types, operating systems, file systems, or file formats.
• E-mail (Electronic Mail) — a means of sending and receiving electronic messages between network users.
• Usenet (newsgroups) — a service for distributing electronic messages among network users (one message is sent to a large group of users for public discussion).
• IRC (Internet Relay Chat) — a service for real-time direct communication on the Internet among many users.
• ICQ (I seek you) — an interactive communication service for users of the World Wide Web, which does not require a permanent IP address.

A user of this service registers on the central server www.icq.com and receives a personal identification number — UIN (Universal Internet Number) — which can always be used to establish contact with other users of the network who are also using this interactive communication service.

Microsoft Office and the Internet

Microsoft Office integrates two powerful information technologies that define the model of working with a computer.

• The first is based on the ability to store information anywhere — on a local hard disk, in a local or corporate network, or on the Internet.
• The second is based on the principle that users actually work not with applications but directly with documents and the information contained in them.

As a result, one of two possible approaches to working can be chosen:

• Working mainly with Microsoft Office applications, with occasional access to the intranet or the Internet for a needed web page, a document, an add-in for an application, or additional information about a program.
• Working mainly within the Internet Explorer browser, using it as the single environment in which you can view and edit any document located on your hard drive, in the company’s network, or on the Internet.

If you want to perform import or export using VBA, you need to use the corresponding method.

To import XML data into an Excel workbook , use the XmlImport method:

expression.XmlImport(Url, ImportMap, Overwrite, Destination)

• expression — a reference to the object (the workbook).
• Url — a URL or full path to the file with XML data.
• ImportMap — the map used when importing the file; if the data was imported earlier, this contains a reference to the object that stores the XML map.
• Overwrite — determines whether to overwrite data that has been mapped by the map specified in the ImportMap parameter; set True to overwrite existing mapped data, set False to append new data to existing data; by default this parameter is True.
• Destination — specifies the range where the imported data will be placed; this parameter holds a reference to the top-left cell of the range.

Importing an XML file

Sub Imp()
    ActiveWorkbook.XmlImport _
        URL:="D:\Data.xml", _
        ImportMap:=Nothing, _
        Overwrite:=True, _
        Destination:=Range("$A$1")
End Sub

To export data from an Excel workbook that contains a map, use the Export method of an XML map (the text refers to ExportXml, but the example shows exporting via an XmlMap object’s Export):

expression.ExportXml(Data)

• expression — a reference to the object (the workbook) that contains the XML map.
• Data — the full path to the file to be exported.

Exporting data to an XML file

Sub Exp()
    ActiveWorkbook.XmlMaps("employees_map").Export _
        URL:="D:\Export.xml"
End Sub