Write to different XML file in function of the value of the cell

roponcio · Dec 5th 2017

Hi all,

I have a macro that I use for work and I create XML files from the data in the Excel file. The first column of the worksheet is named "modifier" and the value in this column may be "create" "delete" or "modify". I am currently writing in the same file indifferently from which is the modifier. What I want to do is create three different files and write them in function of the modifier. So if the modifier is "create" write to Create.xml, if the modifier is "delete" write to Delete.xml and if the modifier is "modify" write to Modify.xml.

My actual macro would be something like this:

Code

Sub roponcio()


Dim SpecAtt AsString


SpecAtt= ThisWorkbook.Sheets("Sheet1").Cells(2, 1)


On Error Resume Next
      MkDir ThisWorkbook.path&"\NR_Scripts"
On Error GoTo 0


Const oRange AsString="B2:B5000"
Dim oCell Asrange


ForEach oCell In range(oRange)


If oCell.Value="create" Then


Sheets("Sheet1").Select


strExtCreate= ThisWorkbook.Path&"ThisWorkbook.path & "\NR_Scripts"\Create_S.xml"


f= FreeFile


i=4


Open strExtCreate For Output As #f


While ThisWorkbook.Sheets("Sheet1").Cells(i, 2) <>""


            Print #f, " <gn1 id="""&Cells(i, 2).Value&""" modifier=""create"">"
            Print #f, " <gn3>"&Cells(i, 2).Value&"</gn3>"
            Print #f, " <gn4>"&Cells(i, 3).Value&"</gn4>"


i= i+1
Wend
Close f




Else
If oCell.Value="update" Then


strExtUpdate= ThisWorkbook.Path&"ThisWorkbook.path & "\NR_Scripts"\Update_S.xml"


Sheets("Sheet1").Select


f= FreeFile


i=4


Open strExtUpdate For Output As #f


While ThisWorkbook.Sheets("Sheet1").Cells(i, 2) <>""


           Print #f, " <gn1 id="""&Cells(i, 2).Value&""" modifier=""create"">"
           Print #f, " <gn3>"&Cells(i, 2).Value&"</gn3>"
           Print #f, " <gn4>"&Cells(i, 3).Value&"</gn4>"


i= i+1


Wend


Close f


End If


Next


End Sub

Display More

It does`t work because it writes the same items in all the files.

Thank you in advance!

**iwrk4dedpr** · Dec 6th 2017

Quote from roponcio

...
My actual macro would be something like this:

Is it this, or something like it?
Please post a workbook that has the code and a sample set of data so that we can step through the code.

roponcio · Dec 6th 2017

Quote from iwrk4dedpr

Is it this, or something like it?
Please post a workbook that has the code and a sample set of data so that we can step through the code.

I just edited the first post with the workbook. Thank you.

**iwrk4dedpr** · Dec 6th 2017

Ok ... this looks something like the code you posted but you now have this line of code:

MkDir ThisWorkbook.Path & "\NR_Scripts_" & Cuando & ""

where I get the following value which will NOT make a folder.

C:\Users\BPettis\Downloads\NR_Scripts_12/5/2017 8:33:40 PM

When I removed the "Cuando" variable from the path and ran the code ... It ran just fine. You'll need to explain further where it isn't working for you.

roponcio · Dec 6th 2017

Quote from iwrk4dedpr

Ok ... this looks something like the code you posted but you now have this line of code:

MkDir ThisWorkbook.Path & "\NR_Scripts_" & Cuando & ""

where I get the following value which will NOT make a folder.

C:\Users\BPettis\Downloads\NR_Scripts_12/5/2017 8:33:40 PM

When I removed the "Cuando" variable from the path and ran the code ... It ran just fine. You'll need to explain further where it isn't working for you.

Display More

Hi Barry

That's not really a problem because I'm still thinking about the name that the folder should have. :tumble:

My problem begins here (I removed the "Cuando" variable as it's not important right now):

Code

[COLOR=#000000]Const oRange As String = "B2:B5000"[/COLOR]
    [COLOR=#000000]Dim oCell As range[/COLOR]
    [COLOR=#000000]For Each oCell In range(oRange)[/COLOR]
  [COLOR=#000000]If oCell.Value = "create" Then[/COLOR]
  [COLOR=#000000]Sheets("ExternalGSMCell").Select[/COLOR]
    [COLOR=#000000]strExt3g2gCreate = ThisWorkbook.Path & "ThisWorkbook.path & "\NR_Scripts_\Create_ExternalGSMCell.xml"[/COLOR]
    [COLOR=#000000]f = FreeFile[/COLOR]
    [COLOR=#000000]i = 4[/COLOR]
 ....................................................................


[COLOR=#000000]Else[/COLOR]
  [COLOR=#000000]If oCell.Value = "update" Then[/COLOR]
  [COLOR=#000000]strExt3g2gUpdate = ThisWorkbook.Path & "ThisWorkbook.path & "\NR_Scripts_\Update_ExternalGSMCell.xml"[/COLOR]
    [COLOR=#000000]f = FreeFile[/COLOR]
    [COLOR=#000000]i = 4[/COLOR]

Display More

As you can see in the "ExternalGSMCell" Sheet the first column is the modifier. What I am trying to do is to print to different file in function of that modifier. If you run the macro you can see that the output files are the same, indifferently from which is the modifier.

Thank you.

roponcio · Dec 6th 2017

I am trying a different approach by sorting first by "modifier", then finding the first row for every "modifier"

**iwrk4dedpr** · Dec 6th 2017

This looks like it won't work.

Code

strExt3g2gCreate = ThisWorkbook.Path & "ThisWorkbook.path & "

yeah your modifier was either create, update, delete. The code you posted ... worked ... well I believe it did.

**iwrk4dedpr** · Dec 6th 2017

Maybe step through the code "F8" in the ide window ... and look at the values in each variable then you can decide on the next step of execution what it should do.

But like I said ... I removed the "cuando" variable and your code worked for me.

Write to different XML file in function of the value of the cell

Participate now!

Share