Loop through multiple files in a directory 1

[indupriya9]

Hi Gurus

I have recorded a macro to import xml files into excel 2003. Importing one file is achieved through this. What I need is a macro that will loop through all the xml files in the directory specified and import data into excel and append the results. All these xml files have the same structure.

The code so far is as follows:

Sub XMLimport()
    ActiveWorkbook.XMLimport URL:= _
        "[URL unfurl="true"]http://intranet/budgets/Project_Budgets/1111.xml",[/URL] ImportMap:=Nothing, _
        Overwrite:=False, Destination:=Range("$A$1")
End Sub

Can anyone please help me with the ocde to loop through all the files in the directory?

Thanks in advance
ip

 

[indupriya9]

I have done some research and got this far.

Now I get an error at Set filenam=File.Name the error is 'Type mismatch'

Sub XMLimport()

Dim fs As FileSearch, i As Long, filePath As Variant, filenam As Variant, fsObject As Variant, file As Variant
Dim wsh As Worksheet
Set fs = Application.FileSearch
    With fs
        .SearchSubFolders = False
        .FileType = msoFileTypeAllFiles
        .LookIn = "\\intranet\budgets\Project_Budgets\"
        .Execute
          For Each filePath In .FoundFiles
            i = i + 1
            Set fsObject = CreateObject("Scripting.FileSystemObject")
            Set file = fsObject.GetFile(filePath)
      [b]      Set filenam = file.Name  <<<<<<<<<error line<<<<[/b]
            
            'MsgBox (filenam)
            
            ActiveWorkbook.XMLimport URL:=filenam, ImportMap:=Nothing, _
        Overwrite:=False, Destination:=Range("$B$1")

        Next filePath
    End With


    
End Sub

Can anyone resolve the problem here for me please?

 

[PHV]

Remove the Set keyword:
filenam = file.Name

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[indupriya9]

Thanks for your prompt reply. That worked. But now it is giving a XML Map beign different error. That may be an issue to be raised in the XMl forum.

Thanks and you again get a star from me.