Finding the excel location in batch file

Hi guys,
I'm attempting to automate a certain process using rvtools and automated excel filtering.
Running rvtools through batch files is easy enough, and the Macro that filters out the needed content is also ready.

When I ran my batch file on my computer, it ran without a problem and worked perfectly. when I tried to run it from a network location but from a different computer, it became apparent that different versions of office are stored on different location on the local computer, not to mention, the fact that some versions are 32 bit and others are 64 bit.

The script currently looks like this (with identifiable information omitted of course):

@echo off
rem #########################
rem Name <<<>>> Machine reporter
rem By David Sankovsky
rem Date August 2015
rem Version 3.0
rem #########################
rem =====================================
rem Include robware/rvtools in searchpath
rem =====================================
set path=%path%;c:\program files (x86)\robware\rvtools
rem =========================
rem Set environment variables
rem =========================
set $VCServer=<<<>>>
set $AttachmentDir=\\<<<>>>\System\Support\SUPPORT\Reports
set $AttachmentFile=<<<>>>.xls
@echo Deleting previous Files...
del \\gns-dc\System\Support\SUPPORT\Reports\<<<>>>* /f/q
@echo connecting to <<<>>>
@echo Report will be saved to \\<<<>>>\System\Support\SUPPORT\Reports
@echo generating RAW report
rem ===================
rem Start RVTools batch 
rem ===================
rvtools.exe -passthroughAuth -s %$VCServer% -c Exportvinfo2xls -d %$AttachmentDir% -f %$AttachmentFile%
@echo Done.
@echo Filtering for <<<>>>
cd C:\Program Files\Microsoft Office 15\root\office15
EXCEL.EXE /e "\\<<<>>>\System\Support\SUPPORT\Reports\script.xlsm"
@echo all Done. file save in %$AttachmentDir% 
@echo file name is <<<>>>.xls
pause

Open in new window


to make it run on another location, I had to change the path to excel to cd C:\Program Files\Microsoft Office\Office15
I don't won't to create many versions of the script, but rather to have something added that will first try the first location, and if it can't find excel.exe it'll move to the next location on the list (assume I might have more than 2 options)

Can you help me with that?
LVL 8
David SankovskySenior SysAdminAsked:
Who is Participating?
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.

oBdACommented:
Easiest possibility: try to replace lines 29 and 30 with this:
start "" /wait "\\<<<>>>\System\Support\SUPPORT\Reports\script.xlsm"

Open in new window

If this doesn't work for you, replace lines 29 and and 30 with the part below. It checks the file type for the program associated with it and uses the respective command line:
set ExcelFile=\\<<<>>>\System\Support\SUPPORT\Reports\script.xlsm
set ExcelFileApp=
for %%a in ("%Excelfile%") do set ExcelFileExt=%%~xa
for /f "tokens=1* delims==" %%a in ('assoc %ExcelFileExt%') do set ExcelFileAssoc=%%b
for /f "tokens=1* delims==" %%a in ('ftype %ExcelFileAssoc%') do set ExcelFileApp=%%b
if not defined ExcelFileApp (
	echo Unable to identify an application associated with '%ExcelFileExt%'.
	pause
	exit /b 1
)
echo Excel file type identified as '%ExcelFileAssoc%', will open with '%ExcelFileApp%'
%ExcelFileApp% "%ExcelFile%"

Open in new window

0
oBdACommented:
Just noticed that on some systems, the association for xlsm has "/dde" as default argument, while others have "/e".
If that is an issue for you, here's another possibility.
As before, replace lines 29 and 30:
set ExcelPath=
for /f "tokens=2*" %%a in ('reg.exe query "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\excel.exe" ^| find.exe /i "Path"') do set ExcelPath=%%b
if not defined ExcelPath (
	echo Unable to identify the Excel installation path.
	pause
	exit /b 1
)
if not "%ExcelPath:~-1%"=="\" set ExcelPath=%ExcelPath%\
echo Path to excel.exe: %ExcelPath%
"%ExcelPath%excel.exe" /e "\\<<<>>>\System\Support\SUPPORT\Reports\script.xlsm"

Open in new window

0
David SankovskySenior SysAdminAuthor Commented:
Basically the script works on my pc if I use that last solution, however is there a way to test which default argument it uses? or if not try the first and then fallback on the other?

Currently, My script looks like this:
@echo off
rem #########################
rem Name <<<>>> Machine reporter
rem By David Sankovsky
rem Date August 2015
rem Version 3.0
rem #########################
rem =====================================
rem Include robware/rvtools in searchpath
rem =====================================
set path=%path%;c:\program files (x86)\robware\rvtools
rem =========================
rem Set environment variables
rem =========================
set $VCServer=<<<>>>
set $AttachmentDir=\\<<<>>>\System\Support\SUPPORT\Reports
set $AttachmentFile=<<<>>>raw.xls
set ExcelFile=\\<<<>>>\System\Support\SUPPORT\Reports\script.xlsm
set ExcelFileApp=
for %%a in ("%Excelfile%") do set ExcelFileExt=%%~xa
for /f "tokens=1* delims==" %%a in ('assoc %ExcelFileExt%') do set ExcelFileAssoc=%%b
for /f "tokens=1* delims==" %%a in ('ftype %ExcelFileAssoc%') do set ExcelFileApp=%%b
if not defined ExcelFileApp (
	echo Unable to identify an application associated with '%ExcelFileExt%'.
	pause
	exit /b 1
)
echo Excel file type identified as '%ExcelFileAssoc%', will open with '%ExcelFileApp%'
@echo Deleting previous Files...
del \\<<<>>>\System\Support\SUPPORT\Reports\<<<>>>* /f/q
@echo connecting to <<<>>>
@echo Report will be saved to \\<<<>>>\System\Support\SUPPORT\Reports
@echo generating RAW report
rem ===================
rem Start RVTools batch 
rem ===================
rvtools.exe -passthroughAuth -s %$VCServer% -c Exportvinfo2xls -d %$AttachmentDir% -f %$AttachmentFile%
@echo Done.
@echo Filtering for <<<>>>. Excel Script will run Autmatically.
%ExcelFileApp% "%ExcelFile%"
@echo all Done. file save in %$AttachmentDir% 
@echo file name is <<<>>>.xls
pause

Open in new window

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

oBdACommented:
Yes, i just noticed that issue with the default arguments; see if my latest comment at http:#a40924077 helps.
0
Bill PrewCommented:
You could of course brute force it and just find the excel.ese file yourself...

for %%A in ("%ProgramFiles%","%ProgramFiles(x86)%") do (if exist "%%~A\" (for /f "tokens=*" %%B in ('dir /b /a-d /s "%%~A\excel.exe" 2^>NUL') do (set ExcelExe=%%~B)))
echo %ExcelExe%

Open in new window

~bp
0
David SankovskySenior SysAdminAuthor Commented:
Odd, I tried Adapting your script into mine, It now alerts me for an incorrect syntax..
What am I missing?

@echo off
rem #########################
rem Name <<<>>> Machine reporter
rem By David Sankovsky
rem Date August 2015
rem Version 3.0
rem #########################
rem =====================================
rem Include robware/rvtools in searchpath
rem =====================================
set path=%path%;c:\program files (x86)\robware\rvtools
rem =========================
rem Set environment variables
rem =========================
set $VCServer=<<<>>>
set $AttachmentDir=\\<<<>>>\System\Support\SUPPORT\Reports
set $AttachmentFile=<<<>>>raw.xls
set ExcelFile=\\<<<>>>\System\Support\SUPPORT\Reports\script.xlsm
set ExcelFileApp=
for %%a in ("%Excelfile%") do set ExcelFileExt=%%~xa
for /f "tokens=1* delims==" %%a in ('assoc %ExcelFileExt%') do set ExcelFileAssoc=%%b
for /f "tokens=1* delims==" %%a in ('ftype %ExcelFileAssoc%') do set ExcelFileApp=%%b
if not defined ExcelFileApp (
	echo Unable to identify an application associated with '%ExcelFileExt%'.
	pause
	exit /b 1
)
if not "%ExcelPath:~-1%"=="\" set ExcelPath=%ExcelPath%\
@echo Path to excel.exe: %ExcelPath%
@echo Deleting previous Files...
del \\<<<>>>\System\Support\SUPPORT\Reports\<<<>>>* /f/q
@echo connecting to <<<>>>
@echo Report will be saved to \\<<<>>>\System\Support\SUPPORT\Reports
@echo generating RAW report
rem ===================
rem Start RVTools batch 
rem ===================
rvtools.exe -passthroughAuth -s %$VCServer% -c Exportvinfo2xls -d %$AttachmentDir% -f %$AttachmentFile%
@echo Done.
@echo Filtering for <<<>>>. Excel Script will run Autmatically.
%ExcelPath%excel.exe /e \\<<<>>>\System\Support\SUPPORT\Reports\script.xlsm
@echo all Done. file save in %$AttachmentDir% 
@echo file name is <<<>>>filtered.xls
pause

Open in new window

0
oBdACommented:
Seems like you mixed the scripts, and the excel start requires double quotes because the path contains spaces.
This should work:
@echo off
rem #########################
rem Name <<<>>> Machine reporter
rem By David Sankovsky
rem Date August 2015
rem Version 3.0
rem #########################
rem =====================================
rem Include robware/rvtools in searchpath
rem =====================================
set path=%path%;c:\program files (x86)\robware\rvtools
rem =========================
rem Set environment variables
rem =========================
set $VCServer=<<<>>>
set $AttachmentDir=\\<<<>>>\System\Support\SUPPORT\Reports
set $AttachmentFile=<<<>>>raw.xls
set ExcelFile=\\<<<>>>\System\Support\SUPPORT\Reports\script.xlsm
set ExcelPath=
for /f "tokens=2*" %%a in ('reg.exe query "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\excel.exe" ^| find.exe /i "Path"') do set ExcelPath=%%b
if not defined ExcelPath (
	echo Unable to identify the Excel installation path.
	pause
	exit /b 1
)
if not "%ExcelPath:~-1%"=="\" set ExcelPath=%ExcelPath%\
@echo Path to excel.exe: %ExcelPath%
@echo Deleting previous Files...
del \\<<<>>>\System\Support\SUPPORT\Reports\<<<>>>* /f/q
@echo connecting to <<<>>>
@echo Report will be saved to \\<<<>>>\System\Support\SUPPORT\Reports
@echo generating RAW report
rem ===================
rem Start RVTools batch 
rem ===================
rvtools.exe -passthroughAuth -s %$VCServer% -c Exportvinfo2xls -d %$AttachmentDir% -f %$AttachmentFile%
@echo Done.
@echo Filtering for <<<>>>. Excel Script will run Autmatically.
"%ExcelPath%excel.exe" /e "%ExcelFile%"
@echo all Done. file save in %$AttachmentDir% 
@echo file name is <<<>>>filtered.xls
pause

Open in new window

0

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
David SankovskySenior SysAdminAuthor Commented:
Works Great! Thanks a lot for all your help!
0
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
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.