Yes. 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 04:39 PM Please respond to [email protected] To <[email protected]> cc Subject RE: [Talk] Quick VBA question Simple is always better. So this then? Sub GetPathAndFile(FullPath As Variant) ' 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" iCount = InStrRev(FullPath,?\?) SourceFile = Mid(FullPath, iCount + 1) SourcePath = Mid(FullPath, 1, iCount - 1) End Function 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 [EMAIL PROTECTED] Sent: Wednesday, December 05, 2007 4:33 PM To: [email protected] Subject: RE: [Talk] Quick VBA question 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. PNC?s 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. 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.
