ratliffjm
asked on
VBSCRIPT to open .xls file and re-save as an .xlsx
I am working on a vbscript that will open the excel file and re-save it as a .xlsx file. The reason why I am doing this because the file is very large and saving it as a .xlsx reduces the sizes tremendously. My code works does create the file but when I try to open it I get an error saying that the converter failed. I am thinking that I am not giving excel enough time to open the .xls file(3,151) KB. It takes about 20 seconds before it will open. Is there a way to make sure the file is open before the re-save. My code is attached.
excel.txt
excel.txt
ASKER
Hello Merowinger,
This is part of a .bat file that I have created for a automated process. It was getting old submitting these programs and waiting for them to finish every morning.
Thanks,
Jeffrey
This is part of a .bat file that I have created for a automated process. It was getting old submitting these programs and waiting for them to finish every morning.
Thanks,
Jeffrey
How about causing the script to Sleep 20000
ednetmanFormatExcel "c:\All Sites_xls_08APR10.xls"
Sub ednetmanFormatExcel(ByVal vFilePath)
Dim xlApp
Dim iSeconds as Integer
iSeconds = 20
Set xlApp = CreateObject("excel.application")
With xlApp.Workbooks.Open(vFilePath) 'open file
With .ActiveSheet
'Set a freeze under column 1 so that the header is always present at the top
.Range("A7").Select
xlApp.ActiveWindow.FreezePanes = True
End With
Sleep iSeconds * 1000 '20 * 1 second
.SaveAs Left(vFilePath, Len(vFilePath) - 4) & ".xlsx", -4143 '-4143=xlWorkbookNormal
.Close True 'save and close
End With
xlApp.Quit
Set xlApp = Nothing
End Sub
ASKER
When I run the code I get an error at Line: 5, Char:15, Error: Expected end of statement
Jeffrey
Jeffrey
Remove "as Integer" from line 5.
Rob.
Rob.
ASKER
Hey Rob,
This time I am getting a different error. "Type mismatch: 'Sleep' at Line 14, Char 3. Code 800A000D
Thanks,
Jeffrey
This time I am getting a different error. "Type mismatch: 'Sleep' at Line 14, Char 3. Code 800A000D
Thanks,
Jeffrey
Change
Sleep iSeconds * 1000 '20 * 1 second
to
WScript.Sleep iSeconds * 1000 '20 * 1 second
Regards,
Rob.
Sleep iSeconds * 1000 '20 * 1 second
to
WScript.Sleep iSeconds * 1000 '20 * 1 second
Regards,
Rob.
ASKER
Hey Rob,
That sort of worked but the file that was created was corrupted. When I do this process manually I get a dialog box that reads
"This workbook contains one or more file format:
* Script objects
To save the workbook without these features, click OK.
To keep using these features, click Cancel, and then select a file format that supports the features."
I click 'OK' and the file conversion is initiated and completed.
I think my script is skipping this step. How do I click 'OK' in the script?
Regards
Jeffrey
That sort of worked but the file that was created was corrupted. When I do this process manually I get a dialog box that reads
"This workbook contains one or more file format:
* Script objects
To save the workbook without these features, click OK.
To keep using these features, click Cancel, and then select a file format that supports the features."
I click 'OK' and the file conversion is initiated and completed.
I think my script is skipping this step. How do I click 'OK' in the script?
Regards
Jeffrey
Try replacing these two lines:
.SaveAs Left(vFilePath, Len(vFilePath) - 4) & ".xlsx", -4143 '-4143=xlWorkbookNormal
.Close True 'save and close
with this
xlApp.DisplayAlerts = False
.SaveAs Left(vFilePath, Len(vFilePath) - 4) & ".xlsx", -4143 '-4143=xlWorkbookNormal
.Close True 'save and close
xlApp.DisplayAlerts = True
Regards,
Rob.
.SaveAs Left(vFilePath, Len(vFilePath) - 4) & ".xlsx", -4143 '-4143=xlWorkbookNormal
.Close True 'save and close
with this
xlApp.DisplayAlerts = False
.SaveAs Left(vFilePath, Len(vFilePath) - 4) & ".xlsx", -4143 '-4143=xlWorkbookNormal
.Close True 'save and close
xlApp.DisplayAlerts = True
Regards,
Rob.
ASKER
Hello Rob,
I am still getting that "File corrupt error..." I forgot to say that I when I do this step manually I change the "Save as type" to : Excel 2007 Workbook (*.xlsx). I think this maybe my problem.
This line
.SaveAs Left(vFilePath, Len(vFilePath) - 4) & ".xlsx", -4143 '-4143=xlWorkbookNormal
is creating a regular excel file which is Microsoft Office Excel Workbook (.xls)
Thanks,
Jeffrey
I am still getting that "File corrupt error..." I forgot to say that I when I do this step manually I change the "Save as type" to : Excel 2007 Workbook (*.xlsx). I think this maybe my problem.
This line
.SaveAs Left(vFilePath, Len(vFilePath) - 4) & ".xlsx", -4143 '-4143=xlWorkbookNormal
is creating a regular excel file which is Microsoft Office Excel Workbook (.xls)
Thanks,
Jeffrey
You must be using Excel 2003 for this, is that right?
If so, according to this:
http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/f305f427-af64-4d62-85b8-e0395161eadf/
You need to use the FileFormat of 51:
.SaveAs Left(vFilePath, Len(vFilePath) - 4) & ".xlsx", 51
Give that a try.
Regards,
Rob.
If so, according to this:
http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/f305f427-af64-4d62-85b8-e0395161eadf/
You need to use the FileFormat of 51:
.SaveAs Left(vFilePath, Len(vFilePath) - 4) & ".xlsx", 51
Give that a try.
Regards,
Rob.
ASKER
Hey Rob,
That works GREAT!!! Awesome stuff man! I got one piece to ask you about. At the beginning of my script I am bringing in my excel file this way.
ednetmanFormatExcel "c:\All Sites_xls_08APR10.xls"
In the program that writes out the excel file everyday the excel file "All sites_xls_08APR10.xls" has the date tacked on with a macro for the date that it was run.
Is there a way for the script to update the date part at the time it was run in the format (e.g. "08APR10") ?
The hope would be to make the script to be more dynamic so I would not have to actually edit the file in the script.
Regards,
Jeffrey
That works GREAT!!! Awesome stuff man! I got one piece to ask you about. At the beginning of my script I am bringing in my excel file this way.
ednetmanFormatExcel "c:\All Sites_xls_08APR10.xls"
In the program that writes out the excel file everyday the excel file "All sites_xls_08APR10.xls" has the date tacked on with a macro for the date that it was run.
Is there a way for the script to update the date part at the time it was run in the format (e.g. "08APR10") ?
The hope would be to make the script to be more dynamic so I would not have to actually edit the file in the script.
Regards,
Jeffrey
Sure, if you change this
ednetmanFormatExcel "c:\All Sites_xls_08APR10.xls"
to
strDate = Right("0" & Day(Date), 2) & MonthName(Month(Date), True) & Right(Year(Date), 2)
MsgBox "c:\All Sites_xls_" & strDate & ".xls"
It should be fine.
Regards,
Rob.
ednetmanFormatExcel "c:\All Sites_xls_08APR10.xls"
to
strDate = Right("0" & Day(Date), 2) & MonthName(Month(Date), True) & Right(Year(Date), 2)
MsgBox "c:\All Sites_xls_" & strDate & ".xls"
It should be fine.
Regards,
Rob.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
BIG "Thanks" goes out to themrobert who edited most of the code. Rob step in supplied some improvements that was instrumental to getting the report fully automated. Mainly Thanks!!
Jeffrey
Jeffrey
http://blogs.msdn.com/dmahugh/archive/2007/02/09/converting-office-documents-to-open-xml.aspx