Import multiple text files into single spreadsheet

mik_tiff

New Member
Joined
Feb 3, 2005
Messages
24
I'm trying (and failing) to create a macro to import a number of text files into a spreadsheet.
The files will all be in the same directory, but the number of files could change (up to 100 files at a time).
I need to import each file to a seperate worksheet and name the worksheet the same as the relevant imported file.
I'm quite new to these macro things and don't understand VB code very well

Thanks
Mik
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Mik

Do you already have code?

If you do perhaps you could post it and further help can be given.

If not you should try recording a macro when you import a few of the text files.

The code generated from that can then be adapted to deal with all the files in the folder.
 

mik_tiff

New Member
Joined
Feb 3, 2005
Messages
24
This is a macro I recorded to import the 1st file, including adding another sheet at the end of the macro.
The next file needs to be added to the new sheet.
There is no code in the macro for naming the sheets the same as the imported file.
The directory (which won't change) is highlighted in red
The file name (which could be anything) is highlighted in yellow

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;T:\EMC\PARMS\P99 Files\Third Party Reports\2004\December\TR_31122004_DASL_M_SMAR_X_HM01_4829.txt" _
, Destination:=Range("A1"))
.Name = "TR_31122004_DASL_M_SMAR_X_HM01_4829"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.Refresh BackgroundQuery:=False
End With
Sheets.Add
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,577
Messages
5,596,996
Members
414,116
Latest member
sfullnet

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