Referencing Cell Value (text) in File Name - Excel converts to a decimal. Why?

[rachel06]

Hi,

Below is my code which just renames the file. What I'm trying to do is append the initials from the analyst (which are entered into G6) to the front of the file name. Right now when I run this, Excel is appending 14.4% onto the front of the file rather than my initials which are in cell G6. I've tried several different options including trying to declare G6 as a variable and all I've gotten was the 14.4% to come out as a decimal instead. I've set cell G6 as text format as well and I'm still getting this random decimal instead of the initials.

Any help is greatly appreciated!!

Sub iPrepApprFile()
'
' Prepare Approval File
'   Copy and paste values in Tabs 6, 10, and 13 and delete extraneous rows
'   Delete unneeded tabs
'   Save as Approval File without macros
'
    Dim WBName, WBPath, BaseName, NewName, EnterName As String
    Dim NBeg, NEnd, Response As Integer
    
    
        
      
' Extract Job ID from Builder name
    WBName = ActiveWorkbook.Name
    WBPath = ActiveWorkbook.Path
    NBeg = InStr(1, WBName, "UMR")
    NEnd = InStr(NBeg, WBName, " ")
    

    BaseName = Mid(WBName, NBeg, NEnd - NBeg)
  
    
' Create new file name - "[JobID] Approval File.xlsx"
    NewName = Sheets("Analysis").Range("G6").Text & " " & BaseName & " Approval File"
    
' Save as new file name, or allow user to enter name
    Response = MsgBox("Save as " & NewName & "?", 3, NewName)

' 6 = Yes, 7 = No, Else is Cancel
    Select Case Response
' If 6, exit Select and save as new file name
    Case 6
' Get file name from user
    Case 7
        NewName = InputBox("Name to save as? ", "Save As")
' If Cancel, exit Sub
        If NewName = "" Then Exit Sub
    Case Else
        Exit Sub
    End Select


' Save with new file name
    NewName = WBPath & "\" & NewName
    ActiveWorkbook.SaveAs Filename:= _
        NewName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
         

End Sub

Any help much appreciated!

 

[jimrward]

What happens if you try .value instead of .text in your newname string

 

[rachel06]

Hello,

Instead of 14.4% at the beginning, I get 0.144290990846314.

 

[Fluff]

What exactly is in G6?

 

[rachel06]

What exactly is in G6?

RL, formatted as text

 

[rachel06]

AH. I figured it out. I forgot that another macro deletes the top six rows before this part. I thought it came after.

It was one of those days! ?‍

 

[Fluff]

Glad you sorted it & thanks for the feedback.