Get file information from XML docs and add to Excel

Question

I have a Drop Box file that users put in XML docs. I want to code for the following processes:

Part 1 : getting the file count

I would like to have an excel sheet that shows the number of files sorted by date in each row

Part 2 : Checking XML

To check the inner xml part for a 1 or 0 of a specific line of code

Part 3 : updating the excel doc dynamically

Each time the excel doc is opened and it checks the sub folders does 1 and 2 adding a new row if new date last modified is found.

I have 5 columns in the excel doc with the top row being a label on a sheet as follow:

Column A has the dates

Column B has part 1 from folder 1

Column C has part 1+2 with xml code being 0

Column D has part 1+2 with xml code being 1 from folder 2

Column E has part 1 from folder 3

I know javascript quite well but I have never used it outside of Quickbase. How would I even get started on getting this set up?

Edit 1:

For clarifications the folder tree looks like this

../Dropbox/SharedWorkFolder/APIReports/auditreports.xlsm
../Dropbox/SharedWorkFolder/APIReports/OrderLog/(xmls for column A+C+D)(roughly 2k files)
../Dropbox/SharedWorkFolder/APIReports/PandA/(xmls for column A+B)(11k files)
../Dropbox/SharedWorkFolder/APIReports/SpecificLookup/(xmls for column A+E)(130 files)

So far I have been reading documentations for excel and I found I have been lacking in my understanding of how diverse it was.

So far I have managed to import the file content themselves but as they very in length and xml contents I need specific xml tags pulled sadly non of them have a date except for the file names and while importing they seem to have grouped individually per file as well.

The file name looks like this 1452521833783-110120160917SS110313 means the 1st string being a transaction number then date+time "SS" and some 6 number string.

I will continue reading the documentation for excel VBA stuff and testing a few things

Edit 2:

And I found this code "Excel VBA using FileSystemObject to list file last date modified" but it keeps giving me an error user defined type not defined.

Edit 3: So far I am able to use this code

Dim iRow

Sub ListFiles()
    iRow = 11
    Call ListMyFiles(Range("C7"), Range("C8"))
End Sub

Sub ListMyFiles(mySourcePath, IncludeSubfolders)
    Set MyObject = New Scripting.FileSystemObject
    Set mySource = MyObject.GetFolder(mySourcePath)
    On Error Resume Next
    For Each myFile In mySource.Files
        iCol = 2
        Cells(iRow, iCol).Value = myFile.Path
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.Name
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.Size
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.DateLastModified
        iRow = iRow + 1
    Next
    If IncludeSubfolders Then
        For Each mySubFolder In mySource.SubFolders
            Call ListMyFiles(mySubFolder.Path, True)
        Next
    End If
End Sub

I still need to loop through all the XML and get specific nodes though and oh having issues with the map because the XML's have no map assigned to them and they all have different amounts of nodes (but 1 series of recurring that I need).

score 0 · Answer 1 · answered May 20 '16 at 17:53

0

Although you can maybe use Javascript to do this (I am not familiar with Excel's integration of js), my first instinct would be to do it through VBA : so before I go further, is there any reason why you specifically want js or would a VBA solution do the trick ? I will edit this answer accordingly.

answered May 20 '16 at 17:53

scardinet

1

Welcome to StackOverflow! Please don't use answers as a way to ask for more information about the question. You should use comments instead.(You'll need to gain enough reputation first to make a comment) – Keiwan May 20 '16 at 18:04
sorry. how can i contribute without the necessary points please ? – scardinet May 20 '16 at 18:06
You can go and look out for questions that you can answer without the need to ask for more information. If your answers are accepted/upvoted you gain reputation and you'll quickly be allowed to comment as well. We've all been there ;) For more information on how to gain reputation see: http://stackoverflow.com/help/whats-reputation – Keiwan May 20 '16 at 18:10
Well to put it simply I dont know VBA – Robert Pratt May 20 '16 at 18:29
So after looking into VBA this past week and testing a few things a VBA might be a better route I think but I ran into a few issues perhaps you can help walk me through. I will add to my original post for more details and room – Robert Pratt May 25 '16 at 13:16

score 0 · Answer 2 · answered Dec 11 '16 at 12:18

(Posted on behalf of the OP).

With much poking and prodding, this is my final edit, with what does what; each portion is done by buttons:

../Dropbox/SharedWorkFolder/APIReports/auditreports.xlsm
../Dropbox/SharedWorkFolder/APIReports/OrderLog/(xmls for column A+C+D)(roughly 2k files)
../Dropbox/SharedWorkFolder/APIReports/PandA/(xmls for column A+B)(11k files)
../Dropbox/SharedWorkFolder/APIReports/SpecificLookup/(xmls for column A+E)(130 files)

with key cells being:

C7: has the full directory name to get the info from
C8: has a boolean for sub folders or not
A9:=REPLACE(B9,1,SEARCH("^",SUBSTITUTE(B9,"\","^",LEN(B9)-LEN(SUBSTITUTE(B9,"\",""))-1)),"")
F9:=SUBSTITUTE(SUBSTITUTE(A9,C9,""),"\","") name of last folder for use in another table
G9:=IF(H9,LEFT(C9,13),"") transaction id num the file has in name to link the xml data to all tables
H9:=IF(RIGHT(C9,3)="xml",TRUE,FALSE) dont even think I need this column any more
I9:=IF(Table4[@FolderName]="OrderLog",TRUE,FALSE) gets order log files only for xml parse

The logic behind all of this is to get all XML files from within your folders to a list and then import the XML data from a specific folder name.

My Module:

Dim iRow
Dim iTar


Sub ListFiles()
    iRow = 11
    Call ListMyFiles(Range("C7"), Range("C8"))
End Sub

Sub ListMyFiles(mySourcePath, IncludeSubfolders)
    Set MyObject = New Scripting.FileSystemObject
    Set mySource = MyObject.GetFolder(mySourcePath)
    On Error Resume Next
    For Each myFile In mySource.Files
        iCol = 2
        Cells(iRow, iCol).Value = myFile.Path
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.Name
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.Size
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.DateLastModified
        iRow = iRow + 1
    Next
    If IncludeSubfolders Then
        For Each mySubFolder In mySource.SubFolders
            Call ListMyFiles(mySubFolder.Path, True)
        Next
    End If
End Sub

Sub filldown()

    Range("A9").Select
    Selection.Copy
    Range("A11").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("F9").Select
    Selection.Copy
    Range("F11").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("G9").Select
    Selection.Copy
    Range("G11").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("H9").Select
    Selection.Copy
    Range("H11").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("I9").Select
    Selection.Copy
    Range("I11").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A1").Select

End Sub


Sub ClearTable1()

    With Sheet1.ListObjects("Table4").ListRows
        Do While .Count >= 1
            .Item(1).Delete
        Loop
    End With
End Sub

Sub ClearTable2()

    With Sheet1.ListObjects("Table2").ListRows
        Do While .Count >= 1
            .Item(1).Delete
        Loop
    End With

End Sub



Sub XmlImport()
    Dim xmpCustomMap As XmlMap
    Set xmpCustomMap = ActiveWorkbook.XmlMaps("OrderLogDataSet_Map")
    Dim x As String
    Dim iPath As String
    Range("B11").Select
    x = "True"
    For i = 1 To Range("Table4").Rows.Count
          iPath = Worksheets("DATA").Cells(i + 10, 2).Value
          iTar = Worksheets("DATA").Cells(i + 10, 9).Value
          If iTar = x Then
           ActiveWorkbook.XmlImport URL:=iPath, ImportMap:=xmpCustomMap, Overwrite:=False
           End If
        ActiveCell.Offset(1, 0).Select
        Next i
End Sub

Get file information from XML docs and add to Excel

2 Answers2