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

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).

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

Please Login or Register  to view this content.

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.

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.

Please Login or Register  to view this content.

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).

Please Login or Register  to view this content.

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.

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

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

Please Login or Register  to view this content.

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.

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.

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

Originally Posted by trizzo

Yes jaslake. Exactly.. tks

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.