Copying text from clipboard to first empty row

I have a 30 line text file with all lines looking like this, some separated by empty lines. All have a 'return' (CR LF) at the end.

Please Login or Register  to view this content.

I have externally copied all the text to the clipboard. My VBA macro includes this code which display it in a temporary message box. (Well, most of it. Apparently there is a 1024 character limit.)

Please Login or Register  to view this content.

But I'm struggling with the next two stages and would appreciate some help please.

1. For each line, set a VBA variable containing the part after last equal sign. Ideally, but not essential, I'd like to use a variable name identical to the string after the first equal sign. So the first line would generate a string variable called 'tFileName' with value '20190706Cadgwith-Coverack-r1060-m7.3.gpx'.

2. In the single, already open workbook 'WalkIndex.xlsm', sheet 'Target', create a new row from these variables at the end of all previous rows. So tFilename would be entered into col C, tDatePrefix into col B, etc. When my source was in another workbook I was doing it with the following code (developed with much help here):

Please Login or Register  to view this content.

I've attached an extract from WalkIndex.xlsm

Any assistance would be much appreciated please.

The message you have entered is too short Please lengthen your message to at least 10 characters.

What? The message was "Well, as you wish, here is the 'expected result workbook' with the extra two cells added. And a text file made by copy pasting the code I provided."

And it had two files attached, but this is my third attempt to send my reply because of that daft message!

Originally Posted by porucha vevrku

Have you tried using the 'Split' function ?

Thanks, no, not yet but I will, splitting on " = ". Any suggestions about #2 please?

Hello terrypin,

This macro will open the text file named "SourceText.txt" which is saved to the workbook's directory. It will read all the lines in the file looking for "tFileName" and "tDatePrefix". The latter will fill both columns "A" and "B" and the former column "C". The file name has the file type removed as in your example. The macro expects these labels to repeat together in pairs throughout the file. Once the file has been read, the data is output to the worksheet "Target" starting with the next empty row.

The macro below has been added to the attached workbook. Try it out on your original text file and let me know the results.

Macro added to the Module "Misc"

Please Login or Register  to view this content.

That looks great Leith, thanks so much for taking the time to develop it! To do it justice I'm going to get some chores out of the way, tackle it methodically afterwards, and report back, probably tomorrow.

Posted Monday 27 January 2020, 1112 UK GMT.

Brilliant, you're a star! I was able to test it sooner than I expected and it worked perfectly.

I'll now tackle extending it to cover the full set of variables, during which I may seek further help if I hit difficulties.

When I have the project finished, I'll begin studying how it works!

Terry, UK

Leith: I hit my first problems very quickly. The actual source files have 31 lines (including blanks), not 4. So I'm getting 'Run-time error '9': Subscript out of range' in the line

Please Login or Register  to view this content.

So far I haven't worked out which of your variables (or statements) I need to change.

Unlike my simplified example the source files are actually named tTrackName.txt, such as "20190707Porthallow-MawnanSmith(Lift)-r765-m7.7". That's a variable I can get into VBA before running your macro. Do I then substitute it as I've shown here?

Please Login or Register  to view this content.

Hello terrypin,

If you post the actual text file you are using, I can then prevent these unexpected problems from occurring.

Originally Posted by Marc L

As I asked for since post #3 …

I promptly showed you a simplified file.

Originally Posted by Leith Ross

Hello terrypin,

If you post the actual text file you are using, I can then prevent these unexpected problems from occurring.

Thanks Leith, here's an example of a typical FULL file.

Hello terrypin,

It has been a busy day. I found the problem in the macro. The Split function looks for delimiting character or characters and returns an array of substrings. The function will return the original string if no delimiter is found in a single zero based element array.

Them macro assumed there would be at 2 or more equal signs in a valid line and that blank lines were truly blank. It appears that the last line of new file you provided contains spaces. I changed the compare line (in red font) in the loop from a > -1 to > 0. This fixed the problem, The amended macro below also allows you to chose the text file to open.

Please Login or Register  to view this content.

Thanks Leith, really grateful for your patient work on this. Sorry about those hidden spaces.

I’ll try your revised macro when I return home later today.

It’s frustrating being a VBA novice, working mainly in ‘copy/paste mode’. On the one hand, accomplishing a task optimally with advanced code like yours is great, but the downside is that I don’t understand it fully enough to be able to adapt it with confidence.

So in parallel I’m also working on an alternative more intuitive approach to copying these variables, and later I’ll raise it in a separate post. It relies on that ground-breaking method you showed me for clipboard management in Windows 10, which I’m now using regularly, thanks again

Terry, UK

Hi Leith,

I've been working on this for a few hours this morning and thought you might appreciate an update.

As expected, your revised macro worked perfectly, and can now be applied to ANY of my source files, thanks a bunch! So I'm now focusing on adapting it.

I've made some comments about my editing aims in the copy of your macro that I will be using for my tentative edits. It's called Sub AddTrackData_All(), directly below yours and so I've attached an updated version of the workbook. I've also added a summary of the variables in the sheet "TEMP".

I deliberately kept my initial post as short and simple as possible. But some extra background detail may now be useful.

I'm a long term user of a program called Macro Express Pro, a high level scripting application. For non-programmers like me its GUI is much easier and intuitive, although of course it doesn't approach VBA for speed or versatility. I'm using it in this project with a program called GPS Utility. Up until recently my MX macro 'GPX ANALYSIS', supplemented by VBA, generated a tracksheet for each GPX file, example attached, and then copied its data to WalkIndex.xlsm.

But I decided to simplify that drastically and no longer prepare a tracksheet. Instead, a greatly culled set of 21 variables are prepared by GPX ANALYSIS.

As well as being displayable in text form and in profile images like the example attached, they are copied directly to a text file of the same name as the GPX file. I may even decide to dispense with that step, relying entirely on the one row summary in WalkIndex.xlsm, but meanwhile I'm using it as a basis for the copying under discussion here.