appending to excel file

E

erin.sebastian

Hello All,
I have a macro that opens a file ,copies it's contents and then writes
those contents to the excel document that is running the macro and is
therefore already open. This works however I want to actually append
the data that it's copying to my open excel file as my macro loops
through a directory and i take specific info from each file and want to
make a master file with ALL the contents in it. Currently my macro
overwrites the data so i always only have 1 row where there should be
1000's of rows. Any idea's on what i am doing wrong,
Here is the code.


Sub XML_DATA_LOAD()

'Load the XML and the XSL (the stylesheet).
Dim varFileArray As Variant
Dim oXML As Object, oXSL As Object
Dim rngMaster As Range
Dim rngData As Range
Dim shtMaster As Worksheet
Set oXML = CreateObject("MSXML.DOMDocument")
Set oXSL = CreateObject("MSXML.DOMDocument")

Dim PathToUse As String
Dim myFile As String

PathToUse = "C:\test\"

varFileArray = GetAllFilesInDir(PathToUse)
For lngI = 0 To UBound(varFileArray)
Debug.Print varFileArray(lngI)
oXML.Load PathToUse & varFileArray(lngI)

oXSL.Load "C:\Marine.xslt"

Dim sHTML As String
sHTML = oXML.transformNode(oXSL)

Open "c:\temp.html" For Output As #1
Print #1, sHTML
Close #1

Sheets("XML Data").Select
Range("A1").Select

Workbooks.Open Filename:="c:\temp.html"

Range("A1:A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range("A1:BA2").Select
Selection.Copy

With Workbooks(1)
.Activate
Sheets("XML Data").Select
Selection.PasteSpecial Paste:=xlPasteAll,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

End With

Workbooks(2).Save
Workbooks(2).Close True

Next lngI



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

Top