Link to home
Start Free TrialLog in
Avatar of ratliffjm
ratliffjmFlag for United States of America

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
Avatar of merowinger
merowinger
Flag of Germany image

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
Avatar of ratliffjm

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
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

Open in new window

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

Jeffrey
Remove "as Integer" from line 5.

Rob.
Hey Rob,

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.
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
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.
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
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.
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

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.
ASKER CERTIFIED SOLUTION
Avatar of RobSampson
RobSampson
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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