Save AS Script in VB

[Habeeb]

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

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

First I would try to open the files directly and not via your VBScript.

Second, 29GB is quite large. are the row and column limits exceeded (assuming Excel 2013 1,048,576 rows and 16,384 columns but less in some older versions.)?

Do you have 32 bit Excel or 64 bit. There is a 2GB memory limit on the 32 bit version.

Do you get any farther if, instead of opening it directly, you go to the "Get External Data" option on the Data tab? Specify commas as delimiters.
Could you use the "Get External Data" option to read the date more directly from your database.

Why do your CSV files have so much redundancy? 176,952 KB to 6,702 KB is a huge reduction. Would there be scope, either to export the files in a different way or, perhaps, to read them into, say, Access and re-export them from there?