Problems with XMLImport (Range and relative references)


New Member
Feb 10, 2015

I've got some problems in designing a macro. As my VBA knowledge is very limited, I'm looking for an easy solution, that I am able to understand.

Idea: I've got a list of 10.000 URLs which are aligned one below the other. Each of those URLs is supposed to be used with XMLImport in the adjacent cell. This will produce entries in the 15 adjacent cells (within the row only).

At the moment, I am stuck with this. I highlighted my specific problems:

Sub Makro1()
' Makro1 Makro

ActiveCell.Offset(0, -1).Range("Tabelle28[[#Headers],[version]]").Select
ActiveCell.FormulaR1C1 = _
ActiveCell.Offset(0, 1).Range("Tabelle28[[#Headers],[version]]").Select
ActiveWorkbook.XmlImport URL:= _
"", _
ImportMap:=Nothing, Overwrite:=True, Destination:=Range("N1")
ActiveWindow.SmallScroll Down:=-6
ActiveCell.Offset(1, 0).Range("Tabelle28[[#Headers],[version]]").Select
End Sub
1. As you can see, only the first (/1 of /10000) URL (from M1) is used and pasted in N1. So, if I change the Range to N3, again only the first URL will be pasted in N3 etc. because that URL is used as plain text obviously. However, I'd like for the macro to copy each URL adjacent to the selected cell and use it with XmlImport in that selected cell.

2. When I start the macro, the URL is processed in N1 only. But, I'd like data output to appear in each cell next to the URLs. URL /3 would be processed in N3, etc.. So, I'd need some sort of relative Range.

For now, I'd be happy with a macro that only processes one URL at the time and then jumps to the next cell, so I could start the macro again. I don't need a loop (just yet), because I want to understand the solutions to my problems first and keep it simple. If I suceed with that, I'd like to try to find a solution for the loop myself before asking for advice again.

Help would be very much appreciated.


Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...