Avatar of Member_2_6492660_1
Member_2_6492660_1Flag for United States of America asked on

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
Microsoft ExcelVB ScriptShell Scripting

Avatar of undefined
Last Comment
RobSampson

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
RobSampson

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Robberbaron (robr)

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.
ASKER
Member_2_6492660_1

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.
RobSampson

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes