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
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