Hi team,

I was wondering if anyone can assist me with grabbing some info from cells in an excel spreadsheet and adding it to an existing .xml file.

Some points:

I have attempted to open the .xml file and add to the table, but unfortunately, the program creates the .xml file only creates a schema based on the info currently in the job. Therefore column headings are in different locations and may be missing depending many different variables. So this seems messy and I would like to avoid this method.

After inspecting many diffent .xml files, the only common reference I can see is that the info I wish to add must be inserted into the file before a known line of text. Here is a jpg showing the insertion point for my data. https://www.dropbox.com/s/cybntd6xe6csmyz/XML%20insert.jpg

In the JPG, the data in magenta is an example of the info I wish to add to the existing .xml file (The existing .xml file actually has the extension of .pad).

I’m sure I can handle formatting my excel data to be arranged / foramtted to match the .xml file, but I simply have no understanding of how to paste it into the fileimediately before the text line nominated in red in my jpg.

I will eventually like to have a macro run on my excel spreadsheet that opens the correct .pad file (will be the same name as my excel file), creates each line of text / code based on data in certain cells of my spreadsheet, copies and then pastes that data into the .pad file imediately before the known constant line, then saves the .pad file.

Can anyone offer assistance on how to insert the info into the .pad via VBA code?

The line in red is the constant line in the .pad file that my data must be added directly before.

XML is a mystery to me I’m afraid.

XML is just a language for creating structured documents that are readable by humans as well as machines. Sometimes it is used well, but not always.

It’s not that hard for a program to write XML from scratch, but it’s generally better to use a library to handle the low-level details for you. OTOH, reading an XML file (or modifying an existing one) without using an XML library is not a pleasant exercise unless you’re guaranteed that the file will strictly adhere to a simple format, in which case you can use simple pattern-matching and search & replace routines to manipulate it.

See XML

How to make BBQ sauce:
1. Make (or buy) tomato sauce.
2. Add some tamarind paste.
3. Enjoy!

Sorry!

>tamarind paste

I’ll look out for it :)

I’ve got some excellent pickled lemon & piri piri paste which goes superbly with all BBQ food. I usually provide that + fine tomato relish and a traditional Aussie tom sauce.

The Rev Dodgson said:

XML is a mystery to me I’m afraid.

Hi Rev, it’s not the XML I need help with. It is the VBA. Really I just want to copy some info from cells in excel to an existing text file.
This info needs to be pasted in the text folder imediately preceding a known searchable word or term.

Is that something you have a handle on?

cheers,,

In basic terms I want to the following: Pick up data from an sheet named ‘stuff’ in an Excel workbook named 1234.xlms and add it to file 1234.txt imediately preceding the word “Icecream”.

It would be somethign like this:

open stuff.xlms
put focus on sheet ‘stuff’
copy cell A1
open c:\*.txt (where * = this .xmls file name)
find word “icecream”
Paste cell A1 contents on the line immediately before the word ‘icecream’
Save *.txt
End.

stan101 said:

The Rev Dodgson said:

XML is a mystery to me I’m afraid.

Hi Rev, it’s not the XML I need help with. It is the VBA. Really I just want to copy some info from cells in excel to an existing text file.
This info needs to be pasted in the text folder imediately preceding a known searchable word or term.

Is that something you have a handle on?

cheers,,

OK, I think I can help with that then.

I’ll post something here later.

Even if you could point me to a link, I’d be very grateful. This VB comes very slowly to me.

stan101 said:

Even if you could point me to a link, I’d be very grateful. This VB comes very slowly to me.

This link:
http://newtonexcelbach.wordpress.com/2011/07/29/importing-selected-rows-from-a-text-file/#comment-6337

has a spreadsheet that will import from a text file with tabular data based on numerical criteria. That might be enough to get you going, or I will be updating it at some time to accept text criteria (but I don’t know when).

The basic procedure I would adopt is:

Read the new data into array1.
Step through the text file, reading the lines into array2 until you find the target text.
Read the rest of the text file into array 3.
Create empty array4.
Copy arrays 1,2 and 3 into array4 as required.
Write array4 to your output file.

Thanks, Rev. You are a champ. I’ll read through it tonight and have a crack tomorrow. I’ll let you know how I get on.

Cheers,