How Can I: Read Workbook Name and Separate By Looking for the Underscore _ Character

[rdsmit1]

I have a file named 025_05-05-2015.xls, where I want to create an xml file from its contents, and also have the xml file named 025 (pulling from the excel file name), and contain the part measure date (the 05-05-2015) within the xml file, which it also pulls out of the original file name. The code below doesn't work to accomplish this and I'm not sure why... Below is a simplified version of my code, any help is greatly appreciated, thanks!

Sub Name_Split_Proof_of_Concept_Click()


Dim FileName As String
Dim FileName1 As String
Dim splitFileName() As String
Dim InventoryName As String
Dim PartMeasureDate As String


FileName1 = ActiveWorkbook.Name
FileName = Replace(FileName, ".xls", "")


If InStr(FileName, "_") > 0 Then
splitFileName = Split(FileName, "_")
InventoryName = splitFileName(0)
PartMeasureDate = splitFileName(1)
End If


'Get the file to save as
Dim file As String
file = Application.GetSaveAsFilename(InventoryName & ".xml", "XML Files (*.xml), *.xml", , "Save File")


'Exit if cancelled
If file = "False" Then Exit Sub

'Open the file and write it
Open file For Output As #1

'Export
Print #1, "<?xml version=""1.0"" encoding=""ISO-8859-1"" ?>"
Print #1, "<Parts>"
Print #1, " <Debug>1</Debug>"
Print #1, " <Part Name=" & InventoryName & ">"
Print #1, " <PartInventoryName>" & InventoryName & "</PartInventoryName>"
Print #1, " <PartCreateDate>"; PartMeasureDate; "</PartCreateDate>"
Print #1, " <EffectiveDate>"; PartMeasureDate; "</EffectiveDate>"
Close #1
End Sub

 

[JoeMo]

This seems problematic:

FileName1 = ActiveWorkbook.Name
FileName = Replace(FileName, ".xls", "")


If InStr(FileName, "_") > 0 Then
splitFileName = Split(FileName, "_")
InventoryName = splitFileName(0)
PartMeasureDate = splitFileName(1)
End If

You've not defined FileName so it takes the default value of "". That causes the lines between IF and End IF to be skipped entirely. Is FileName supposed to be identical to FileName1?

 

[rdsmit1]

That's all it was too! I F***ing love you! Made my day, thanks!

 

[JoeMo]

That's all it was too! I F***ing love you! Made my day, thanks!

You are welcome - thanks for the reply.