Mitch,
I hope you don't mind if I offer a simplification to your subroutine. This
line of code
icount = InStrRev(FullPath, "\")
could replace your Do loop. Then your last two statements would look like
SourceFile = Mid(FullPath, icount + 1)
SourcePath = Mid(FullPath, 1, icount - 1)
The VBA function InStrRev will locate the position in the string (counting
from the left) of the rightmost instance of the search string.
Eric Pitzer
PNC Financial Services Group, Inc.
Xpack Network Services Division
1-800-919-7749
"Lawrence, Mitchell" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
12/05/2007 03:36 PM
Please respond to
[email protected]
To
<[email protected]>
cc
Subject
RE: [Talk] Quick VBA question
Thanks. Indeed the Source* variables are declared globally. I?ve changed
the function to a subroutine and assigned strFind = FullPath
Thank you,
Mitch Lawrence
Lead Applications Analyst
Technical Support - NPR/Automation
CHRISTUS Information Management
?: [EMAIL PROTECTED]
Send a "thank you" to someone!
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Neal Quinn
Sent: Wednesday, December 05, 2007 3:28 PM
To: [email protected]
Subject: Re: [Talk] Quick VBA question
No, unfortunately it won't. But don't worry, it's a minor fix. A
FUNCTION must return a value, whereas a SUBROUTINE cannot. You have a
function, and do not assign a value to it in the code. Also, unless
SourceFile and SourcePath are declared outside the module (i.e., at the
top of the module, prior to any SUB or FUNCTION declarations), then their
scope is limited to the GetPathAndFile function (although you didn't
declare them with a DIM statement within the function).
I suspect that you have made SourceFile and SourcePath global to the
module, which is fine, and therefore their values can be passed back and
forth between routines. If this is the case, then you might want to
change the Function to a Sub.
To get to the specifics, you need to initialize strFind before you test it
in the Left command. It is a zero length string until it has been
assigned a value. I would start with strFind = FullPath, outside the Do
loop.
Neal Quinn
Montefiore Medical Center
----- Original Message -----
From: Lawrence, Mitchell
To: [email protected]
Sent: Wednesday, December 05, 2007 4:05 PM
Subject: RE: [Talk] Quick VBA question
This is over my head. Will my code work or not?
I?m passing the FullPath from a 2 part /C command parameter separated by a
pipe:
C:\Bss70\Bss80.exe C:\Bss70\BWS_Scripts\ENDOFYEAR.bws/r HOLIDAY_BONUS /C
ALT.TEST55|C:\Bss70\BWS_Scripts\ALT.HOL.GIFT.2007.xls
FilePath = strWord(Command, 2, ?|?)
GetPathAndFile(FilePath)
I would need SourcePath = ?C:\Bss70\BWS_Scripts? and SourceFile =
?ALT.HOL.GIFT.2007.xls?
Thank you,
Mitch Lawrence
Lead Applications Analyst
Technical Support - NPR/Automation
CHRISTUS Information Management
?: [EMAIL PROTECTED]
Send a "thank you" to someone!
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Neal Quinn
Sent: Wednesday, December 05, 2007 2:51 PM
To: [email protected]
Subject: Re: [Talk] Quick VBA question
I had to correct the last line: it's in bold font.
Neal Quinn
----- Original Message -----
From: Neal Quinn
To: [email protected]
Sent: Wednesday, December 05, 2007 3:46 PM
Subject: Re: [Talk] Quick VBA question
Perhaps a FileSystemObject would be more to the point:
function GetPathAndFile(FullPath as String) as String
dim fileparts(1) as string
dim fso as new filesystemobject
fileparts(0) = fso.getabsolutepathname(FullPath)
fileparts(1) = fso.getbasename(FullPath)
GetPathAndFile = join(fileparts,"^") 'or whatever identifier you want
to use.
set fso = nothing
end function
You can split the path and file out with:
whatever 1 by 2 array you want = split(GetPathAndFile(FullPath),"^").
Neal Quinn
Montefiore Medical Center
----- Original Message -----
From: Lawrence, Mitchell
To: [email protected]
Sent: Wednesday, December 05, 2007 3:08 PM
Subject: [Talk] Quick VBA question
Will this function return what is expected (see comments)
Function GetPathAndFile(FullPath As String)
' This function takes a FullPath and will return the Path and File
from that FullPath
' Ex: GetPathAndFile (?C:\Path\To\File.txt?) will give
' SourcePath = "C:\Path\To"
' SourceFile = "File.txt"
Dim strFind As String
Do Until Left(strFind, 1) = "\"
iCount = iCount + 1
strFind = Right(FullPath, iCount)
If iCount = Len(FullPath) Then
Exit Do
End If
Loop
SourceFile = Right(strFind, Len(strFind) - 1)
SourcePath = Left(FullPath, Len(strFind))
End Function
Thank you,
Mitch Lawrence
Lead Applications Analyst
Technical Support - NPR/Automation
CHRISTUS Information Management
?: [EMAIL PROTECTED]
Send a "thank you" to someone!
The contents of this email are the property of PNC. If it was not addressed to
you, you have no legal right to read it. If you think you received it in error,
please notify the sender. Do not forward or copy without permission of the
sender. This message may contain an advertisement of a product or service and
thus may constitute a commercial electronic mail message under US Law. PNCs
postal address is 249 Fifth Avenue, Pittsburgh, PA 15222. If you do not wish to
receive any additional advertising or promotional messages from PNC at this
e-mail address, click here to Unsubscribe.
https://pnc.p.delivery.net/m/u/pnc/uni/p.asp By unsubscribing to this message,
you will be unsubscribed from all advertising or promotional messages from PNC.
Removing your e-mail address from this mailing list will not affect your
subscription to alerts, e-newsletters or account servicing e-mails.
