PDA

View Full Version : TextBox Has Numericals, Value is String of Hexadecimals



SubSurge
01-19-2016, 10:39 AM
Hi, I am trying to implement a bit of code into the "Save" button of a UserForm where when the user clicks "Save" the file will be saved in a certain folder with the filename equal to an ID number they enter in TextBox6. If I use the following code...

'Declare variables for saving file
Dim Path As String
Dim FileName As String
Path = "X:\Path\To\Folder\"
FileName = Me.TextBox6.Value & ".xlsm"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Path & FileName, FileFormat:=52
Application.DisplayAlerts = True

...then the filename comes out in a hexidecimal string rather than the actual number typed in Me.TextBox6.

If I decide to sacrifice the ".xlsm" in the hopes that FileFormat:=52 will render the extension correctly, I try this variation...

'Declare variables for saving file
Dim Path As String
Dim FileName As Long
Path = "X:\Path\To\Folder\"
FileName = Me.TextBox6.Value
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Path & FileName, FileFormat:=52
Application.DisplayAlerts = True
...I get a slightly different hex filename. What I need is VBA to pass whatever is typed in the ID field ("123456") to be passed to the FileName variable in such a way that the ActiveWorkbook.SaveAs command will save the file as "X:\Path\To\Folder\123456.xlsm" ...

I am new to VBA, using Excel 2010, and have tried googling. Any help is appreciated.

SubSurge
01-19-2016, 10:45 AM
As another troubleshooting step I tried adding a line before Application.DisplayAlerts = False that calls a MsgBox that displays the FileName variable, and it prints on the screen correctly. So what is happening to the value when that variable is passed to the SaveAs command?

SamT
01-19-2016, 02:39 PM
What happens with

FileName = TextBox6 & ".xlsm" Me. and .Value are usually superfluous.

and

FileName = CStr(TextBox6) & ".xlsm"

I don't see why you're getting a "Hex string"

snb
01-20-2016, 01:38 AM
reduce the code to


Sub M_save()
ActiveWorkbook.SaveAs "X:\Path\To\Folder\" & TextBox6.Text & ".xlsm", 5
End Sub

Avoid variables that do not vary.