Rick_Stanich
Technical User
I have a file name in this format.
Ruby Head_Rev 2_Sabre_Op 20_M0020-ALL - H23410_2023-6-6_103655 AM.xls
I am trying to find the 4th occurrence of the "_" (Under score) and obtain the value of the string to its Left, not including the "_".
Obtain this portion of the string: "Ruby Head_Rev 2_Sabre_Op 20"
Using Split I get a portion of the string to its Right (M0020-ALL - H23410), but not the entire string. I think Split is cutting off the string at the first "_" to the right?
For testing purposes I have added MsgBox's to see my result and also removed the SaveAs code.
I've tried incorporating Left into the Split code but I have been unsuccessful.
Any help is appreciated.
Rick Stanich
CMM Programming and Consulting
Ruby Head_Rev 2_Sabre_Op 20_M0020-ALL - H23410_2023-6-6_103655 AM.xls
I am trying to find the 4th occurrence of the "_" (Under score) and obtain the value of the string to its Left, not including the "_".
Obtain this portion of the string: "Ruby Head_Rev 2_Sabre_Op 20"
Using Split I get a portion of the string to its Right (M0020-ALL - H23410), but not the entire string. I think Split is cutting off the string at the first "_" to the right?
For testing purposes I have added MsgBox's to see my result and also removed the SaveAs code.
I've tried incorporating Left into the Split code but I have been unsuccessful.
Code:
Dim objXL, strMessage
On Error Resume Next
Set objXL = GetObject(,"Excel.Application")
If Not TypeName(objXL) = "Empty" then
'MsgBox "The active workbook name is - " & objXL.ActiveWorkbook.Name 'for testing, retrieves active workbook name ok
If Left(objXL.ActiveWorkbook.Name,18) = "Ruby Head_Rev 2_Op" Then 'uses generic file naming
sFileName = Split(objXL.ActiveWorkbook.Name, "_")(3)
MsgBox "Generic - " & sFileName 'for testing
'objXL.ActiveWorkbook.SaveAs "C:\Users\Public\Documents\Zeiss\CALYPSO\workarea\results\Excel Files" & "\" & sFileName & "\" & objXL.ActiveWorkbook.Name
End If
If Left(objXL.ActiveWorkbook.Name,24) = "Ruby Head_Rev 2_Sabre_Op" Then 'uses Sabre file naming
sFileName = Split(objXL.ActiveWorkbook.Name, "_")(4)
MsgBox "Sabre - " & sFileName 'for testing
'objXL.ActiveWorkbook.SaveAs "C:\Users\Public\Documents\Zeiss\CALYPSO\workarea\results\Excel Files" & "\" & sFileName & "\" & objXL.ActiveWorkbook.Name
End If
Else
MsgBox "No active Excel file open."
End If
Any help is appreciated.
Rick Stanich
CMM Programming and Consulting