Converting Excel Rows to XML or TXT Files

ericdavised

New Member
Joined
Apr 9, 2018
Messages
1
Hello,

I am very new to VBA, but when I get an example to run off of, I can learn that way. I've found another post on this website to be helpful, but I am running into a few issues. https://www.mrexcel.com/forum/excel...t-each-row-spreadsheet-into-txt-xml-file.html

Goal:
  1. I want to take an excel spreadsheet, take every row and generate a new txt file or xml file
  2. I want the files generated from each row to be named based on column a of that row.

For example, if I had the following row in excel:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65, width: 89"]Filename[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 137"]
<tbody>[TR]
[TD="class: xl65, width: 137"]Application File Path[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88"]Log Directory[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD="class: xl65, width: 115"]Call List Directory[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Config File Directory[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl65, width: 130"]Overrides Directory[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="width: 89"]0004f2c601d1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 137"]
<tbody>[TR]
[TD="class: xl65, width: 137"]firmware/[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl65, width: 88"]logs/us/desk/[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD="class: xl65, width: 115"]call_lists/us/desk/[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]<style type="text/css"><!--br {mso-data-placement:same-cell;}--></style>
config/us/desk/
[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl65, width: 130"]overrides/us/desk/[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


I want the file to be called 0004f2c601d1.cfg and the file to look like:

<?xml version="1.0" standalone="yes"?>
<!-- Default Master SIP Configuration File-->
<!-- For information on configuring Polycom VoIP phones please refer to the -->
<!-- Configuration File Management white paper available from: -->
<!-- http://www.polycom.com/common/docum...n_file_management_on_soundpoint_ip_phones.pdf -->
<APPLICATION APP_FILE_PATH="firmware/" DECT_FILE_PATH="3111-17823-001.dect.ld" CONFIG_FILES="
config/us/desk/
" SERVICE_FILES="" MISC_FILES="" LOG_FILE_DIRECTORY="logs/us/desk/" OVERRIDES_DIRECTORY="overrides/us/desk/" CONTACTS_DIRECTORY="" LICENSE_DIRECTORY="" USER_PROFILES_DIRECTORY="" CALL_LISTS_DIRECTORY="" COREFILE_DIRECTORY="">
<style type="text/css"><!--br {mso-data-placement:same-cell;}--></style> <APPLICATION_SPIP300 APP_FILE_PATH_SPIP300="sip_213.ld" CONFIG_FILES_SPIP300="phone1_213.cfg, sip_213.cfg" />
<APPLICATION_SPIP500 APP_FILE_PATH_SPIP500="sip_213.ld" CONFIG_FILES_SPIP500="phone1_213.cfg, sip_213.cfg" />
<APPLICATION_SPIP301 APP_FILE_PATH_SPIP301="sip_318.ld" CONFIG_FILES_SPIP301="phone1_318.cfg, sip_318.cfg" />
<APPLICATION_SPIP320 APP_FILE_PATH_SPIP320="sip_335.ld" CONFIG_FILES_SPIP320="" />
<APPLICATION_SPIP330 APP_FILE_PATH_SPIP330="sip_335.ld" CONFIG_FILES_SPIP330="" />
<APPLICATION_SPIP430 APP_FILE_PATH_SPIP430="sip_327.ld" CONFIG_FILES_SPIP430="phone1_327.cfg, sip_327.cfg" />
<APPLICATION_SPIP501 APP_FILE_PATH_SPIP501="sip_318.ld" CONFIG_FILES_SPIP501="phone1_318.cfg, sip_318.cfg" />
<APPLICATION_SPIP600 APP_FILE_PATH_SPIP600="sip_318.ld" CONFIG_FILES_SPIP600="phone1_318.cfg, sip_318.cfg" />
<APPLICATION_SPIP601 APP_FILE_PATH_SPIP601="sip_318.ld" CONFIG_FILES_SPIP601="phone1_318.cfg, sip_318.cfg" />
<APPLICATION_SPIP670 APP_FILE_PATH_SPIP670="sip_404.ld" CONFIG_FILES_SPIP670="" />
<APPLICATION_SSIP4000 APP_FILE_PATH_SSIP4000="sip_318.ld" CONFIG_FILES_SSIP4000="phone1_318.cfg, sip_318.cfg" />
<APPLICATION_SSIP6000 APP_FILE_PATH_SSIP6000="sip_404.ld" CONFIG_FILES_SSIP6000="" />
<APPLICATION_SSIP7000 APP_FILE_PATH_SSIP7000="sip_404.ld" CONFIG_FILES_SSIP7000="" />
</APPLICATION>

Here's what I have tried:

I've tried to import the above file as an XML Map, but I am only ever able to export one row, the top row. How can I create a macro to go through multiple rows? Is the XML Map constricting?''

Recorded Macro
Here's the recorded macro I get when I do this. Though this macro doesn't save the filename from the first column of my spreadsheet.

ActiveWorkbook.XmlMaps("APPLICATION_Map").Export Url:= _
"C:\Users\user1\Desktop\export.xml"
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Forum statistics

Threads
1,223,249
Messages
6,171,031
Members
452,374
Latest member
keccles

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top