Link to home
Start Free TrialLog in
Avatar of RWayneH
RWayneHFlag for United States of America

asked on

VBScript Open file.

Set objWorkbook = objExcel.Workbooks.Open "C:\Temp\"&oNewest.Name

Is returning a syntax error...  if I have an Excel file name stored in oNewest.Name  how do I tell VBScript to open this file?
Avatar of byundt
byundt
Flag of United States of America image

Does adding parentheses help? They are required in VBA, so I am guessing perhaps VBScript as well.
Set objWorkbook = objExcel.Workbooks.Open("C:\Temp\"&oNewest.Name)

Open in new window

Avatar of RWayneH

ASKER

Sorry, trying:   Set objExcel.DisplayAlerts = False   for file open and SaveAs, because the file may already exist, if it does, I need to suppress this.  Tried: Set objWorkbook.DisplayAlerts = False that failed too.  Any ideas?
DisplayAlerts is a property of the Application object, not an object in and of itself. Try it without the Set.
Avatar of RWayneH

ASKER

It does not look like I need this for the Workbooks.Open, but do need it for the SaveAs
Avatar of RWayneH

ASKER

 
Not so sure the the .Workbook.Open is working correctly, because am getting a popup, after the file is saved, which is the whole point of the SaveAs...  popup is: The file format and extension of  <filename> dont match.  The file could be corrupted or unsafe.  Unles you trust its source, dont open it.  Do you want to open it anyway?  The SaveAs is just changing the filename, but not saving it as an Excel format..  so end users that need to use the file, also get the file open popup warning...   I had this working before, but now it is not..  once the file is open, I could use some vba to do the SaveAs to get the proper Excel format, but thought it would be better to do it all in the same process.

Using:
Set objExcel = CreateObject("Excel.Application")
    objExcel.Application.DisplayAlerts = False
    Set objWorkbook = objExcel.Workbooks.Open("C:\Temp\"&oNewest.Name)
    objExcel.Application.Visible = True
    objExcel.Activeworkbook.SaveAs FileSaveLocationAndNameFirmed
    objExcel.Activeworkbook.Close

Avatar of Bill Prew
Bill Prew

What's the extension of the file you are reading, and what's the extension of the file you are saving it as?

»bp
ASKER CERTIFIED SOLUTION
Avatar of Bill Prew
Bill Prew

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
SaveAs will work nicely if you change the filename but not the file extension. That said, the SaveAs method will gladly save a .xlsb file with .xlsx file extension. And you should expect an error upon opening such a file in Excel, because the file was saved with the same file format as originally implied by the .xlsb file extension. To avoid such issues, you should always specify both extension and file format when using SaveAs.

If you are running VBA in Excel, you can use enums like xlOpenXMLWorkbookMacroEnabled. Otherwise, you should use numbers like 51 through 56. Microsoft help doesn't make it easy to find those numbers, so I refer to Microsoft Excel MVP Ron de Bruin's webpage https://www.rondebruin.nl/win/s5/win001.htm
Bill Prew,
That's a great link to Microsoft documentation. That page didn't use to include the file extension column, so I never used it.

Brad
Avatar of RWayneH

ASKER

Hello, I see that I left this one out there not Closed...  Update is that the solution is working great.. we have a webservice that has a "Save as Excel" button in it, but this save does save it (downloads to default browser download location).  Then I move the file to and save it to a SharePoint location, but when others went to go use the file, Excel would popup a message box prior to the file opening saying that it was not an Excel format and it had to be resaved...  

To get around this I used, VBScript in UFTO, to open the file, (newest file in a gaving folder, rename it, add a few things, sheet name etc.) then resave the file to the SharePoint location, as a true Excel file, so user did not get the message popup about format mismatch...  This was actually so another process that uses PowerBI, can use the file, with the format mismatch PowerBI was having issues using the file.

End of day, using VBScript in UFTO, (MicroFocus Unified Functional Testing One) to allowed me to do in that what I could not get to work in Excel.  Thanks for all the comments and help and sorry for not closing the question way back ago.