Importing multiple xml files into excel

[indupriya9]

Hi Everyone

I have tried to write a macro that imports XML documents in a specific directory into Excel 2003. The file search part is being handled correctly. But the import of XML part is where it errors out. The error is "Object Required"

Here is the code that I have written.

Sub SImport() 
' 
' XMLimport Macro 
' Macro recorded 24/09/2007 by Priya 
' 

' 

Dim fs As FileSearch, i As Long, filePath As Variant, filenam As Variant, fsObject As Variant, file As Variant 
Dim wsh As Worksheet 
Dim xmpCustomMap As XmlMap 

Set fs = Application.FileSearch 
Path = "\\intranet\budgets\project_budgets\" 
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) 
filenam = file.Name 
FullName = Path + filenam 

MsgBox (FullName) 
'Set xmpCustomMap = ActiveWorkbook.XmlMaps("MyFields_Map") 

'ActiveWorkbook.XMLimport URL:=FullName, ImportMap:=xmpCustomMap, _ 
'Overwrite:=False, Destination:=Range("$B$1") 
ActiveWorbook.XmlMaps("MyFields_Map").Import FullName, False (Error Line) 
.NewSearch 
Next filePath 
End With 

End Sub

Can anyone out there help me resolve this problem please?

Thanks in advance
Priya

 

[Bong]

Is there an existing XmlMap named "MyFields_Map"?
Also, the argument of Import is a URL so it may require "file://" before the path.

_________________
Bob Rashkin

 

[indupriya9]

Hi Bong

Thanks for your reply. The XMLMAP "MyFields_Map" I have defined that manually by adding the XML file and then wrote the vba programe. I am not sure whether this action will this save the XmlMap.

How do I define the XMLMap any idea?

Regards
Priya

 

[Bong]

Adds an XML map to the specified workbook. Returns an XmlMap object.

expression.Add(Schema, RootElementName)

expression Required. An expression that returns a Workbook object.

Schema Required String. The schema to be added as an XML map. The string can be a path to a schema file, or the schema itself. The path can be specified in the Universal Naming Convention (UNC) or Uniform Resource Locator (URL) format.

RootElementName Optional String. The name of the root element. This argument can be ignored if the schema contains only one root element.

_________________
Bob Rashkin

 

[tsuji]

[1]
>But the import of XML part is where it errors out. The error is "Object Required"
>ActiveWorbook.XmlMaps("MyFields_Map").Import FullName, False (Error Line)
[tt]ActiveWor[highlight]k[/highlight]book.XmlMaps("MyFields_Map").Import FullName, False[/tt]
[2]
>.NewSearch
Why newseach at that postion where you are still processing all the founds?
[3]
I see you post the question all over the tek-tips forums. Are you just trying your chances? Or have you reflected simply do you talk with a language that you understand, and if not completely, why not you study a bit that part you don't understand before doing the task again? For instance, you say display in excel application is fine, and in html is also fine, and in text is also fine. What is the purpose then?