PDA

View Full Version : FileSystemObject create file containing text copied from IE



b.hill
09-30-2012, 02:59 PM
I am trying to put copied text (copied via Browser.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT) into a variable. I want to then delete an old file and create a new file containing the copied text (same data.xml filename).

Thanks to DRJ, I can edit the text how I would like once it is in the file, but before the text is edited I need help deleting the old data.xml file and putting the text I copied into a new data.xml file with the same filepath.

The code below will edit the text once it is in the data.xml filepath.


Dim FSO As New FileSystemObject
Dim Stream As TextStream
Dim Text As String
Dim Filename As String
Filename = "C:\Users\GKC\Documents\data.xml"
Set Stream = FSO.OpenTextFile(Filename)
Text = Stream.ReadAll
Stream.Close
Text = Replace(Text, "<?xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" & Chr(34) & _
"UTF-8" & Chr(34) & " standalone=" & Chr(34) & "True" & Chr(34) & "?>", "", , , vbTextCompare)
Text = Replace(Text, "-", "", , , vbTextCompare)
Kill Filename
Open
Filename For Append As #1
Print #1, Text
Close #1
Set Stream = Nothing
Set FSO = Nothing

patel
10-01-2012, 12:12 AM
Your code does not work on excel 2010, this works for me


Dim Text As String, Filename As String
Set FSO = CreateObject("Scripting.FileSystemObject")
Filename = "C:\Users\GKC\Documents\data.xml"
Set Stream = FSO.OpenTextFile(Filename)
Text = Stream.ReadAll
Stream.Close
Text = Replace(Text, "<?xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" & Chr(34) & _
"UTF-8" & Chr(34) & " standalone=" & Chr(34) & "True" & Chr(34) & "?>", "", , , vbTextCompare)
Text = Replace(Text, "-", "", , , vbTextCompare)
Kill Filename
Open
Filename For Append As #1
Print #1, Text
Close #1
Set Stream = Nothing
Set FSO = Nothing

snb
10-01-2012, 12:42 AM
or

sub snb()

c01 = "C:\Users\GKC\Documents\data.xml"
with CreateObject("Scripting.FileSystemObject")
c02=replace(.OpenTextFile(Filename).ReadAll , "<?xml version=""1.0"" encoding=""UTF-8"" standalone=""True""?>", "")
.createtextfile(filename).write c02
end with
end sub