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.Fi
Set CurrentFolder = fso.GetFolder("D:\inventor
Set objExcel = CreateObject("Excel.Applic
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(Fi
boolFirst = true
Else
Set FromWorkbook = objExcel.Workbooks.Open(Fi
FromWorkbook.Sheets(1).Cop
FromWorkbook.Close
End If
End If
Next
ToWorkbook.SaveAs "d:\inventory\test.xlsx",5
ToWorkbook.Close False
Set ToWorkbook = Nothing
End Sub
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.Fi
Set CurrentFolder = fso.GetFolder("D:\inventor
Set objExcel = CreateObject("Excel.Applic
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(Fi
boolFirst = true
Else
Set FromWorkbook = objExcel.Workbooks.Open(Fi
FromWorkbook.Sheets(1).Cop
FromWorkbook.Close
End If
End If
Next
ToWorkbook.SaveAs "d:\inventory\test.xlsx",5
ToWorkbook.Close False
Set ToWorkbook = Nothing
End Sub
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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.
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.
You could rename the sheet before you copy it....
Rob.
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)
Rob.
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
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.