Create and name multiple Excel files

I have a folder of Excel files.  They were created in an earlier version of Excel (2007). Without getting into all of the details, they need to be recreated in a newer version of Excel (2010).  For everything that is going to need to be done, it is going to be a big, multi-step process that I'll have to do in stages. However, as a start, what I need is the following:

1. There are approximately 50 Excel files in MyFolder on my Desktop. They were all created in Excel 2007 and obviously all have different names. They are macro-enabled.
2.  Using an existing blank Excel file that was created in Excel 2010, I need to make 50 copies of that Excel 2010 file and rename (or name) and save each one into another folder using the same exact names as the 2007 versions.

So, all I'm trying to do is make copies of the Excel 2007 versions in Excel 2010.  I could obviously just do it one by one but I have multiple folders with multiple files that have this issue, so it would take a very long time.  I'm hoping someone could show me a routine to accomplish this.  Thank you.
dbfromnewjerseyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
I'm not sure what you are trying to achieve here as excel file 2007 is also .xlsx and 2010 file as well is .xlsx

And for macro even for 2007 is .xlsm and for 2010 is also .xlsm ..

Now when you open it in 2010 its already compatible for 2010 and has converted over from 2007..Now if you can help me understand what you are trying to do..may be i can add..

Saurabh...
dbfromnewjerseyAuthor Commented:
The existing Excel files are used with an Oracle system. They are known as "templates".  They have a bunch of macros included with them.  It is a known problem that they do not work properly with Excel 2010.  The solution therefore appears to be to recreate the "blank" templates in Excel 2010 and then copy the data from the 2007 files to the 2010 files.   This has been tested by me already on a couple of files and appears to work and to be the only solution. That's why I mentioned in my first post about not getting into all of the details because it is a complicated problem that requires a lot of explaining.   Therefore, all I'm trying to do is create a bunch of "blank" Excel 2010 formatted  "templates" (which by the way are generated by an Oracle system) and then rename them using the same names as the 2007 versions.
dbfromnewjerseyAuthor Commented:
Clarification:  The plan is to generate one "blank" formatted template from Oracle and make multiple copies of that one and rename each one accordingly.
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Saurabh Singh TeotiaCommented:
So what i understand this is what you want to do..You got excel 2010 templates which you just exported from orcale..Now you want them to rename as excel 2007 templates same what you had earlier with the same name?? Is this what you want to do??

Now assuming you want to do this mapping..Do you have a template of file in place which says what is the old name and what will be the new name??

Saurabh..
dbfromnewjerseyAuthor Commented:
This is what I want step by step:

1.  the Oracle system generates a single preformatted Excel template. The template contains macros, column headers, etc. but no actual production data.
2. Because my system has Excel 2010 installed, the template referred to above gets created in Excel 2010.
3. I have a folder of about 50 existing production templates that were created years ago in Excel 2007.
4. I want to make multiple copies of the Excel 2010 template I created; one for each existing Excel 2007 template and give each of the 2010 templates the same name as the 2007 templates (but put them in a different folder because I need to keep the 2007 templates).
5. Once I have all of the "blank" templates created with the same names, I will then perform a copy routine to copy the data from each 2007 template to the 2010 template with the same name. But the copy routine is portion is not being asked here. All I'm asking for is how to create multiple copies of the 2010 template I generated in Oracle and then name and save them to match the 2007 names.
dbfromnewjerseyAuthor Commented:
To illustrate:  Let's say I have a folder named MY2007files and it contains the following five Excel 2007 files:

File1
File2
File3
File4
File5

Let's say I then generated an Excel 2010 template.  It currently has the generic name of Template1.   I want a routine that will look in the My2007files folder and for each file in there, make a copy of the 2010 Template, rename it to match the 2007 file names and place the new files in a different folder; say My2010files.

BEFORE:

My2007files
File1
File2
File3
File4
File5

My2010files
Template1

AFTER:

My2007files
File1
File2
File3
File4
File5

My2010files
File1
File2
File3
File4
File5
Saurabh Singh TeotiaCommented:
Use this code it will do what you are looking for...

Sub movemydata()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Dim wb As Workbook
    Dim wb1 As Workbook
    Dim xpath As String, ypath As String
    Dim ws As Worksheet, fname As String

    xpath = "Your path here where 2010 template is"
    If Right(xpath, 1) <> "\" Then xpath = xpath & "\"



    ypath = "Your path here where 2007 file is"
    If Right(ypath, 1) <> "\" Then ypath = ypath & "\"

    fname = Dir(ypath & "*.xls*")

    Do Until fname = ""
        Set wb = Workbooks.Open(xpath & "Template.xlsx")

        Set wb1 = Workbooks.Open(ypath & fname)


        For Each ws In wb1.Worksheets

            ws.Move after:=wb.Sheets(wb.Sheets.Count)

        Next ws

        wb.SaveAs Filename:=xpath & fname
        wb.Close (True)
        
fname=Dir()

    Loop

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True


End Sub

Open in new window


Saurabh...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.