About Us

Dave Peterson

Hope you got it working the way you want.

wrote:

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 wrote:
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

wrote:

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

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

Dave Peterson wrote:
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).

wrote:

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

wrote:
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 wrote:
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.

wrote:

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 wrote:
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://"????).

wrote:

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

Mark Driscol wrote:
Very nice.

Mark

Gary''s Student wrote:
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.
--
Gary's Student

" wrote:

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

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
get the current filename as a variable	Janis	Excel Programming	10	September 2nd 06 02:01 PM
Converting a Variable Filename to a Constant Filename	Magnivy	Excel Programming	2	August 15th 06 06:13 PM
Variable Filename calls	Grymjack	Excel Programming	0	June 7th 06 05:34 PM
Put Filename Variable in Macro	David	Excel Programming	1	December 27th 05 03:00 AM
Filename Variable?	Penflex	Excel Programming	2	May 5th 05 05:11 PM

Menu

About Us