Writing to a text file

[scantor145]

Excel 2002 with Visual Basic 6.3

I have an XML file, which I'll call 2SimpleSamples.xml

<?xml version="1.0"?>
<NewDataSet>
<Measurements>
<Index>0</Index>
<Pos>1</Pos>
<ChemNumber>7</ChemNumber>
<ReadNumber>1</ReadNumber>
<Repeats>0</Repeats>
<FilterNumber>1</FilterNumber>
<Signal>193075</Signal>
<Reference>76668</Reference>
<TimeStamp>1255</TimeStamp>
</Measurements>
<Measurements>
<Index>1</Index>
<Pos>1</Pos>
<ChemNumber>7</ChemNumber>
<ReadNumber>2</ReadNumber>
<Repeats>0</Repeats>
<FilterNumber>1</FilterNumber>
<Signal>191867</Signal>
<Reference>76204</Reference>
<TimeStamp>1496</TimeStamp>
</Measurements>
</NewDataSet>

In a macro I have commands to select the xml file:

Please Login or Register  to view this content.

In order to format the data in a prescribed way in Excel, I have another file called 2SimpleSamples.xsl which is a style sheet associated with the file 2Simplesamples.xml. Actually, inserting the following line between <?xml version="1.0"?> and <NewDataSet> in the xml file above makes the association:

<?xml-stylesheet type="text/xsl" href="2simplesamples.xsl"?>

In my macro, after I open the xml file I need to insert(write) the above line into the xml file. Is it possible, and if so, how?

Thanks

[Jim Rech]

So you just want to insert a row and put some text in A2?

If so then:

Rows(2).Insert
Range("A2").Value = "<?xml-stylesheet type=""text/xsl""
href=""2simplesamples.xsl""?>"


--
Jim
"scantor145" <[email protected]> wrote
in message news:[email protected]...
|
| Excel 2002 with Visual Basic 6.3
|
| I have an XML file, which I'll call 2SimpleSamples.xml
|
| <?xml version="1.0"?>
| <NewDataSet>
| <Measurements>
| <Index>0</Index>
| <Pos>1</Pos>
| <ChemNumber>7</ChemNumber>
| <ReadNumber>1</ReadNumber>
| <Repeats>0</Repeats>
| <FilterNumber>1</FilterNumber>
| <Signal>193075</Signal>
| <Reference>76668</Reference>
| <TimeStamp>1255</TimeStamp>
| </Measurements>
| <Measurements>
| <Index>1</Index>
| <Pos>1</Pos>
| <ChemNumber>7</ChemNumber>
| <ReadNumber>2</ReadNumber>
| <Repeats>0</Repeats>
| <FilterNumber>1</FilterNumber>
| <Signal>191867</Signal>
| <Reference>76204</Reference>
| <TimeStamp>1496</TimeStamp>
| </Measurements>
| </NewDataSet>
|
| In a macro I have commands to select the xml file:
|
|
|
| Code:
| --------------------
| FilterList = "XML Files(*.xml),*.xml" 'Type of file to open
|
|
| With Application
| MyFileMod = .GetOpenFilename(filefilter:=FilterList)
| End With
| --------------------
|
| In order to format the data in a prescribed way in Excel, I have
| another file called 2SimpleSamples.xsl which is a style sheet
| associated with the file 2Simplesamples.xml. Actually, inserting the
| following line between <?xml version="1.0"?> and <NewDataSet> in the
| xml file above makes the association:
|
| <?xml-stylesheet type="text/xsl" href="2simplesamples.xsl"?>
|
| In my macro, after I open the xml file I need to insert(write) the
| above line into the xml file. Is it possible, and if so, how?
|
| Thanks
|
|
| --
| scantor145
| ------------------------------------------------------------------------
| scantor145's Profile:
http://www.excelforum.com/member.php...o&userid=14766
| View this thread: http://www.excelforum.com/showthread...hreadid=383308
|

[scantor145]

No, sorry. Maybe I wasn't clear enough. The xml file as listed is NOT in an Excel spreadsheet. An xml file is like a text file. I want to WRITE some text to a specific location within the xml file via macro commands, save it then open it up in Excel.

Sorry for any confusion.

So you just want to insert a row and put some text in A2?

If so then:

Rows(2).Insert
Range("A2").Value = "<?xml-stylesheet type=""text/xsl""
href=""2simplesamples.xsl""?>"


--
Jim
"scantor145" <[email protected]> wrote
in message news:[email protected]...
|
| Excel 2002 with Visual Basic 6.3
|
| I have an XML file, which I'll call 2SimpleSamples.xml
|
| <?xml version="1.0"?>
| <NewDataSet>
| <Measurements>
| <Index>0</Index>
| <Pos>1</Pos>
| <ChemNumber>7</ChemNumber>
| <ReadNumber>1</ReadNumber>
| <Repeats>0</Repeats>
| <FilterNumber>1</FilterNumber>
| <Signal>193075</Signal>
| <Reference>76668</Reference>
| <TimeStamp>1255</TimeStamp>
| </Measurements>
| <Measurements>
| <Index>1</Index>
| <Pos>1</Pos>
| <ChemNumber>7</ChemNumber>
| <ReadNumber>2</ReadNumber>
| <Repeats>0</Repeats>
| <FilterNumber>1</FilterNumber>
| <Signal>191867</Signal>
| <Reference>76204</Reference>
| <TimeStamp>1496</TimeStamp>
| </Measurements>
| </NewDataSet>
|
| In a macro I have commands to select the xml file:
|
|
|
| Code:
| --------------------
| FilterList = "XML Files(*.xml),*.xml" 'Type of file to open
|
|
| With Application
| MyFileMod = .GetOpenFilename(filefilter:=FilterList)
| End With
| --------------------
|
| In order to format the data in a prescribed way in Excel, I have
| another file called 2SimpleSamples.xsl which is a style sheet
| associated with the file 2Simplesamples.xml. Actually, inserting the
| following line between <?xml version="1.0"?> and <NewDataSet> in the
| xml file above makes the association:
|
| <?xml-stylesheet type="text/xsl" href="2simplesamples.xsl"?>
|
| In my macro, after I open the xml file I need to insert(write) the
| above line into the xml file. Is it possible, and if so, how?
|
| Thanks
|
|
| --
| scantor145
| ------------------------------------------------------------------------
| scantor145's Profile:
http://www.excelforum.com/member.php...o&userid=14766
| View this thread: http://www.excelforum.com/showthread...hreadid=383308
|

[Rich_z]

Because a text file is not a random access file, inserting text into the file at any place other than the end is a problem. Effectively what you need to do is:

• Open a new file
• print your text to the new file
• open the old file
• read each line and print it to the new file
• close both files
• delete the old file
• rename the new file

This would work something like this:

Please Login or Register  to view this content.

Regards

Rich

[Jim Rech]

This is an example of creating a new text file from an old, inserting a new
second line.

Then the macro deletes the original file and renames the new one to the old.

>>An xml file is like a text file.

Very much like a text file<g>

--
Jim

Sub a()
Dim TextLine As String
Open "c:\file.txt" For Input As #1
Open "c:\newfile.txt" For Output As #2
Line Input #1, TextLine
Print #2, TextLine
Print #2, "Inserted text"
Do While Not EOF(1)
Line Input #1, TextLine
Print #2, TextLine
Loop
Close #1
Close #2
Kill "c:\file.txt"
Name "c:\newfile.txt" As "c:\file.txt"
End Sub



"scantor145" <[email protected]> wrote
in message news:[email protected]...
|
| No, sorry. Maybe I wasn't clear enough. The xml file as listed is NOT
| in an Excel spreadsheet. An xml file is like a text file. I want to
| WRITE some text to a specific location within the xml file via macro
| commands, save it then open it up in Excel.
|
| Sorry for any confusion.
|
|
|
|
|
|
|
| Jim Rech Wrote:
| > So you just want to insert a row and put some text in A2?
| >
| > If so then:
| >
| > Rows(2).Insert
| > Range("A2").Value = "<?xml-stylesheet type=""text/xsl""
| > href=""2simplesamples.xsl""?>"
| >
| >
| > --
| > Jim
| > "scantor145" <[email protected]>
| > wrote
| > in message
| > news:[email protected]...
| > |
| > | Excel 2002 with Visual Basic 6.3
| > |
| > | I have an XML file, which I'll call 2SimpleSamples.xml
| > |
| > | <?xml version="1.0"?>
| > | <NewDataSet>
| > | <Measurements>
| > | <Index>0</Index>
| > | <Pos>1</Pos>
| > | <ChemNumber>7</ChemNumber>
| > | <ReadNumber>1</ReadNumber>
| > | <Repeats>0</Repeats>
| > | <FilterNumber>1</FilterNumber>
| > | <Signal>193075</Signal>
| > | <Reference>76668</Reference>
| > | <TimeStamp>1255</TimeStamp>
| > | </Measurements>
| > | <Measurements>
| > | <Index>1</Index>
| > | <Pos>1</Pos>
| > | <ChemNumber>7</ChemNumber>
| > | <ReadNumber>2</ReadNumber>
| > | <Repeats>0</Repeats>
| > | <FilterNumber>1</FilterNumber>
| > | <Signal>191867</Signal>
| > | <Reference>76204</Reference>
| > | <TimeStamp>1496</TimeStamp>
| > | </Measurements>
| > | </NewDataSet>
| > |
| > | In a macro I have commands to select the xml file:
| > |
| > |
| > |
| > | Code:
| > | --------------------
| > | FilterList = "XML Files(*.xml),*.xml" 'Type of file to open
| > |
| > |
| > | With Application
| > | MyFileMod = .GetOpenFilename(filefilter:=FilterList)
| > | End With
| > | --------------------
| > |
| > | In order to format the data in a prescribed way in Excel, I have
| > | another file called 2SimpleSamples.xsl which is a style sheet
| > | associated with the file 2Simplesamples.xml. Actually, inserting the
| > | following line between <?xml version="1.0"?> and <NewDataSet> in the
| > | xml file above makes the association:
| > |
| > | <?xml-stylesheet type="text/xsl" href="2simplesamples.xsl"?>
| > |
| > | In my macro, after I open the xml file I need to insert(write) the
| > | above line into the xml file. Is it possible, and if so, how?
| > |
| > | Thanks
| > |
| > |
| > | --
| > | scantor145
| > |
| > ------------------------------------------------------------------------
| > | scantor145's Profile:
| > http://www.excelforum.com/member.php...o&userid=14766
| > | View this thread:
| > http://www.excelforum.com/showthread...hreadid=383308
| > |
|
|
| --
| scantor145
| ------------------------------------------------------------------------
| scantor145's Profile:
http://www.excelforum.com/member.php...o&userid=14766
| View this thread: http://www.excelforum.com/showthread...hreadid=383308
|

[scantor145]

Thanks, but I don't understand how the new text is being inserted into the second line. What if I want the text to be inserted after line 22?

[Dave Peterson]

This part of Jim's code:

Line Input #1, TextLine
Print #2, TextLine
Print #2, "Inserted text"
Do While Not EOF(1)
Line Input #1, TextLine
Print #2, TextLine
Loop

Reads the first record, writes it out, then writes that "inserted text" line.

Then it just keeps reading/writing each input record.

If you wanted the insertion after the 22nd record, you could just count when you
read in the records.

Option Explicit
Sub a2()

Dim TextLine As String
Dim recCtr As Long

recCtr = 0

Open "c:\file.txt" For Input As #1
Open "c:\newfile.txt" For Output As #2

Do While Not EOF(1)
Line Input #1, TextLine
recCtr = recCtr + 1
Print #2, TextLine
If recCtr = 22 Then
Print #2, "Inserted text"
End If
Loop

Close #1
Close #2

Kill "c:\file.txt"

Name "c:\newfile.txt" As "c:\file.txt"

End Sub

scantor145 wrote:
>
> Thanks, but I don't understand how the new text is being inserted into
> the second line. What if I want the text to be inserted after line 22?
>
> --
> scantor145
> ------------------------------------------------------------------------
> scantor145's Profile: http://www.excelforum.com/member.php...o&userid=14766
> View this thread: http://www.excelforum.com/showthread...hreadid=383308

--

Dave Peterson