Excel VBA 24-Hour Trainer (2015)

Part IV
Advanced Programming Techniques

Lesson 26
Managing External Data

One of the most versatile and useful benefits of Excel is its ability to import data from external sources. Lessons 29–33 include examples of sharing data back and forth with other Microsoft Office applications from Excel.

Prior to Excel 97, data in an Excel workbook was entered manually. An Excel workbook was essentially a self-contained object, having almost no contact with the outside world except for the person working in the project.

Starting with Office 97, Microsoft became devoted to providing more and better tools for importing and exporting data to the Internet, database programs, and text-related software applications. Excel leads the way in this effort among all Office applications. In this lesson, you learn how to use VBA to share data between Excel and other external sources, including Access, the Internet, and text files.

Creating QueryTables from Web Queries

The Internet as we know it has only been around since the mid-1990s—not that long ago really—but it's hard to imagine what life would be like today without the World Wide Web. The public's desire is only increasing for access to the galaxy of information that is stored on the web. With each new release of its Office suite, Microsoft has improved the capacity of its applications to interact with web-based information.

NOTE When you connect Excel to an external source such as the Internet, you add a QueryTable to your worksheet. Objects that can connect to external data sources include a cell range, an Excel table, a pivot table, a text file, and a web query. In this case, you are adding a QueryTable to a worksheet because you are querying the web for information that will be displayed on your worksheet.

Suppose you are interested in monitoring the stock prices of a half-dozen or so technology companies. If you want to avoid the monotony of going to a financial website and entering the same stock symbols every time, you can automate the process with a web query, and refresh the data anytime you like.

When you build a web query, you need to tell Excel the website from which to extract the information, and the cell address on the destination sheet where you want the QueryTable to be located. Some background information about URLs and their parameters might be helpful for you to understand what is going on.

If you open your web browser and enter the URL http://money.cnn.com/quote/quote.html?symb=YHOO+GOOG, you reach a site that provides a table of stock quotes for Yahoo! and Google. With this URL, you are essentially passing URL parameters that enable you to pass information such as search criteria to a website. In this case, the URL parameters being used are the symbols for Yahoo! (YHOO) and Google (GOOG).

The following macro places the QueryTable on cell A1, and points to one of the bevy of websites out there that provide current stock quotes. For demonstration purposes, I chose a few companies that are all headquartered in the Silicon Valley area where I live and own my Excel development company. The stock symbols of those companies are the criteria that apply URL parameters through the code to gather the stock quote information that populates the QueryTable. Figure 26.1 shows what the result looked like when I ran this macro in November 2014:

Sub ImportStocks()

'Declare variables for destination worksheet,

'and two halves of the connection string:

'one half for the URL, and the other half for

'the quotes, to make it easier for you to edit.

Dim wsDestination As Worksheet

Dim strURL As String, strStocks As String

'Set your preferred destination worksheet; here it is Sheet2.

Set wsDestination = Worksheets("Sheet2")

'Define the URL for getting your stock quotes.

'There are many websites where you can do this.

strURL = "http://money.cnn.com/quote/quote.html?symb="

'Define your stocks of interest. I only selected these

'as an example of nearby Silicon Valley businesses.

strStocks = "AAPL,CSCO,EBAY,GOOG,INTC,ORCL,YHOO"

'My preference is to activate the destination worksheet

'and select cell A1.

Application.Goto wsDestination.Range("A1"), True

'Clear the cells in the worksheet so you know the data

'being imported will not be confused with other data

'you may have imported previously and not yet deleted.

Cells.Clear

'Add your QueryTable with the connection string

'and other useful methods you see in the With structure.

With wsDestination.QueryTables.Add _

(Connection:="URL;" & strURL & strStocks, _

Destination:=Range("$A$1"))

.BackgroundQuery = True

.SaveData = True

.AdjustColumnWidth = True

.WebSelectionType = xlSpecifiedTables

.WebFormatting = xlWebFormattingNone

.WebTables = """wsod_multiquoteTable"""

.Refresh BackgroundQuery:=False

End With

'Release object variable memory.

Set wsDestination = Nothing

End Sub

Figure 26.1

With the worksheet active, you can refresh the data by right-clicking cell A1 and selecting Refresh, as shown in Figure 26.2. Alternatively, you can execute the VBA expression Range("A1").QueryTable.Refresh in the Immediate window or in a macro. Each time you refresh the data, you see the most recent version of the information in the data source, including any changes that were made to the data.

Figure 26.2

NOTE Does your web query take too long to refresh? You can cancel the Refresh method if it's running longer than you want to wait with this block of code:

If Application.Wait(Now + TimeValue("0:00:10")) Then

With Worksheets(1).QueryTables(1)

If .Refreshing Then

.CancelRefresh

MsgBox "Refresh was cancelled.", , "FYI…"

End If

End With

End If

While on the subject of corporate performance, the following macro opens a .csv file for you, depending on which stock symbol you are searching for, and copies several years of historical stock price activity to Sheet3 of your workbook:

Sub ImportHistory()

Dim strStockSymbol As String

Dim strURL1 As String, strURL2 As String

'Download the past years' stock price activity.

strURL1 = "http://ichart.finance.yahoo.com/table.csv?s="

strURL2 = "&d=2&e=18&f=2010&g=d&a=2&b=13&c=1986&ignore=.csv"

strStockSymbol = "EBAY"

Workbooks.Open Filename:=strURL1 & strStockSymbol & strURL2

'Copy data from the csv file to your worksheet.

Range("A1").CurrentRegion.Copy _

ThisWorkbook.Worksheets("Sheet3").Range("A1")

'Close the csv file without saving it.

ActiveWorkbook.Close False

'Autofit the columns.

Columns.AutoFit

End Sub

NOTE Another example in the Try It section leads you in a step-by-step process of creating a web query.

Creating a QueryTable for Access

In upcoming lessons you learn about importing and exporting data between Excel and Access, using VBA and a technology called Structured Query Language, or SQL. Because this lesson deals with external data, you might be interested to know how to quickly, albeit manually, import an Access table directly to your worksheet.

Click the Data tab on the Ribbon, and find the Get External Data section at the far left. Click the leftmost icon that is labeled From Access as shown in Figure 26.3.

Figure 26.3

You see the Select Data Source dialog box. Navigate to the folder holding your Access database, select the folder, and also select the name of the database file. Click Open as shown in Figure 26.4.

Figure 26.4

The Select Table dialog box displays, so all you need to do is click to select the name of the table, and then click OK as shown in Figure 26.5. After that, the Import dialog box displays. I chose to keep the imported table as a Table format, placed onto my worksheet, starting in cell A1 as shown in Figure 26.6. Your Access table loads onto your worksheet as shown in Figure 26.7, with the top row having AutoFilter buttons to help you with your future searches.

Figure 26.5

Figure 26.6

Figure 26.7

NOTE The Select Table dialog box may contain tables and queries, and you can import data from either of them. You might want to be aware that parameter queries do not appear in this dialog box.

Using Text Files to Store External Data

Hail the text file, the true foot soldier interface for transferring information between two or more otherwise disparate platforms. In the modern age of computing, it's always been the text file that could be relied on for one application downloading its information in a comma-delimited or fixed-length file, and another application like Excel being able to accept the data.

Text files are not pretty, they are almost never formatted, and they are not easy to read. But when all else fails, they come through and are fairly easy to program. The following examples show how text files can help you in your everyday work.

Suppose you want Excel to add a new record to a text file that records the date and time a particular Excel workbook was saved. Let's say your C drive has a folder named YourFilePath, which holds a text file named LogFile.txt. The following VBA code goes into the ThisWorkbook module of the Excel file you are monitoring. Modify the macro as needed for your folder path and/or name of your text file.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim intCounter As Integer, myFileName As String

myFileName = "C:\YourFilePath\LogFile.txt"

intCounter = FreeFile

Open myFileName For Append As #intCounter

Write #intCounter, ThisWorkbook.FullName, Now, Application.UserName

Close #intCounter

End Sub

This macro creates four new text files, naming each with the prefix MyFile, followed by a number suffix in order from 1 to 4. For example, the first file is named MyFile001.txt, the second file is named MyFile002.txt, and so on. The starting number of 1 is derived by the code line For intCounter = 1 to 4. If you wanted to create four new text files starting with the name MyFile038.txt, you'd establish the starting number of 38 by specifying it with the line of code For intCounter = 38 to 41.

Sub CreateTextFiles()

Dim intCounter As Integer, strFile As String

For intCounter = 1 To 4

strFile = "MyFile" & Format(intCounter, "000")

strFile = "C:\YourFilePath\" & strFile & ".txt"

Open strFile For Output As #1

Close

Next intCounter

End Sub

The following macro copies the text of your comments in your worksheet's used range into a text file, where they are listed along with the cell values in that range. This is a very fast macro.

Sub Comment2Text()

Dim cmt As Comment, rng As Range

Dim iRow As Long, iCol As Long

Dim strText As String

Set rng = Range("A1").CurrentRegion

Open "C:\YourFilePath\YourFileName.txt" For Output As #1

For iRow = 1 To rng.Rows.Count

For iCol = 1 To rng.Columns.Count

If Not Cells(iRow, iCol).Comment Is Nothing Then

strText = strText & Cells(iRow, iCol).Text & _

"(" & Cells(iRow, iCol).Comment.Text & ")" & ";"

Else

strText = strText & Cells(iRow, iCol).Text & ";"

End If

Next iCol

strText = Left(strText, Len(strText) - 1)

Print #1, strText

strText = ""

Next iRow

Close

End Sub

If you want to know how many lines a particular text file has, the following macro tells you:

Sub Test1()

Dim MyObject As Object, LineCount As Variant

Set MyObject = _

CreateObject("Scripting.FileSystemObject")

With MyObject.OpenTextFile("C:\YourFilePath\YourFileName.txt", 1)

LineCount = Split(.ReadAll, vbNewLine)

End With

MsgBox UBound(LineCount) - LBound(LineCount) + 1

End Sub

Export each sheet in this workbook as a text file, with each file named as the sheet tab name. Text file macros compile very quickly.

Sub TextExport()

Dim rng As Range

Dim iWks As Integer, LRow As Long, iCol As Long

Dim sTxt As String, sPath As String

sPath = "C:\YourFilePath\"

For iWks = 1 To Worksheets.Count

Open sPath & Worksheets(iWks).Name & ".txt" For Output As #1

Set rng = Worksheets(iWks).Range("A1").CurrentRegion

For LRow = 1 To rng.Rows.Count

For iCol = 1 To rng.Columns.Count

sTxt = sTx t& Worksheets(iWks).Cells(LRow, iCol).Value & vbTab

Next iCol

Print #1, Left(sTxt, Len(sTxt) - 1)

sTxt = ""

Next LRow

Close #1

Next iWks

MsgBox "The text files can be found in " & Left(sPath, Len(sPath) - 1)

End Sub

If you would like to see a text file's contents in a message box, you can use the following code:

Sub GetTextMessage()

Dim sTxt As String, sText As String, sPath As String

sPath = "C:\YourFilePath\YourFileName.txt"

If Dir(sPath) = "" Then

MsgBox "File was not found."

Exit Sub

End If

Close

Open sPath For Input As #1

Do Until EOF(1)

Line Input #1, sTxt

sText = sText & sTxt & vbLf

Loop

Close

sText = Left(sText, Len(sText) - 1)

MsgBox sText

End Sub

Suppose you want to save the contents of cell A1 on Sheet1 as a text file. The following example shows how you can do that:

Sub SaveCellValue()

Open "C:\YourFilePath\YourFileName.txt" For Append As #1

Print #1, Sheets("Sheet1").Range("A1").Value

Close #1

End Sub

Finally, this macro demonstrates how to delete a text file if it exists, and replaces it with a new text file of the same name. If the text file does not exist, the macro creates a new text file:

Sub DeleteAndCreate()

Dim strFile As String, intFactor As Integer

On Error Resume Next

strFile = "C:\YourFilePath\YourFileName.txt"

Kill strFile

Err.Clear

intFactor = FreeFile

Open strFile For Output Access Write As #intFactor

Close #intFactor

End Sub

Try It

What is today's date, and what is the current time of day? In this lesson you create a web query to access the website of the United States Naval Observatory, where the day and time are recorded on the Master Clock of the United States Navy. The web query imports a display of the current day and time for several North American time zones.

Lesson Requirements

To get the sample workbook, you can download Lesson 26 from the book's website at www.wrox.com/go/excelvba24hour.

Step-by-Step

1. Open a new workbook.

2. From your worksheet, press Alt+F11 to go to the Visual Basic Editor.

3. From the menu bar in the VBE, click InsertModule.

4. In your new module, type Sub TimeAfterTime and press Enter. VBA produces the following two lines of code, separated by an empty line:

5. Sub TimeAfterTime()

End Sub

5. Open a With structure for the destination worksheet:

With Worksheets("Sheet1")

6. Declare a String type variable for the website address:

Dim strURL As String

7. Define the website address from which the information will be imported to your worksheet:

8. strURL = _

"http://tycho.usno.navy.mil/cgi-bin/timer.pl"

8. For consistency, I prefer to activate the worksheet that will receive the web data. Cell A1 is a convenient cell to start with:

Application.Goto .Range("A1"), True

9. Clear the cells in the worksheet so you know the data being imported will not be confused with other data you may have imported previously and not yet deleted:

Cells.Clear

10.Open a With structure for the Add method of your new QueryTable. You must specify the connection, URL, destination sheet, and other information that follows:

11. With .QueryTables.Add _

12. (Connection:="URL;" &strURL, Destination:=.Range("A1"))

13. .BackgroundQuery = True

14. .TablesOnlyFromHTML = False

15. .Refresh BackgroundQuery:=False

.SaveData = True

11.Close the With structure of the QueryTable's Add method:

End With

12.Close the With structure for the destination worksheet:

End With

13.Your entire macro looks as follows:

14. Sub TimeAfterTime()

15. 'Open a With structure for the destination worksheet.

16. With Worksheets("Sheet1")

17. 'Declare a String type variable for the website address.

18. Dim strURL As String

19. 'Define the website address, from which the information

20. 'will be imported to your worksheet.

21. strURL = _

22. "http://tycho.usno.navy.mil/cgi-bin/timer.pl"

23. 'For consistency, I prefer to activate the worksheet

24. 'that will receive the web data.

25. 'Cell A1 is a convenient cell to situate yourself.

26. Application.Goto .Range("A1"), True

27. 'Clear the cells in the worksheet so you know the data

28. 'being imported will not be confused with other data

29. 'you may have imported previously and not yet deleted.

30. Cells.Clear

31. 'Open a With structure for the Add method of your new

32. 'QueryTable. The connection, URL, and destination sheet,

33. 'and other information that follows, must be specified.

34. With .QueryTables.Add _

35. (Connection:="URL;" &strURL, Destination:=.Range("A1"))

36. .BackgroundQuery = True

37. .TablesOnlyFromHTML = False

38. .Refresh BackgroundQuery:=False

39. .SaveData = True

40. 'Close the With structure of the QueryTable's Add method.

41. End With

42. 'Close the With structure for the destination worksheet.

43. End With

End Sub

14.Press Alt+Q to return to the worksheet.

15.You can test the macro by pressing Alt+F8 to display the Macro dialog box as shown in Figure 26.8. Run the macro named TimeAfterTime. The result resembles Figure 26.9.

Figure 26.8

Figure 26.9

REFERENCE Please select the video for Lesson 26 online at www.wrox.com/go/excelvba24hour. You will also be able to download the code and resources for this lesson from the website.