recurring process

malcolmUK · Feb 17th 2021

Hi all,

New to the board and posting in general so apologies if I get anything wrong

It's probably easier if I explain from scratch.

I download a report each day in xml format, import this to excel then run vba code to split the data into batches of 26. I then move onto next report repeating the same process. The problem I have is I need to paste the code into excel each time I open a new XML file. Is there a way around this?

Any help appreciated. The code I use is the following:

Code

Sub test()
Dim iCalc As Long, i As Long
iCalc = Application.Calculation
With Application
.Calculation = xlManual
.ScreenUpdating = False
.EnableEvents = False
End With
With ThisWorkbook.ActiveSheet
For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row Step 26
Range(.Range("A1:H1").Address(0, 0) & "," & .Range(Cells(i, "A"), Cells(i, "H").Resize(30)).Address(0, 0)).Copy
Workbooks.Add
Range("A1").PasteSpecial xlPasteValues
With ActiveWorkbook
.SaveAs Filename:="C:\data\" & i & ".xlsx"
.Close
End With
Next i
End With
With Application
.Calculation = iCalc
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

Display More

**Carim** · Feb 17th 2021

Hello and Welcome to the Forum

The code you have posted does not deal with how to import the xml file you have downloaded ...

So, it could be you are looking for a simple macro :

Code

Sub ImportXML()
Dim xmFile As String
Application.DisplayAlerts = False
xmFile = "D:\test.xml"
Workbooks.OpenXML Filename:=xmFile, LoadOption:=xlXmlLoadImportToList
Application.DisplayAlerts = True
End Sub

or are you looking for a macro which will loop all the xml files you do download everyday ... to import all of them ...???

Hope this will help

**royUK** · Feb 17th 2021

You don't need ThisWorkBook before ActiveSheet. Just ActiveSheet will be sufficient, the code witll then run on any sheet that is active. In which case you could have a workbook stored with the code and run it on any sheet.

The best way though is to save the workbook as an excel addin

malcolmUK · Mar 2nd 2021

quick question regarding naming of the files: .SaveAs Filename:="C:\data\" & i & ".xlsx" is renaming them as 2, 28, 54 etc etc - is it possible to manipulate this so it comes out as batch01,02,03 etc?

**Carim** · Mar 2nd 2021

Hello,

If your question is about inserting the string " batch " ...

Code

.SaveAs Filename:="C:\data\" & "batch " & i & ".xlsx"

Hope this will help

malcolmUK · Mar 2nd 2021

the output thats being created is 2.xlsx, 28.xslx etc etc I would like to keep it simply as batch01.xlsx, batch02.xlsx

**Carim** · Mar 2nd 2021

OK ... without your macro ... my guess would be

Code

Dim j As Long
j = 1
.SaveAs Filename:="C:\data\" & "batch " & Format(j, "00") & ".xlsx"
j = j + 1

Hope this will help

recurring process

Share

Similar Threads

Insert X Rows To Table & Increment Date In Cell