Importing multiple text files into excel

J

Jazzer

Board Regular
Joined
Jun 14, 2011
Messages
71
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>Hi All,<o:p></o:p>
<o:p> </o:p>
I need to import multiple text files (4 to be exact) into the same worksheet in excel<o:p></o:p>
I need the first text file to contain the headers but the following 3 just need to be imported below without headers<o:p></o:p>
<o:p> </o:p>
Can anybody help with the required code?<o:p></o:p>
<o:p> </o:p>
Thanks<o:p></o:p>
James<o:p></o:p>
<o:p> </o:p>
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
You should be able to use the Macro Recorder to get most of the code you need (just record yourself doing it manually).

One key thing you will need is the ability to dynamically find the next blank row so you know where to paste the next data range to. Assuming we can use column A to determine this, and you want to paste the data starting in column A, this line of code will select that cell for you:
Code: 
    Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
So armed with this knowledge, take a crack at it and see how far you get. If you run into trouble, post back to this ticket.
 
Upvote 0
Joe4 said:
You should be able to use the Macro Recorder to get most of the code you need (just record yourself doing it manually).

One key thing you will need is the ability to dynamically find the next blank row so you know where to paste the next data range to. Assuming we can use column A to determine this, and you want to paste the data starting in column A, this line of code will select that cell for you:
Code: 
    Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
So armed with this knowledge, take a crack at it and see how far you get. If you run into trouble, post back to this ticket.
Click to expand...

Thanks for that... I'm almost there now

one issue that I have is with some of the other coding in my macro...

I am successfully using the following

Dim c As Range
Dim SrchRng
Set SrchRng = ActiveSheet.Range("K9", ActiveSheet.Range("K65536").End(xlUp))
Do
Set c = SrchRng.Find("GB00B1347K44", LookIn:=xlValues)
If Not c Is Nothing Then c.EntireRow.DELETE
Loop While Not c Is Nothing

I would also like to delete any rows which contain just 3 in col J

Can this be added to the code above?

Thanks
James
 
Upvote 0
Another issue I am having is with finding the next blank line when pasting in the 2ns, 3rd & 4th text files

Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select

is finding the correct cell

however I then seem to be pasting a couple of rows below for some reason...

any ideas?

Thanks
James
 
Upvote 0
you might want to either activate the new sheet, or qualify it as activesheet.cells......
 
Upvote 0
however I then seem to be pasting a couple of rows below for some reason...

any ideas?
Click to expand...
What does your paste command look like?
 
Upvote 0
Joe4 said:
What does your paste command look like?
Click to expand...

OK - so the first file is pasted into cell A7 then I am looking to paste the following files into the next available row...

Range("A7").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;U:\operations\Ops\CAPITAL MARKETS\LCH RECS\LCH SETTLEMENT TODAY RECS\NOMAD_GILT_OPEN_TODAY.txt" _
, Destination:=Range("$A$7"))
.Name = "NOMAD_GILT_OPEN_TODAY"
.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 = False
.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)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;U:\operations\Ops\CAPITAL MARKETS\LCH RECS\LCH SETTLEMENT TODAY RECS\NOMAD_GILT_CLOSE_TODAY.txt" _
, Destination:=Range("$A$48"))
.Name = "NOMAD_GILT_CLOSE_TODAY"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 1252
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.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)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;U:\operations\Ops\CAPITAL MARKETS\LCH RECS\LCH SETTLEMENT TODAY RECS\NOMAD_GERMAN_OPEN_TODAY.txt" _
, Destination:=Range("$A$96"))
.Name = "NOMAD_GERMAN_OPEN_TODAY"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 1252
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.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)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;U:\operations\Ops\CAPITAL MARKETS\LCH RECS\LCH SETTLEMENT TODAY RECS\NOMAD_GERMAN_CLOSE_TODAY.txt" _
, Destination:=Range("$A$228"))
.Name = "NOMAD_GERMAN_CLOSE_TODAY"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 1252
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.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)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
 
Upvote 0
If you minimize your VBA window to about one-quarter the size of your window, you can then step through your VBA code line-by-line using the F8 key and watch what is doing on your spreadsheet.

I suspect that you may have some extra lines in your text files at the top or bottom that may appear to be emptym but really aren't (may have some special spaces in them).

Also, when posting code, it makes it much more readable if you can use the codes tags. Simply highlight all the code and click on the Code tag button from the editor menu (looks like a pound sign).
 
Upvote 0
Code: 
Sub Update_Nomad_Data()
'
' Update_Nomad_Data Macro
'
'
    Rows("7:7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.DELETE Shift:=xlUp
    Rows("7:7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.DELETE Shift:=xlUp
    Range("A7").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;U:\operations\Ops\CAPITAL MARKETS\LCH RECS\LCH SETTLEMENT TODAY RECS\NOMAD_GILT_OPEN_TODAY.txt" _
        , Destination:=Range("$A$7"))
        .Name = "NOMAD_GILT_OPEN_TODAY"
        .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 = False
        .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)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
  Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;U:\operations\Ops\CAPITAL MARKETS\LCH RECS\LCH SETTLEMENT TODAY RECS\NOMAD_GILT_CLOSE_TODAY.txt" _
        , Destination:=Range("$A$48"))
        .Name = "NOMAD_GILT_CLOSE_TODAY"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1252
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .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)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;U:\operations\Ops\CAPITAL MARKETS\LCH RECS\LCH SETTLEMENT TODAY RECS\NOMAD_GERMAN_OPEN_TODAY.txt" _
        , Destination:=Range("$A$96"))
        .Name = "NOMAD_GERMAN_OPEN_TODAY"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1252
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .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)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;U:\operations\Ops\CAPITAL MARKETS\LCH RECS\LCH SETTLEMENT TODAY RECS\NOMAD_GERMAN_CLOSE_TODAY.txt" _
        , Destination:=Range("$A$228"))
        .Name = "NOMAD_GERMAN_CLOSE_TODAY"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1252
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .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)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Columns("A:Y").Select
    Columns("A:Y").EntireColumn.AutoFit
    Columns("C:C").Select
    Selection.DELETE Shift:=xlToLeft
    Columns("K:M").Select
    Selection.DELETE Shift:=xlToLeft
    Columns("L:L").Select
    Selection.DELETE Shift:=xlToLeft
    Columns("O:S").Select
    Selection.DELETE Shift:=xlToLeft
    Range("I7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("I8").Select
    ActiveCell.FormulaR1C1 = _
        "=CHOOSE(MATCH(RC[-6],{""Q"",""R"",""U"",""T""},0),RC[-1],-RC[-1],-RC[-1],RC[-1])"
    Range("I8").Select
    Selection.AutoFill Destination:=Range("I8:I500")
    Range("I8:I500").Select
    Dim r As Long
Dim lrow As Long
Application.ScreenUpdating = False
lrow = Range("L" & Rows.Count).End(xlUp).Row
For r = lrow To 8 Step -1
If Cells(r, 12).Value <> 710 And Cells(r, 15).Value <> 1788 Then
    Rows(r).DELETE
End If
Next r
Application.ScreenUpdating = True
 
 Dim c As Range
    Dim SrchRng
    Set SrchRng = ActiveSheet.Range("K9", ActiveSheet.Range("K65536").End(xlUp))
    Do
        Set c = SrchRng.Find("GB00B1347K44", LookIn:=xlValues)
        If Not c Is Nothing Then c.EntireRow.DELETE
    Loop While Not c Is Nothing
    End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
C
  • Solved
Extracting data with VBA macro button from files in designated folder
2 3
Replies
20
Views
1K
Micron
M
F
  • Solved
VBA - Append CSV data to current file
2
Replies
12
Views
958
Domenic
D
E
  • Solved
Force Excel 2019 VBA to import multiple txt file to existing sheet & not seperate wk.book / wk.sheets
Replies
1
Views
654
Domenic
D
H
  • Solved
VBA to import fixed width text file into Excel
Replies
3
Views
912
rlv01
R

Forum statistics

Threads
1,224,586
Messages
6,179,729
Members
452,939
Latest member
WCrawford

Share this page

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
Back
Top