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

clubcrowds

New Member
Joined
Feb 17, 2014
Messages
8
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.
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

shadow12345

Well-known Member
Joined
May 10, 2004
Messages
1,238
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
Code:
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

New Member
Joined
Feb 17, 2014
Messages
8
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

Well-known Member
Joined
May 10, 2004
Messages
1,238
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

Well-known Member
Joined
May 10, 2004
Messages
1,238
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.

Code:
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

New Member
Joined
Feb 17, 2014
Messages
8
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,518
Messages
5,602,138
Members
414,505
Latest member
quoctrungvu99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top