Hi,

I have the following code to open in turn all files in a directory and run a
similar routine on them. The programming will recognise the file path, but my
"Fname" is blank and not picking up any of the files in the directory

Help gratefully recieved...

Private Sub ImportNew_Click()


Dim Fileloc As String
Dim Fname As String

Dim wkbk As Workbook


Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6
Draft.rpt"
Fname = Dir(Fileloc & "*.rpt")

While Fname <> ""


Workbooks.OpenText FileDir & Fname

ConvertCostCenterMacro


Thanks

Graham

Your Fileloc string does not look OK. You should have the folder path here
including a \ (backslash) at the end. Together with "*.rpt" the Dir command
will look for all files ending of that type.


"GrahamL" wrote:

> Hi,
>
> I have the following code to open in turn all files in a directory and run a
> similar routine on them. The programming will recognise the file path, but my
> "Fname" is blank and not picking up any of the files in the directory
>
> Help gratefully recieved...
>
> Private Sub ImportNew_Click()
>
>
> Dim Fileloc As String
> Dim Fname As String
>
> Dim wkbk As Workbook
>
>
> Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6
> Draft.rpt"
> Fname = Dir(Fileloc & "*.rpt")
>
> While Fname <> ""
>
>
> Workbooks.OpenText FileDir & Fname
>
> ConvertCostCenterMacro
>
>
> Thanks
>
> Graham

Thanks..I have amended to

Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6
Draft.rpt\ & *.rpt"

Fname = Dir(Fileloc)


....but still no joy ...Fname = ""

Graham


"Alok" wrote:

> Your Fileloc string does not look OK. You should have the folder path here
> including a \ (backslash) at the end. Together with "*.rpt" the Dir command
> will look for all files ending of that type.
>
>
> "GrahamL" wrote:
>
> > Hi,
> >
> > I have the following code to open in turn all files in a directory and run a
> > similar routine on them. The programming will recognise the file path, but my
> > "Fname" is blank and not picking up any of the files in the directory
> >
> > Help gratefully recieved...
> >
> > Private Sub ImportNew_Click()
> >
> >
> > Dim Fileloc As String
> > Dim Fname As String
> >
> > Dim wkbk As Workbook
> >
> >
> > Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6
> > Draft.rpt"
> > Fname = Dir(Fileloc & "*.rpt")
> >
> > While Fname <> ""
> >
> >
> > Workbooks.OpenText FileDir & Fname
> >
> > ConvertCostCenterMacro
> >
> >
> > Thanks
> >
> > Graham

Graham,
The problem is that :
Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6
Draft.rpt
already points to a single file ("NR6 Draft.rpt"), not a folder.
So you cannot add a file name to the end of that.

The containing folder is :
"C:\Documents and Settings\FINPORT03\My Documents\Share\"
Is that the folder want to search with Dir() ?

NickHK

"GrahamL" <(E-Mail Removed)> ¼¶¼g©ó¶l¥ó·s»D:85922697-B415-48FD-A245-(E-Mail Removed)...
> Thanks..I have amended to
>
> Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6
> Draft.rpt\ & *.rpt"
>
> Fname = Dir(Fileloc)
>
>
> ...but still no joy ...Fname = ""
>
> Graham
>
>
> "Alok" wrote:
>
>> Your Fileloc string does not look OK. You should have the folder path
>> here
>> including a \ (backslash) at the end. Together with "*.rpt" the Dir
>> command
>> will look for all files ending of that type.
>>
>>
>> "GrahamL" wrote:
>>
>> > Hi,
>> >
>> > I have the following code to open in turn all files in a directory and
>> > run a
>> > similar routine on them. The programming will recognise the file path,
>> > but my
>> > "Fname" is blank and not picking up any of the files in the directory
>> >
>> > Help gratefully recieved...
>> >
>> > Private Sub ImportNew_Click()
>> >
>> >
>> > Dim Fileloc As String
>> > Dim Fname As String
>> >
>> > Dim wkbk As Workbook
>> >
>> >
>> > Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6
>> > Draft.rpt"
>> > Fname = Dir(Fileloc & "*.rpt")
>> >
>> > While Fname <> ""
>> >
>> >
>> > Workbooks.OpenText FileDir & Fname
>> >
>> > ConvertCostCenterMacro
>> >
>> >
>> > Thanks
>> >
>> > Graham

See NikHK's response. On the other hand if your folder is named
"C:\Documents and Settings\FINPORT03\My Documents\Share\NR6
> Draft.rpt\" then you do not need & in the Fileloc string. It should be
Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6
Draft.rpt\*.rpt"

"GrahamL" wrote:

> Thanks..I have amended to
>
> Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6
> Draft.rpt\ & *.rpt"
>
> Fname = Dir(Fileloc)
>
>
> ...but still no joy ...Fname = ""
>
> Graham
>
>
> "Alok" wrote:
>
> > Your Fileloc string does not look OK. You should have the folder path here
> > including a \ (backslash) at the end. Together with "*.rpt" the Dir command
> > will look for all files ending of that type.
> >
> >
> > "GrahamL" wrote:
> >
> > > Hi,
> > >
> > > I have the following code to open in turn all files in a directory and run a
> > > similar routine on them. The programming will recognise the file path, but my
> > > "Fname" is blank and not picking up any of the files in the directory
> > >
> > > Help gratefully recieved...
> > >
> > > Private Sub ImportNew_Click()
> > >
> > >
> > > Dim Fileloc As String
> > > Dim Fname As String
> > >
> > > Dim wkbk As Workbook
> > >
> > >
> > > Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6
> > > Draft.rpt"
> > > Fname = Dir(Fileloc & "*.rpt")
> > >
> > > While Fname <> ""
> > >
> > >
> > > Workbooks.OpenText FileDir & Fname
> > >
> > > ConvertCostCenterMacro
> > >
> > >
> > > Thanks
> > >
> > > Graham

Hi,

Thanks and sorry to add to the confusion...my folder is actually called NR6
Draft . rpt and contains a multitude of other files with .rpt file type. This
routine ran perfectly well for years, but I have recently transfered to XL
2000 from 97 version which must have something to do with the problem.

Graham


"NickHK" wrote:

> Graham,
> The problem is that :
> Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6
> Draft.rpt
> already points to a single file ("NR6 Draft.rpt"), not a folder.
> So you cannot add a file name to the end of that.
>
> The containing folder is :
> "C:\Documents and Settings\FINPORT03\My Documents\Share\"
> Is that the folder want to search with Dir() ?
>
> NickHK
>
> "GrahamL" <(E-Mail Removed)> ¼¶¼g©ó¶l¥ó·s»D:85922697-B415-48FD-A245-(E-Mail Removed)...
> > Thanks..I have amended to
> >
> > Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6
> > Draft.rpt\ & *.rpt"
> >
> > Fname = Dir(Fileloc)
> >
> >
> > ...but still no joy ...Fname = ""
> >
> > Graham
> >
> >
> > "Alok" wrote:
> >
> >> Your Fileloc string does not look OK. You should have the folder path
> >> here
> >> including a \ (backslash) at the end. Together with "*.rpt" the Dir
> >> command
> >> will look for all files ending of that type.
> >>
> >>
> >> "GrahamL" wrote:
> >>
> >> > Hi,
> >> >
> >> > I have the following code to open in turn all files in a directory and
> >> > run a
> >> > similar routine on them. The programming will recognise the file path,
> >> > but my
> >> > "Fname" is blank and not picking up any of the files in the directory
> >> >
> >> > Help gratefully recieved...
> >> >
> >> > Private Sub ImportNew_Click()
> >> >
> >> >
> >> > Dim Fileloc As String
> >> > Dim Fname As String
> >> >
> >> > Dim wkbk As Workbook
> >> >
> >> >
> >> > Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6
> >> > Draft.rpt"
> >> > Fname = Dir(Fileloc & "*.rpt")
> >> >
> >> > While Fname <> ""
> >> >
> >> >
> >> > Workbooks.OpenText FileDir & Fname
> >> >
> >> > ConvertCostCenterMacro
> >> >
> >> >
> >> > Thanks
> >> >
> >> > Graham
>
>
>

Alok,
I did assume that
Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6
Draft.rpt"
actually referred to file, rather than a folder with a "." in the name,
which would be valid.

But you response covers that.

NickHK

"Alok" <(E-Mail Removed)> ¼¶¼g©ó¶l¥ó·s»D:A8D21844-079B-4024-B07B-(E-Mail Removed)...
> See NikHK's response. On the other hand if your folder is named
> "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6
>> Draft.rpt\" then you do not need & in the Fileloc string. It should be
> Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6
> Draft.rpt\*.rpt"
>
> "GrahamL" wrote:
>
>> Thanks..I have amended to
>>
>> Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6
>> Draft.rpt\ & *.rpt"
>>
>> Fname = Dir(Fileloc)
>>
>>
>> ...but still no joy ...Fname = ""
>>
>> Graham
>>
>>
>> "Alok" wrote:
>>
>> > Your Fileloc string does not look OK. You should have the folder path
>> > here
>> > including a \ (backslash) at the end. Together with "*.rpt" the Dir
>> > command
>> > will look for all files ending of that type.
>> >
>> >
>> > "GrahamL" wrote:
>> >
>> > > Hi,
>> > >
>> > > I have the following code to open in turn all files in a directory
>> > > and run a
>> > > similar routine on them. The programming will recognise the file
>> > > path, but my
>> > > "Fname" is blank and not picking up any of the files in the directory
>> > >
>> > > Help gratefully recieved...
>> > >
>> > > Private Sub ImportNew_Click()
>> > >
>> > >
>> > > Dim Fileloc As String
>> > > Dim Fname As String
>> > >
>> > > Dim wkbk As Workbook
>> > >
>> > >
>> > > Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6
>> > > Draft.rpt"
>> > > Fname = Dir(Fileloc & "*.rpt")
>> > >
>> > > While Fname <> ""
>> > >
>> > >
>> > > Workbooks.OpenText FileDir & Fname
>> > >
>> > > ConvertCostCenterMacro
>> > >
>> > >
>> > > Thanks
>> > >
>> > > Graham

Fileloc = "C:\Documents and Settings\FINPORT03\" & _
"My Documents\Share\NR6 Draft.rpt\"
Fname = Dir(Fileloc & "*.rpt")

--
Regards,
Tom Ogilvy



"GrahamL" wrote:

> Hi,
>
> Thanks and sorry to add to the confusion...my folder is actually called NR6
> Draft . rpt and contains a multitude of other files with .rpt file type. This
> routine ran perfectly well for years, but I have recently transfered to XL
> 2000 from 97 version which must have something to do with the problem.
>
> Graham
>
>
> "NickHK" wrote:
>
> > Graham,
> > The problem is that :
> > Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6
> > Draft.rpt
> > already points to a single file ("NR6 Draft.rpt"), not a folder.
> > So you cannot add a file name to the end of that.
> >
> > The containing folder is :
> > "C:\Documents and Settings\FINPORT03\My Documents\Share\"
> > Is that the folder want to search with Dir() ?
> >
> > NickHK
> >
> > "GrahamL" <(E-Mail Removed)> ¼¶¼g©ó¶l¥ó·s»D:85922697-B415-48FD-A245-(E-Mail Removed)...
> > > Thanks..I have amended to
> > >
> > > Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6
> > > Draft.rpt\ & *.rpt"
> > >
> > > Fname = Dir(Fileloc)
> > >
> > >
> > > ...but still no joy ...Fname = ""
> > >
> > > Graham
> > >
> > >
> > > "Alok" wrote:
> > >
> > >> Your Fileloc string does not look OK. You should have the folder path
> > >> here
> > >> including a \ (backslash) at the end. Together with "*.rpt" the Dir
> > >> command
> > >> will look for all files ending of that type.
> > >>
> > >>
> > >> "GrahamL" wrote:
> > >>
> > >> > Hi,
> > >> >
> > >> > I have the following code to open in turn all files in a directory and
> > >> > run a
> > >> > similar routine on them. The programming will recognise the file path,
> > >> > but my
> > >> > "Fname" is blank and not picking up any of the files in the directory
> > >> >
> > >> > Help gratefully recieved...
> > >> >
> > >> > Private Sub ImportNew_Click()
> > >> >
> > >> >
> > >> > Dim Fileloc As String
> > >> > Dim Fname As String
> > >> >
> > >> > Dim wkbk As Workbook
> > >> >
> > >> >
> > >> > Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6
> > >> > Draft.rpt"
> > >> > Fname = Dir(Fileloc & "*.rpt")
> > >> >
> > >> > While Fname <> ""
> > >> >
> > >> >
> > >> > Workbooks.OpenText FileDir & Fname
> > >> >
> > >> > ConvertCostCenterMacro
> > >> >
> > >> >
> > >> > Thanks
> > >> >
> > >> > Graham
> >
> >
> >