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.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

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
Back
Top