Automate VBA and save as new file plus update xml from range

[clubcrowds]

I appreciate this may be a big question but .... I have a macro, I would like it to auto run at 17:00 and 22:00, save with a new file name and update an xml file with a range of cell from a worksheet in the workbook. The XML layout matches the worksheet, at present I manually copy and paste.

 

[shadow12345]

that kind of a multipart question really. The first bit ... run at a given time... as long as its open it will run.

this goes in the This workbook section

Private Sub Workbook_Open()

    Application.OnTime TimeValue("17:00:00"), "MyMacro" ' change this to your macro name
    Application.OnTime TimeValue("22:00:00"), "MyMacro" ' change this to your macro name


End Sub

The others are possible but we need more information.

New file name... what file name do you want? the current name of the workbook + the date & time? or something else
The XML file ... what is the name of the file, and what range of cells do you want to copy and paste into it?

Regards.

 

[clubcrowds]

Thanks for this

1) Preferably Date then time then Workbook name
2) I copy and paste from several ranges in Worksheets to several XML files but if I know the generic code I can repeat and amend names. If we just said it was A2:d4 in Sheet1 for an example that pastes into B2:e5 in the xml file opened in excel.

Thank you

 

[shadow12345]

So that could be something like this... for the first part... im fiddling with the XML part now

Sub MyMacro()
With ActiveWorkbook
.SaveAs Format(Now, "dd-mmm-yyyy hh-mm-ss") & " " & ActiveWorkbook.Name
End With




End Sub

 

[shadow12345]

I think something like this... i tried to make it as generic as possible. Im sure there are better ways, but this is what i came up with.

Sub MyMacro()
With ActiveWorkbook
.SaveAs Format(Now, "dd-mmm-yyyy hh-mm-ss") & " " & ActiveWorkbook.Name
End With

FromBook = ActiveWorkbook.Name


    Workbooks.OpenXML Filename:= _
        "C:\Desktop\sample_xml_feed_enetpulse_soccer.xml", LoadOption _
        :=xlXmlLoadImportToList
Tobook = ActiveWorkbook.Name
        

Workbooks(FromBook).Sheets("Sheet1").Range("a2:d4").Copy
Workbooks(Tobook).Sheets("Sheet1").Range("b2").Select
ActiveSheet.Paste



End Sub

 

[clubcrowds]

You're a star. Two small question what code is the right format to then save the xml files as xml with the same name again. I had a go ( trying format xlOpenXMLWorkbook) but it is saving them as a different format &

How do I reselect the original worksheet at the end? Is it just "ActiveWorkbook(FromBook).select"

Thanks