# Solved: VB Script IF Statement



## computerman29642 (Dec 4, 2007)

Does any one know if there is a way to have an if statement to open an Excel workbook if the name is one thing, and if not then open a different Excel workbook?


----------



## Rollin_Again (Sep 4, 2003)

Are we talking about a stand alone VBS script or a VBA macro? Can you explain further in detail how you want the script to work and what type of input is required by the script.

Regards,
Rollin


----------



## computerman29642 (Dec 4, 2007)

Here is my objective (not sure if should be done by Macro or Script)...

I would like for teh code to look for a certain filename. If that filename is found then this code will run. If that filename is not found, then the second code will run.

I can paste the code that I am using now, an dthe code that I have been playing around with if you like.


----------



## DoubleHelix (Dec 10, 2004)

http://www.devguru.com/Technologies/vbscript/QuickRef/filesystemobject_fileexists.html


----------



## computerman29642 (Dec 4, 2007)

Thanks DoubleHelix. 

I will take a look at the link you provided.


----------



## Rollin_Again (Sep 4, 2003)

If the provided link does not provide you with a solution please post your code 

Regards,
Rollin


----------



## computerman29642 (Dec 4, 2007)

I have been unable to get the code provided by th elink to work with the codes I have.

Here is a copy of the code that I have been playing around with...


```
Dim objExcel
Dim objWorkBook
Set objExcel = CreateObject("EXCEL.APPLICATION")
Set objWorkBook = objExcel.Workbooks.Open("C:\test.xls")
On Error Resume Next
objExcel.DisplayAlerts=False
REM objWorkBook.SaveAs "C:\test2.htm",44
objExcel.DisplayAlerts=True
objWorkBook.Close True
objExcel.Quit
Set objWorkBook = Nothing
Set objExcel = Nothing
```
Here is my original code...


```
@echo off

Rem ping -n 40 localhost > nul

set filepath=\\rcsrv01\Users\cgravlee\My Documents\Timesheets\

For /f "usebackq tokens=*" %%I in (`dir /b /a-d "%filepath%WT*.xls"^|^|Echo BT.xls`) do start /I excel.exe "%filepath%%%I"

Rem There are several special symbols used with commands:
Rem The Pipe |
Rem Command1 | Command2
Rem Sends the output from Command1 to Command2
Rem The combine symbol &
Rem Command1 & Command2
Rem Execute Command1 then execute Command2
Rem Conditional combine if successful
Rem Command1 && Command2
Rem Execute Command1 then only if Command1 was successful execute Command2
Rem Conditional combine if fails
Rem Command1 || Command2
Rem Execute Command1 then only if Command1 fails execute Command2

Rem So the For loop is doing this command :
Rem dir /b /a-d "%filepath%test*.xls" || Echo otherfile.xls

Rem The Echo command is executed only if the dir command fails (File not found is Rem considered a failure).

Rem As far as the caret, it's just the way the For command works, any | symbols have to be Rem written as ^|
```
Here is what I would like to happen. If the Excel workbook is Named "WT*.xls" then open the workbook. If the Excel workbook is named "BT.xls", I would like for the workbook to opened, renamed to "WT__K4 Cell entry_".


----------



## TheOutcaste (Aug 8, 2007)

Let's see if I follow all of what you want:


Check the filepath for file named *WT*.xls*
If present, open it.
If not present
make a copy of *BT.xls*
Rename this copy to *WT_K4 Cell entry.xls*
Open *WT_K4 Cell entry.xls*.

One question, will there ever be more than one file with a name that matches *WT*.xls*?
For example, if the *BT.xls* file is renamed to *WT_K4 Cell entry.xls* it will need to be deleted/renamed before this is run again, or it will be found.
If there could be more than one, how would you decide which to open? Creation date, modified date, alphabetical order?

If there is only one file named *WT*.xls*, this will do what you want:

```
Set filepath=\\rcsrv01\Users\cgravlee\My Documents\Timesheets\
Set pattern=WT*.xls
Set AltFileSource=BT.xls
Set AltFilename=WT_K4 Cell entry.xls
Set Filename=%AltFileSource%
For /F "usebackq tokens=*" %%I In (`dir /b /a-d "%filepath%%pattern%"`) Do Set Filename=%%I
If /I %Filename%==%AltFileSource% Copy /B "%filepath%%AltFileSource%" "%Filepath%%AltFilename%"&Set Filename=%AltFilename%
start /I excel.exe "%filepath%%Filename%
```
If *WT_K4 Cell entry.xls* exists, Copy will prompt to overwrite, so you may want to add the /Y switch to suppress prompting. You can also use Xcopy.

HTH

Jerry


----------



## DoubleHelix (Dec 10, 2004)

Looks like batch code, not VBScript.


----------



## computerman29642 (Dec 4, 2007)

Jerry,

I must apologize. I did not explain one one part of what I would like to happen very well. When the workbook is to be named, the name shoule not be "WT_K4 Cell entry.xls". What I actually meant was it would be "WT_" and whatever is in Cell K4 of the Excel workbook.


----------



## computerman29642 (Dec 4, 2007)

I found some code at the link below:

http://www.freevbcode.com/ShowCode.asp?ID=7530&NoBox=True

I have been playing around with the code, but I cannot get it to work properly.


```
Dim ExcelApp
Dim ExcelWorkbook
Dim ExcelSheet
Dim MyFileName
Dim FileCheck

MyFileName = "C:\WT_" + ExcelSheet.Cells(4, 11).Value + ".xls"

On Error Resume Next

Set ExcelApp = CreateObject("Excel.Application")

FileCheck = MyFileName
  If FileCheck = "WT_" + ExcelSheet.Cells(4, 11).Value + ".xls" Then

    Set ExcelWorkbook = ExcelApp.Workbooks.Open(MyFileName)
    ExcelWorkbook.Save
  Else

    Set ExcelWorkbook = ExcelApp.Workbooks.Open("C:\BT.xls")
    ExcelWorkbook.SaveAs "C:\WT_" + ExcelSheet.Cells(4, 11).Value + ".xls"
  End If

        ExcelWorkbook.Close
        ExcelApp.Quit
        Set ExcelApp = Nothing
        Set ExcelWorkbook = Nothing
        Set ExcelSheet = Nothing
```


----------



## computerman29642 (Dec 4, 2007)

TheOutcaste said:


> Let's see if I follow all of what you want:
> 
> 
> Check the filepath for file named *WT*.xls*
> ...


Jerry, is there a way to use this method, and rename the file using the weekending date?


----------



## TheOutcaste (Aug 8, 2007)

computerman29642 said:


> I found some code at the link below:
> 
> http://www.freevbcode.com/ShowCode.asp?ID=7530&NoBox=True
> 
> ...


Looks like you left out the actual check to see if the file exists:
* FileCheck = Dir$(MyFileName**)*
And if the value in K4 (ExcelSheet.Cells(4, 11).Value) is a Date _serial_ number and not actually a Date string, you may have to convert it to a string. And if it contains slashes (/) you'd have to remove them, as you can't have a slash as part of a file name.



computerman29642 said:


> Jerry, is there a way to use this method, and rename the file using the weekending date?


It would get quite complex to determine the weekending date in a batch file, but it can be done.
I'm not that familiar with Excel and writing macros as I use Quattro Pro.
You might want to ask in the Business Applications forum, they have some pretty good Excel Gurus in there. Probably be as easy for them to do this for Excel as for me to do this in Quattro Pro.

This would be easy to do with a startup macro in Quattro Pro. For example, if K4 has the week ending date, I would set K5 to be the actual file name generated from K4 (removing /'s and converting to a string WT2008-12-26.qpw for example), and put the following macro in cells A1-A5 on a sheet named Macros and name the first cell *_NBSTARTMACRO*:

```
{IF @FILEEXISTS(A:K5)}{BRANCH Macros:A3}
{Let Macros:A1,"{QUIT}"}{FileSaveAs @CELL("contents",A:K5)}{QUIT}
{FILEOPEN @CELL("contents",A:K5)}
{ACTIVATE "C:\BT.qpw (unmodified)"}
{FileClose}
```
This opens the file if it exists, if not it disables the startup macro and saves itself as the new file.

HTH

Jerry


----------



## computerman29642 (Dec 4, 2007)

Jerry, is it possible to modify the code below to close the Excel workbook?


```
set filepath=C:\

For /f "usebackq tokens=*" %%I in (`dir /b /a-d "%filepath%WT*.xls
"^|^|Echo BT.xls`) do start /I excel.exe "%filepath%%%I"
```


----------



## TheOutcaste (Aug 8, 2007)

Only thing you can do from a Command Prompt would be to use taskkill to kill the excel program, which would close all open workbooks. And if any of them have been changed, excel will prompt you to save/cancel.

This would be something to use a VB script or Autoit for, to select the window by name, save if needed, and then close.

Neither of which I'm very familiar with, so hopefully someelse will be able to jump in here.

Jerry


----------



## computerman29642 (Dec 4, 2007)

Thanks Jerry.


----------

