Find the nth occurrence of a string and report value to the left. 1

Rick_Stanich · Jun 8, 2023

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.

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

SkipVought · Jun 8, 2023

Hi,

Try this...

Code:

'
   Dim i As Integer, a, FileName1 As String
   Const DSH = "_"

   a = Split(objXL.ActiveWorkbook.Name, DSH)

   For i = 0 to 3
      sFileName1 = a(i) & DSH
   Next

   sFileName1 = Left(sFileName1, Len(sFileName1) - 1)

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

strongm · Jun 8, 2023

Code:

[COLOR=blue]Dim s As String, t() As String

s = "Ruby Head_Rev 2_Sabre_Op 20_M0020-ALL - H23410_2023-6-6_103655 AM.xls"

t = Split(s, "_")
ReDim Preserve t(3)
s = Join(t, "_")
MsgBox s[/color]

Andrzejek · Jun 8, 2023

Rick is using VBS, if that makes any difference.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

strongm · Jun 8, 2023

Mine works fine with VBS if you change

[tt]Dim s As String, t() As String[/tt]

to

[tt]Dim s, t[/tt]

(or even <ahem> leave out the declaration altogether ...)

Rick_Stanich · Jun 8, 2023

I ended up removing the declaration.
Thank you.

Rick Stanich
CMM Programming and Consulting

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Find the nth occurrence of a string and report value to the left. 1

Rick_Stanich

Technical User

SkipVought

Programmer

strongm

MIS

Andrzejek

Programmer

strongm

MIS

Rick_Stanich

Technical User

Similar threads

Part and Inventory Search

Sponsor