VBA Macro to load .txt data in various spreadsheets at the end of each file (accumulating)

[trizzo]

Does anybody has a VBA Macro where I can load new .txt data in various excel 2007 spreadsheets?
For example, I have two .txt files (file1.txt and file2.txt) and I would like to load its contents in their respective excel files (File1.xlsx and File2.xlsx) while clicking in a VBA button in the Main.xlsx file. The files File1.xlsx and File2.xlsx has already some values in it but are not updated yet with the contents of the file1.txt and file2.txt.
I have attached the 5 files.
Can anyone help me please?
Thanks!

[PingPing]

Consider using ADO to load a text file into a Recordset object. Then open the workbook, find the last row on a sheet and paste the recordset to the row immediately below it (the Range object has a method 'CopyFromRecordset' that pastes a recordset to the worksheet at the cell specified).

[trizzo]

I have never used ADO, actually I am new to excel VBA... I found a code with ADO, maybe this can help me?

Public Function Read_Text_File() As ADODB.Recordset

      Dim rs As ADODB.Recordset
      Set rs = New ADODB.Recordset
      Dim conn As ADODB.Connection
      Set conn = New ADODB.Connection
      conn.Open "DRIVER={Microsoft Text Driver (*.txt; *.csv)};" & _
                  "DBQ=" & App.Path & ";", "", ""

      rs.Open "select * from [test#txt]", conn, adOpenStatic, _
                  adLockReadOnly, adCmdText
      Set Read_Text_File = rs
      Set rs = Nothing
      Set conn = Nothing
End Function

[jaslake]

Hi trizzo
Please wrap your code in Code Tags. This is not optional (See Rule #3) and is strictly enforced. After you've done so I'll post some code that appears to do as you require.

[jaslake]

Hi trizzo
Well you didn't quite use Code Tags, you used Quote Tags. Code Tags is the # (pound sign button). You can go back and fix it. In the meantime, place this code in a General Module of Workbook Main. Put a Forms Command Button on Sheet 1 of Workbook Main. Assign this macro to that button.

The code assumes all files are in the same directory and that your Naming Convention is as you described, that is, the .txt file and the .xlsx file have the same name. Let me know of issues.

Option Explicit

Sub Open_My_Files()
    Dim MyFile As String
    Dim Main As String
    Dim myPath As String
    Dim tWs As Worksheet
    Dim sWs As Worksheet
    Dim sp
    Dim NR As Long

    myPath = ActiveWorkbook.Path & "\"
    Main = ActiveWorkbook.Name

    MyFile = Dir(myPath)

    Application.ScreenUpdating = False
    Do While MyFile <> ""
        If Not MyFile = Main And MyFile Like "*.xlsx" Then
            Workbooks.Open myPath & MyFile
            Set tWs = ActiveWorkbook.Sheets("Sheet1")
            sp = Split(MyFile, ".")(0) & ".txt"
            Workbooks.OpenText Filename:=sp _
                    , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
                    xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
                    Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 9), _
                    Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
                    Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1)), _
                    TrailingMinusNumbers:=True
            Set sWs = ActiveWorkbook.Worksheets(1)
            With sWs
                .Range(.Cells(1, 1), (.Cells(2, 1))).EntireRow.Delete
                .UsedRange.Copy
                With tWs
                    NR = .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0).Row
                    .Range("A" & NR).PasteSpecial
                    Application.CutCopyMode = False
                End With
            End With
            ActiveWorkbook.Close False
            ActiveWorkbook.Close True
        End If
        MyFile = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

[trizzo]

Hey jaslake, how are you? I have just put the code tags. sorry

[trizzo]

I got this error while clicking in the macro button:
Run-time error '1004'

'File1.txt' could not be found. Check the spelling of the file name, and verify that the file location is correct.

If you are trying to open the file from your list of most recently used files, make sure that the file has not been renamed, moved, or delete.

I checked the file name is correct and it is located in the same directory of Main.xlsx. I did exactly what you said.

Where in the code the file1.txt is being specified? For example if I have another file with another name, how should I add it? Let`s say I have a file named XAD5E.txt

[jaslake]

Hi trizzo
I'm attaching your files with the Button and the Code embedded. As previously indicated, all of these files should be located (by themselves) in one Folder. You'll notice that Main is now Mail.xlsm...Main.xlsx is gone. I wrote the code in Main.xlsx then saved it as Main.xlsm. Main.xlsx was then deleted. Play with these files...the code has been tested and does work for me with these files.

Regarding this

Where in the code the file1.txt is being specified?

The code looks at every .xlsx file in the folder. If the found file name is not the file that's running the code (in this case Main but it can be anything), the .xlsx file is opened. So, in our case, it finds File1.xlsx and opens it. The code then looks for a .txt file with the same name as the .xlsx file it just opened (File1.txt).

sp = Split(MyFile, ".")(0) & ".txt"

If it doesn't find it you get the error message you received. Error checking can and should be built into the code to pass the File if this situation exists.

Regarding this

Let`s say I have a file named XAD5E.txt

If you ALSO have a file called XAD5E.xlsx the code will find and open XAD5E.xlsx and XAD5E.txt. If you DON'T have a file called XAD5E.xlsx nothing happens.

Let me know how it goes.

[trizzo]

It is still not working for me. I saved all the files in the same folder and simply run the button "run me", and I got the same error. The only difference now is when I get the error and click on End error it opens the spreadsheet File1.xlsx and then it is selected the cells from line 8 to 15, columns A to M.

[jaslake]

Hi trizzo
Replace Main.xlsm with the attached workbook. Let me know if this clears the error.

[trizzo]

it worked now with the new workbook! what did you do?

[jaslake]

Hi trizzo
I added one small word
The offending line of code now reads

 Workbooks.OpenText Filename:=myPath & sp _
                    , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
                    xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
                    Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 9), _
                    Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
                    Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1)), _
                    TrailingMinusNumbers:=True

[trizzo]

when I click on Debug it highlights this part of the code:

 Workbooks.OpenText Filename:=sp _
                    , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
                    xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
                    Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 9), _
                    Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
                    Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1)), _
                    TrailingMinusNumbers:=True

[trizzo]

Fantastic!! Thanks a lot! Great program!
Now, I need to treat a condition, not sure if this is possible.
When copying from .txt to the .xlsx file and in case it founds a duplicate row, the row is not copied.
Is it possible to do that?

[jaslake]

Hi trizzo
I assume you mean that if it already exists in the .xlsx file don't post again? Yes, this is possible...a bit more complex. I'll look at it and get back to you.

[jaslake]

Hi trizzo
Try the code in the attached. It appears to accommodate this

When copying from .txt to the .xlsx file and in case it founds a duplicate row, the row is not copied

Let me know of issues.

[trizzo]

Yes jaslake. Exactly.. tks

[jaslake]

Hi trizzo
Yes as in the code in Post #16 did as required?

Yes jaslake. Exactly.. tks

[trizzo]

No jaslake... I tested the new program and it is loading the same data, it is duplicating the data yet.

[jaslake]

Hi trizzo
I'm unable to duplicate the issue of duplicate records so, I'm uploading all the files again to make certain we're using the same files. As before, these 5 files should be in their own folder. Let me know of issues.