appending to excel file
 

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