Visual Basic script

[Nocton]

I have a vbs file to copy a spreadsheet (.xls) file from Dropbox and then run it. All works well, but as one of the users does not have Excel, but uses OpenOffice Calc, I want to make sure that on all users PCs the file opens in Calc.
The lines:

Code: Select all

iRC = oShell.Run("""" & sFileName & """", 1, True)

Code: Select all

iRC = oShell.Run("""" & "C:\Program Files (x86)\OpenOffice 4\program\scalc.exe" & """", 1, True)

work OK, where sFilename is the xls file I wish to open.

However, I have not been able to find a way to combine them so that Calc opens and then loads sFileName

Does anyone have any suggestions?

[AllYourBase]

Code: Select all

iRC = oShell.Run("""" & sFileName & """", 1, True)

Surely the above will open the file using whatever program is associated with .xls files on the target PC? You shouldn't need to change anything.

[Itsallaguess]

Code: Select all

iRC = oShell.Run("""" & sFileName & """", 1, True)

Surely the above will open the file using whatever program is associated with .xls files on the target PC? You shouldn't need to change anything.

Yes, it will, but that's the problem...

Some people have Excel installed, but the OP wants a script that will always open the spreadsheet in OpenOffice Calc, even where Excel might be the associated program to .xls files...

The Excel users, presumably, also have OpenOffice Calc installed too and would like to maintain the normal association with .xls files in normal operation...

Cheers,

Itsallaguess

[Nocton]

Exactly right, Itsallaguess.
Calc's default is the Open Document Format .ods, but it can read and write to Microsoft's proprietary .xls format and that is the format the file to be opened is saved as. Normally there is no problem and now the later versions of Excel can read and write in ODF format, but occasionally with fancy formatting there are subtle changes when opened by different programs so I'd like to keep the Calc format, especially as the main editor of the file uses only Calc.

[Infrasonic]

Not the answer you're looking for but could you not save the spreadsheet in both .xls and open document formats (.ods .fods) so that the OO user(s) can file associate that way?

If there's no macros involved then using the free MS Office Online to open .xls from Dropbox would also work if it's a predominantly read only scenario.

[scotia]

How about using the FileExists Function to determine if OpenOffice scalc is present, and if so then use the version that specifies Openoffice scalc, otherwise default to the other version which will normally go for Excel. There are numerous examples on the web - look for VBScript FileExists

[Nocton]

I do not want to save in two different formats as then if one file in one format is changed, the other will not be changed. Since the Shell command can open Calc or the file in separate statements, it must be possible to specify Calc and the file to be opened in one statement.

[ReformedCharacter]

This works for me:

Dim objShell
Set objShell = WScript.CreateObject( "WScript.Shell" )
objShell.Run("""C:\Program Files (x86)\Microsoft Office\root\Office16\excel.exe """) & "c:\yourfolder\yourfile"
Set objShell = Nothing

I don't have Calc installed but I think the syntax is good.

A batch file might be easier

RC

[kiloran]

Haven't tried VB for this, but a command line:
"C:\Program Files\LibreOffice\program\scalc.exe" -o "C:\Users\User\Downloads\testfile.xlsx"

may help you. This is for LibreOffice but I'm sure the OpenOffice equivalent will be OK

--kiloran

[swill453]

If the main editor uses Calc and all the other users also have Calc installed and you want to force them to open it in Calc, then why on earth don't you just save and distribute it in .ods format?

You may have a reason for saving it in .xls format, but you haven't given it here.

Scott.

[StepOne]

If the main editor uses Calc and all the other users also have Calc installed and you want to force them to open it in Calc, then why on earth don't you just save and distribute it in .ods format?

You may have a reason for saving it in .xls format, but you haven't given it here.

Scott.

I'm not the OP but I'm guessing the spreadsheet comes in from an external source as .xls

[Nocton]

Thanks ReformedCharacter, that works. Nice and simple too. I knew it would not be complicated and gives complete control of how each user opens the file.

It does not/did not work with iRC = in front which was to help show the errors with

Code: Select all

Set oShell = CreateObject("WScript.Shell")

and

Code: Select all

Wscript.Quit iRC

at the end.

Instead it causes/shows an error.

[scotia]

If the main editor uses Calc and all the other users also have Calc installed and you want to force them to open it in Calc

I now see what was required - I mistakenly thought that some had calc, and others had excel, and some had both - with the requirement to use calc if present. Ignore my contribution with fileexists - I should read the requirement more carefully.