XML Source RTF file in Excel to Text

[ddobrzynski]

I am designing an Excel Report that will generate a scope of work for that project. Currently using XML Sources to bring in the data to Excel but the Scope of work is an RTF file only generates the file name. I am trying to code a macro that will take an XML Source that is an RTF file and display it as text in the cell. Is this even possible?

 

[Dan_W]

Hi. I'm not sure I fully understand what you're asking for. Do you mean you're trying to get the XML code contained with an RTF file?

 

[ddobrzynski]

Hi. I'm not sure I fully understand what you're asking for. Do you mean you're trying to get the XML code contained with an RTF file?

Thanks for the response!
Yes, I'm creating an Excel report from a Program called SI D-tools. I am trying to pull a Scope of work RTF file from its XML data but when I do all I get is the file name and not the contents.

 

[Dan_W]

Hmm, so RTF isn't encoded using XML, it has its own special code (you can see it if you open the rtf file in notepad for example.)
If what you're trying to do is extract the contents from an RTF file, XML is not going to help you. You will need to use a method in VBA; is using vba a possibility for you? If so, I can try and dig out some code for you.

 

[ddobrzynski]

Yes, it is. I just don't know enough about VBA. Thank you for the help.

 

[Dan_W]

No problem. It may not yet work, but leave it with me, and I'll see what I can try and sort out for you. I probably won't be able yo get back to you until the end of the weekend, if that's ok.

 

[ddobrzynski]

No problem. It may not yet work, but leave it with me, and I'll see what I can try and sort out for you. I probably won't be able yo get back to you until the end of the weekend, if that's ok.

Awesome! No, there is no rush at all. Thank you again i really appreciate it.

 

[Dan_W]

Hi. I've started to have a look into this, and I think I may have been overthinking it. I have a couple of questions:
- Do you have a sample of the RTF file (even if anonymised data) that I can check to see what it is that you're trying to extract? Presumably, you just need the text, and not the formatting?
- Can you explain a little more about your project - specifically, what do you do with the contents once it's been extracted?
- Do you have MS Word on the computer? I ask because this could potentially be an easy solution.

Thanks.

 

[ddobrzynski]

It doesn't look like I can upload a file to here. But yes, I just want to take the text from an RTF file and display that in an excel cell. Yes, I have MS Word. I tried a script that would open word and copy the text to the cell but that used a file location which will be different for every report I generate with XML source code.

This is how I am creating the report: D-TOOLS EXCEL REPORTS

In the XML Source code, it looks like it just brings in the name of the file and nothing else " <xs:element name="ScopeOfWorkRtfFile" type="xs:string"/>"

Thank you.