This might turn out to be a double post and if so, I apologize...something
went wrong in attempting to post this the first time.

I'd like to, using VBA in Excel 2003, open a browser window and locate a
file, then highlight that file and have the file name entered into a WS.

I have a code that opens a browser window and also record all the file names
in a particular folder but will not allow me to selectively choose which file
name I want recorded.

Again I apologize if this hits the board twice but something went wrong on
the first try......TIA,

Don

Like an open file dialog box but from a web browser? So a web page?
scott

On Fri, 22 Feb 2008 15:11:01 -0800, Don
wrote:

This might turn out to be a double post and if so, I apologize...something
went wrong in attempting to post this the first time.

I'd like to, using VBA in Excel 2003, open a browser window and locate a
file, then highlight that file and have the file name entered into a WS.

I have a code that opens a browser window and also record all the file names
in a particular folder but will not allow me to selectively choose which file
name I want recorded.

Again I apologize if this hits the board twice but something went wrong on
the first try......TIA,

Don

Don,

Do you only need one file returned? How about application.getopenfilename?

Activecell = application.GetOpenFilename

But, if I remember correctly, that will return the the whole path and
filename. You could put code in there to trim out the path and return just
the file name.

Do you need more than one file name? Lookup GetOpenFilename in VBA Help.
There is a "MultiSelect" argument that will allow you to select more than
one file. But once again, it will return one string with files (including
paths) separated by commas (I think). You could then have a string array
and separate each file name into an element of the string array, trimming
out path info in the process.

HTH,

Conan





"Don" wrote in message
...
This might turn out to be a double post and if so, I apologize...something
went wrong in attempting to post this the first time.

I'd like to, using VBA in Excel 2003, open a browser window and locate a
file, then highlight that file and have the file name entered into a WS.

I have a code that opens a browser window and also record all the file
names
in a particular folder but will not allow me to selectively choose which
file
name I want recorded.

Again I apologize if this hits the board twice but something went wrong on
the first try......TIA,

Don

Hi Conan...

That's exactly what I wanted....I can trim the path off, not a problem.
Works great. Thank you very much....

Don

"Conan Kelly" wrote:

Don,

Do you only need one file returned? How about application.getopenfilename?

Activecell = application.GetOpenFilename

But, if I remember correctly, that will return the the whole path and
filename. You could put code in there to trim out the path and return just
the file name.

Do you need more than one file name? Lookup GetOpenFilename in VBA Help.
There is a "MultiSelect" argument that will allow you to select more than
one file. But once again, it will return one string with files (including
paths) separated by commas (I think). You could then have a string array
and separate each file name into an element of the string array, trimming
out path info in the process.

HTH,

Conan





"Don" wrote in message
...
This might turn out to be a double post and if so, I apologize...something
went wrong in attempting to post this the first time.

I'd like to, using VBA in Excel 2003, open a browser window and locate a
file, then highlight that file and have the file name entered into a WS.

I have a code that opens a browser window and also record all the file
names
in a particular folder but will not allow me to selectively choose which
file
name I want recorded.

Again I apologize if this hits the board twice but something went wrong on
the first try......TIA,

Don

Well Conan...here's what I came up with. Probably not as clean as it could
be, but it works....:)

Dim LastCol As Long

Sub FindFilename()

On Error Resume Next

' Assume Sheet1 to be a blank sheet

Sheet1.Range("A1").Select

ActiveCell = Application.GetOpenFilename

Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="\", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), _
Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1)), _
TrailingMinusNumbers:=True

' LastCol = Last column in row 1 that has data in it.

LastCol = ActiveSheet.UsedRange.Columns.Count
Range("A2") = ActiveSheet.Cells(1, LastCol)

MsgBox Range("A2")
MsgBox Range("A3")

' Then I can move contents of A3 to the sheet and/or cell required

End Sub

MsgBoxs only for testing...I use them a lot...

Thanks again Conan.....really appreciate the help I get on here....

Don

"Conan Kelly" wrote:

Don,

Do you only need one file returned? How about application.getopenfilename?

Activecell = application.GetOpenFilename

But, if I remember correctly, that will return the the whole path and
filename. You could put code in there to trim out the path and return just
the file name.

Do you need more than one file name? Lookup GetOpenFilename in VBA Help.
There is a "MultiSelect" argument that will allow you to select more than
one file. But once again, it will return one string with files (including
paths) separated by commas (I think). You could then have a string array
and separate each file name into an element of the string array, trimming
out path info in the process.

HTH,

Conan





"Don" wrote in message
...
This might turn out to be a double post and if so, I apologize...something
went wrong in attempting to post this the first time.

I'd like to, using VBA in Excel 2003, open a browser window and locate a
file, then highlight that file and have the file name entered into a WS.

I have a code that opens a browser window and also record all the file
names
in a particular folder but will not allow me to selectively choose which
file
name I want recorded.

Again I apologize if this hits the board twice but something went wrong on
the first try......TIA,

Don

Forgot to add that in A3 I have this formula which gets rid of the extension
on the file name.

=LEFT(A2,LEN(A2)-4)

I haven't been able to do this in VBA yet. Can it be done in VBA?

Again, thanks for the help,

Don


"Conan Kelly" wrote:

Don,

Do you only need one file returned? How about application.getopenfilename?

Activecell = application.GetOpenFilename

But, if I remember correctly, that will return the the whole path and
filename. You could put code in there to trim out the path and return just
the file name.

Do you need more than one file name? Lookup GetOpenFilename in VBA Help.
There is a "MultiSelect" argument that will allow you to select more than
one file. But once again, it will return one string with files (including
paths) separated by commas (I think). You could then have a string array
and separate each file name into an element of the string array, trimming
out path info in the process.

HTH,

Conan





"Don" wrote in message
...
This might turn out to be a double post and if so, I apologize...something
went wrong in attempting to post this the first time.

I'd like to, using VBA in Excel 2003, open a browser window and locate a
file, then highlight that file and have the file name entered into a WS.

I have a code that opens a browser window and also record all the file
names
in a particular folder but will not allow me to selectively choose which
file
name I want recorded.

Again I apologize if this hits the board twice but something went wrong on
the first try......TIA,

Don

OK...this threads done, I think....figured it out, cleaned it up a bit, added
a couple of safeguards and here's the final product.....and amazingly, IT
WORK!!...LOL

Dim LastCol As Long
Dim MyStr As String

Sub FindFilename()

On Error Resume Next

' clear contents of rows 1 through 5

Rows("1:5").ClearContents
Sheet1.Range("A1").Select
ActiveCell = Application.GetOpenFilename

' Use text to columns to seperate path into sub-folders

Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="\", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1),
Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1)), TrailingMinusNumbers:=True

' LastCol = Last column in row 1 that has data in it.

LastCol = ActiveSheet.UsedRange.Columns.Count
MyStr = ActiveSheet.Cells(1, LastCol)

' Cut the extension off the file name

Range("A2") = Left(MyStr, Len(MyStr) - 4)
Columns("A:A").EntireColumn.AutoFit
MsgBox Range("A2")

' Then I can move contents of A2 to the sheet and/or cell required

End Sub

Ya'll have a great day now.....and thanks again,

Don


"Conan Kelly" wrote:

Don,

Do you only need one file returned? How about application.getopenfilename?

Activecell = application.GetOpenFilename

But, if I remember correctly, that will return the the whole path and
filename. You could put code in there to trim out the path and return just
the file name.

Do you need more than one file name? Lookup GetOpenFilename in VBA Help.
There is a "MultiSelect" argument that will allow you to select more than
one file. But once again, it will return one string with files (including
paths) separated by commas (I think). You could then have a string array
and separate each file name into an element of the string array, trimming
out path info in the process.

HTH,

Conan





"Don" wrote in message
...
This might turn out to be a double post and if so, I apologize...something
went wrong in attempting to post this the first time.

I'd like to, using VBA in Excel 2003, open a browser window and locate a
file, then highlight that file and have the file name entered into a WS.

I have a code that opens a browser window and also record all the file
names
in a particular folder but will not allow me to selectively choose which
file
name I want recorded.

Again I apologize if this hits the board twice but something went wrong on
the first try......TIA,

Don

Don,

I see how that works for you. If you wanted to, though, you could clean it
up by extracting the file name in VBA and then putting it into the cell you
need, in stead of putting it in empty cell and parsing it in XL.

Something like this should work:

Sub FindFilename()
Dim LastCol As Long
Dim MyStr As String
Dim pstrFileName as string

'Stores the file selected (including path) in a string variable
pstrFileName = Application.GetOpenFilename

'Should trim off the whole path leaving only the file
'name and extension and then stores it in the same string variable.
pstrFileName = Right(pstrFileName, InStrRev(pstrFileName,"\"))

'I Think that should work, but if it is 1 character off, add/subtract
'1 from the InStrRev() function inside the right function (put a "+1"
'or a "-1" between the closing paren. of InStrRev() and the closing
'paren. of Right()). Like so:
'pstrFileName = Right(pstrFileName, InStrRev(pstrFileName,"\") + 1)
'pstrFileName = Right(pstrFileName, InStrRev(pstrFileName,"\") - 1)

'Make sure you check to see what is being stored in the variable with
a...
'Debug.Print pstrFileName
'...or a...
'MsgBox pstrFileName

'Trims off the extention from the file name (if that extension is a
period/decimal point
'with a 3 character extention)
pstrFileName = Left(pstrFileName,Len(pstrFileName)-4)

'if you might have extensions of varying lengths, then you could use
'InStrRev() with Left() to look for the period/decimal point.

'Puts the file name in the exact location you need it in
Workbooks("[your workbook name]").Worksheets("[desired sheet
neme]").Range("[desired cell address]") = pstrFileName

'Any other code necessary:
...
...
...

End Sub


(this has not been tested. I typed this in "freehand" into this post. Did
not copy/paste from the VBE. Typos are possible...hopefully you are using
"Option Explicit" so any would be caught)

HTH,

Conan






"Don" wrote in message
...
OK...this threads done, I think....figured it out, cleaned it up a bit,
added
a couple of safeguards and here's the final product.....and amazingly, IT
WORK!!...LOL

Dim LastCol As Long
Dim MyStr As String

Sub FindFilename()

On Error Resume Next

' clear contents of rows 1 through 5

Rows("1:5").ClearContents
Sheet1.Range("A1").Select
ActiveCell = Application.GetOpenFilename

' Use text to columns to seperate path into sub-folders

Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True,
OtherChar _
:="\", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1),
Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1)), TrailingMinusNumbers:=True

' LastCol = Last column in row 1 that has data in it.

LastCol = ActiveSheet.UsedRange.Columns.Count
MyStr = ActiveSheet.Cells(1, LastCol)

' Cut the extension off the file name

Range("A2") = Left(MyStr, Len(MyStr) - 4)
Columns("A:A").EntireColumn.AutoFit
MsgBox Range("A2")

' Then I can move contents of A2 to the sheet and/or cell required

End Sub

Ya'll have a great day now.....and thanks again,

Don


"Conan Kelly" wrote:

Don,

Do you only need one file returned? How about
application.getopenfilename?

Activecell = application.GetOpenFilename

But, if I remember correctly, that will return the the whole path and
filename. You could put code in there to trim out the path and return
just
the file name.

Do you need more than one file name? Lookup GetOpenFilename in VBA Help.
There is a "MultiSelect" argument that will allow you to select more than
one file. But once again, it will return one string with files
(including
paths) separated by commas (I think). You could then have a string array
and separate each file name into an element of the string array, trimming
out path info in the process.

HTH,

Conan





"Don" wrote in message
...
This might turn out to be a double post and if so, I
apologize...something
went wrong in attempting to post this the first time.

I'd like to, using VBA in Excel 2003, open a browser window and locate
a
file, then highlight that file and have the file name entered into a
WS.

I have a code that opens a browser window and also record all the file
names
in a particular folder but will not allow me to selectively choose
which
file
name I want recorded.

Again I apologize if this hits the board twice but something went wrong
on
the first try......TIA,

Don

Thanks again Conan.....although the files I'm dealing with do have different
extensions, to date they are all 4 characters including the period. However
I think I will incorporate your suggestion that will all for different length
extensions, just to cover that base if it occurs.

And also storing the filename via VBA variable will be used in the final go.

Always appreciate the help I get on here.....you have a great day now,

Don

"Conan Kelly" wrote:

Don,

I see how that works for you. If you wanted to, though, you could clean it
up by extracting the file name in VBA and then putting it into the cell you
need, in stead of putting it in empty cell and parsing it in XL.

Something like this should work:

Sub FindFilename()
Dim LastCol As Long
Dim MyStr As String
Dim pstrFileName as string

'Stores the file selected (including path) in a string variable
pstrFileName = Application.GetOpenFilename

'Should trim off the whole path leaving only the file
'name and extension and then stores it in the same string variable.
pstrFileName = Right(pstrFileName, InStrRev(pstrFileName,"\"))

'I Think that should work, but if it is 1 character off, add/subtract
'1 from the InStrRev() function inside the right function (put a "+1"
'or a "-1" between the closing paren. of InStrRev() and the closing
'paren. of Right()). Like so:
'pstrFileName = Right(pstrFileName, InStrRev(pstrFileName,"\") + 1)
'pstrFileName = Right(pstrFileName, InStrRev(pstrFileName,"\") - 1)

'Make sure you check to see what is being stored in the variable with
a...
'Debug.Print pstrFileName
'...or a...
'MsgBox pstrFileName

'Trims off the extention from the file name (if that extension is a
period/decimal point
'with a 3 character extention)
pstrFileName = Left(pstrFileName,Len(pstrFileName)-4)

'if you might have extensions of varying lengths, then you could use
'InStrRev() with Left() to look for the period/decimal point.

'Puts the file name in the exact location you need it in
Workbooks("[your workbook name]").Worksheets("[desired sheet
neme]").Range("[desired cell address]") = pstrFileName

'Any other code necessary:
...
...
...

End Sub


(this has not been tested. I typed this in "freehand" into this post. Did
not copy/paste from the VBE. Typos are possible...hopefully you are using
"Option Explicit" so any would be caught)

HTH,

Conan






"Don" wrote in message
...
OK...this threads done, I think....figured it out, cleaned it up a bit,
added
a couple of safeguards and here's the final product.....and amazingly, IT
WORK!!...LOL

Dim LastCol As Long
Dim MyStr As String

Sub FindFilename()

On Error Resume Next

' clear contents of rows 1 through 5

Rows("1:5").ClearContents
Sheet1.Range("A1").Select
ActiveCell = Application.GetOpenFilename

' Use text to columns to seperate path into sub-folders

Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True,
OtherChar _
:="\", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1),
Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1)), TrailingMinusNumbers:=True

' LastCol = Last column in row 1 that has data in it.

LastCol = ActiveSheet.UsedRange.Columns.Count
MyStr = ActiveSheet.Cells(1, LastCol)

' Cut the extension off the file name

Range("A2") = Left(MyStr, Len(MyStr) - 4)
Columns("A:A").EntireColumn.AutoFit
MsgBox Range("A2")

' Then I can move contents of A2 to the sheet and/or cell required

End Sub

Ya'll have a great day now.....and thanks again,

Don


"Conan Kelly" wrote:

Don,

Do you only need one file returned? How about
application.getopenfilename?

Activecell = application.GetOpenFilename

But, if I remember correctly, that will return the the whole path and
filename. You could put code in there to trim out the path and return
just
the file name.

Do you need more than one file name? Lookup GetOpenFilename in VBA Help.
There is a "MultiSelect" argument that will allow you to select more than
one file. But once again, it will return one string with files
(including
paths) separated by commas (I think). You could then have a string array
and separate each file name into an element of the string array, trimming
out path info in the process.

HTH,

Conan





"Don" wrote in message
...
This might turn out to be a double post and if so, I
apologize...something
went wrong in attempting to post this the first time.

I'd like to, using VBA in Excel 2003, open a browser window and locate
a
file, then highlight that file and have the file name entered into a
WS.

I have a code that opens a browser window and also record all the file
names
in a particular folder but will not allow me to selectively choose
which
file
name I want recorded.

Again I apologize if this hits the board twice but something went wrong
on
the first try......TIA,

Don

Hi again Conan and again I hope this isn't a duplicate post but it didn't
seem to go through the first try....

I hate to keep bothering you with this as I do have one macro that does do
the job, but you introduced me to a new piece of code I've never used before
and it's much cleaner than what I came up with. I would like to get it to
work, if possible. Here's the new code I came up with based on your last
response:

Option Explicit
Sub FindFilename()
Dim LastCol As Long
Dim MyStr As String
Dim pstrFileName As String

pstrFileName = Application.GetOpenFilename

MsgBox pstrFileName
Range("A1") = pstrFileName

pstrFileName = Right(pstrFileName, InStrRev(pstrFileName, "\"))

MsgBox pstrFileName
Range("A2") = pstrFileName

pstrFileName = Left(pstrFileName, InStrRev(pstrFileName, ".") - 1)

MsgBox pstrFileName
Range("A3") = pstrFileName

End Sub


Below are the results returned on one run of the macro:

A1 = C:\DOCS\UserGuide.ico
A2 = uide.ico
A3 = uide

I've tried this code on several runs on different files and the macro is
indiscriminate
in where it cuts the first part of the path off. It does not key on the
last instance of "\" in the path. As I said, it's indicriminate and
selecting several different files, there appears to be no consistency in
where the code lands for removal of the first part of the path.

The step between A2 and A3 works correctly everytime.

Could you please take a look and see what I'm missing here? I would
appreciate it very much....thanks again for your time.


Don


"Conan Kelly" wrote:

Don,

I see how that works for you. If you wanted to, though, you could clean it
up by extracting the file name in VBA and then putting it into the cell you
need, in stead of putting it in empty cell and parsing it in XL.

Something like this should work:

Sub FindFilename()
Dim LastCol As Long
Dim MyStr As String
Dim pstrFileName as string

'Stores the file selected (including path) in a string variable
pstrFileName = Application.GetOpenFilename

'Should trim off the whole path leaving only the file
'name and extension and then stores it in the same string variable.
pstrFileName = Right(pstrFileName, InStrRev(pstrFileName,"\"))

'I Think that should work, but if it is 1 character off, add/subtract
'1 from the InStrRev() function inside the right function (put a "+1"
'or a "-1" between the closing paren. of InStrRev() and the closing
'paren. of Right()). Like so:
'pstrFileName = Right(pstrFileName, InStrRev(pstrFileName,"\") + 1)
'pstrFileName = Right(pstrFileName, InStrRev(pstrFileName,"\") - 1)

'Make sure you check to see what is being stored in the variable with
a...
'Debug.Print pstrFileName
'...or a...
'MsgBox pstrFileName

'Trims off the extention from the file name (if that extension is a
period/decimal point
'with a 3 character extention)
pstrFileName = Left(pstrFileName,Len(pstrFileName)-4)

'if you might have extensions of varying lengths, then you could use
'InStrRev() with Left() to look for the period/decimal point.

'Puts the file name in the exact location you need it in
Workbooks("[your workbook name]").Worksheets("[desired sheet
neme]").Range("[desired cell address]") = pstrFileName

'Any other code necessary:
...
...
...

End Sub


(this has not been tested. I typed this in "freehand" into this post. Did
not copy/paste from the VBE. Typos are possible...hopefully you are using
"Option Explicit" so any would be caught)

HTH,

Conan






"Don" wrote in message
...
OK...this threads done, I think....figured it out, cleaned it up a bit,
added
a couple of safeguards and here's the final product.....and amazingly, IT
WORK!!...LOL

Dim LastCol As Long
Dim MyStr As String

Sub FindFilename()

On Error Resume Next

' clear contents of rows 1 through 5

Rows("1:5").ClearContents
Sheet1.Range("A1").Select
ActiveCell = Application.GetOpenFilename

' Use text to columns to seperate path into sub-folders

Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True,
OtherChar _
:="\", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1),
Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1)), TrailingMinusNumbers:=True

' LastCol = Last column in row 1 that has data in it.

LastCol = ActiveSheet.UsedRange.Columns.Count
MyStr = ActiveSheet.Cells(1, LastCol)

' Cut the extension off the file name

Range("A2") = Left(MyStr, Len(MyStr) - 4)
Columns("A:A").EntireColumn.AutoFit
MsgBox Range("A2")

' Then I can move contents of A2 to the sheet and/or cell required

End Sub

Ya'll have a great day now.....and thanks again,

Don


"Conan Kelly" wrote:

Don,

Do you only need one file returned? How about
application.getopenfilename?

Activecell = application.GetOpenFilename

But, if I remember correctly, that will return the the whole path and
filename. You could put code in there to trim out the path and return
just
the file name.

Do you need more than one file name? Lookup GetOpenFilename in VBA Help.
There is a "MultiSelect" argument that will allow you to select more than
one file. But once again, it will return one string with files
(including
paths) separated by commas (I think). You could then have a string array
and separate each file name into an element of the string array, trimming
out path info in the process.

HTH,

Conan





"Don" wrote in message
...
This might turn out to be a double post and if so, I
apologize...something
went wrong in attempting to post this the first time.

I'd like to, using VBA in Excel 2003, open a browser window and locate
a
file, then highlight that file and have the file name entered into a
WS.

I have a code that opens a browser window and also record all the file
names
in a particular folder but will not allow me to selectively choose
which
file
name I want recorded.

Again I apologize if this hits the board twice but something went wrong
on
the first try......TIA,

Don

Don,

That's because I screwed up!!! :-( Like I said, I typed it in freehand in
the response to your post without testing it out. I guess that is what can
happen.

pstrFileName = Right(pstrFileName, InStrRev(pstrFileName, "\"))

should be something like this:

pstrFileName = Right(pstrFileName, len(pstrFileName) -
InStrRev(pstrFileName, "\"))

Hopefully that should work for you.

Sorry for the mistake.

Conan




"Don" wrote in message
...
Hi again Conan and again I hope this isn't a duplicate post but it didn't
seem to go through the first try....

I hate to keep bothering you with this as I do have one macro that does do
the job, but you introduced me to a new piece of code I've never used
before
and it's much cleaner than what I came up with. I would like to get it to
work, if possible. Here's the new code I came up with based on your last
response:

Option Explicit
Sub FindFilename()
Dim LastCol As Long
Dim MyStr As String
Dim pstrFileName As String

pstrFileName = Application.GetOpenFilename

MsgBox pstrFileName
Range("A1") = pstrFileName

pstrFileName = Right(pstrFileName, InStrRev(pstrFileName, "\"))

MsgBox pstrFileName
Range("A2") = pstrFileName

pstrFileName = Left(pstrFileName, InStrRev(pstrFileName, ".") - 1)

MsgBox pstrFileName
Range("A3") = pstrFileName

End Sub


Below are the results returned on one run of the macro:

A1 = C:\DOCS\UserGuide.ico
A2 = uide.ico
A3 = uide

I've tried this code on several runs on different files and the macro is
indiscriminate
in where it cuts the first part of the path off. It does not key on the
last instance of "\" in the path. As I said, it's indicriminate and
selecting several different files, there appears to be no consistency in
where the code lands for removal of the first part of the path.

The step between A2 and A3 works correctly everytime.

Could you please take a look and see what I'm missing here? I would
appreciate it very much....thanks again for your time.


Don


"Conan Kelly" wrote:

Don,

I see how that works for you. If you wanted to, though, you could clean
it
up by extracting the file name in VBA and then putting it into the cell
you
need, in stead of putting it in empty cell and parsing it in XL.

Something like this should work:

Sub FindFilename()
Dim LastCol As Long
Dim MyStr As String
Dim pstrFileName as string

'Stores the file selected (including path) in a string variable
pstrFileName = Application.GetOpenFilename

'Should trim off the whole path leaving only the file
'name and extension and then stores it in the same string variable.
pstrFileName = Right(pstrFileName, InStrRev(pstrFileName,"\"))

'I Think that should work, but if it is 1 character off, add/subtract
'1 from the InStrRev() function inside the right function (put a "+1"
'or a "-1" between the closing paren. of InStrRev() and the closing
'paren. of Right()). Like so:
'pstrFileName = Right(pstrFileName, InStrRev(pstrFileName,"\") + 1)
'pstrFileName = Right(pstrFileName, InStrRev(pstrFileName,"\") - 1)

'Make sure you check to see what is being stored in the variable with
a...
'Debug.Print pstrFileName
'...or a...
'MsgBox pstrFileName

'Trims off the extention from the file name (if that extension is a
period/decimal point
'with a 3 character extention)
pstrFileName = Left(pstrFileName,Len(pstrFileName)-4)

'if you might have extensions of varying lengths, then you could use
'InStrRev() with Left() to look for the period/decimal point.

'Puts the file name in the exact location you need it in
Workbooks("[your workbook name]").Worksheets("[desired sheet
neme]").Range("[desired cell address]") = pstrFileName

'Any other code necessary:
...
...
...

End Sub


(this has not been tested. I typed this in "freehand" into this post.
Did
not copy/paste from the VBE. Typos are possible...hopefully you are
using
"Option Explicit" so any would be caught)

HTH,

Conan






"Don" wrote in message
...
OK...this threads done, I think....figured it out, cleaned it up a bit,
added
a couple of safeguards and here's the final product.....and amazingly,
IT
WORK!!...LOL

Dim LastCol As Long
Dim MyStr As String

Sub FindFilename()

On Error Resume Next

' clear contents of rows 1 through 5

Rows("1:5").ClearContents
Sheet1.Range("A1").Select
ActiveCell = Application.GetOpenFilename

' Use text to columns to seperate path into sub-folders

Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True,
OtherChar _
:="\", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1),
Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1)), TrailingMinusNumbers:=True

' LastCol = Last column in row 1 that has data in it.

LastCol = ActiveSheet.UsedRange.Columns.Count
MyStr = ActiveSheet.Cells(1, LastCol)

' Cut the extension off the file name

Range("A2") = Left(MyStr, Len(MyStr) - 4)
Columns("A:A").EntireColumn.AutoFit
MsgBox Range("A2")

' Then I can move contents of A2 to the sheet and/or cell required

End Sub

Ya'll have a great day now.....and thanks again,

Don


"Conan Kelly" wrote:

Don,

Do you only need one file returned? How about
application.getopenfilename?

Activecell = application.GetOpenFilename

But, if I remember correctly, that will return the the whole path and
filename. You could put code in there to trim out the path and return
just
the file name.

Do you need more than one file name? Lookup GetOpenFilename in VBA
Help.
There is a "MultiSelect" argument that will allow you to select more
than
one file. But once again, it will return one string with files
(including
paths) separated by commas (I think). You could then have a string
array
and separate each file name into an element of the string array,
trimming
out path info in the process.

HTH,

Conan





"Don" wrote in message
...
This might turn out to be a double post and if so, I
apologize...something
went wrong in attempting to post this the first time.

I'd like to, using VBA in Excel 2003, open a browser window and
locate
a
file, then highlight that file and have the file name entered into a
WS.

I have a code that opens a browser window and also record all the
file
names
in a particular folder but will not allow me to selectively choose
which
file
name I want recorded.

Again I apologize if this hits the board twice but something went
wrong
on
the first try......TIA,

Don