daily import xml from web site and export data to txt file

[Tetonne]

good evening to all
is it possible to automate the import and update of a xml file in an excel file via vba?
for example : https://www.vertex42.com/sitemaps/pages.xml

when opening the excel file the vba macro check if the collection of the xml file took place within 12 hours for example.
if yes do nothing (say ready)
otherwise the update of the xml is launch in the excel. (with an update message when it is finished)

after I'll make a pivot from data in an other sheet

I would like to export to .txt file range B1:B max of lines

Can someone help?

file name is yyyymmdd.txt
Thanks you in advance

 

[Marc L]

Hi, you can start with the XmlImport method as you can see in VBA help​

or even directly open the URL under Excel like when manually you open a workbook …​

​

 

[Tetonne]

i found this

Sub ImportXMLtoList()
     Dim strTargetFile As String
     Application.DisplayAlerts = False
     strTargetFile = "C:\Users\Tetonne\file.xml"
     Workbooks.OpenXML Filename:=strTargetFile, LoadOption:=xlXmlLoadImportToList
     Application.DisplayAlerts = True
End Sub

it does the part of the job. I call it via a bouton.

can someone help to make this improuvement.

place the xml data in a existing sheet call data for example.
so that it will replace the existing information in the table.

Thanks a lot

 

[Marc L]

First, as this is a wild cross posting (call the police !)​

so according to any Excel forum rules like you was already warned on another forum​

you must add a link for each same thread created on any other forum !​

Or don't be surprised any helper won't waste time as it could be already solved elsewhere …​

​

And no need to find such useless code as it is yet explained in VBA help, a must read !​

​

place the xml data in a existing sheet call data for example.

Useless to create the text file just working directly on the opened xml file …​

​

 

[Tetonne]

ok thanks