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
ratliffjmAsked:
Who is Participating?
 
RobSampsonCommented:
P.S. themrrobert provided most of the code, so don't forgot to acknowledge that when accepting an answer.

Rob.
0
 
merowingerCommented:
Why don't use the Office File Converter? It's made for this cases ;)
http://blogs.msdn.com/dmahugh/archive/2007/02/09/converting-office-documents-to-open-xml.aspx
0
 
ratliffjmAuthor Commented:
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
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
themrrobertCommented:
How about causing the script to Sleep 20000
0
 
themrrobertCommented:

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

Open in new window

0
 
ratliffjmAuthor Commented:
When I run the code I get an error at Line: 5, Char:15, Error: Expected end of statement

Jeffrey
0
 
RobSampsonCommented:
Remove "as Integer" from line 5.

Rob.
0
 
ratliffjmAuthor Commented:
Hey Rob,

This time I am getting a different error. "Type mismatch: 'Sleep' at Line 14, Char 3. Code 800A000D

Thanks,

Jeffrey
0
 
RobSampsonCommented:
Change

  Sleep iSeconds * 1000 '20 * 1 second

to

  WScript.Sleep iSeconds * 1000 '20 * 1 second

Regards,

Rob.
0
 
ratliffjmAuthor Commented:
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
0
 
RobSampsonCommented:
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.
0
 
ratliffjmAuthor Commented:
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
0
 
RobSampsonCommented:
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.
0
 
ratliffjmAuthor Commented:
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

0
 
RobSampsonCommented:
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.
0
 
ratliffjmAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.