Filename variable

[starke1120]

I have a variable that contains a Path and filename.. is there a way to
strip out just the file name using vba code...

for example:

strPathFile = c:\windows\test.png

i need

strFileName = test.png

I need somthing to start at the right most character and then move left
till it finds "\"...

Thanks,

Dave

 

[Dave Peterson]

If you're using xl2k or higher, you can use InstrRev to get the position of the
last \.

dim LastSlash as long
dim strFileName as string
strPathFile = c:\windows\test.png
lastslash = instrrev(strpathfile, "\")
if lastslash > 0 then
msgbox mid(strpathfile, lastslash + 1)
end if

If you're using xl97 or below, you can just loop from the far right back to the
beginning.

dim LastSlash as long
dim iCtr as long
dim strFileName as string
strPathFile = c:\windows\test.png
lastslash = 0
for ictr = len(strpathfile) to 1 step -1
if mid(strpathfile, ictr,1) = "\" then
lastslash = ictr
exit for
end if
next ictr
if lastslash > 0 then
msgbox mid(strpathfile, lastslash + 1)
end if

(This will work for xl2k+, too.)

 

[Mark Driscol]

Assuming the text is in cell B2, here is a formula from one of John
Walkenbach's books that will do it. You don't need to use VBA.

=RIGHT(B2,LEN(B2)-FIND("*",SUBSTITUTE(B2,"\","*",LEN(B2)-LEN(SUBSTITUTE(B2,"\","")))))

Basically, it finds out how many "\" there are, finds the location of
the last one, and then takes the characters to the right of it.


Mark

 

[Guest]

Try:

Function getThatName(s As String) As String
s2 = Split(s, "\")
getThatName = s2(UBound(s2))
End Function

Will get the filename. Does not matter how deeply the path is nested.

 

[Mark Driscol]

Very nice.

Mark

Try:

Function getThatName(s As String) As String
s2 = Split(s, "\")
getThatName = s2(UBound(s2))
End Function

Will get the filename. Does not matter how deeply the path is nested.

 

[starke1120]

Everyone thanks for the help.. Im hoping before to long I can learn
VBA and start to understand why things work the way they do..

So far my Sub is working with one exception and I can't figure it out.
My sFileName variable is returning a value of "57", but in the message
box it shows the correct file name... any thoughts?

Here is the code...
_________________________________________________________________
Private Sub cboUpdate_Click()

Dim hyplink As String
Dim retval As Long
Dim sFileName As String


'set hyplink value as the forms text box.
hyplink = Forms!frmGuides!hyperlink1

'get the file name only, start to the right and go until the
backslash
sFileName = InStrRev(hyplink, "\")
If sFileName > 0 Then

'msgbox to show the right file name, which works....
MsgBox Mid(hyplink, sFileName + 1)
End If

'set the file Dest
retval = CopyFile(hyplink, "c:\Route\" & sFileName, 1)

Debug.Print hyplink
Debug.Print sFileName


If retval = 0 Then ' failure
MsgBox "Copy failed --" & hyplink & ".", vbCritical, "UPS DMS"

Else ' success
MsgBox "Copy succeeded."
End If

End Sub
____________________________________________________________________


Thanks!

Dave

 

[Dave Peterson]

This might get you further:

(Untested)

Option Explicit
Private Sub cboUpdate_Click()

Dim hyplink As String
Dim retval As Long
Dim sFileName As String
Dim SlashPos As Long

'set hyplink value as the forms text box.
hyplink = frmGuides.hyperlink1

'get the file name only, start to the right and go until the backslash
SlashPos = InStrRev(hyplink, "\")
If SlashPos > 0 Then
sFileName = Mid(hyplink, sFileName + 1)
End If

'set the file Dest
retval = FileCopy(hyplink, "c:\Route\" & sFileName)

Debug.Print hyplink
Debug.Print sFileName

If retval = 0 Then ' failure
MsgBox "Copy failed --" & hyplink & ".", vbCritical, "UPS DMS"

Else ' success
MsgBox "Copy succeeded."
End If

End Sub

Notice that I used VBA's FileCopy--not FSO's CopyFile.

But if you want...

Option Explicit
Private Sub cboUpdate_Click()

Dim FSO As Object
Dim hyplink As String
Dim retval As Long
Dim sFileName As String
Dim SlashPos As Long

Set FSO = CreateObject("Scripting.FileSystemobject")

'set hyplink value as the forms text box.
hyplink = frmGuides.hyperlink1

'get the file name only, start to the right and go until the backslash
SlashPos = InStrRev(hyplink, "\")
If SlashPos > 0 Then
'msgbox to show the right file name, which works....
sFileName = Mid(hyplink, sFileName + 1)
End If

'set the file Dest
retval = FSO.CopyFile(Source:=hyplink, Destination:="c:\Route\" & sFileName,
_
overwrite:=True)

Debug.Print hyplink
Debug.Print sFileName

If retval = 0 Then ' failure
MsgBox "Copy failed --" & hyplink & ".", vbCritical, "UPS DMS"

Else ' success
MsgBox "Copy succeeded."
End If

End Sub

And depending on what's in that hyperlink address, you might have to strip more
stuff off (does it start with "File://"????).

 

[starke1120]

Dave,

Thanks for the reply, Im a bit confused though.. My file copy does
work, its the sFilename that is holding the incorrrect value. In the
msgbox that pops up, it does show the correct value of the file name
being copied, on the othern had, but "immediate window" when I do
debug.print sFileName it prints "57" to the immediate window and in the
dest folder it names the file name "57". I dont understand why the
msgbox shows the correct filename, but the varaiable holds the value of
"57".

Thanks,

Dave

 

[Dave Peterson]

you used:
sFileName = InStrRev(hyplink, "\")

This returns the position of the last backslash--not the name of the file. The
message box included in my first response was only to show you how to obtain the
name of the file. But you didn't use it. I replaced the message box with this:
sFileName = Mid(hyplink, sFileName + 1)
But this was a typo...

It should have used that SlashPos variable:
sFileName = Mid(hyplink, SlashPos + 1)

I don't understand how copyfile could have worked in the code you showed.

There is a VBA command named FileCopy, though.

 

[starke1120]

Dave,

Thanks for the update... I added
_________________________________
'set the file Dest
retval = CopyFile(hyplink, "c:\Route\" & Mid(hyplink, SlashPOS + 1),
1)
_________________________________

and now the file name appears... I believe I left out some important
code and this may be why you didnt understand why it worked. Im a
newbie at VBA and didnt realize my code was referring back to these
lines...

Private Declare Function CopyFile Lib "kernel32.dll" Alias "CopyFileA"
_
(ByVal lpExistingFileName As String, ByVal lpNewFileName As String,
_
ByVal bFailIfExists As Long) As Long

This is at the very top of my form VBA...

Thanks for all your help!

Dave

 

[Dave Peterson]

Yeah. The explains how CopyFile works for you <bg>.

Glad you got it working.

Dave,

Thanks for the update... I added
_________________________________
'set the file Dest
retval = CopyFile(hyplink, "c:\Route\" & Mid(hyplink, SlashPOS + 1),
1)
_________________________________

and now the file name appears... I believe I left out some important
code and this may be why you didnt understand why it worked. Im a
newbie at VBA and didnt realize my code was referring back to these
lines...

Private Declare Function CopyFile Lib "kernel32.dll" Alias "CopyFileA"
_
(ByVal lpExistingFileName As String, ByVal lpNewFileName As String,
_
ByVal bFailIfExists As Long) As Long

This is at the very top of my form VBA...

Thanks for all your help!

Dave

 

[starke1120]

I do have one more question, the script works perfect if it has a drive
letter specified in

'set the file Dest
retval = CopyFile(hyplink, "c:\route\" & Mid(hyplink, sFileName +
1), 1)

but what if I want to copy it to a Network drive but do not want to
hard map it. I dont want the user's mapping to the directory where I
copy the files to for security purposes. I tried
__________________________________
'set the file Dest
retval = CopyFile(hyplink, "\\153.2.96.52\c$\route\" & Mid(hyplink,
sFileName + 1), 1)
__________________________________

\\153.2.96.52\c$\route\ works when I type it in the address bar of
Windows explorer, so I was assuming it would work here.. but it
bombs...

Thanks,


Dave

Dave,

Thanks for the update... I added
_________________________________
'set the file Dest
retval = CopyFile(hyplink, "c:\Route\" & Mid(hyplink, SlashPOS + 1),
1)
_________________________________

and now the file name appears... I believe I left out some important
code and this may be why you didnt understand why it worked. Im a
newbie at VBA and didnt realize my code was referring back to these
lines...

Private Declare Function CopyFile Lib "kernel32.dll" Alias "CopyFileA"
_
(ByVal lpExistingFileName As String, ByVal lpNewFileName As String,
_
ByVal bFailIfExists As Long) As Long

This is at the very top of my form VBA...

Thanks for all your help!

Dave

 

[Dave Peterson]

First, I don't have a network drive to test, and I don't know about the API that
you used to know if it supports those UNC paths.

But VBA's FileCopy and FSO's CopyFile will work with UNC paths (if I remember
correctly).

 

[starke1120]

You are correct Dave... FSO worked like a charm!!! Thanks for all your
help!

 

[starke1120]

Just noticed a small issue and I can't seem to figure it out.
Hopefully this will be the last (at least for this script.

Once I changed it over to FSO, the network drives works but now my
"retval" line ...

retval = FSO.CopyFile(Source:=strhyplink,
Destination:="\\192.168.0.51\Route\" & Mid(strhyplink, strFileName +
1))

stays at "zero" and always throws up the message box... here is my
code in full (I changed some stuff....

_______________________________________________________________________
Private Sub cboUpdate_Click()
Dim FSO As Object
Dim strhyplink As String
Dim retval As Long
Dim strFileName As String


Set FSO = CreateObject("Scripting.FileSystemobject")

'set strhyplink value as the forms text box.
strhyplink = Forms!frmGuides!txtSource

'get the file name only, start to the right and go until the
backslash
strFileName = InStrRev(strhyplink, "\")
If strFileName > 0 Then

'msgbox to show the right file name,
MsgBox Mid(strhyplink, strFileName + 1)
End If

'set the file Dest
retval = FSO.CopyFile(Source:=strhyplink,
Destination:="\\192.168.0.51\Route\" & Mid(strhyplink, strFileName +
1))

Debug.Print strhyplink
Debug.Print Mid(strhyplink, strFileName + 1)


If retval = 0 Then ' failure
MsgBox "Import Failed!!!" & vbNewLine & vbNewLine & _
"File Name: '" & Mid(strhyplink, strFileName + 1) & "'"
& vbNewLine & vbNewLine & _
"Reason: File already exist!", vbCritical, "UPS DMS"

Else ' success
MsgBox "Import of '" & Mid(strhyplink, strFileName + 1) & "'
succeeded."
Me.txtDest = strFileName
End If

'release memory
Set FSO = Nothing


End Sub
________________________________________________________________

Thanks

Dave

You are correct Dave... FSO worked like a charm!!! Thanks for all your
help!

 

[Dave Peterson]

Again, strFileName is a string--it should be used to hold the name of the
file--not the position of the last backslash character.

And you can actually tell FSO to overwrite any file when you copy:

Option Explicit
Private Sub cboUpdate_Click()
Dim FSO As Object
'Dim FSO As Scripting.FileSystemObject
Dim strHyplink As String
Dim strFileName As String
Dim SlashPos As Long

Set FSO = CreateObject("Scripting.FileSystemobject")

'set strhyplink value as the forms text box.
strhyplink = Forms!frmGuides!txtSource
'strHyplink = "c:\my documents\excel\book1.xls"

'get the file name only, start to the right and go until the backslash
SlashPos = InStrRev(strHyplink, "\")
If SlashPos > 0 Then
'msgbox to show the right file name,
strFileName = Mid(strHyplink, SlashPos + 1)
Else
strFileName = strHyplink
End If

Debug.Print strHyplink

On Error Resume Next
FSO.CopyFile Source:=strHyplink, _
Destination:="\\192.168.0.51\Route\" & strFileName, _
overwritefiles:=True
If Err.Number <> 0 Then
MsgBox Err.Number & vbLf & Err.Description
MsgBox "Import Failed!!!" & vbNewLine & vbNewLine & _
"File Name: '" & strFileName & "'" & vbNewLine & vbNewLine & _
"Reason: File already exist!", vbCritical, "UPS DMS"
Err.Clear
Else ' success
MsgBox "Import of '" & strFileName & "' succeeded."
Me.txtDest = strFileName
End If
On error goto 0

'release memory
Set FSO = Nothing

End Sub

 

[starke1120]

Dave,

Thanks for all your help... Sometimes Im a little slow.. , I see
what you are saying now that Im looking back over your code.

Dave

 

[Dave Peterson]

Hope you got it working the way you want.

Dave,

Thanks for all your help... Sometimes Im a little slow.. , I see
what you are saying now that Im looking back over your code.

Dave