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.
'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.