VBS script to make a copy of spreadsheet to a new spreadsheet

Posted on 2007-10-19
Last Modified: 2012-06-27
I need a VBS script that will copy all worksheets in a spreadsheet to a new spreadsheet.  Save As does not work and neither does copy and paste.

I have the following VBS code taken from which does everything I want except it only copies one spreadsheet and you have to know the name of the spreadsheet.

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\OldFile.xls")
Set objWorksheet = objWorkbook.Worksheets("Sheet1")


Set objNewWorkbook = objExcel.Workbooks(2)

I need code that will do all worksheets and without knowing the names of the spreadsheets.

Thanks in advance.
Question by:drunkennoodle
    LVL 92

    Expert Comment

    by:Patrick Matthews
    drunkennoodle said:
    >>Save As does not work

    Why not?

    Author Comment

    To clarify I'm looking for VB script.  I know I can do a Save As or a copy and paste but that is not the resolution I'm looking for.
    LVL 92

    Expert Comment

    by:Patrick Matthews
    drunkennoodle said:
    >> I know I can do a Save As or a copy and paste but that is not the resolution I'm looking for.

    Why not?  What could be simpler than:

    Dim xlApp, xlWb

    Set xlApp = CreateObject("Excel.Application")
    Set xlWb = xlApp.Workbooks.Open("C:\Scripts\OldFile.xls")
    xlWb.SaveAs "c:\Scripts\NewFile.xls"

    Set xlWb = Nothing
    Set xlApp = Nothing

    Author Comment

    For a specific file the File->Save is not working for Excel 2007 in compatablity mode.  File-Save As .xlsx does work but that file is not able to Save As .xls.  When I do try to save Excel will crash.  This happens on multiple computers that are fully patched.  This is not an issue with other files.  

    I was thinking that building a new spreadsheet and coping the worksheets over would fix the problem.

    I ran the code and get Windows Script Host error The server threw an exception with code 80010105.

    Accepted Solution

    I fixed the corrupted spreadsheet by going to help and typing corrupt and followed these steps

    Click the Microsoft Office Button , and then click Open.
    In the Open dialog box, select the corrupted workbook that you want to open.
    Click the arrow next to the Open button, and then click Open and Repair.
    Do one of the following:
    To recover as much of the workbook data as possible, click Repair.

    Thanks for your help.
    LVL 80

    Expert Comment

    Points (500) refunded because you solved the problem yourself and posted the method used.

    byundt--Microsoft Excel Page Editor

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now