Here is s snip of the code that I want to be able to enter a variable
into via a Input Box for the user to enter.

The piece I want to enter is the 20070904log.xls bit as this changes
on a daily basis.

ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-8],'N:\1._Document_Version_Control_Log
\Logs\20070904Log.xls'!DirName,1,FALSE)),""This is a new or modified
Dir "",""Not a new Dir"")"

So any (polite!) suggestions would be welcome.

Thank you

Mark

dim fName as string

fname = InputBox("Please enter the filename", "File Name", "log.xls")
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-8],'N:\1._Document_Version_Control_Log
\Logs\" & fname & "'!DirName,1,FALSE)),""This is a new or modified
Dir "",""Not a new Dir"")"

This does not do anything to check that what they entered is valid.

Peter Richardson
"(E-Mail Removed)" wrote:

> Here is s snip of the code that I want to be able to enter a variable
> into via a Input Box for the user to enter.
>
> The piece I want to enter is the 20070904log.xls bit as this changes
> on a daily basis.
>
> ActiveCell.FormulaR1C1 = _
> "=IF(ISNA(VLOOKUP(RC[-8],'N:\1._Document_Version_Control_Log
> \Logs\20070904Log.xls'!DirName,1,FALSE)),""This is a new or modified
> Dir "",""Not a new Dir"")"
>
> So any (polite!) suggestions would be welcome.
>
> Thank you
>
> Mark
>
>

Dim fInput As String
fInput = InputBox("Enter the file name", "Title Here")
If fInput = "" Or fInput = False Then Exit Sub
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-8],'N:\1._Document_Version_Control_Log
\Logs\" & fInput & ".xls'!DirName,1,FALSE)),""This is a new or
modified Dir "",""Not a new Dir"")"

etradeg...@hotmail.com wrote:
> Here is s snip of the code that I want to be able to enter a variable
> into via a Input Box for the user to enter.
>
> The piece I want to enter is the 20070904log.xls bit as this changes
> on a daily basis.
>
> ActiveCell.FormulaR1C1 = _
> "=IF(ISNA(VLOOKUP(RC[-8],'N:\1._Document_Version_Control_Log
> \Logs\20070904Log.xls'!DirName,1,FALSE)),""This is a new or modified
> Dir "",""Not a new Dir"")"
>
> So any (polite!) suggestions would be welcome.
>
> Thank you
>
> Mark

ACK! Peter beat me to it.

-Jeff-
JW wrote:
> Dim fInput As String
> fInput = InputBox("Enter the file name", "Title Here")
> If fInput = "" Or fInput = False Then Exit Sub
> ActiveCell.FormulaR1C1 = _
> "=IF(ISNA(VLOOKUP(RC[-8],'N:\1._Document_Version_Control_Log
> \Logs\" & fInput & ".xls'!DirName,1,FALSE)),""This is a new or
> modified Dir "",""Not a new Dir"")"
>
> etradeg...@hotmail.com wrote:
> > Here is s snip of the code that I want to be able to enter a variable
> > into via a Input Box for the user to enter.
> >
> > The piece I want to enter is the 20070904log.xls bit as this changes
> > on a daily basis.
> >
> > ActiveCell.FormulaR1C1 = _
> > "=IF(ISNA(VLOOKUP(RC[-8],'N:\1._Document_Version_Control_Log
> > \Logs\20070904Log.xls'!DirName,1,FALSE)),""This is a new or modified
> > Dir "",""Not a new Dir"")"
> >
> > So any (polite!) suggestions would be welcome.
> >
> > Thank you
> >
> > Mark

Thanks a ton!

only by a couple minutes. That's the time it took you to add the check of
the input .

"JW" wrote:

> ACK! Peter beat me to it.
>
> -Jeff-
> JW wrote:
> > Dim fInput As String
> > fInput = InputBox("Enter the file name", "Title Here")
> > If fInput = "" Or fInput = False Then Exit Sub
> > ActiveCell.FormulaR1C1 = _
> > "=IF(ISNA(VLOOKUP(RC[-8],'N:\1._Document_Version_Control_Log
> > \Logs\" & fInput & ".xls'!DirName,1,FALSE)),""This is a new or
> > modified Dir "",""Not a new Dir"")"
> >
> > etradeg...@hotmail.com wrote:
> > > Here is s snip of the code that I want to be able to enter a variable
> > > into via a Input Box for the user to enter.
> > >
> > > The piece I want to enter is the 20070904log.xls bit as this changes
> > > on a daily basis.
> > >
> > > ActiveCell.FormulaR1C1 = _
> > > "=IF(ISNA(VLOOKUP(RC[-8],'N:\1._Document_Version_Control_Log
> > > \Logs\20070904Log.xls'!DirName,1,FALSE)),""This is a new or modified
> > > Dir "",""Not a new Dir"")"
> > >
> > > So any (polite!) suggestions would be welcome.
> > >
> > > Thank you
> > >
> > > Mark
>
>

You could also try the following technique to get the filename (I prefer
this method, as it eliminates having to check the validity of the
filename):

Dim strFileName As String

strFileName = Application.GetOpenFilename(FileFilter:="Excel Files
(*.xls),*.xls", _
Title:="Select Document
Version Control Log")

This only gets the file name without actually opening the file. Just
remember that the value of strFileName will be "False" if the user
cancels out of the dialog box.
--
Regards,
Bill Renaud