"Andibevan" <andibevan@remove-to-prevent-spam-hotmail.com> wrote in message news:OH75ynpZFHA.3220@TK2MSFTNGP14.phx.gbl...
> Hi All,
>
> I have written the following code to open the most recent file in a
> directory.
>
> It seems to be getting stuck on Set xlApp = Excel.Application
>
> It says the variable is undefined - I have done quite a bit of coding in VBA
> but am just starting in VB.
>
> Any help would be really appreciated.
>
> Ta
>
> Andi
>
>
> Option Explicit
>
> 'Finds the most recent file in a directory
>
> 'Sub Most_Recent_File()
>
> Dim fs, f, f1, f2
>
> 'Dim vDT As Date
>
> Dim fldr
>
> 'Dim vFName As String
>
> Dim vDT, vFName
>
> Dim Val_RECENT, Val_Open
>
> Dim xlApp
>
> fldr = "S:\SmartMarket\SMV Project Administration\Active Logs\Project Log\"
>
> 'fldr = File_Val
>
> Set fs = CreateObject("Scripting.FileSystemObject")
>
> Set f = fs.getfolder(fldr)
>
> Set f1 = f.Files
>
> For Each f2 In f1
>
> If f2.DateLastModified > vDT Then
>
> vFName = f2.Name
>
> vDT = f2.DateLastModified
>
> End If
>
> Next
>
> 'Debug.Print vFName, vDT
>
> 'If IsMissing(Date_Val) Then
>
> Val_Recent = vFName
>
> 'Else
>
> 'Val_RECENT = vDT
>
> 'End If
>
> 'End Sub
>
> Val_Open = fldr & Val_Recent
>
> MsgBox (Val_Open)
>
> 'Application.Excel.Workbooks.Open Val_Open
>
> 'Set xlApp = New Excel.Application
>
> Set xlApp = Excel.Application
>
> xlApp.Workbooks.Open(Val_Open)
>
>

> "Steven Burn" <somewhere@in-time.invalid> wrote in message
> news:O82iCupZFHA.3780@tk2msftngp13.phx.gbl...
> For starters, the following are all declared as VARIANTS, you MUST ensure
> you explicitly declare them as the type they are
> intended as. For example;

> Dim objFSO as Object

> Instead of;

> Dim fs

> As for the Excel one, use the following;

> Dim xlApp As Object
> Set xlApp = CreateObject("Excel.Application")
> xlApp.Workbooks.Open(Val_Open)

"Peter Aitken" <pait***@CRAPnc.rr.com> wrote in message news:eGuhH8qZFHA.2756@tk2msftngp13.phx.gbl...
> > "Steven Burn" <somewhere@in-time.invalid> wrote in message
> > news:O82iCupZFHA.3780@tk2msftngp13.phx.gbl...
> > For starters, the following are all declared as VARIANTS, you MUST ensure
> > you explicitly declare them as the type they are
>  > intended as. For example;
>
> > Dim objFSO as Object
>
> > Instead of;
>
> > Dim fs
>
> > As for the Excel one, use the following;
>
> > Dim xlApp As Object
> > Set xlApp = CreateObject("Excel.Application")
> > xlApp.Workbooks.Open(Val_Open)
>
>
> But your code does *not* do what you claim it does. Object is a generic type
> just like Variant and will also result in late binding. Better to use
>
> Dim fso As New FileSystemObject
> Dim xlApp As New Excel.Application
>
> Of course you must add the appropriate references to the project.
>
> Peter Aitken
>
>

"Jan Hyde" <StellaDrin***@REMOVE.ME.uboot.com> wrote in message news:o2gr91td22akimett2n02mt6d6i60fk1sk@4ax.com...
> "Steven Burn" <somewhere@in-time.invalid>'s wild thoughts
> were released on Wed, 1 Jun 2005 14:48:55 +0100 bearing the
> following fruit:
>
> >Declaring them as objects requires no prior reference though so I
> personally tend to do that and then use CreateObject() to
> create the object required ;o)
>
> Declaring them a variant requires no prior reference either.
>
> You said
>
> 'you MUST ensure you explicitly declare them as the type
> they are intended as.'
>
> Then gave an example which does no such thing. I think that
> is the point Peter was trying to make to you.
>
>
>
> Jan Hyde (VB MVP)
>
> --
> Crosscheck: A term used in chess to indicate checking an opponent's king by a bishop or queen on the diagonal. (Stan Kegel)
>
> [Abolish the TV Licence - http://www.tvlicensing.biz/]
>