Merging TXT files in a current worksheet using VBA

[chrisou]

Hi Everyone

I am new to VBA and I am trying to merge TXT files using VBA into a current worksheet in a specific place as adjacent columns contain formulas which will acces this data
The first line of each TXT file is NOT to be merged
I have managed to import the first TXT file but not the others...
I have included a tab "Merged files when imported" to who what the program should do when the 3 files are correctly imported

The afiles are attached as .csv as they would not upload as .txt

Many thanks in advance for your help


Chris

[snb]

Always use cell A1 in a workbook.

[chrisou]

Thank you for this feedback but how does this help me with the VBA code?

[snb]

It makes any VBA code simpler and more robust.

[arnelgp]

not optimal code, but you may try.

[chrisou]

HI Arnelgp
Many thanks for this solution which does work well with the 3 files that I sent, importing correctly the text into the columns and performing the calculation equally..
However, when I try with the real files (many more rows per file) i receive an error message ; Runtime error "9". Subscript out of range.

In the VBA code the liner below is highlighted
sht.Cells(last_row, j + 1) = var(j)

Many thanks for any help that you can provide

Chris

[arnelgp]

can you upload the "real" file. your sheet has 6 columns to fill, so
i am thinking there are rows in the textfile that has "less" than 6 columns.

overwrite my subs with this one. if still does not work upload your "real" textfile.


'arnelgp
Private Sub Import_Textfile(ByVal tFile As String)
Debug.Print tFile
Dim first_row As Long, last_row As Long
Dim content As String, var As Variant
Dim cols As Integer
Dim iFile As Integer
Dim i As Long, j As Long
Dim sht As Worksheet
Set sht = ActiveSheet
first_row = 3
last_row = LastRow(sht.Range("a1")) + 1
iFile = FreeFile
Open tFile For Input As #iFile
Line Input #iFile, content
While Not EOF(iFile)
i = i + 1
If i > 1 Then
var = Split(content, ";")
cols = 6
If UBound(var) < 6 Then
cols = UBound(var)
End If
For j = 0 To cols Step 1
sht.Cells(last_row, j + 1) = var(j)
Next
last_row = last_row + 1
End If
Line Input #iFile, content
Wend
If i > 1 Then
var = Split(content, ";")
For j = 0 To 6 Step 1
sht.Cells(last_row, j + 1) = var(j)
Next
End If
Close #iFile


sht.Range("H3:J3").Select
Selection.Copy


sht.Range("H4:J" & last_row).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False
sht.Range("A1").Select
End Sub




Public Function LastRow(ByRef Rng As Range)
With Rng.Parent
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
End With
End Function

[chrisou]

Hi Arnelgp
Thanks for your reply. I have tried inputing the code but it created an error when I ran it (I most probably have not inserted the code correctly as there are 2 modules in the file you originally sent).
PLease find attached 2 examples of the original files (but in.csv format) that I need to import among many other files
Thanks once again for your help
Chris

[arnelgp]

i manually open the 2 csv in Excel and found that on the 2 files, the last row has incomplete entry.
even incomplete it will now import it.
or you need to verify (or delete?) the incomplete entry.

[chrisou]

Hi Arnelgp
Many many thanks for your help. Your solution works very well.
For my understanding of the code, if the files to be inputed had 9 columns of text (and not 6 as the data to date) how would this change the VBA code?
Many thanks
Chris

[arnelgp]

actually 7, (0 to 6, index of the array)
change col=6 to col=9, be warned that it will overwrite your formula in column H up to J
so you need to adjust.
also there is a code there that copy h3:j3 (down to the last row), you need to modify
the code here also.

[chrisou]

Hi Arnelgp

Many thanks for your clear responses. If I could abuse of your kindness one last time before closing this thread.
What would need to be changed in the VBA code, to choose where to import the data to avoid overwriting data already inputted from a previous research ie actually the data is imported into A2 but what would need to be done to choose another cell in the column A or that automatically it entered the data at the end of the current worksheet in the next available empty row in column A?

Many thanks for your support
Chris

[chrisou]

Hi Arnelgp

My error but your code is correctly importing any new files into the next available empty row in the spreadsheet.
Many thanks for your help and for solving my problem

Chris

[arnelgp]

here is the Modified for the Date part.

[chrisou]

Hi Arnelgp

You are a genius and an excel guru. Many thanks for your help and patience in solving my problem and helping me to learn from you

Chris:clap::clap::clap:

[arnelgp]

:friends: