Referencing a file in VBA after user enters name in a text box 2

[tearsmith]

All,

I am working on a spreadsheet that needs to open another document and extract information. The problem is that the name and/or location of the document will change between each use. I want to use a dialog box and have the user enter the location of the document to pull, and have VBA code use that imput in it equation.

File name = TextBox1.Text
TextBox1 Text = "C:\My Documents\Census\45698.xls"

Then VBA code to go to that File and open

Help!

 

[xlbo]

workbooks.open filename:=textbox1.text

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[calculus]

Not sure the level of detail you need, but this will get you going. I'm assuming the user will enter a full path and filename here, if that's not the case you may need to handle that seperately.

FileName = InputBox("Enter File Name&quot
Workbooks.Open Filename:= FileName

calculus

 

[Loomah]

It may be a bit risky to ask a user to manually enter a file name so I would use the GetOpenFilename dialog like this (maybe!)

ChDir "C:\My Documents\Census"
sFile = Application.GetOpenFilename("excel files(*.xls),*.xls")
If sFile <> False Then
    Workbooks.Open sFile
End If

This allows the user to select the file with minimum fuss.
;-)


If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[willyboy58]

Loomah,

Thanks for the line:

ChDir "C:\My Documents\Census"