Website Parsing/Retrieval using XML

I recently posted links to the HTML and Internet Explorer object references. These are excellent tools to get web data into MS Office for further processing.

Lately I have been reading into XML as it appears to be a much faster way to access web pages than instantiating Internet Explorer. My first experiment into XML a few months ago was a disaster; I chalk it up to jumping in too fast without studying, but often that is the way I learn best.

Here is some sample code showing how to retrieve information (in Excel) from a website using XML. First you will need to set a reference to Microsoft XML, in my case (Excel 2003) it is version 6.0. The filename in Windows XP is c:\windows\system32\msxml6.dll.

Sub TestXML()
' check google.com using xml
Dim xSite As XMLHTTP60

Set xSite = New XMLHTTP60
xSite.Open "GET", "http://www.google.com/", False
xSite.Send

Do Until xSite.readyState = 4
Loop

MsgBox xSite.getAllResponseHeaders
MsgBox xSite.getResponseHeader("Last-Modified")
MsgBox "Status Text: " & xSite.statusText & vbCr & vbCr & "Status: " & xSite.Status

Range("A1") = Left$(xSite.responseText, 100)
End Sub

Please note that as far as I can tell, this will only work in IE 7.

First we set a reference to the XML object, then use the Open method to build a connection string. The Send method actually executes the action and accesses the website. Then we run through some message boxes showing some samples of what you can do with the query.

The first message box shows the hidden response headers from the google homepage. Once you list them, you can single them out, which is what the second message box does. The third message box shows off the Status and StatusText properties, which you can use (along with readyState) to see if a webpage has finished loading before your macro continues.

The last part shows how you can parse the website into an Excel range.

For more information on the properties and methods you can access with XML, check out:
http://msdn2.microsoft.com/en-us/library/ms535874(VS.85).aspx


Enjoy,
JP

---

Resending Outlook Messages via VBA Code

I just posted some new code on the Outlook page which demonstrates how to automate the "Resend This Message..." functionality which is found on the Actions menu of an opened Sent email.

Along with the code I've also posted brief instructions on how to add a macro to a toolbar button in Outlook (a highly useful feature which more people should use) and some really sweet VBA code to list all of the control IDs for the Office CommandBars.

Check it out at:

http://www.codeforexcelandoutlook.com/ResendMsg.html

Enjoy,
JP

---

HTML Object Libraries

As part of my code section on automating Internet Explorer, here are the links to the object libraries being used. Very useful, I recommend experimentation to find the right way to access whatever website you are trying to integrate with.

DHTML Object Reference: http://msdn2.microsoft.com/en-us/library/ms533050(vs.85).aspx
Internetexplorer Object Reference: http://msdn2.microsoft.com/en-us/library/aa752084(VS.85).aspx

I was not able to find a printed version of this, or even something you can browse easily (like the programmer references for Outlook and Excel), if anyone out there has seen this, please let me know.

Enjoy,
JP

---

Format SSN - Updated formula

Here is an update to the code I have posted on the site at


http://www.codeforexcelandoutlook.com/formatssn.html


I realized that the last part of the formula doesn't check for dashes. Here is the updated formula:


=IF(AND(LEN(A4)>6, LEN(A4)<9,>9, NOT(ISERROR(FIND("-",A4)))),A4,LEFT(A4,3)&"-"&MID(A4,4,2)&"-"&RIGHT(A4,4)))

Notice the addition of NOT(ISERROR(FIND("-",A4))) in the second IF formula.

If the formula already has dashes in it, it will simply return the value in A4.

Enjoy,

JP

---

Import data to Access from Excel

Back in January, I promised to post a macro that imports data from Excel into Access programmatically.

(See http://www.codeforexcelandoutlook.com/2008/01/my-first-access-macro.html.)

It takes a given spreadsheet and imports it into a given Access file/table. The spreadsheet must have a header row, with no blank rows or columns inside the table block. You must set a reference to the appropriate Access object library (Microsoft Access x.0 Object Library).

How it works:

First it uses a simple API call to get the local username, so that the spreadsheet can be (temporarily) saved to the user's desktop. Then it checks to see if the .mdb file is already open by looking for a matching .ldb file -- if an Access .mdb file is open, a .ldb file with the same name is created in the same folder to indicate that the database file is open/locked. We don't want to update a file that is already open by another user, to avoid data corruption.

The spreadsheet is saved to the user's desktop and closed for import. Then an instance of Access is started and the spreadsheet imported into the specified table. After a Msgbox is shown to confirm the import, the temp file is deleted from the desktop.


Option Explicit
‘ set a reference to Access Object Library before using this code

Public Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" ( _
ByVal lpBuffer As String, _
nSize As Long) As Long

‘ function to get username
Public Function Username() As String
Dim lpBuff As String * 1024
GetUserName lpBuff, Len(lpBuff)
Username = Left$(lpBuff, (InStr(1, lpBuff, vbNullChar)) - 1)
End Function

Sub ImportReportToAccess()
'
' open Access database and append ss to appropriate table
'
Application.ScreenUpdating = False

' first check if db file is locked, if so then don't run!

If Dir("C:\databasefile.ldb") = "" Then
' If a .mdb file is open, a matching .ldb file with the same name is opened in the same directory

Dim AWName As String
Dim UserN As String

' the newest report
AWName = ActiveWorkbook.Name

ActiveSheet.UsedRange

UserN = Username

Workbooks(AWName).SaveAs "C:\Documents And Settings\" & UserN & "\Desktop\" & AWName, FileFormat:=xlNormal
Workbooks(AWName).Close

Dim axsApp As Access.Application
Set axsApp = New Access.Application

With axsApp
.OpenCurrentDatabase "C:\databasefile.mdb", True
.DoCmd.OpenTable "table name"
.DoCmd.TransferSpreadsheet acImport, , "table name", "C:\Documents And Settings\" & UserN & "\Desktop\" & AWName, True
.CloseCurrentDatabase
End With

MsgBox "import complete"

Kill "C:\Documents And Settings\" & UserN & "\Desktop\" & AWName

Else
MsgBox ("Database file appears to be locked. Please try again later."), vbCritical
Exit Sub
End If

ExitProc:
Set axsApp = Nothing
Application.ScreenUpdating = True
End Sub



Enjoy,
JP

---

Personal.xls workbook Not Available When You Start Microsoft Excel

If you normally keep your macros in a personal.xls workbook (in your XLSTART folder) but suddenly it doesn't open, try this technique for restoring it.

I assume you have already checked to make sure it isn't just hidden (Window>Unhide) or disabled (Help>About Microsoft Excel>Disabled Items).

1. Quit all instances of Microsoft Excel, then press Ctrl-Alt-Del and click the Processes tab (in Win XP).

2. Sort the running processes by filename by clicking on the first header.


3. Look for EXCEL.EXE and, if it is present, click 'End Process' and 'Yes' to allow the Task Manager to remove Excel from memory.

4. Repeat for any other instances of Excel still present in memory.

5. Open Excel normally and the personal.xls workbook should now open.

Works with:

Excel 2002
Excel 2003
on Win XP

--JP

---

VBA Macro to Remove Stationery from Email Message

While browing some other lovely VBA blogs, I came across this site with some really excellent code.

I hate receiving Outlook emails with stationery, which I remove from my replies with extreme prejudice. This code will do so programmatically, which is a real treat. Check out the code here:

http://adam.rosi-kessel.org/weblog/2008/01/26/windows-outlook-2003-tip-vba-macro-to-remove-stationery-from-email-message


Enjoy,
JP

---