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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
themrrobertCommented:
How about causing the script to Sleep 20000
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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

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

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

Rob.
ratliffjmAuthor Commented:
Hey Rob,

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

Thanks,

Jeffrey
RobSampsonCommented:
Change

  Sleep iSeconds * 1000 '20 * 1 second

to

  WScript.Sleep iSeconds * 1000 '20 * 1 second

Regards,

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

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

Rob.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows OS

From novice to tech pro — start learning today.