Concatinate a filename

Hi All........

I am trying to concatenate two cells together to form a filename in a
link............no joy, .......all I get is "That filename is not
valid"........

=[clean(c6)&"_"&b6.xls]Sheet1!$A9

C6 is a name, as Jones, Fred
B6 is a string as R1938

The filename I 'm looking for is Jones,Fred_R1938.xls and it does
exist........and of course works if I hard code the filename into the
formula........

=clean(c6)&"_"&b6 works fine in a test of that standalone part, but not in
the link formula

Any help would be appreciated........

Vaya con Dios,
Chuck, CABGx3

You'd want to use the =indirect() worksheet function. But that doesn't work
with closed files.

Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed
workbook.

You can find the function at Harlan's FTP site:
ftp://members.aol.com/hrlngrv/

CLR wrote:
>
> Hi All........
>
> I am trying to concatenate two cells together to form a filename in a
> link............no joy, .......all I get is "That filename is not
> valid"........
>
> =[clean(c6)&"_"&b6.xls]Sheet1!$A9
>
> C6 is a name, as Jones, Fred
> B6 is a string as R1938
>
> The filename I 'm looking for is Jones,Fred_R1938.xls and it does
> exist........and of course works if I hard code the filename into the
> formula........
>
> =clean(c6)&"_"&b6 works fine in a test of that standalone part, but not in
> the link formula
>
> Any help would be appreciated........
>
> Vaya con Dios,
> Chuck, CABGx3

--

Dave Peterson

Thank you kind Sir............I went and got the PULL file and will try it
tomorrow........about to fall off my chair tonight...........

Thanks again loads.......

Vaya con Dios,
Chuck, CABGx3



"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:42E98A10.FCEF2CE6@verizonXSPAM.net...
> You'd want to use the =indirect() worksheet function. But that doesn't
work
> with closed files.
>
> Harlan Grove wrote a UDF called PULL that will retrieve the value from a
closed
> workbook.
>
> You can find the function at Harlan's FTP site:
> ftp://members.aol.com/hrlngrv/
>
> CLR wrote:
> >
> > Hi All........
> >
> > I am trying to concatenate two cells together to form a filename in a
> > link............no joy, .......all I get is "That filename is not
> > valid"........
> >
> > =[clean(c6)&"_"&b6.xls]Sheet1!$A9
> >
> > C6 is a name, as Jones, Fred
> > B6 is a string as R1938
> >
> > The filename I 'm looking for is Jones,Fred_R1938.xls and it does
> > exist........and of course works if I hard code the filename into the
> > formula........
> >
> > =clean(c6)&"_"&b6 works fine in a test of that standalone part, but not
in
> > the link formula
> >
> > Any help would be appreciated........
> >
> > Vaya con Dios,
> > Chuck, CABGx3
>
> --
>
> Dave Peterson

I went after Harlan's UDF and got it but can't for the life of me figure out
how to use it.
I pasted it into a regular module and, all I can get "Sub or function not
defined" on this line..

n = InStrRev(Len(expr), expr, "\")

Anybody see what I'm doing wrong?

Vaya con Dios,
Chuck, CABGx3


"Dave Peterson" wrote:

> You'd want to use the =indirect() worksheet function. But that doesn't work
> with closed files.
>
> Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed
> workbook.
>
> You can find the function at Harlan's FTP site:
> ftp://members.aol.com/hrlngrv/
>
> CLR wrote:
> >
> > Hi All........
> >
> > I am trying to concatenate two cells together to form a filename in a
> > link............no joy, .......all I get is "That filename is not
> > valid"........
> >
> > =[clean(c6)&"_"&b6.xls]Sheet1!$A9
> >
> > C6 is a name, as Jones, Fred
> > B6 is a string as R1938
> >
> > The filename I 'm looking for is Jones,Fred_R1938.xls and it does
> > exist........and of course works if I hard code the filename into the
> > formula........
> >
> > =clean(c6)&"_"&b6 works fine in a test of that standalone part, but not in
> > the link formula
> >
> > Any help would be appreciated........
> >
> > Vaya con Dios,
> > Chuck, CABGx3
>
> --
>
> Dave Peterson
>

Instrrev was added in xl2k.

You could create your own InstrRev97 function and use that:

Function InStrRev97(mystr As Variant, mydelim As String) As Long
Dim i As Long
InStrRev97 = 0
For i = Len(mystr) To 1 Step -1
If Mid(mystr, i, 1) = mydelim Then
InStrRev97 = i
Exit Function
End If
Next i
End Function

(Just add this to the bottom of that general module.

so
n = InStrRev(Len(expr), expr, "\")
becomes
n = InStrRev97(expr, "\")



=====
There are some other functions that were added in xl2k (split for example). But
I took a cursory glance at Harlan's code and didn't see any others that would
cause you trouble.

(Post back when you see that I missed one!)

CLR wrote:
>
> I went after Harlan's UDF and got it but can't for the life of me figure out
> how to use it.
> I pasted it into a regular module and, all I can get "Sub or function not
> defined" on this line..
>
> n = InStrRev(Len(expr), expr, "\")
>
> Anybody see what I'm doing wrong?
>
> Vaya con Dios,
> Chuck, CABGx3
>
> "Dave Peterson" wrote:
>
> > You'd want to use the =indirect() worksheet function. But that doesn't work
> > with closed files.
> >
> > Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed
> > workbook.
> >
> > You can find the function at Harlan's FTP site:
> > ftp://members.aol.com/hrlngrv/
> >
> > CLR wrote:
> > >
> > > Hi All........
> > >
> > > I am trying to concatenate two cells together to form a filename in a
> > > link............no joy, .......all I get is "That filename is not
> > > valid"........
> > >
> > > =[clean(c6)&"_"&b6.xls]Sheet1!$A9
> > >
> > > C6 is a name, as Jones, Fred
> > > B6 is a string as R1938
> > >
> > > The filename I 'm looking for is Jones,Fred_R1938.xls and it does
> > > exist........and of course works if I hard code the filename into the
> > > formula........
> > >
> > > =clean(c6)&"_"&b6 works fine in a test of that standalone part, but not in
> > > the link formula
> > >
> > > Any help would be appreciated........
> > >
> > > Vaya con Dios,
> > > Chuck, CABGx3
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Thanks Dave.........it got past that line by following your instructions, but
now stops on

n = InStrRev(Len(xref), xref, "!")

I tried modifying it, but no joy.....I'm just shooting in the dark.

Vaya con Dios,
Chuck, CABGx3



"Dave Peterson" wrote:

> Instrrev was added in xl2k.
>
> You could create your own InstrRev97 function and use that:
>
> Function InStrRev97(mystr As Variant, mydelim As String) As Long
> Dim i As Long
> InStrRev97 = 0
> For i = Len(mystr) To 1 Step -1
> If Mid(mystr, i, 1) = mydelim Then
> InStrRev97 = i
> Exit Function
> End If
> Next i
> End Function
>
> (Just add this to the bottom of that general module.
>
> so
> n = InStrRev(Len(expr), expr, "\")
> becomes
> n = InStrRev97(expr, "\")
>
>
>
> =====
> There are some other functions that were added in xl2k (split for example). But
> I took a cursory glance at Harlan's code and didn't see any others that would
> cause you trouble.
>
> (Post back when you see that I missed one!)
>
> CLR wrote:
> >
> > I went after Harlan's UDF and got it but can't for the life of me figure out
> > how to use it.
> > I pasted it into a regular module and, all I can get "Sub or function not
> > defined" on this line..
> >
> > n = InStrRev(Len(expr), expr, "\")
> >
> > Anybody see what I'm doing wrong?
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> > "Dave Peterson" wrote:
> >
> > > You'd want to use the =indirect() worksheet function. But that doesn't work
> > > with closed files.
> > >
> > > Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed
> > > workbook.
> > >
> > > You can find the function at Harlan's FTP site:
> > > ftp://members.aol.com/hrlngrv/
> > >
> > > CLR wrote:
> > > >
> > > > Hi All........
> > > >
> > > > I am trying to concatenate two cells together to form a filename in a
> > > > link............no joy, .......all I get is "That filename is not
> > > > valid"........
> > > >
> > > > =[clean(c6)&"_"&b6.xls]Sheet1!$A9
> > > >
> > > > C6 is a name, as Jones, Fred
> > > > B6 is a string as R1938
> > > >
> > > > The filename I 'm looking for is Jones,Fred_R1938.xls and it does
> > > > exist........and of course works if I hard code the filename into the
> > > > formula........
> > > >
> > > > =clean(c6)&"_"&b6 works fine in a test of that standalone part, but not in
> > > > the link formula
> > > >
> > > > Any help would be appreciated........
> > > >
> > > > Vaya con Dios,
> > > > Chuck, CABGx3
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

You'll have to do the same kind of thing.

n = InStrRev(Len(xref), xref, "!")
becomes
n = InStrRev97(expr, "!")




CLR wrote:
>
> Thanks Dave.........it got past that line by following your instructions, but
> now stops on
>
> n = InStrRev(Len(xref), xref, "!")
>
> I tried modifying it, but no joy.....I'm just shooting in the dark.
>
> Vaya con Dios,
> Chuck, CABGx3
>
> "Dave Peterson" wrote:
>
> > Instrrev was added in xl2k.
> >
> > You could create your own InstrRev97 function and use that:
> >
> > Function InStrRev97(mystr As Variant, mydelim As String) As Long
> > Dim i As Long
> > InStrRev97 = 0
> > For i = Len(mystr) To 1 Step -1
> > If Mid(mystr, i, 1) = mydelim Then
> > InStrRev97 = i
> > Exit Function
> > End If
> > Next i
> > End Function
> >
> > (Just add this to the bottom of that general module.
> >
> > so
> > n = InStrRev(Len(expr), expr, "\")
> > becomes
> > n = InStrRev97(expr, "\")
> >
> >
> >
> > =====
> > There are some other functions that were added in xl2k (split for example). But
> > I took a cursory glance at Harlan's code and didn't see any others that would
> > cause you trouble.
> >
> > (Post back when you see that I missed one!)
> >
> > CLR wrote:
> > >
> > > I went after Harlan's UDF and got it but can't for the life of me figure out
> > > how to use it.
> > > I pasted it into a regular module and, all I can get "Sub or function not
> > > defined" on this line..
> > >
> > > n = InStrRev(Len(expr), expr, "\")
> > >
> > > Anybody see what I'm doing wrong?
> > >
> > > Vaya con Dios,
> > > Chuck, CABGx3
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > You'd want to use the =indirect() worksheet function. But that doesn't work
> > > > with closed files.
> > > >
> > > > Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed
> > > > workbook.
> > > >
> > > > You can find the function at Harlan's FTP site:
> > > > ftp://members.aol.com/hrlngrv/
> > > >
> > > > CLR wrote:
> > > > >
> > > > > Hi All........
> > > > >
> > > > > I am trying to concatenate two cells together to form a filename in a
> > > > > link............no joy, .......all I get is "That filename is not
> > > > > valid"........
> > > > >
> > > > > =[clean(c6)&"_"&b6.xls]Sheet1!$A9
> > > > >
> > > > > C6 is a name, as Jones, Fred
> > > > > B6 is a string as R1938
> > > > >
> > > > > The filename I 'm looking for is Jones,Fred_R1938.xls and it does
> > > > > exist........and of course works if I hard code the filename into the
> > > > > formula........
> > > > >
> > > > > =clean(c6)&"_"&b6 works fine in a test of that standalone part, but not in
> > > > > the link formula
> > > > >
> > > > > Any help would be appreciated........
> > > > >
> > > > > Vaya con Dios,
> > > > > Chuck, CABGx3
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Hi,

use

=INDIRECT("'["&CLEAN(C6)&"_"&B6&".xls]Sheet1'!$A9")

Regards

Govind.

CLR wrote:
> Hi All........
>
> I am trying to concatenate two cells together to form a filename in a
> link............no joy, .......all I get is "That filename is not
> valid"........
>
> =[clean(c6)&"_"&b6.xls]Sheet1!$A9
>
> C6 is a name, as Jones, Fred
> B6 is a string as R1938
>
> The filename I 'm looking for is Jones,Fred_R1938.xls and it does
> exist........and of course works if I hard code the filename into the
> formula........
>
> =clean(c6)&"_"&b6 works fine in a test of that standalone part, but not in
> the link formula
>
> Any help would be appreciated........
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
>

Thanks Govind...........I appreciate your response.

Vaya con Dios,
Chuck, CABGx3



"Govind" <adgraj1@indiatimes.com> wrote in message
news:qjgGe.29$lz1.183@news.oracle.com...
> Hi,
>
> use
>
> =INDIRECT("'["&CLEAN(C6)&"_"&B6&".xls]Sheet1'!$A9")
>
> Regards
>
> Govind.
>
> CLR wrote:
> > Hi All........
> >
> > I am trying to concatenate two cells together to form a filename in a
> > link............no joy, .......all I get is "That filename is not
> > valid"........
> >
> > =[clean(c6)&"_"&b6.xls]Sheet1!$A9
> >
> > C6 is a name, as Jones, Fred
> > B6 is a string as R1938
> >
> > The filename I 'm looking for is Jones,Fred_R1938.xls and it does
> > exist........and of course works if I hard code the filename into the
> > formula........
> >
> > =clean(c6)&"_"&b6 works fine in a test of that standalone part, but not
in
> > the link formula
> >
> > Any help would be appreciated........
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> >

Hi Govind.......

I tried your INDIRECT formula and it worked ok, after I enclosed the CLEAN
portion as (CLEAN(c6)) in parenthesis, but as Dave eluded, only if the File
is open.........I suppose I could "open the file, obtain the data, and close
the file", but I would rather not have to do that unless absolutely
necessary.........

Any other ideas, please?

Vaya con Dios,
Chuck, CABGx3



"Govind" wrote:

> Hi,
>
> use
>
> =INDIRECT("'["&CLEAN(C6)&"_"&B6&".xls]Sheet1'!$A9")
>
> Regards
>
> Govind.
>
> CLR wrote:
> > Hi All........
> >
> > I am trying to concatenate two cells together to form a filename in a
> > link............no joy, .......all I get is "That filename is not
> > valid"........
> >
> > =[clean(c6)&"_"&b6.xls]Sheet1!$A9
> >
> > C6 is a name, as Jones, Fred
> > B6 is a string as R1938
> >
> > The filename I 'm looking for is Jones,Fred_R1938.xls and it does
> > exist........and of course works if I hard code the filename into the
> > formula........
> >
> > =clean(c6)&"_"&b6 works fine in a test of that standalone part, but not in
> > the link formula
> >
> > Any help would be appreciated........
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> >
>