ProfessionalExcel.com In the office, Excel is a way of life… not just a tool

24Apr/149

Export Excel table range to XML using VBA

Today I needed to export a table in Excel into an XML format, and here's the code I whipped up to do it.

All you have to do is select the table range, then run the ExportRangeToXML procedure. You'll be prompted for a filename and location to save the XML file. You can customise the XML Element names used in the XML structure for the table and rows. The table headers are used for Element names in each row of data.

'*****************************************************
' Purpose:   Exports a selected range (table) to an xml
'            format using table headers.
' Notes:     Requires entry of file name. Uses table headers
'            as Element names for each row.
'
' Developer:        Date:       Description:
' Chris Read        24/04/14    Created.
'*****************************************************
Public Sub ExportRangeToXML()
 
Dim strXML As String
Dim varTable As Variant
Dim intRow As Integer
Dim intCol As Integer
Dim intFileNum As Integer
Dim strFilePath As String
Dim strRowElementName As String
Dim strTableElementName As String
Dim varColumnHeaders As Variant
 
    'Set custom names
    strTableElementName = "Table"
    strRowElementName = "Row"
 
    'Set file path
    strFilePath = Application.GetSaveAsFilename(, "(*.xml),*.xml", , "Save As...")
    If strFilePath = vbNullString Then Exit Sub
 
    'Get table data
    varTable = Selection.Value
    varColumnHeaders = Selection.Rows(1).Value
 
    'Build xml
    strXML = "<?xml version=""1.0"" encoding=""utf-8""?>"
    strXML = strXML & "<" & strTableElementName & ">"
    For intRow = 2 To UBound(varTable, 1)
        strXML = strXML & "<" & strRowElementName & ">"
        For intCol = 1 To UBound(varTable, 2)
            strXML = strXML & "<" & varColumnHeaders(1, intCol) & ">" & _
                varTable(intRow, intCol) & "</" & varColumnHeaders(1, intCol) & ">"
        Next
        strXML = strXML & "</" & strRowElementName & ">"
    Next
    strXML = strXML & "</" & strTableElementName & ">"
 
    'Get next file number
    intFileNum = FreeFile
 
    'Open the file, write output, then close file
    Open strFilePath For Output As #intFileNum
    Print #intFileNum, strXML
    Close #intFileNum
 
End Sub
23Apr/136

Replace IF(ISERROR()) with value_if_false argument

This tip relates to the technique of trapping and handling errors in a formula. Prior to Excel 2007 (e.g. Excel 2003, 2002, 2000), this was accomplished using a combination of the IF and ISERROR functions to test for an error in a formula, e.g. =IF(ISERROR(MyFormula),ReturnSomethingElse,MyFormula).

A reader of my blog was after some code to do something a little different to a previous post which looked at replacing the Excel 2007 IFERROR function with IF and ISERROR.

The slight twist on this was when you wanted to get rid of the error trapping (IF and ISERROR) and just have the original formula, warts (errors) and all!

In the VBA code below, I have developed a handy procedure (ReplaceIFISERRORCellFormulas) that will convert a range of selected cells that contain IF(ISERROR()) formulas to instead display the formula used in the 'value_if_false' argument of the original IF(ISERROR()) function.

30Mar/124

Use VBA to test the internet connection

In some of my applications I utilise the IE web browser control to display reports, some of which request internet content (e.g. Google Chart API and Google Maps API).

The trouble is, you can't always guarantee that the users PC has an internet connection. So, what's the best approach to the problem? Well, check the internet connection using the handy bit if VBA code below!

Private Declare Function InternetGetConnectedStateEx Lib "wininet.dll" (ByRef lpdwFlags As Long, ByVal lpszConnectionName As String, ByVal dwNameLen As Integer, ByVal dwReserved As Long) As Long
 
'Testing for internet connection
Public Function IsInternetConnected() As Boolean
 
    IsInternetConnected = InternetGetConnectedStateEx(0, "", 254, 0)
 
End Function
Filed under: Userforms, VBA 4 Comments