在Excel中使用VBA导入XML时插入源URL(Insert Source Url when Importing XML using VBA in Excel)

在Excel中使用VBA导入XML时插入源URL(Insert Source Url when Importing XML using VBA in Excel)

我已经让VBA脚本正在扫描XML文件列表，然后将它们导入到单独的工作表中：

Sub adds() For x = 1 To 5 Worksheets("zips").Select Worksheets("zips").Activate mystr = "http://example.com/data.xml" mystr = Cells(x, 1) Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = x ActiveWorkbook.XmlImport URL:=mystr, _ ImportMap:= _ Nothing, Overwrite:=True, Destination:=Range("$B$1") Next x End Sub

脚本运行正常，导入了正确表格的五个工作表，但我还要做的是确保将源URL（mystr）添加到$ A $ 1，$ A $ 2，$ A $ 3 ...每排前的细胞。 最后，我将结合所有工作表，目标是按来源对组合条目进行排序。

这是我使用VBA的第一天，所以到目前为止很难，任何帮助都将非常感谢！

I've got the VBA script working to scan a list of XML files and then import them into individual worksheets:

Sub adds() For x = 1 To 5 Worksheets("zips").Select Worksheets("zips").Activate mystr = "http://example.com/data.xml" mystr = Cells(x, 1) Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = x ActiveWorkbook.XmlImport URL:=mystr, _ ImportMap:= _ Nothing, Overwrite:=True, Destination:=Range("$B$1") Next x End Sub

The script runs fine and five worksheets with the right tables are imported, but what I also want to do is make sure that the source URL (mystr) is added in to the $A$1, $A$2, $A$3...cells before each row. In the end, I will be combining all the worksheets, and the goal is to sort through the combined entries by their source.

This is my first day using VBA, so getting this far was difficult enough, and any help would be greatly appreciated!

最满意答案

我想你需要做的就是反向第6行：

Sub adds() For x = 1 To 5 Worksheets("zips").Select Worksheets("zips").Activate mystr = "http://example.com/data.xml" Cells(x, 1).Value = mystr Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = x ActiveWorkbook.XmlImport URL:=mystr, _ ImportMap:= _ Nothing, Overwrite:=True, Destination:=Range("$B$1") Next x End Sub

在回复评论时 - 尝试添加以下行：

i = 2 Do While Worksheets(CStr(x)).Cells(i, 2).Value <> "" Worksheets(CStr(x)).Cells(i, 1).Value = mystr i = i + 1 Loop

I think all you need to do is reverse line 6:

Sub adds() For x = 1 To 5 Worksheets("zips").Select Worksheets("zips").Activate mystr = "http://example.com/data.xml" Cells(x, 1).Value = mystr Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = x ActiveWorkbook.XmlImport URL:=mystr, _ ImportMap:= _ Nothing, Overwrite:=True, Destination:=Range("$B$1") Next x End Sub

In reply to comment - try adding following lines:

i = 2 Do While Worksheets(CStr(x)).Cells(i, 2).Value <> "" Worksheets(CStr(x)).Cells(i, 1).Value = mystr i = i + 1 Loop