Excel 2002 with Visual Basic 6.3

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

<?xml version="1.0"?
<NewDataSet
<Measurements
<Index0</Index
<Pos1</Pos
<ChemNumber7</ChemNumber
<ReadNumber1</ReadNumber
<Repeats0</Repeats
<FilterNumber1</FilterNumber
<Signal193075</Signal
<Reference76668</Reference
<TimeStamp1255</TimeStamp
</Measurements
<Measurements
<Index1</Index
<Pos1</Pos
<ChemNumber7</ChemNumber
<ReadNumber2</ReadNumber
<Repeats0</Repeats
<FilterNumber1</FilterNumber
<Signal191867</Signal
<Reference76204</Reference
<TimeStamp1496</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

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" 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
| <Index0</Index
| <Pos1</Pos
| <ChemNumber7</ChemNumber
| <ReadNumber1</ReadNumber
| <Repeats0</Repeats
| <FilterNumber1</FilterNumber
| <Signal193075</Signal
| <Reference76668</Reference
| <TimeStamp1255</TimeStamp
| </Measurements
| <Measurements
| <Index1</Index
| <Pos1</Pos
| <ChemNumber7</ChemNumber
| <ReadNumber2</ReadNumber
| <Repeats0</Repeats
| <FilterNumber1</FilterNumber
| <Signal191867</Signal
| <Reference76204</Reference
| <TimeStamp1496</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
|

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"
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
| <Index0</Index
| <Pos1</Pos
| <ChemNumber7</ChemNumber
| <ReadNumber1</ReadNumber
| <Repeats0</Repeats
| <FilterNumber1</FilterNumber
| <Signal193075</Signal
| <Reference76668</Reference
| <TimeStamp1255</TimeStamp
| </Measurements
| <Measurements
| <Index1</Index
| <Pos1</Pos
| <ChemNumber7</ChemNumber
| <ReadNumber2</ReadNumber
| <Repeats0</Repeats
| <FilterNumber1</FilterNumber
| <Signal191867</Signal
| <Reference76204</Reference
| <TimeStamp1496</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

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" wrote
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"
| 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
| | <Index0</Index
| | <Pos1</Pos
| | <ChemNumber7</ChemNumber
| | <ReadNumber1</ReadNumber
| | <Repeats0</Repeats
| | <FilterNumber1</FilterNumber
| | <Signal193075</Signal
| | <Reference76668</Reference
| | <TimeStamp1255</TimeStamp
| | </Measurements
| | <Measurements
| | <Index1</Index
| | <Pos1</Pos
| | <ChemNumber7</ChemNumber
| | <ReadNumber2</ReadNumber
| | <Repeats0</Repeats
| | <FilterNumber1</FilterNumber
| | <Signal191867</Signal
| | <Reference76204</Reference
| | <TimeStamp1496</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
|

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

--
scantor14
-----------------------------------------------------------------------
scantor145's Profile: http://www.excelforum.com/member.php...fo&userid=1476
View this thread: http://www.excelforum.com/showthread.php?threadid=38330

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

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

--
Rich_
-----------------------------------------------------------------------
Rich_z's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread.php?threadid=38330