It loads an XML doc (typically 100kb), parses the Node Tree, searches for specific nodes using XPath, extracts the displayed text of each and sticks it in an array that's printed to a worksheet every 500 lines or so. I recently built a Tool to extract data from corporate accounts. Then you can print that Array to a worksheet. Rather than trying to import an entire file to the worksheet every time, why not strip your document down to a NodeList, and then either target specific nodes (if you're only after certain data) or iterate over them and extract the data values into an array.
Generally, you want to strip away as much unwanted data as early as possible in the process. Not knowing what your XMl files are like or what you're trying to extract from them, this is mostly speculative: Sheets("XMLData").UsedRange.WrapText = False If IncludeSubfolders Then 'SEARCH SUBFOLDERS FOR SAME CRITERIAįor Each MySubFolder In mySource.SubFolders ImportMap:=Nothing, Overwrite:=True, Destination:=Range("$B$" & LastRow + 1) LastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).RowĪctiveWorkbook.XmlImport URL:=mySource & "\" & myfile.Name, _ If Right(myfile.Name, 3) = "XML" Or Right(myfile.Name, 3) = "xml" Then 'IS XML? 'FIND XML FILES ONLY, APPLY SEARCH CRIERIA, DISPLAY MATCHES ONLY Set mySource = MyObject.GetFolder(mySourcePath) Set MyObject = New Scripting.FileSystemObject Sub ListMyFiles(mySourcePath, IncludeSubfolders) Set ShellApplication = CreateObject("Shell.Application").BrowseForFolder(0, "Please choose a folder", 0, OpenAt) Can you please help me on how to improve my code? Sub ListFiles()Īpplication.Calculation = xlCalculationManual
I need to import more than 200,000 files. Several minutes to import less than 20 files. I have tried to run the code with only two files and it takes about 30 seconds.
I have adapted a VBA code to import all the XML files I have on a folder into Excel.
0 kommentar(er)
