Using a TextBox value in a file String

[Mark Dullingham]

I have the following code ina standard module, the user can enter there own
file name via a form.

Sub The_SubSave()
Dim fName As String
fName = frmOptions.TextBox1.Value
If frmOptions.CheckBox2.Value = False Then
ActiveWorkbook.SaveCopyAs "C:\AutoSaves" & "fName" _
& "." & Format(Date, "dd-mmm-yyyy") & "." & Format(Time, "hh-mm-ss") &
".xls"
Else
ActiveWorkbook.SaveCopyAs "C:\AutoSaves\DDE Sheet" _
& "." & Format(Date, "dd-mmm-yyyy") & "." & Format(Time, "hh-mm-ss") &
".xls"
End If
StartTimer

End Sub

The problem is if the checkbox is false the first if statement which include
the textbox value as part if the file path does'nt work.

Ive trie placing in( ) and " " of having the tectbox in the string but it
does not wok!
The Else option works fine!
Does anyone have any suggestions

Thanks in advance for any help

 

[paul.robinson]

Hi
fName is already a string so you want to use fName not "fName"
You also have a periods (.) in the middle of your strings , as well as
the one in front of your xls at the end. This is a file name no no so
you should remove them.
regards
Paul

 

[Mark Dullingham]

Thanks for your reply.

The () in the in the string are there to define the 2 format statements abd
don't appear in the file name.

If called for -

ActiveWorkbook.SaveCopyAs "C:\AutoSaves\DDE Sheet" _
& "." & Format(Date, "dd-mmm-yyyy") & "." & Format(Time, "hh-mm-ss") & ".xls"

Works fine. What I want to do is replace DDE Sheet with the fname , manely
the value of textbox1, but in it's current format it doesn't wor!!

 

[Dave Peterson]

Maybe something like:

ActiveWorkbook.SaveCopyAs "C:\AutoSaves\" & fname & _
& "." & Format(now, "dd-mmm-yyyy.hh-mm-ss") & ".xls"

or (Since I'm confused):

ActiveWorkbook.SaveCopyAs "C:\AutoSaves\" & textbox1.text & _
& "." & Format(now, "dd-mmm-yyyy.hh-mm-ss") & ".xls"

(I changed the date and time to now, too.)

 

[Mark Dullingham]

Dave,

Thanks a million, both your suggestions worked great.

That was the last thing I had to get working on this project and it's been
frustrating me for days.

Thanks for taking the time to reply, much appreciated.

Mark