Writing to a text file

S

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:



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
 
J

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
in message |
| 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?action=getinfo&userid=14766
| View this thread: http://www.excelforum.com/showthread.php?threadid=383308
|
 
S

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. :(
 
R

Rich_z

Because a text file is not a random access file, inserting text int
the file at any place other than the end is a problem. Effectivel
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:


Code
-------------------

Sub Test
Dim Input_File as Integer
Dim Output_File as Integer
Dim Source_Line as String
'*
Input_File=FreeFile()
Open "Old_File" for Input access read as #Input_File
Output_File=FreeFile()
Open "New_File" for output access write as #Output_File
Print #Output_File, "My header text"
While Not Eof(Input_File)
Line Input #Input_File, Source_Line '* Use line input as some text may contain commas
Print #Output_File, Source_Line
Wend
Close #Output_File
Close #Input_File
Kill "Old_File"
Name "New_File" as "Old_File"
End Sub

-------------------


Regards

Ric
 
J

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.

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



in message |
| 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
| > | > |
| > | 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?action=getinfo&userid=14766
| > | View this thread:
| > http://www.excelforum.com/showthread.php?threadid=383308
| > |
|
|
| --
| scantor145
| ------------------------------------------------------------------------
| scantor145's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=14766
| View this thread: http://www.excelforum.com/showthread.php?threadid=383308
|
 
S

scantor145

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Writing to a text file 1
speeding up a file conversion from text file to XML format 6
Security Error - Writing to a Text File 3
FWT Newsletter - Weekly - September 13, 2004 1

Top