recurring process

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

  • 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
    1. Sub ImportXML()
    2. Dim xmFile As String
    3. Application.DisplayAlerts = False
    4. xmFile = "D:\test.xml"
    5. Workbooks.OpenXML Filename:=xmFile, LoadOption:=xlXmlLoadImportToList
    6. Application.DisplayAlerts = True
    7. 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

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • 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

  • 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?

  • Hello,


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


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


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

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


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


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)