Terry, I've addressed some of your comments from various threads of this topic:
<C1
"I too wish there was a way to avoid the clipboard. Short of doing it entirely
and glacially slowly by simulating keystrokes and mouse clicks in my MX macro.
It came as a surprise to find that it needs nearly a hundred lines of code to
do a simple paste in Windows 10 Pro, although I
now happily ignore that overhead sitting in a module!"
GS:
Both Claus and I are suggesting a solution for that; - read the text file
directly to an array! Here's the problem as I see it:
a) You are using MX Pro to record the manual steps involved to get the GPX
data into a text file (I assume from its output .gpx file) and also into
Clipboard so you can use a VBA macro in WalkIndex.xlsm to put the data into
your worksheet.
b) What Claus and I are proposing is to read the .gpx file directly into an
array and process its data in Excel to bypass all those steps you now take to
get to the same place; - an array in Excel containing the .gpx file contents!
Skip the Clipboard, text file steps altogether! Just amend your button macro to
read the .gpx fire directly into an array before it does whatever else required
to populate the fields in the target worksheet.
<C2
"As explained, the data source must be either the small text files that
replaced them, or the clipboard. The latter is my preference for the reason I
gave, namely that it would allow me to get rid of that remaining text file step
and go straight from the data gathered by my MX Pro macro to WalkIndex.xlsm via
the clipboard."
GS:
Once the Clipboard clears the data is lost; - keeping the text files is backup
for that data. I suggest you keep the text files in some kind of folder
structure so they list chronologically so you have a good foundation for a
text-based database system.
" My MX macro works with a
program called GPS Utility to assign string values to all the variables
detailed in my opening post. In future I need to copy those to WalkIndex
as described, and to a small text files, one for each walk,
tTrackName.txt. These are the much simplified replacements for the
individual track sheets I had been making that looked like this:
https://www.dropbox.com/s/qg52nd2bhn...book.jpg?raw=1
(I'm considering eliminating even the text files, as all required data
will be in WalkIndex.) "
GS:
I'm sure you are doing all this because you don't know of a better way! You
can't eliminate the text files because MXP processes their contents? Or does
MXP create them from what it does with GPS Utility? What, exactly, does GPSU
do?
I suggest this very simple approach entirely within WalkIndex.xlsm using VBA:
a) Read .gpx contents to array;
b) Parse that data for loading an output array to populate fields in your
worksheet and to store the data in a text file for future retrieval;
(assumes worksheet is a reusable template deliberately designed to
receive/display array data)
c) Button on worksheet can be replaced with your own Ribbon menu[s] as this
project evolves further over coming years. Given how long you've been at this,
it deserves being transformed into its own Excel-based application (IMO) and so
since you are now in the process of evolving it more then why not let us help
you get to a better place with it all!
<C3
"No success so far with the first of my 'outstanding points', getting the col A
date in form 'ddd dd/mm/yy'. This test macro works OK, but after many attempts
I still haven't succeded in doing the same within your macro, as Case "A"
Sub TestSplit_1()
Dim tDate1 As String
Dim tDate1Array() As String
tDate1 = "Thursday 19 September 2019"
tDate1Array() = Split(tDate1)
tDate1 = tDate1Array(1) & " " & tDate1Array(2) & " " &
tDate1Array(3)
MsgBox tDate1
Range("A800") = tDate1
End Sub"
GS:
As Clause states, you are passing a string value to the worksheet, NOT A DATE!
He has uploaded the fix in his revised xlsm.
Summary:
The problem I'm see after examing the code in the example xlsm file[s] that
were downloaded is that EVERYTHING depends on the Clipboard containing the .gpx
data MXP put there; - very bad idea since any mishap (however caused) makes it
highly possible for the Clipboard to get unintentionally cleared, causing code
to error out trying to process tAll_VBA. What makes it a bad idea is that this
project has *external dependancies not under its control!* (This does not
conform to what's considered program development "Best Practices"!)
Now Terry, (as you very well know) I've looked into MXP extensively in the past
and clearly see its value for automating steps to complete tasks; - but this
project has evolved into something way beyond MXP's step-up-from-novice
approach! Sooo.., if you are into a revision then NOW is the time to DO IT
RIGHT, don't you think?
To get there I need the following from you:
1) A sample .gpx file from GPSU (or whatever your source generates);
2) Current version of the template worksheet to receive the data;
3) List of process descriptions to include;
4) (Most important) your design intent.
--
Garry
Free usenet access at
http://www.eternal-september.org
Classic
VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.
vb.general.discussion