Using a value from an Input box?
 

Hi I am trying to get excel to ask for a file name,eg C23456.1,using an
input box and then taking that answer and putting it into my code. So
far I have....

ans = InputBox("What is the bank file reference")

ChDir "L:\Accounts\Bank downloads\Todays Download"
Workbooks.OpenText Filename:= _
"L:\Accounts\Bank Downloads\Todays Download\ THE ANSWER
HERE!."

Can someone please help

James


--
James_Newton
------------------------------------------------------------------------
James_Newton's Profile: http://www.excelforum.com/member.php...o&userid=18327
View this thread: http://www.excelforum.com/showthread...hreadid=473687

Using a value from an Input box?
 

Hello James,

You can concatenate (join) strings together using an ampersand (&) so
sFullPath below is made up of a) the path, b) the filename and c) the file
extension.

Dim sAns As String
Dim sFullPath As String

sAns = InputBox("What is the bank file reference")
sFullPath = "L:\Accounts\Bank Downloads\Todays Download\ " & sAns & ".xls"

Just an observation, but this method relies on the user typing exactly the
right file name and is therefore liable to errors. Have you considered
letting the user select a file themselves? Search help for "FileDialog
Property" which contains an example.

Hope this helps.

Best regards

John


"James_Newton"
wrote in message
news:James_Newton.1wh0ue_1128585917.3486@excelforu m-nospam.com...

Hi I am trying to get excel to ask for a file name,eg C23456.1,using an
input box and then taking that answer and putting it into my code. So
far I have....

ans = InputBox("What is the bank file reference")

ChDir "L:\Accounts\Bank downloads\Todays Download"
Workbooks.OpenText Filename:= _
"L:\Accounts\Bank Downloads\Todays Download\ THE ANSWER
HERE!."

Can someone please help

James


--
James_Newton
------------------------------------------------------------------------
James_Newton's Profile:
http://www.excelforum.com/member.php...o&userid=18327
View this thread: http://www.excelforum.com/showthread...hreadid=473687

Using a value from an Input box?
 

Of course I've just realised you could be asking for a Save file as.........
in which this would obviously work! You could still use the same FileDialod
object with a msoFileDialogSaveAs dialog type.

Best regards

John

"John" wrote in message
...
Hello James,

You can concatenate (join) strings together using an ampersand (&) so
sFullPath below is made up of a) the path, b) the filename and c) the file
extension.

Dim sAns As String
Dim sFullPath As String

sAns = InputBox("What is the bank file reference")
sFullPath = "L:\Accounts\Bank Downloads\Todays Download\ " & sAns & ".xls"

Just an observation, but this method relies on the user typing exactly the
right file name and is therefore liable to errors. Have you considered
letting the user select a file themselves? Search help for "FileDialog
Property" which contains an example.

Hope this helps.

Best regards

John


"James_Newton"
wrote in message
news:James_Newton.1wh0ue_1128585917.3486@excelforu m-nospam.com...

Hi I am trying to get excel to ask for a file name,eg C23456.1,using an
input box and then taking that answer and putting it into my code. So
far I have....

ans = InputBox("What is the bank file reference")

ChDir "L:\Accounts\Bank downloads\Todays Download"
Workbooks.OpenText Filename:= _
"L:\Accounts\Bank Downloads\Todays Download\ THE ANSWER
HERE!."

Can someone please help

James


--
James_Newton
------------------------------------------------------------------------
James_Newton's Profile:
http://www.excelforum.com/member.php...o&userid=18327
View this thread:
http://www.excelforum.com/showthread...hreadid=473687