Rectangle 27 4

You can customize the ribbon in Excel using a combination of XML and VBA.

There is also a free application that helps you design and validate the XML to modify the ribbon called the "Custom UI Editor For Microsoft Office": http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2006/05/25/customuieditor.aspx

VBA code to add a basic ribbon in Excel 2010? - Stack Overflow

excel vba excel-2007 ribbon excel-2010
Rectangle 27 13

Sub ImportXMLtoList()
Dim strTargetFile As String
Dim wb as Workbook

     Application.Screenupdating = False
     Application.DisplayAlerts = False
     strTargetFile = "C:\example.xml"
     Set wb = Workbooks.OpenXML(Filename:=strTargetFile, LoadOption:=xlXmlLoadImportToList)
     Application.DisplayAlerts = True

     wb.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets("Sheet2").Range("A1")
     wb.Close False
     Application.Screenupdating = True


End Sub

hey Tim, thanks a lot......... this worked :). It was my bad, I didnt specify the sheet name properly.

How to fetch data from XML file to Excel sheet using VBA - Stack Overf...

xml excel vba excel-vba
Rectangle 27 5

Sub MakeXML(iCaptionRow As Integer, iDataStartRow As Integer, sOutputFileName As String)
    Dim Q As String
    Q = Chr$(34)

    Dim sXML As String

    sXML = "<?xml version=" & Q & "1.0" & Q & " encoding=" & Q & "UTF-8" & Q & "?>"
    sXML = sXML & "<rows>"


    ''--determine count of columns
    Dim iColCount As Integer
    iColCount = 1
    While Trim$(Cells(iCaptionRow, iColCount)) > ""
        iColCount = iColCount + 1
    Wend

    Dim iRow As Integer
    iRow = iDataStartRow

    While Cells(iRow, 1) > ""
        sXML = sXML & "<row id=" & Q & iRow & Q & ">"

        For icol = 1 To iColCount - 1
           sXML = sXML & "<" & Trim$(Cells(iCaptionRow, icol)) & ">"
           sXML = sXML & Trim$(Cells(iRow, icol))
           sXML = sXML & "</" & Trim$(Cells(iCaptionRow, icol)) & ">"
        Next

        sXML = sXML & "</row>"
        iRow = iRow + 1
    Wend
    sXML = sXML & "</rows>"

    Dim nDestFile As Integer, sText As String

    ''Close any open text files
    Close

    ''Get the number of the next free text file
    nDestFile = FreeFile

    ''Write the entire file to sText
    Open sOutputFileName For Output As #nDestFile
    Print #nDestFile, sXML
    Close
End Sub

Sub test()
    MakeXML 1, 2, "C:\Users\jlynds\output2.xml"
End Sub

How to generate XML from an Excel VBA macro? - Stack Overflow

xml excel vba
Rectangle 27 20

Do NOT loop through all cells!! There is a lot of overhead in communications between worksheets and VBA, for both reading and writing. Looping through all cells will be agonizingly slow. I'm talking hours.

In Excel 2007 and later, sheets are about 1000 times larger (1048576 rows 16384 columns = 17 billion cells, compared to 65536 rows 256 columns = 17 million in Excel 2003). You will run into an "Out of memory" error if you try to load the whole sheet into a Variant; on my machine I can only load 32 million cells at once. So you have to limit yourself to the range you know has actual data in it, or load the sheet bit by bit, e.g. 30 columns at a time.

Option Explicit

Sub test()

    Dim varSheetA As Variant
    Dim varSheetB As Variant
    Dim strRangeToCheck As String
    Dim iRow As Long
    Dim iCol As Long

    strRangeToCheck = "A1:IV65536"
    ' If you know the data will only be in a smaller range, reduce the size of the ranges above.
    Debug.Print Now
    varSheetA = Worksheets("Sheet1").Range(strRangeToCheck)
    varSheetB = Worksheets("Sheet2").Range(strRangeToCheck) ' or whatever your other sheet is.
    Debug.Print Now

    For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)
        For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2)
            If varSheetA(iRow, iCol) = varSheetB(iRow, iCol) Then
                ' Cells are identical.
                ' Do nothing.
            Else
                ' Cells are different.
                ' Code goes here for whatever it is you want to do.
            End If
        Next iCol
    Next iRow

End Sub

To compare to a sheet in a different workbook, open that workbook and get the sheet as follows:

Set wbkA = Workbooks.Open(filename:="C:\MyBook.xls")
Set varSheetA = wbkA.Worksheets("Sheet1") ' or whatever sheet you need

Good tip. I realized that there is only one column I need to iterate through. How would you recommend I do that? And compare it to a column in another workbook. Diff it rather.

VBA Macro to compare all cells of two Excel files - Stack Overflow

excel vba excel-vba
Rectangle 27 8

  • uses the MSXML2 namespace as the old Microsoft.XMLDOM one is only maintained for legacy support - see here
  • uses the "SelectionNamespaces" property to fix the problem which MSXML2 has with XPath when a document has a default namespace - see here. We create a namespace called r (any name would do though) that has the same URI reference as the default namesepace in the document (http://www.regonline.com/api in this case)
  • uses that new namespace in XPath as the prefix for any element which has an unprefixed name. That's a complicated way of saying that rather than looking for /ID, we'll look for /r:ID

Here is the code:

Sub foo()

Dim xmlDoc As Object
Dim xmlNodeList As Object
Dim xmlNode As Object

Set xmlDoc = CreateObject("MSXML2.DOMDocument.6.0")
xmlDoc.setProperty "SelectionNamespaces", "xmlns:r='http://www.regonline.com/api'"
xmlDoc.async = False
xmlDoc.Load "C:\Users\colin\Desktop\yoz1234.xml"

Set xmlNodeList = xmlDoc.selectNodes("/r:ResultsOfListOfEvent/r:Data/r:APIEvent/r:ID")

For Each xmlNode In xmlNodeList
    MsgBox xmlNode.Text
Next xmlNode

End Sub

Excel VBA getting specific node from XML - Stack Overflow

xml excel vba ixmldomnode
Rectangle 27 6

Try this - you can modify the below code to fetch any child node

Set xmlDoc = CreateObject("Microsoft.XMLDOM")
xmlDoc.SetProperty "SelectionLanguage", "XPath"
xmlDoc.Async = False
xmlDoc.Load("C:\Users\pankaj.jaju\Desktop\Test.xml")

Set nodeXML = xmlDoc.getElementsByTagName("ID")
For i = 0 To nodeXML.Length - 1
    MsgBox nodeXML(i).Text
Next

This worked great. For any readers this is how I used the answer above to adapt my code (since I load the XML from a URL - not a file):

Sub ListEvents()
Dim myURL As String

myURL = getAPI("GetEvents", "filter=&orderBy=")

Set xmlDoc = CreateObject("Microsoft.XMLDOM")
xmlDoc.setProperty "SelectionLanguage", "XPath"
xmlDoc.async = False

With CreateObject("MSXML2.XMLHTTP")
    .Open "GET", myURL, False
    .send
    xmlDoc.LoadXML .responseText
End With

Set nodeXML = xmlDoc.getElementsByTagName("ID")
For i = 0 To nodeXML.Length - 1
    MsgBox nodeXML(i).Text
Next
End Sub

Sorry, where do we input a URL in the second portion of the answer?

Excel VBA getting specific node from XML - Stack Overflow

xml excel vba ixmldomnode
Rectangle 27 3

Public strSubTag As String
Public iStartCol As Integer
Public iEndCol As Integer
Public strSubTag2 As String
Public iStartCol2 As Integer
Public iEndCol2 As Integer

Sub Create()
Dim strFilePath As String
Dim strFileName As String

'ThisWorkbook.Sheets("Sheet1").Range("C3").Activate
'strTag = ActiveCell.Offset(0, 1).Value
strFilePath = ThisWorkbook.Sheets("Sheet1").Range("B4").Value
strFileName = ThisWorkbook.Sheets("Sheet1").Range("B5").Value
strSubTag = ThisWorkbook.Sheets("Sheet1").Range("F3").Value
iStartCol = ThisWorkbook.Sheets("Sheet1").Range("F4").Value
iEndCol = ThisWorkbook.Sheets("Sheet1").Range("F5").Value

strSubTag2 = ThisWorkbook.Sheets("Sheet1").Range("G3").Value
iStartCol2 = ThisWorkbook.Sheets("Sheet1").Range("G4").Value
iEndCol2 = ThisWorkbook.Sheets("Sheet1").Range("G5").Value

Dim iCaptionRow As Integer
iCaptionRow = ThisWorkbook.Sheets("Sheet1").Range("B3").Value
'strFileName = ThisWorkbook.Sheets("Sheet1").Range("B4").Value
MakeXML iCaptionRow, iCaptionRow + 1, strFilePath, strFileName

End Sub


Sub MakeXML(iCaptionRow As Integer, iDataStartRow As Integer, sOutputFilePath As String, sOutputFileName As String)
    Dim Q As String
    Dim sOutputFileNamewithPath As String
    Q = Chr$(34)

    Dim sXML As String


    'sXML = sXML & "<rows>"

'    ''--determine count of columns
    Dim iColCount As Integer
    iColCount = 1

    While Trim$(Cells(iCaptionRow, iColCount)) > ""
        iColCount = iColCount + 1
    Wend


    Dim iRow As Integer
    Dim iCount  As Integer
    iRow = iDataStartRow
    iCount = 1
    While Cells(iRow, 1) > ""
        'sXML = sXML & "<row id=" & Q & iRow & Q & ">"
        sXML = "<?xml version=" & Q & "1.0" & Q & " encoding=" & Q & "UTF-8" & Q & "?>"
        For iCOl = 1 To iColCount - 1
          If (iStartCol = iCOl) Then
               sXML = sXML & "<" & strSubTag & ">"
          End If
          If (iEndCol = iCOl) Then
               sXML = sXML & "</" & strSubTag & ">"
          End If
         If (iStartCol2 = iCOl) Then
               sXML = sXML & "<" & strSubTag2 & ">"
          End If
          If (iEndCol2 = iCOl) Then
               sXML = sXML & "</" & strSubTag2 & ">"
          End If
           sXML = sXML & "<" & Trim$(Cells(iCaptionRow, iCOl)) & ">"
           sXML = sXML & Trim$(Cells(iRow, iCOl))
           sXML = sXML & "</" & Trim$(Cells(iCaptionRow, iCOl)) & ">"
        Next

        'sXML = sXML & "</row>"
        Dim nDestFile As Integer, sText As String

    ''Close any open text files
        Close

    ''Get the number of the next free text file
        nDestFile = FreeFile
        sOutputFileNamewithPath = sOutputFilePath & sOutputFileName & iCount & ".XML"
    ''Write the entire file to sText
        Open sOutputFileNamewithPath For Output As #nDestFile
        Print #nDestFile, sXML

        iRow = iRow + 1
        sXML = ""
        iCount = iCount + 1
    Wend
    'sXML = sXML & "</rows>"

    Close
End Sub

How to generate XML from an Excel VBA macro? - Stack Overflow

xml excel vba
Rectangle 27 3

You might like to consider ADO - a worksheet or range can be used as a table.

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adPersistXML = 1

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

''It wuld probably be better to use the proper name, but this is
''convenient for notes
strFile = Workbooks(1).FullName

''Note HDR=Yes, so you can use the names in the first row of the set
''to refer to columns, note also that you will need a different connection
''string for >=2007
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
        & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"


cn.Open strCon
rs.Open "Select * from [Sheet1$]", cn, adOpenStatic, adLockOptimistic

If Not rs.EOF Then
    rs.MoveFirst
    rs.Save "C:\Docs\Table1.xml", adPersistXML
End If

rs.Close
cn.Close

This beats using a loop for 200,000 rows +1 :)

How to generate XML from an Excel VBA macro? - Stack Overflow

xml excel vba
Rectangle 27 1

Depending on how comfortable with VBA you are, you could parse the file yourself. A reference to "Microsoft XML" (msxml#.dll) and a look at this question should be all you need to get you on your way!

Read XML using VBA Excel Macro and customize the generated table - Sta...

xml excel vba excel-vba
Rectangle 27 9

If it were me, I would create a second spreadsheet that references the sheet to be tested. (Go to tools>references>browse) This new workbook can contain all the tests you want without having to dirty up your main workbook. This also frees you up to build an interface for yourself if you want one.

As a side note, if you want to hide procedures from the Excel Macro menu but still have access to them, at the top of your module put:

Option Private Module

Then make all the procedures you want to be able to use "Public". You can then call them from the immediate window (ctrl-g in the VBE) but they won't be listed in the macro list.

But if you keep your unit tests separate (as you probably should), then you don't really have to worry about Public/Private modules.

How to setup a unit test in VBA Excel Macro? - Stack Overflow

unit-testing excel vba excel-vba
Rectangle 27 8

Rubberduck is a free and open source VBE add-in that includes an IDE integrated Unit Testing framework and works on most of the major Office Products.

  • Download and install the latest release.
  • Open the Excel workbook your VBA project resides in.
  • Go to Tools >> Refrences and add a reference to Rubberduck.
  • Add a new standard module.
'@TestModule
Rubberduck.AssertClass
`@TestModule

Private Assert As Rubberduck.Assert

Start writing tests. Any Public Sub marked with the '@TestMethod attribute will be discovered and run by the Test Explorer.

'@TestMethod
Public Sub OnePlusOneIsTwo()
    Assert.AreEqual 2, Add(1,1)
End Sub

Great info. For what it's worth, the instructions on the linked wiki are more current than the instructions given here.

How to setup a unit test in VBA Excel Macro? - Stack Overflow

unit-testing excel vba excel-vba
Rectangle 27 4

Below are two methods to output the fields you need. Note, that the XML you have posted does not contain the header definitions for namespace "xa:" so is not fully formed XML. I've removed them in the example so MSXML2.DOMDocument doesn't throw a parse error.

Option Explicit
Sub XMLMethod()
Dim XMLString As String
Dim XMLDoc As Object
Dim boolValue As Boolean
Dim xmlDocEl As Object
Dim xMeContext As Object
Dim xChild As Object
Dim xorder As Object


    XMLString = Sheet1.Range("A1").Value

    'Remove xa: in this example
    'reason : "Reference to undeclared namespace prefix: 'xa'."
    'Shouldn't need to do this if full XML is well formed containing correct namespace
    XMLString = Replace(XMLString, "xa:", vbNullString)

    Set XMLDoc = CreateObject("MSXML2.DOMDocument")
    'XMLDoc.setProperty "SelectionNamespaces", "xa:"

        'XMLDoc.Load = "C:\Users\ooo\Desktop\test.xml" 'load from file
    boolValue = XMLDoc.LoadXML(XMLString)  'load from string

    Set xmlDocEl = XMLDoc.DocumentElement
    Set xMeContext = xmlDocEl.SelectSingleNode("//MeContext")
        Debug.Print Split(xMeContext.XML, """")(1)
    For Each xChild In xmlDocEl.ChildNodes

        If xChild.NodeName = "Orders" Then
            For Each xorder In xChild.ChildNodes
                Debug.Print Split(xorder.XML, """")(1)
                Debug.Print xorder.Text
            Next xorder

        ElseIf xChild.Text = "" Then
            Debug.Print Split(xChild.XML, """")(1)
        Else
            Debug.Print xChild.Text
        End If


    Next xChild

    'Output:
    'ABCe0552553
    'ABCe05525531
    '1
    'Cust1234
    'Smith
    'New York
    '101
    'MP3 Player
    '102
    'Radio


End Sub

And the following uses regex, which is really only useful if the XML is fixed to exactly your example each time. It's not really recommended for parsing XML in general unless you want speed over reliability.

Option Explicit

Sub RegexMethod()
Dim XMLString As String
Dim oRegex As Object
Dim regexArr As Object
Dim rItem As Object
Dim writeArray(1 To 1, 1 To 10) As Variant
Dim col As Long

    'Assumes Sheet1.Range("A1").Value holds example XMLString
    XMLString = Sheet1.Range("A1").Value

    Set oRegex = CreateObject("vbscript.regexp")
    With oRegex
        .Global = True
        .Pattern = "(id=""|>)(.+?)(""|</)"
        Set regexArr = .Execute(XMLString)

        'No lookbehind so replace unwanted chars
        .Pattern = "(id=""|>|""|</)"
        For Each rItem In regexArr
            'Change Debug.Print to fill an array to write to Excel
            Debug.Print .Replace(rItem, vbNullString)

            col = col + 1
            writeArray(1, col) = .Replace(rItem, vbNullString)
        Next rItem
    End With

    Sheet1.Range("A5:J5").Value = writeArray


End Sub


Sub XMLMethod()
Dim XMLString As String
Dim XMLDoc As Object
Dim boolValue As Boolean
Dim xmlDocEl As Object
Dim xMeContext As Object
Dim xChild As Object
Dim xorder As Object
Dim writeArray(1 To 1, 1 To 10) As Variant
Dim col As Long


    XMLString = Sheet1.Range("A1").Value

    'Remove xa: in this example
    'reason : "Reference to undeclared namespace prefix: 'xa'."
    'Shouldn't need to do this if full XML is well formed
    XMLString = Replace(XMLString, "xa:", vbNullString)

    Set XMLDoc = CreateObject("MSXML2.DOMDocument")
    'XMLDoc.setProperty "SelectionNamespaces", "xa:"

        'XMLDoc.Load = "C:\Users\ooo\Desktop\test.xml" 'load from file
    boolValue = XMLDoc.LoadXML(XMLString)  'load from string

    Set xmlDocEl = XMLDoc.DocumentElement
    Set xMeContext = xmlDocEl.SelectSingleNode("//MeContext")
        'Debug.Print Split(xMeContext.XML, """")(1)
        col = col + 1
        writeArray(1, col) = Split(xMeContext.XML, """")(1)
    For Each xChild In xmlDocEl.ChildNodes

        If xChild.NodeName = "Orders" Then
            For Each xorder In xChild.ChildNodes
                col = col + 1
                'Debug.Print Split(xorder.XML, """")(1)
                writeArray(1, col) = Split(xorder.XML, """")(1)
                col = col + 1
                'Debug.Print xorder.Text
                writeArray(1, col) = xorder.Text
            Next xorder
        ElseIf xChild.Text = "" Then
            col = col + 1
            'Debug.Print Split(xChild.XML, """")(1)
            writeArray(1, col) = Split(xChild.XML, """")(1)
        Else
            col = col + 1
            'debug.Print xChild.Text
            writeArray(1, col) = xChild.Text
        End If


    Next xChild

    Sheet1.Range("A5:J5").Value = writeArray


End Sub

Thank you for taking the time to write the two code examples. You are displaying the output per XML record in vertical fashion (one Excel row per output). Is it possible to have the output in horizontal fashion (displaying the output to columns rather than rows) ?

I've updated my answer to show a crude way to dump the data into an array for writing out to a sheet.

Parse Data in XML Document to Excel Worksheet - Stack Overflow

xml excel vba excel-vba
Rectangle 27 5

thanks, was already aware of it but wasnt sure how i should compare the sheets. thanks everyone

How to setup a unit test in VBA Excel Macro? - Stack Overflow

unit-testing excel vba excel-vba
Rectangle 27 7

Colin Pickard is mostly correct, but don't confuse the "password to open" protection for the entire file with the VBA password protection, which is completely different from the former and is the same for Office 2003 and 2007 (for Office 2007, rename the file to .zip and look for the vbaProject.bin inside the zip). And that technically the correct way to edit the file is to use a OLE compound document viewer like CFX to open up the correct stream. Of course, if you are just replacing bytes, the plain old binary editor may work.

BTW, if you are wondering about the exact format of these fields, they have it documented now:

The following link gives details for the XSLM format files. gbanik.blogspot.co.uk/2010/08/ The solution's the same as the one outlined by Yuhong Bao above, but makes for interesting reading and includes screenshots.

Is there a way to crack the password on an Excel VBA Project? - Stack ...

excel excel-vba passwords
Rectangle 27 7

Colin Pickard is mostly correct, but don't confuse the "password to open" protection for the entire file with the VBA password protection, which is completely different from the former and is the same for Office 2003 and 2007 (for Office 2007, rename the file to .zip and look for the vbaProject.bin inside the zip). And that technically the correct way to edit the file is to use a OLE compound document viewer like CFX to open up the correct stream. Of course, if you are just replacing bytes, the plain old binary editor may work.

BTW, if you are wondering about the exact format of these fields, they have it documented now:

The following link gives details for the XSLM format files. gbanik.blogspot.co.uk/2010/08/ The solution's the same as the one outlined by Yuhong Bao above, but makes for interesting reading and includes screenshots.

Is there a way to crack the password on an Excel VBA Project? - Stack ...

excel excel-vba passwords
Rectangle 27 4

Use the ParamArray. The ParamArray must always be the last thing to be declared and must be of the type Variant. It will allow you to enter as many variables as you like without having to define each one of them.

Function IfAmong(TextToCheck, ParamArray Text() As Variant) As Boolean
    Dim txt As Variant
    ' Default value if not found
    IfAmong = False
    For Each txt In Text()
        ' Make sure input is text
        If TypeName(txt) = "String" Then
            ' Test if they're equal ignoring case
            If LCase(txt) = LCase(TextToCheck) Then
                ' Set to true as found
                IfAmong = True
                ' There's no point to keep searching as we've already got our answer so lets exit
                Exit For
            End If
        End If
    Next txt
End Function

This way you avoid using a dictionary which could cause reference errors. Your Dictionary in your function also doesn't handle duplicate values. A Dictionary doesn't allow you to have multiple keys of the same value so as soon as you have a duplicate text value your function will fall over as these are not handled.

Thanks, good point. I had a similar routine for an array function, so just was reapplying that here. Your code is better and more efficient. Highly appreciated.

Just be advised that the dim i and i in next i are not required and next i will cause error. I edited the code, but need peer review before getting applied.

For i = LBound(Text) to UBound(Text)
For Each Loop

how to have multiple optional arguments in a user defined function in ...

excel vba excel-vba user-defined-functions
Rectangle 27 4

Use the ParamArray. The ParamArray must always be the last thing to be declared and must be of the type Variant. It will allow you to enter as many variables as you like without having to define each one of them.

Function IfAmong(TextToCheck, ParamArray Text() As Variant) As Boolean
    Dim txt As Variant
    ' Default value if not found
    IfAmong = False
    For Each txt In Text()
        ' Make sure input is text
        If TypeName(txt) = "String" Then
            ' Test if they're equal ignoring case
            If LCase(txt) = LCase(TextToCheck) Then
                ' Set to true as found
                IfAmong = True
                ' There's no point to keep searching as we've already got our answer so lets exit
                Exit For
            End If
        End If
    Next txt
End Function

This way you avoid using a dictionary which could cause reference errors. Your Dictionary in your function also doesn't handle duplicate values. A Dictionary doesn't allow you to have multiple keys of the same value so as soon as you have a duplicate text value your function will fall over as these are not handled.

Thanks, good point. I had a similar routine for an array function, so just was reapplying that here. Your code is better and more efficient. Highly appreciated.

Just be advised that the dim i and i in next i are not required and next i will cause error. I edited the code, but need peer review before getting applied.

For i = LBound(Text) to UBound(Text)
For Each Loop

how to have multiple optional arguments in a user defined function in ...

excel vba excel-vba user-defined-functions
Rectangle 27 2

The VBA implementation of regex does not allow lookbehind so ?<= will not work. The example you've shown will work fine in most regex flavours but not VBA/VB6.

Often with VBA you have to come up with a few shortcuts to achieve what you need, for example some workarounds could include

  • reverse the string first, use regex to match then reverse the result
Option Explicit

Sub test()
Dim testthis As String

 testthis = getLink("<a href=""http://www.google.com""class=""linkClass "" >Google Link</a><br/>")
End Sub

Function getLink(strData As String) As String
    Dim RE As Object, REMatches As Object

    Set RE = CreateObject("vbscript.regexp")
    With RE
        .MultiLine = False
        .Global = False
        .IgnoreCase = True
        .Pattern = "href=.+?"""
    End With
     Debug.Print RE.Pattern
    Set REMatches = RE.Execute(strData)
    getLink = Replace(Replace(REMatches(0), """", ""), "href=", "")

End Function

There's quite a few different ways to achieve this so you may well find a better solution.

html - Regex match in VBA (Excel) - Stack Overflow

html regex excel-vba excel-2010
Rectangle 27 2

The VBA implementation of regex does not allow lookbehind so ?<= will not work. The example you've shown will work fine in most regex flavours but not VBA/VB6.

Often with VBA you have to come up with a few shortcuts to achieve what you need, for example some workarounds could include

  • reverse the string first, use regex to match then reverse the result
Option Explicit

Sub test()
Dim testthis As String

 testthis = getLink("<a href=""http://www.google.com""class=""linkClass "" >Google Link</a><br/>")
End Sub

Function getLink(strData As String) As String
    Dim RE As Object, REMatches As Object

    Set RE = CreateObject("vbscript.regexp")
    With RE
        .MultiLine = False
        .Global = False
        .IgnoreCase = True
        .Pattern = "href=.+?"""
    End With
     Debug.Print RE.Pattern
    Set REMatches = RE.Execute(strData)
    getLink = Replace(Replace(REMatches(0), """", ""), "href=", "")

End Function

There's quite a few different ways to achieve this so you may well find a better solution.

html - Regex match in VBA (Excel) - Stack Overflow

html regex excel-vba excel-2010
Rectangle 27 2

This seems to be an inherent 'problem' with vba and I believe the only solution is to format your date field to a text field prior to exporting, obviously change Selection to the actual range

Sub convertToStr()
  Dim c As Range

  For Each c In Selection
    c.Value = "'" + CStr(c.Value)
  Next c

End Sub

In the end I went with just this one addition to the VBA code, so simple but took hours and hours, now the dates are saved correctly no matter how they appear in the excel worksheet, so I shall tick this as the answer as it gave me the idea.. Just insert this line ws.Columns("B:B").NumberFormat = "dd/mm/yyyy" before the save as command in the VBA script.

Excel 2007 Save worksheet as CSV differs to save worksheet as csv in V...

excel vba csv date