using inputbox content in filesaveas routine

[colinharwood]

Hi I am trying to take the value from an inputbox to use as the directory path in a filesaveas routine, but cannot figure out the correct format using the following code

Sub Path_Exists()
Dim Path As String
Dim Folder As String
Dim Answer As VbMsgBoxResult
Dim Response As String
'Update the path to a valid path on your PC
Path = "D:\Tonbridge MES"
Folder = Dir(Path, vbDirectory)

If Folder = vbNullString Then
Answer = MsgBox("Path D:\Tonbridge MES does not exist. Would you like to create it?", vbYesNo, "Create Path?")
Select Case Answer
Case vbYes
VBA.FileSystem.MkDir (Path)
Case Else
Response = InputBox("Please enter the location where you would like the file saved to.")
ActiveWorkbook.SaveAs FileName:="Response" \ "Email list", FileFormat:=xlCSV

End Select

End If
End Sub

It works up until the Case else statement. ANy help much appreciated
Thanks
Colin

 

[Joe4]

Yoiu have a few things backwards. Only literal text is enclosed in double-quotes, variables are not. And you join the various pieces with an ampersand (&).
So this line:

ActiveWorkbook.SaveAs FileName:="Response" \ "Email list", FileFormat:=xlCSV

would need to look something like this:

ActiveWorkbook.SaveAs FileName:=Response & "\Email list.csv", FileFormat:=xlCSV

 

[colinharwood]

Thanks very much
Colin

 

[Joe4]

You are welcome.

 

[colinharwood]

Hi Joe4
Just tried your change, but it still fails at the Activeworkbook saveas line

 

[colinharwood]

All sorted
I had to add a line to make the directory

 

[mikerickson]

Rather than an input box, have you looked at Application.GetSaveAsFileName as a user interface

 

[colinharwood]

No I haven't used that before, could you explain please

 

[mikerickson]

It brings up a standard file choosing dialog box. Run this to she it in action.

Dim uiPath as Variant

uiPath = application.GetSaveAsFilename

If uiPath = False Then
    MsgBox "user canceled"
Else
    MsgBox "path chosen is " & uiPath
End If

 

[colinharwood]

Thanks a lot, that is quite an elegant solution