0

can someone help me tweak the below code to convert a file to base64 string. Say i have a file and file path in cell A1. Cell A1 contains the text ("C:\testing\test1.xls"). I want to base64 test1.xls to string. I want to loop through all the files in column A. Any help is appreciated.

Here is the code i got. This code converts the text stored in cell A1.

Sub base64()
'this converts the A1 text to base64
    Dim objXML As MSXML2.DOMDocument
    Dim objNode As MSXML2.IXMLDOMElement
    Dim arrData() As Byte

    arrData = StrConv(Cells(1, 1), vbFromUnicode)

    Set objXML = New MSXML2.DOMDocument
    Set objNode = objXML.createElement("b64")

    objNode.DataType = "bin.base64"
    objNode.nodeTypedValue = arrData

    MsgBox objNode.Text

End Sub

Thanks! Jack

Community
  • 1
  • 1
JayeshG
  • 3
  • 1
  • 6

1 Answers1

0

See my edits below:

Firstly and importantly we have added a loop using the variable i which is also specifying the row we are accessing.

We have also created the variable lastRow and used a Range.Find method to find the last non-empty row in column A.

The third edit is also very important which is to add your objNode elements onto the objXML document.

Sub base64()
    Dim objXML As DOMDocument
    Dim i As Integer
    Dim lastRow As Long
    Dim objRoot As IXMLDOMElement
    Set objXML = New DOMDocument
    Set objRoot = objXML.createElement("root")
    objXML.appendChild objRoot


    Range("A1").Select
    lastRow = Columns("A").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For i = 1 To lastRow
        addElement i, objRoot, objXML
    Next i

    MsgBox objXML.XML

End Sub

Private Function addElement(rowNumber As Integer, objRootNode As IXMLDOMElement, objDOM As DOMDocument)
    Dim objNode As IXMLDOMElement
    Dim arrData() As Byte
    Set objNode = objDOM.createElement("b64")

    arrData = StrConv(Cells(rowNumber, 1).Value, vbFromUnicode)
    objNode.DataType = "bin.base64"
    objNode.nodeTypedValue = arrData

    objRootNode.appendChild objNode
End Function
Bijan Rafraf
  • 393
  • 1
  • 7
  • Thanks, Bijan! Let me try this out. I'll get back. – JayeshG Nov 06 '17 at 16:38
  • Hi Bijan, it says object doesn't support this property or method at this line of code. "objXML.appendChild (objNode)". Anything we're missing here? – JayeshG Nov 06 '17 at 16:42
  • Hi, I have edited my answer to include the root node object. This acts as the top of the tree as it were and all elements are added below that. – Bijan Rafraf Nov 06 '17 at 16:54
  • why it says object required at this point "Set objRoot = xmlDoc.DocumentElement" – JayeshG Nov 06 '17 at 17:11
  • apologies, the xmlDoc object is set below this point, I have edited the answer to put this above the point where we now use it. – Bijan Rafraf Nov 06 '17 at 17:16
  • Now it says object doesn't support this property or method at this line of code "objRoot.appendChild (objNode)", not sure why this is. Sorry for the trouble. – JayeshG Nov 06 '17 at 17:30
  • OK I have edited again and this is successfully running in my Excel. I have split into a separate function as the XMLElement could not be assigned twice in the same function call and I have correctly included the root element now. – Bijan Rafraf Nov 06 '17 at 18:14
  • Thanks Bijan. Sorry i should have made this clear. We're converting text in cell A1, but how we can actually convert the file itself into a string which is stored in cell A1? This was my primary task actually. – JayeshG Nov 06 '17 at 18:49
  • Cell A1 have file path to a file which i want to convert to base64 and store the base64 text into a string. – JayeshG Nov 06 '17 at 21:00
  • Hi @JayeshG I can't write all of your code for you. Hopefully you can see through what I've done here and understand it. To complete your project, you should look up the different issues involved. [This answered question will help you with reading a file](https://stackoverflow.com/questions/11528694/read-parse-text-file-line-by-line-in-vba) – Bijan Rafraf Nov 07 '17 at 09:44