I would like to automate the responses to the import text file dialog box after the file is selected. I'm not sure how to build the responses into my macro. The dialog box portion is:
Application.Dialogs(xlDialogImportTextFile).Show
I would like to automate the responses to the import text file dialog box after the file is selected. I'm not sure how to build the responses into my macro. The dialog box portion is:
Application.Dialogs(xlDialogImportTextFile).Show
I use the following code to open a text file into XL
Range "File1" contains the full path of the file
eg
S:\Finance Birmingham\Ovation GLs\XLGL_06Jan2003.dsk
Workbooks.OpenText FileName:= _
Range("File1").Text, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False _
, Space:=False, Other:=True, OtherChar:="~", FieldInfo:=Array(Array(1, 1 _
), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1))
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Basically, you can get pretty much the same effect by using the macro recorder & going through the process of setting your delimiters etc (mine was a "~" - as you can se from the code above)
Hope this helps - post back if this doesn't help solve the problem
:cheers:
Maybe this will help a little -
Jack in the UK
Code:
Sub Button1_Click()
ChDir "C:\Documents and Settings\ME\Desktop"
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\ME\Desktop\Ozgrid.txt.txt", Origin:=xlMSDOS, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True
End Sub
Hi and in addition to what have already been suggested:
[Option Explicit
Sub OpenTxtFiles()
Dim stGetFile As String
stGetFile = Application.GetOpenFilename("Textfiles (*.txt),*.txt", , _
"Select a textfile to be open...")
'In case user cancel
If stGetFile = "False" Then Exit Sub
Workbooks.Open stGetFile
End Sub
As far as I remember this will also fire up the "Text to columns"-wizard, if necessary.
You can use the macro recorder to set up all of that, the only qualifier being that the recorder stores absolutes within it's coding..ie - it will always open exactly the same file every time..
It's easy enough to modify the code afterwards by substituting the absolute filename with range(blah blah)..
It worked for me anyway and therefore, by definition, must be relatively easy..
Don’t have an account yet? Register yourself now and be a part of our community!