VBS Script Fails to run

Windows 2003 R2 Standard Member Server SP2
Excel 2010 SP1

a expert on here helped me with this script
from a command prompt  

c:\cscript c:\util\csvmerge.vbs

this error happens

csvmerge.vbs(23,17) Microsoft Excel Cannot rename a sheet to the same name as another sheet a referenced object library or a workbook referenced by visual basic

This same script ran great on my Windows 7 Pro computer where it was developed.

This Server runs a Inventory program that runs a script that generates 20 .cvs2 files

This script is suppose to place all the csv2 files into one spreadsheet and each on an individual sheet.

One Note I am having another issue installing Office SP2 on this server tring to fix that now
Dont know if this may be the issue.


  Dim FromWorkbook
    Dim ToWorkbook
    Dim fso
    Dim CurrentFolder
    Dim objExcel
    Dim boolFirst
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set CurrentFolder = fso.GetFolder("D:\inventory")
    Set objExcel = CreateObject("Excel.Application")
    ProcessFolder CurrentFolder

Sub ProcessFolder(ByVal Folder)
   Dim Files
   Set Files = Folder.Files
   Dim File
    For Each File In Files
        If right(File.Name,3) = "csv" Then
            If not boolfirst Then
                Set ToWorkbook = objExcel.Workbooks.Open(File)
                boolFirst = true
            Else
                Set FromWorkbook = objExcel.Workbooks.Open(File)
                FromWorkbook.Sheets(1).Copy ToWorkbook.Sheets(1)
                FromWorkbook.Close
            End If
        End If
    Next
    ToWorkbook.SaveAs "d:\inventory\test.xlsx",51
    ToWorkbook.Close False
    Set ToWorkbook = Nothing
End Sub
LVL 23
Thomas GrassiSystems AdministratorAsked:
Who is Participating?
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.

RobSampsonCommented:
Hi, I suspect that the copy of a sheet is trying to add a sheet of the same name that already exists in ToWorkbook.

Change this line:
                FromWorkbook.Sheets(1).Copy ToWorkbook.Sheets(1)

Open in new window


to this, to rename the target sheet before copying it over.
                For Each objSheet In ToWorkbook.Sheets
                	If LCase(objSheet.Name) = LCase(FromWorkbook.Sheets(1).Name) Then objSheet.Name = objSheet.Name & " (renamed)"
                Next
                FromWorkbook.Sheets(1).Copy ToWorkbook.Sheets(1)

Open in new window


Regards,

Rob.
0

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
Robberbaron (robr)Commented:
are you trying to run this as a scheduled task ?
if so, make sure you load a user profile..

make the changes RobS suggests and logon to the server as that user to check. I found Excel didnt always play nice. Ended up using the ADO linkages to make changes to a workbook.
0
Thomas GrassiSystems AdministratorAuthor Commented:
Rob

Worked

I have about 20 - 30 files to add to this spreadsheet.
I did run into an error but figured it out.
The file names are very long and excel sheets are restricted to 31 characters.
I have to reduce the file name down to less than 31 characters.
Need a script to do that .
Working on that now.
0
RobSampsonCommented:
You could rename the sheet before you copy it....
                For Each objSheet In FromWorkbook.Sheets
                	objSheet.Name = Left(objSheet.Name, 30)
                Next
                For Each objSheet In ToWorkbook.Sheets
                	If LCase(objSheet.Name) = LCase(FromWorkbook.Sheets(1).Name) Then objSheet.Name = Left(objSheet.Name, 20) & " (renamed)"
                Next
                FromWorkbook.Sheets(1).Copy ToWorkbook.Sheets(1)

Open in new window


Rob.
0
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.