Import data from a txt file

JimM

Well-known Member
Joined
Nov 11, 2003
Messages
686
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
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

JimM

Well-known Member
Joined
Nov 11, 2003
Messages
686
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,104
Messages
5,599,746
Members
414,333
Latest member
willfrederick

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top