Dear Friends,
I am an Oracle programmer, I have extracted some file in "CSV" format, there are some more then 50 files and these files are big. I need to write an automated process to save these files in Excel as the size is reduced and its easy for the users to read them.
I have copied a vbs script from the forum modified it to work for me.
Below is the Script and the issues I am having.
*************************************
Set app = CreateObject("Excel.Application")
app.Visible = False
app.DisplayAlerts = False
Set fso = CreateObject("Scripting.FileSystemObject")
For Each f In fso.GetFolder("N:\Oracle Documentation\MasterDocumentation\MATERIALIZED_VIEW\test").Files
WScript.Echo "The File extension is: " & fso.GetExtensionName(f)
WScript.Echo "The File extension is: " & fso.GetBaseName(f)
If LCase(fso.GetExtensionName(f)) = "csv" Then
WScript.Echo "The File extension Matched"
Set wb = app.Workbooks.Open(f.Path)
WScript.Echo "App opened"
wb.SaveAs "N:\Oracle Documentation\MasterDocumentation\MATERIALIZED_VIEW\test\" & fso.GetBaseName(f) & ".xls", -4143
WScript.Echo "Save AS done"
wb.Close SaveChanges=True
WScript.Echo "Close OK"
'wb.Save
'wb.Close True
End if
Next
app.Quit
Set app = Nothing
Set fso = Nothing
WScript.Echo "End of Script"
*************************************
there are five files in the "test" directory
File 1 size in CSV is: 176,952 KB (This works fine and it saves the file in Excel and the size is reduced to 6702 KB)
File 2 size in CSV is: 44,341 KB (This works fine and it saves the file in Excel and the size is reduced to 4265 KB)
All the below files error out as:
File 3 size in CSV is: 13875040 KB
File 4 size in CSV is: 8989044 KB
File 5 size in CSV is: 29795968 KB
Line: 13
Char: 3
Error: Microsoft Office Excel cannot access the file 'N:\Oracle Documentation\MasterDocumentation\MATERIALIZED_VIEW\test\file_name.csv' there are several possible reasons:
. The file name or path does not exist (its not true as the path is same for the other files where it works)
. The file is being used by another program (I closed Excel and made sure it is not being used)
. The workbook you are trying to save has the same name as a currently open workbook. ( No such file by this name exists)
Code: 800A03EC
Source: Microsoft Excel
Any help is appreciated
I am an Oracle programmer, I have extracted some file in "CSV" format, there are some more then 50 files and these files are big. I need to write an automated process to save these files in Excel as the size is reduced and its easy for the users to read them.
I have copied a vbs script from the forum modified it to work for me.
Below is the Script and the issues I am having.
*************************************
Set app = CreateObject("Excel.Application")
app.Visible = False
app.DisplayAlerts = False
Set fso = CreateObject("Scripting.FileSystemObject")
For Each f In fso.GetFolder("N:\Oracle Documentation\MasterDocumentation\MATERIALIZED_VIEW\test").Files
WScript.Echo "The File extension is: " & fso.GetExtensionName(f)
WScript.Echo "The File extension is: " & fso.GetBaseName(f)
If LCase(fso.GetExtensionName(f)) = "csv" Then
WScript.Echo "The File extension Matched"
Set wb = app.Workbooks.Open(f.Path)
WScript.Echo "App opened"
wb.SaveAs "N:\Oracle Documentation\MasterDocumentation\MATERIALIZED_VIEW\test\" & fso.GetBaseName(f) & ".xls", -4143
WScript.Echo "Save AS done"
wb.Close SaveChanges=True
WScript.Echo "Close OK"
'wb.Save
'wb.Close True
End if
Next
app.Quit
Set app = Nothing
Set fso = Nothing
WScript.Echo "End of Script"
*************************************
there are five files in the "test" directory
File 1 size in CSV is: 176,952 KB (This works fine and it saves the file in Excel and the size is reduced to 6702 KB)
File 2 size in CSV is: 44,341 KB (This works fine and it saves the file in Excel and the size is reduced to 4265 KB)
All the below files error out as:
File 3 size in CSV is: 13875040 KB
File 4 size in CSV is: 8989044 KB
File 5 size in CSV is: 29795968 KB
Line: 13
Char: 3
Error: Microsoft Office Excel cannot access the file 'N:\Oracle Documentation\MasterDocumentation\MATERIALIZED_VIEW\test\file_name.csv' there are several possible reasons:
. The file name or path does not exist (its not true as the path is same for the other files where it works)
. The file is being used by another program (I closed Excel and made sure it is not being used)
. The workbook you are trying to save has the same name as a currently open workbook. ( No such file by this name exists)
Code: 800A03EC
Source: Microsoft Excel
Any help is appreciated