Import data from a txt file

[JimM]

Hi guys

I have a macro that manipulates data from an imported txt file. The macro runs great but I'd like to go one step further and semi-automate the importing of the data.

Ideally the macro should

1. Create a new workbook
2. Run the data>import external data>import data routine
3. Navigate to the F:\test directory
4. Allow the user to click on the correct file in the 'Select Data Source' box
5. Select the 'delimited', 'comma delimiter' and put data into cell A1 options

I can do some of the code but don't know what to do at step 4 - any help much appreciated

Cheers

Jim

 

[Andrew Poulsom]

Have a look at GetOpenFileName in VBA Help.

http://www.dailydoseofexcel.com/archives/2004/06/09/getopenfilename/

 

[JimM]

Thanks Andrew,
I'm struggling to get it to work though, the macro runs but doesn't import anything - any ideas ?? (I've changed the default directory to F:\Integral from F:\Test intentionally)

Cheers

Jim



Sub Macro1()
'
Workbooks.Add
Dim imptname
ChDir "F:\Integral\"

imptname = Application.GetOpenFilename("Text Files (*.txt), *.txt")



With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & imptname, Destination:=Range("A1"))
.Name = Mid(imptname, 13, 14)
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
'.Refresh BackgroundQuery:=False
End With
End Sub