Avatar of RWayneH
RWayneH
Flag 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?
ProgrammingVB ScriptMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
RWayneH

8/22/2022 - Mon
byundt

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

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

DisplayAlerts is a property of the Application object, not an object in and of itself. Try it without the Set.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
RWayneH

ASKER
It does not look like I need this for the Workbooks.Open, but do need it for the SaveAs
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

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Bill Prew

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
byundt

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
byundt

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
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. 
Your help has saved me hundreds of hours of internet surfing.
fblack61