VBA Code to prompt for filename to be imported

[nowonda]

Hi there,

I have an excel that runs some macro on data that's being imported and then gets refreshed on a regular basis. What I do is import the data from a txt file, run macro on it, refresh data, run macro etc. The loop for running the macro and the refresh is done, what I'd like to do is have Excel prompt me to load the file, as for the moment I'm doing that manually.

So now:

1. Open excel file that contains macro
2. Import data manually
3. Start macro that periodically performs some operations on the data and the re-imports the file to check for updates

Wanted:

1. Same
2. Start macro - here I should be promped for the file and after selecting it the macro will start its usual loop.

The VBA code for an import has smth like this in it:

With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT; C:\Documents and Settings\jo\Desktop\blabla.txt" , Destination:=Range("A1"))

So what I need is make excel prompt me for that blabla.txt. I tried using Application.GetOpenFilename method instead of the import code above, but while now it does prompt me for the filename, what it does after that is open a new excel file containing the data i selected, which is not what i want.

Anybody can give me a hand here?

Any help much appreciated!

Cheers!


********************************30 mins later edit

lol, i found a way to do it, 20 min after i posted the prev message

for those interested - i'm using GetOpenFilename to open the file, but then I choose not to actually open it, so excel won't open a new file, but the name of the file i tried to load is still in the memory so I used that to feed into my import code.

Sub TestIt()
NewFN = Application.GetOpenFilename("Text Files (*.txt), *.txt")
'Workbooks.Open Filename:=NewFN
Range("A1") = NewFN
End Sub

Cheers!

[nowonda]

lol, i found a way to do it, 20 min after i posted the prev message

for those interested - i'm using GetOpenFilename to open the file, but then I choose not to actually open it, so excel won't open a new file, but the name of the file i tried to load is still in the memory so I used that to feed into my import code.

Sub TestIt()
NewFN = Application.GetOpenFilename("Text Files (*.txt), *.txt")
'Workbooks.Open Filename:=NewFN
Range("A1") = NewFN
End Sub

Cheers!

[NeedSomeAnswers]

Try this for getting the filename !

Dim sFullName As String
 
Application.FileDialog(msoFileDialogOpen).Show
sFullName = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)

[nowonda]

that works too, thanks!

cheers!