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

rachel06

Board Regular
Joined
Feb 3, 2016
Messages
52
Office Version
  1. 365
Platform
  1. Windows
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!!

Code:
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!
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,784
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
What happens if you try .value instead of .text in your newname string
 

rachel06

Board Regular
Joined
Feb 3, 2016
Messages
52
Office Version
  1. 365
Platform
  1. Windows
Hello,

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
What exactly is in G6?
 

rachel06

Board Regular
Joined
Feb 3, 2016
Messages
52
Office Version
  1. 365
Platform
  1. Windows
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! 🤦‍♀️
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,386
Messages
5,636,015
Members
416,892
Latest member
Bensch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top