loading files

[andrew299]

I want to load a file into a spreadsheet using a macro
I use the command

Application.FindFile

Which does the job lovely but with one problem
I keep getting the message about it needing to be delimited etc. The file in question is a sheet with info that was laoded form a text file.
can I turn off this delimited box? I know I can do it by using specifying striaght of what I want

Workbooks.OpenText Filename:="filename.txt" _
, Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier _
:=xlNone, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1))

but I want to be able to pick out the file from a list.

Alternatively would it be possible to read the name of the file from the open file box straight into the above code - swap "filename.txt" for a variable?

Sorry for the long question

Andrew299

 

[RobBroekhuis]

I think application.displayalerts=false will take care of this for you. If not, check back.
Rob

 

[andrew299]

Thanks rob
But I have already tried that but it doesnt seem to work
andrew

 

[andrew299]

This is the sub I am using at present
What it does is

1)call the EXE
2)open find file dialog and open workbook
3)copy sheet from newly opened file to my original file

Sub extract2()

Dim r
r = Shell("myexefile.exe", 1)
Application.DisplayAlerts = False


Application.FindFile 'This is where I have the box appear

Cells.Select
Selection.Copy

Windows("mymainfile&quot.Activate

Sheets("main&quot.Select
Range("a1&quot.Select
ActiveSheet.Paste

Application.DisplayAlerts = True
End Sub


Any ideas?
Thanks for being so helpful with all my qs rob

 

[RobBroekhuis]

I've not used the .findfile method. If all else fails, I'd go with

application.getopenfilename(...)

followed by a

workbooks.open ...

using the filename you got from the getopenfilename method. That's how I've always read in new files, and it works nicely.
Rob