Trim Workbook Path?
 

Hi -

How can I replace part of the ThisWorkBook.Path property? Lucking,
the 10 characters I need to replace the END of the path (not in the
middle). To be even more specific:
THIS: \\server\drive\folder\folder\folder\DRAFT Recs
BECOMES: \\server\drive\folder\folder\folder\FINAL Recs

The "DRAFT Recs" folder itself shouldn't be re-named or deleted ...
I'm really just moving individual files from one folder to the other.
Problem is the whole path name will vary for different users ...

I would assume that the solution is to Ttrim the path by 10 characters
or to replace "DRAFT" with "FINAL" (and some of Ron deBruin's code to
re-name the file) ... but I have no idea how to do either of these?

any ideas?

thanks, ray

Trim Workbook Path?
 

If you're using xl2k or higher, you can use instrrev to search for the last
backslash. Then drop everything after that and replace it with your new string.

Option Explicit
Sub testme02()
Dim myOldPath As String
Dim myNewPath As String
Dim myNewStr As String
Dim LastBackSlash As Long

myOldPath = "\\server\drive\folder\folder\folder\DRAFT Recs"
myNewStr = "final recs"

LastBackSlash = InStrRev(stringcheck:=myOldPath, stringmatch:="\",
Start:=-1, compa=vbTextCompare)

If LastBackSlash = 0 Then
MsgBox "no backslashes"
Else
myNewPath = Left(myOldPath, LastBackSlash) & myNewStr
MsgBox myOldPath & vbLf & myNewPath
End If
End Sub

Or you could if that last portion is unique, you could use replace (still
requires xl2k or higher) to replace that string.

Option Explicit
Sub testme03()
Dim myOldPath As String
Dim myNewPath As String
Dim myOldStr As String
Dim myNewStr As String

myOldPath = "\\server\drive\folder\folder\folder\DRAFT Recs"
myOldStr = "\draft recs"
myNewStr = "\final recs"

myNewPath = Replace(expression:=myOldPath, _
Find:=myOldStr, Replace:=myNewStr, Start:=1, _
Count:=-1, compa=vbTextCompare)

MsgBox myOldPath & vbLf & myNewPath
End Sub


Ray wrote:

Hi -

How can I replace part of the ThisWorkBook.Path property? Lucking,
the 10 characters I need to replace the END of the path (not in the
middle). To be even more specific:
THIS: \\server\drive\folder\folder\folder\DRAFT Recs
BECOMES: \\server\drive\folder\folder\folder\FINAL Recs

The "DRAFT Recs" folder itself shouldn't be re-named or deleted ...
I'm really just moving individual files from one folder to the other.
Problem is the whole path name will vary for different users ...

I would assume that the solution is to Ttrim the path by 10 characters
or to replace "DRAFT" with "FINAL" (and some of Ron deBruin's code to
re-name the file) ... but I have no idea how to do either of these?

any ideas?

thanks, ray

--

Dave Peterson

Trim Workbook Path?
 

Hi Dave -

Thanks for the response ... I'd already found the 'Replace' function
and deleted the Posting (via GoogleGroups) but guess that doesn't
completely delete it.

OK, so I've gotten past one problem ... but run into another. Here's
the current code:

Sub SaveFinal()
Dim Store As String, Day As String, Period As String
Dim OurCopy As String, SOCopy As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Store = ActiveSheet.Range("D16").Value
Day = ActiveSheet.Range("D17").Value
Period = ActiveSheet.Range("D18").Value
Draft = ThisWorkbook.Path & "\" & ThisWorkbook.Name
Final = Replace(ThisWorkbook.Path, "DRAFT", "FINAL") & "\" &
Replace(ThisWorkbook.Name, "_DRAFT", "")

If Right(ThisWorkbook.Name, 9) < "DRAFT.xls" Then
MsgBox "Sorry, this file isn't eligible to be saved as a FINAL
version ... you must save as DRAFT first!"
Exit Sub
End If

If ActiveWorkbook.Sheets("Journal").Visible = False Then
MsgBox "Sorry, only a POSTED CashRec can be saved as FINAL
version ... please post the Rec and try again!"
Else

If Dir(Replace(ThisWorkbook.Path, "DRAFT", "FINAL")) = "" Then MkDir
(Replace(ThisWorkbook.Path, "DRAFT", "FINAL"))

ActiveWorkbook.Save

' RON'S CODE: Name "C:\Users\Ron\SourceFolder\Test.xls"
As "C:\Users\Ron\DestFolder\TestNew.xls"

' ERROR on next line
Name ThisWorkbook.Path & "\" & ThisWorkbook.Name As
Replace(ThisWorkbook.Path, "DRAFT", "FINAL") & "\" &
Replace(ThisWorkbook.Name, "_DRAFT", "")

End If

Unload SaveType
End Sub


I get error 75: Path/File access error on the line indicated .... any
ideas how to modify this to do what I want?3

Thanks again,
ray

Trim Workbook Path?
 

I'd check to see if that new path actually exists.

You could always try to create the new folder in code:

on error resume next
Replace(ThisWorkbook.Path, "DRAFT", "FINAL")
on error goto 0

And I'd use the compare parm in the replace statement, too. I'd want to do a
text comparison.



Ray wrote:

Hi Dave -

Thanks for the response ... I'd already found the 'Replace' function
and deleted the Posting (via GoogleGroups) but guess that doesn't
completely delete it.

OK, so I've gotten past one problem ... but run into another. Here's
the current code:

Sub SaveFinal()
Dim Store As String, Day As String, Period As String
Dim OurCopy As String, SOCopy As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Store = ActiveSheet.Range("D16").Value
Day = ActiveSheet.Range("D17").Value
Period = ActiveSheet.Range("D18").Value
Draft = ThisWorkbook.Path & "\" & ThisWorkbook.Name
Final = Replace(ThisWorkbook.Path, "DRAFT", "FINAL") & "\" &
Replace(ThisWorkbook.Name, "_DRAFT", "")

If Right(ThisWorkbook.Name, 9) < "DRAFT.xls" Then
MsgBox "Sorry, this file isn't eligible to be saved as a FINAL
version ... you must save as DRAFT first!"
Exit Sub
End If

If ActiveWorkbook.Sheets("Journal").Visible = False Then
MsgBox "Sorry, only a POSTED CashRec can be saved as FINAL
version ... please post the Rec and try again!"
Else

If Dir(Replace(ThisWorkbook.Path, "DRAFT", "FINAL")) = "" Then MkDir
(Replace(ThisWorkbook.Path, "DRAFT", "FINAL"))

ActiveWorkbook.Save

' RON'S CODE: Name "C:\Users\Ron\SourceFolder\Test.xls"
As "C:\Users\Ron\DestFolder\TestNew.xls"

' ERROR on next line
Name ThisWorkbook.Path & "\" & ThisWorkbook.Name As
Replace(ThisWorkbook.Path, "DRAFT", "FINAL") & "\" &
Replace(ThisWorkbook.Name, "_DRAFT", "")

End If

Unload SaveType
End Sub

I get error 75: Path/File access error on the line indicated .... any
ideas how to modify this to do what I want?3

Thanks again,
ray

--

Dave Peterson