Help! Import XML data into Excel hourly

[jon1974]

Hello,

I need help with what should be a simple task, but I can't seem to figure it out. I need to take XML data from a thermostat device and paste it into an Excel spreadsheet. I can do this manually without issue, but I want this to be automated and done hourly.

Here is what I want it to look like:

First Column, first row: Insert current date and time
Second Column, first row: import current XML data here

Then, one hour later, I want it to drop to the next available row and repeat. When I have attempted to do this via Macros in Excel, it give me an error on the second attempt about overwriting the XML data. I don't need fancy charts or stylish formatting, just simple data scraping.

Thanks in advance!

Jon

[jon1974]

Here is an example of how I can do this once...

https://youtu.be/KCvcHhEB9-8

[Keebellah]

Herés the modified code:

Sub XML_Data_Macro()
'
' XML_Data_Macro Macro
'
Dim lstRow  As Long '*  variable to hold the last row
lstRow = Cells(Rows.Count, 1).End(xlUp).Row '*  find the last filled row
If Len(Trim(Cells(lstRow, 1).Value)) > 0 Then lstRow = lstRow + 1  '*  if this is > 1 then add 1
Cells(lstRow, 1).Value = Format(Now(), "dd/mm/yyyy hh:mm")      '*  place timestanp in column A
Range("B" & lstRow).Select
ActiveWorkbook.XmlImport URL:="C:\Users\Jon\Desktop\books.xml", ImportMap:= _
    Nothing, Overwrite:=True, Destination:=Range(Range("B" & lstRow))
Cells.Columns.AutoFit
End Sub

[jon1974]

Here is the error I receive

Capture.PNG

[Keebellah]

Record a macro while you're doing the actual importing and use the data from that macro and combine it with what I gave you, how hard can it get?

[jon1974]

I figured it out. There was an extra Range() in there:

Sub XML_Data_Macro()
'
' XML_Data_Macro Macro
'
Dim lstRow  As Long '*  variable to hold the last row
lstRow = Cells(Rows.Count, 1).End(xlUp).Row '*  find the last filled row
If Len(Trim(Cells(lstRow, 1).Value)) > 0 Then lstRow = lstRow + 1  '*  if this is > 1 then add 1
Cells(lstRow, 1).Value = Format(Now(), "dd/mm/yyyy hh:mm")      '*  place timestanp in column A
Range("B" & lstRow).Select
ActiveWorkbook.XmlImport URL:="http://www.w3schools.com/xml/note.xml", _
        ImportMap:=Nothing, Overwrite:=True, Destination:=Range("B" & lstRow)
Cells.Columns.AutoFit
End Sub

[Keebellah]

You're getting the error becasue of this part and that's you responsability.

The Url of file location

ActiveWorkbook.XmlImport URL:=
"C:\Users\Jon\Desktop\books.xml", ImportMap:= _
    Nothing, Overwrite:=True, Destination:=Range(Range("B" & lstRow))

All I did was help you with the row
I think you'll have to replace that section with the valid URL where you're getting the data from

[jon1974]

I don't have access to the exact source, since I am not at the site of the equipment. I was just using a local test .xml file, but I can use a web hosted sample file instead to make it simpler.

http://www.w3schools.com/xml/note.xml

Sub XML_Data_Macro()
'
' XML_Data_Macro Macro
'
Dim lstRow  As Long '*  variable to hold the last row
lstRow = Cells(Rows.Count, 1).End(xlUp).Row '*  find the last filled row
If Len(Trim(Cells(lstRow, 1).Value)) > 0 Then lstRow = lstRow + 1  '*  if this is > 1 then add 1
Cells(lstRow, 1).Value = Format(Now(), "dd/mm/yyyy hh:mm")      '*  place timestanp in column A
Range("B" & lstRow).Select
ActiveWorkbook.XmlImport URL:="http://www.w3schools.com/xml/note.xml", ImportMap:= _
    Nothing, Overwrite:=True, Destination:=Range(Range("B" & lstRow))
Cells.Columns.AutoFit
End Sub

I still get the same error. Is the Range(Range part correct?

[Keebellah]

Sorry my mistake:

Sub XML_Data_Macro()
'
' XML_Data_Macro Macro
'
Dim lstRow  As Long '*  variable to hold the last row
lstRow = Cells(Rows.Count, 1).End(xlUp).Row '*  find the last filled row
If Len(Trim(Cells(lstRow, 1).Value)) > 0 Then lstRow = lstRow + 1  '*  if this is > 1 then add 1
Cells(lstRow, 1).Value = Format(Now(), "dd/mm/yyyy hh:mm")      '*  place timestanp in column A
Range("B" & lstRow).Select
ActiveWorkbook.XmlImport URL:="http://www.w3schools.com/xml/note.xml", ImportMap:= _
    Nothing, Overwrite:=True, Destination:=Range("B" & lstRow)
Cells.Columns.AutoFit
End Sub

[jon1974]

Thank you very much for your help. I just now need this to be automated hourly. I believe I can do this with Task Scheduler. If you have any insights into that portion, feel free to offer suggestions.