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:
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>
[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="
<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"
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:
- I want to take an excel spreadsheet, take every row and generate a new txt file or xml file
- 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"