"Open Browser Window And Select A File"

[Don]

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

 

[scott]

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

 

[Conan Kelly]

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]

Hi Conan...

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

Don

 

[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

 

[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

 

[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]

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]

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

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

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

 

[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

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

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]

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

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

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

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


:

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





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]

Thank you Conan for the quick reply.....works beautifully. Really appreciate
you sharing your knowledge, this forumn is truly a great resource because of
you and all the others that contribute

Have a great day,

Tks again, 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

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

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






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


:

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





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

 

[Conan Kelly]

Just in case you are unfamiliar with how InStrRev() works, I'll break it
down for you.

InStrRev() returnes the starting position FROM THE LEFT of the
character/string but starts searching from the RIGHT!!!

So, in this string:

C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE

InStrRev("C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE", "\") will
return 43, because the last backslash is the 43rd character from the left.
What I was doing before was then taking the 43 characters from the right of
the string, so this is how it would break down:

Right("C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE",
InStrRev("C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE", "\"))
Right("C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE", 43)
"m Files\Microsoft Office\OFFICE11\EXCEL.EXE"

(BE CAREFUL OF INSERTED LINE BREAKS)

....because I took 43 characters from the RIGHT. What I needed to do is
calculate the length of "EXCEL.EXE" and then take that number of characters
from the right, and in order to do that, I should have taken the length
(len()) of the whole string minus the value from the InStrRev() function.
That would give me the length of the file name, then I can use that length
in the Right() function to return just the file name.

HTH,

Conan

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

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

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






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


:

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





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

 

[Conan Kelly]

Don,

Thank you for the feedback. I'm glad to help.

If you don't quite follow what was going on with the code that was wrong,
look at my other reply for an explanation.

Thanks again,

Conan

Thank you Conan for the quick reply.....works beautifully. Really
appreciate
you sharing your knowledge, this forumn is truly a great resource because
of
you and all the others that contribute

Have a great day,

Tks again, 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

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


:

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






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


:

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





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