I have a string variable 'tAll_VBA' in my workbook 'WalkIndex.xlsm',
sheet 'Target'. It contains 21 values on 21 separate lines, all ending
in a CR/LF character. I want to copy all except the first two lines to
19 specified columns of the first empty row of the worksheet (i.e col A
empty). The variable and target cols are as follows:

Destination Source
WalkIndex Line number
column in tVBA_All
---------- ------------
No copy 2
A 4
B 5
C 3
H 11
I 9
J 6
K 7
L 8
M 10
N 12
O 13
P 14
R 15
S 16
T 17
U 19
V 18
W 20
Y 21
Not copied 1

I'm not sure how that mapping will be displayed after pressing Send so
here it is again in Line order:

Col Line
1
2
C 3
A 4
B 5
J 6
K 7
L 8
I 9
M 10
H 11
N 12
O 13
P 14
R 15
S 16
T 17
V 18
U 19
W 20
Y 21

I have previously used the following successfully in another macro to
get the last row:

Set wi = Workbooks("WalkIndex.xlsm")
Set ws = wi.Sheets("Target")

'Find the first empty row (col A) in WalkIndex
With ws
'Find first empty row (last used + 1)
nr = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
'MsgBox nr

Any pointers on how to code this would be much appreciated please.

Terry, UK

Hi Terry,

Am Wed, 29 Jan 2020 11:21:00 +0000 schrieb Terry Pinnell:

I have a string variable 'tAll_VBA' in my workbook 'WalkIndex.xlsm',
sheet 'Target'. It contains 21 values on 21 separate lines, all ending
in a CR/LF character. I want to copy all except the first two lines to
19 specified columns of the first empty row of the worksheet (i.e col A
empty). The variable and target cols are as follows:

Col Line
1
2
C 3
A 4
B 5
J 6
K 7
L 8
I 9
M 10
H 11
N 12
O 13
P 14
R 15
S 16
T 17
V 18
U 19
W 20
Y 21

try:

strCols = "C,A,B,J,K,L,I,M,H,N,O,P,R,S,T,V,U,W,Y"
varCols = Split(strCols, ",")
varTmp = Split(tAll_VBA, Chr(10))
For i = 2 To UBound(varTmp)
Sheets("Target").Cells(i + 1, varCols(i - 2)) = varTmp(i)
Next


Regards
Claus B.
--
Windows10
Office 2016

Claus Busch wrote:

Hi Terry,

Am Wed, 29 Jan 2020 11:21:00 +0000 schrieb Terry Pinnell:

I have a string variable 'tAll_VBA' in my workbook 'WalkIndex.xlsm',
sheet 'Target'. It contains 21 values on 21 separate lines, all ending
in a CR/LF character. I want to copy all except the first two lines to
19 specified columns of the first empty row of the worksheet (i.e col A
empty). The variable and target cols are as follows:

Col Line
1
2
C 3
A 4
B 5
J 6
K 7
L 8
I 9
M 10
H 11
N 12
O 13
P 14
R 15
S 16
T 17
V 18
U 19
W 20
Y 21

try:

strCols = "C,A,B,J,K,L,I,M,H,N,O,P,R,S,T,V,U,W,Y"
varCols = Split(strCols, ",")
varTmp = Split(tAll_VBA, Chr(10))
For i = 2 To UBound(varTmp)
Sheets("Target").Cells(i + 1, varCols(i - 2)) = varTmp(i)
Next


Regards
Claus B.

Thanks Claus, very grateful for your help. I'm confident your code is
VERY close to working but it's giving some incorrect results at present.
I've included a link to the test workbook WalkIndex-Claus.xlsm so that
you can see what I mean in detail. Should have done that in my original
post, sorry.

https://www.dropbox.com/s/xbwgtqs23z...aus.xlsm?raw=1

Terry, East Grinstead, UK

Hi Terry,

Am Wed, 29 Jan 2020 17:49:49 +0000 schrieb Terry Pinnell:

Thanks Claus, very grateful for your help. I'm confident your code is
VERY close to working but it's giving some incorrect results at present.
I've included a link to the test workbook WalkIndex-Claus.xlsm so that
you can see what I mean in detail. Should have done that in my original
post, sorry.

https://www.dropbox.com/s/xbwgtqs23z...aus.xlsm?raw=1

try:

Dim objData As New MSForms.DataObject
Dim tAll_VBA
Dim strCols As String
Dim strValues As String
'Types for these arrays?
Dim varTmp As Variant
Dim varCols As Variant
Dim varValues As Variant
Dim LRow As Long
Dim i As Integer

objData.GetFromClipboard
tAll_VBA = objData.GetText()
'For testing
MsgBox tAll_VBA
Debug.Print tAll_VBA

'Now need to get that into a new row of WI

'This block from Claus
strCols = "A,B,C,H,I,J,K,L,M,N,O,P,R,S,T,U,V,W"
varCols = Split(strCols, ",")
strValues = "3,4,2,10,8,5,6,7,9,11,12,13,14,15,16,17,18,19 "
varValues = Split(strValues, ",")
varTmp = Split(tAll_VBA, Chr(10))
With Sheets("Target")
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
For i = LBound(varCols) To UBound(varCols)
Select Case varCols(i)
Case "J", "K", "L"
.Cells(LRow, varCols(i)) = Format(varTmp(varValues(i)), "hh:mm")
Case Else
.Cells(LRow, varCols(i)) = varTmp(varValues(i))
End Select
Next
End With

Or download the workbook from he
https://1drv.ms/x/s!AqMiGBK2qniTgel7...Xqp_g?e=ISgyGm


Regards
Claus B.
--
Windows10
Office 2016

Claus Busch wrote:

Hi Terry,

Am Wed, 29 Jan 2020 17:49:49 +0000 schrieb Terry Pinnell:

Thanks Claus, very grateful for your help. I'm confident your code is
VERY close to working but it's giving some incorrect results at present.
I've included a link to the test workbook WalkIndex-Claus.xlsm so that
you can see what I mean in detail. Should have done that in my original
post, sorry.

https://www.dropbox.com/s/xbwgtqs23z...aus.xlsm?raw=1

try:

Dim objData As New MSForms.DataObject
Dim tAll_VBA
Dim strCols As String
Dim strValues As String
'Types for these arrays?
Dim varTmp As Variant
Dim varCols As Variant
Dim varValues As Variant
Dim LRow As Long
Dim i As Integer

objData.GetFromClipboard
tAll_VBA = objData.GetText()
'For testing
MsgBox tAll_VBA
Debug.Print tAll_VBA

'Now need to get that into a new row of WI

'This block from Claus
strCols = "A,B,C,H,I,J,K,L,M,N,O,P,R,S,T,U,V,W"
varCols = Split(strCols, ",")
strValues = "3,4,2,10,8,5,6,7,9,11,12,13,14,15,16,17,18,19 "
varValues = Split(strValues, ",")
varTmp = Split(tAll_VBA, Chr(10))
With Sheets("Target")
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
For i = LBound(varCols) To UBound(varCols)
Select Case varCols(i)
Case "J", "K", "L"
.Cells(LRow, varCols(i)) = Format(varTmp(varValues(i)), "hh:mm")
Case Else
.Cells(LRow, varCols(i)) = varTmp(varValues(i))
End Select
Next
End With

Or download the workbook from he
https://1drv.ms/x/s!AqMiGBK2qniTgel7...Xqp_g?e=ISgyGm


Regards
Claus B.

Excellent, works perfectly now, thanks a lot Claus ;-)

One minor point that probably has a simple answer; did you notice that
the entries in cols N, O and P are red? The cols are not formatted with
red font so I'm puzzled.

Terry, UK

Hi Terry,

Am Wed, 29 Jan 2020 21:26:51 +0000 schrieb Terry Pinnell:

One minor point that probably has a simple answer; did you notice that
the entries in cols N, O and P are red? The cols are not formatted with
red font so I'm puzzled.

some of your cells are formatted.
Insert a line into the code (after LRow=...)to clear the formats:

LRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
..Rows(LRow).ClearFormats

What kind of file is your source and what is the name of it?
Perhaps there is an easier way without the clipboard.


Regards
Claus B.
--
Windows10
Office 2016

Claus Busch wrote:

Hi Terry,

Am Wed, 29 Jan 2020 21:26:51 +0000 schrieb Terry Pinnell:

One minor point that probably has a simple answer; did you notice that
the entries in cols N, O and P are red? The cols are not formatted with
red font so I'm puzzled.

some of your cells are formatted.
Insert a line into the code (after LRow=...)to clear the formats:

LRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
.Rows(LRow).ClearFormats

What kind of file is your source and what is the name of it?
Perhaps there is an easier way without the clipboard.


Regards
Claus B.

You're right, the issue about formatting has arisen because I decided a
few weeks ago to make major simplifications. I try to keep my Excel
questions much simplified, but - although I don't want to lose you due
to boredom! - I think the following background would be useful now.

The previous WalkIndex layout had 54 columns! As you see it now has half
that number. It lists 25 years of walks and has naturally evolved. So
has the method of updating it. For some years that has come from a
complex macro written with Macro Express Pro. That too is evolving. I
supplement it with calls to VBA macros, like the one under discussion.
Where I sacrifice my comfort zone for speed and versatility. With the
same motive I also call Python scripts in PaintShop Pro (with help from
the experts).

Almost all the values in WalkIndex.xlsm originate from the GPX file made
during a walk (currently by my iPhone 6S+). 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.)

So the current status is this: when my MX macro has finished its
analysis, and before calling any VBA macro, the data is in two places:
1. tTrackName.txt, where it looks like this
https://www.dropbox.com/s/6nkh9tvpi3...m4.4.txt?raw=1

2. On the clipboard, in more compact form in variable tAll_VBA. That is
ready for access when the MX macro then opens WalkIndex.xlsm (or a test
version of that during our discussion).

I've changed the cell colour of cols N, O & P from red to black.

So far I've listed these outstanding points:

1. Col A was developed in the latest version of my MX macro as a text
string which resists Excel formatting. My previous col A entries were
formatted ddd dd/mm/yy and that's the appearance I want to retain. So I
will re-examine my MX macro. Note that I'm using 'Date of walk' as the
first part of the concatenated string in col R. That's the closest I
could get in MX to my preference of 'Monday 8th July 2019, Mawnan Smith
to Falmouth', which I achieved in my earlier VBA code versions, via that
intermediate track sheet. This is an important requirement, as the next
stage of my MX macro is to completely automate the preparation of a JPG,
in which the title uses col R, like this example:
https://www.dropbox.com/s/6hj3bdblko...m6.5.jpg?raw=1

2. Cols J and K are also presumably suffering from some formatting
issue, as they should be 09:34 and 13:21

3. Cols H to P are not quite centered, although their alignment is set
to Center.

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!

Best wishes,

Terry, East Grinstead, UK

Claus Busch wrote:

Hi Terry,

Am Wed, 29 Jan 2020 21:26:51 +0000 schrieb Terry Pinnell:

One minor point that probably has a simple answer; did you notice that
the entries in cols N, O and P are red? The cols are not formatted with
red font so I'm puzzled.

some of your cells are formatted.
Insert a line into the code (after LRow=...)to clear the formats:

LRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
.Rows(LRow).ClearFormats

What kind of file is your source and what is the name of it?
Perhaps there is an easier way without the clipboard.


Regards
Claus B.

One small simplification:
https://www.dropbox.com/s/7iwg59xtzz...laus.jpg?raw=1

Terry, East Grinstead, UK

Claus Busch wrote:

Hi Terry,

Am Wed, 29 Jan 2020 21:26:51 +0000 schrieb Terry Pinnell:

One minor point that probably has a simple answer; did you notice that
the entries in cols N, O and P are red? The cols are not formatted with
red font so I'm puzzled.

some of your cells are formatted.
Insert a line into the code (after LRow=...)to clear the formats:

LRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
.Rows(LRow).ClearFormats

What kind of file is your source and what is the name of it?
Perhaps there is an easier way without the clipboard.


Regards
Claus B.

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

Terry, UK

Terry,
Couple of things:

Date formatting and its display format are 2 separate steps:

1a. Format colA as Date

1b. Then choose 'Custom' to set the display format how you want it:
[$-409]ddd dd/mm/yy;@ Wed 01/01/20
OR
[$-409]ddd dd-mm-yy;@ Wed 01-01-20
OR
[$-409]ddd-ddmmyy;@ Wed-010120


Nix on the 100+ lines of code! A text file being used as a database should be
properly configured as a database so its content can be more simply managed.

2a. Your text file is problematic because its worksheet source uses rows
for spacing rather than RowHeight, which makes using code to import its
data a rather daunting task of checking for empty lines and other stuff
that makes managing the data into the target worksheet way more complex
than it needs to be.

2b. Using ADODB to read the text file into a recordset requires only 1 line
of code to dump the data into the worksheet cols in 1 shot.

2c. Reading the data into an array will also enable dumping it into the
sheet in 1 shot.

2d. It's a rather simple task to manage how the target sheet handles your
preferred formatting of the data after (or even before) importing to the
worksheet.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Terry,

Am Thu, 30 Jan 2020 13:38:03 +0000 schrieb Terry Pinnell:

The previous WalkIndex layout had 54 columns! As you see it now has half
that number. It lists 25 years of walks and has naturally evolved. So
has the method of updating it. For some years that has come from a
complex macro written with Macro Express Pro. That too is evolving. I
supplement it with calls to VBA macros, like the one under discussion.
Where I sacrifice my comfort zone for speed and versatility. With the
same motive I also call Python scripts in PaintShop Pro (with help from
the experts).

Almost all the values in WalkIndex.xlsm originate from the GPX file made
during a walk (currently by my iPhone 6S+). 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.)

So the current status is this: when my MX macro has finished its
analysis, and before calling any VBA macro, the data is in two places:
1. tTrackName.txt, where it looks like this
https://www.dropbox.com/s/6nkh9tvpi3...m4.4.txt?raw=1

2. On the clipboard, in more compact form in variable tAll_VBA. That is
ready for access when the MX macro then opens WalkIndex.xlsm (or a test
version of that during our discussion).

I've changed the cell colour of cols N, O & P from red to black.

So far I've listed these outstanding points:

1. Col A was developed in the latest version of my MX macro as a text
string which resists Excel formatting. My previous col A entries were
formatted ddd dd/mm/yy and that's the appearance I want to retain. So I
will re-examine my MX macro. Note that I'm using 'Date of walk' as the
first part of the concatenated string in col R. That's the closest I
could get in MX to my preference of 'Monday 8th July 2019, Mawnan Smith
to Falmouth', which I achieved in my earlier VBA code versions, via that
intermediate track sheet. This is an important requirement, as the next
stage of my MX macro is to completely automate the preparation of a JPG,
in which the title uses col R, like this example:
https://www.dropbox.com/s/6hj3bdblko...m6.5.jpg?raw=1

2. Cols J and K are also presumably suffering from some formatting
issue, as they should be 09:34 and 13:21

3. Cols H to P are not quite centered, although their alignment is set
to Center.

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!

why don't you read the range of the file you provided as jpg into an
array? You can use that array the same way you use the array from
clipboard in the code.
varData=range("B6:B29")


Regards
Claus B.
--
Windows10
Office 2016

Claus Busch wrote:

Hi Terry,

Am Thu, 30 Jan 2020 13:38:03 +0000 schrieb Terry Pinnell:

The previous WalkIndex layout had 54 columns! As you see it now has half
that number. It lists 25 years of walks and has naturally evolved. So
has the method of updating it. For some years that has come from a
complex macro written with Macro Express Pro. That too is evolving. I
supplement it with calls to VBA macros, like the one under discussion.
Where I sacrifice my comfort zone for speed and versatility. With the
same motive I also call Python scripts in PaintShop Pro (with help from
the experts).

Almost all the values in WalkIndex.xlsm originate from the GPX file made
during a walk (currently by my iPhone 6S+). 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.)

So the current status is this: when my MX macro has finished its
analysis, and before calling any VBA macro, the data is in two places:
1. tTrackName.txt, where it looks like this
https://www.dropbox.com/s/6nkh9tvpi3...m4.4.txt?raw=1

2. On the clipboard, in more compact form in variable tAll_VBA. That is
ready for access when the MX macro then opens WalkIndex.xlsm (or a test
version of that during our discussion).

I've changed the cell colour of cols N, O & P from red to black.

So far I've listed these outstanding points:

1. Col A was developed in the latest version of my MX macro as a text
string which resists Excel formatting. My previous col A entries were
formatted ddd dd/mm/yy and that's the appearance I want to retain. So I
will re-examine my MX macro. Note that I'm using 'Date of walk' as the
first part of the concatenated string in col R. That's the closest I
could get in MX to my preference of 'Monday 8th July 2019, Mawnan Smith
to Falmouth', which I achieved in my earlier VBA code versions, via that
intermediate track sheet. This is an important requirement, as the next
stage of my MX macro is to completely automate the preparation of a JPG,
in which the title uses col R, like this example:
https://www.dropbox.com/s/6hj3bdblko...m6.5.jpg?raw=1

2. Cols J and K are also presumably suffering from some formatting
issue, as they should be 09:34 and 13:21

3. Cols H to P are not quite centered, although their alignment is set
to Center.

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!

why don't you read the range of the file you provided as jpg into an
array? You can use that array the same way you use the array from
clipboard in the code.
varData=range("B6:B29")


Regards
Claus B.

Thanks Claus but those track sheets are not made any more. Stopping them
was past of my major recent changes. 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.

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

I'm happy with the solution you wrote for me but struggling a bit over
the few outstanding code points I raised.

Terry, East Grinstead, UK

GS wrote:

Terry,
Couple of things:

Date formatting and its display format are 2 separate steps:

1a. Format colA as Date

1b. Then choose 'Custom' to set the display format how you want it:
[$-409]ddd dd/mm/yy;@ Wed 01/01/20
OR
[$-409]ddd dd-mm-yy;@ Wed 01-01-20
OR
[$-409]ddd-ddmmyy;@ Wed-010120

Despite being a VBA novice I do know how to format an Excel column!

Did you see the worksheet example I referenced earlier in the thread?
Obviously I formatted col A to ddd dd/mm/yy. The issue is that using the
latest code that doesn't work.

Nix on the 100+ lines of code! A text file being used as a database should be
properly configured as a database so its content can be more simply managed.

2a. Your text file is problematic because its worksheet source

The new source of the text data is the clipboard, NOT that previously
used worksheet, shown to explain the evolving nature of my project.

As I explained, the current WalkIndex still holds old and new data. The
'old' was entered in a variety of ways over 25 years, about 800 walks.
From manual to automated in many stages. Not surprisingly a sort of
'fault line' now seems to need handling at the date on which the new
approach starts.

For col A in practical terms I'm seeking an edit to Claus's last code.
Assuming that could be most neatly achieved by adding a class "A", I
spent a couple of hours trying that last night. But although I could do
it an additional macro, I didn't understand Claus's code well enough to
incorporate it.

uses rows
for spacing rather than RowHeight, which makes using code to import its
data a rather daunting task of checking for empty lines and other stuff
that makes managing the data into the target worksheet way more complex
than it needs to be.

2b. Using ADODB to read the text file into a recordset requires only 1 line
of code to dump the data into the worksheet cols in 1 shot.

2c. Reading the data into an array will also enable dumping it into the
sheet in 1 shot.

2d. It's a rather simple task to manage how the target sheet handles your
preferred formatting of the data after (or even before) importing to the
worksheet.

As mentioned previously, I'm reconciled to the large overhead of VBA
code required in Win 10 Pro to support the clipboard, now that I know it
works reliably.

I'll prepare a fresh sample called WalkIndex-Test.xlsm containing a few
old entries, reflecting its current layout and the latest code I'm using
to add new entries.

Terry, East Grinstead, UK

Hi Terry,

Am Thu, 30 Jan 2020 21:18:44 +0000 schrieb Terry Pinnell:

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"

your date is a text value.
Have another look at OneDrive. I changed the code to get a real date.


Regards
Claus B.
--
Windows10
Office 2016

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

Claus Busch wrote:

Hi Terry,

Am Thu, 30 Jan 2020 21:18:44 +0000 schrieb Terry Pinnell:

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"

your date is a text value.
Have another look at OneDrive. I changed the code to get a real date.


Regards
Claus B.

Thanks Claus but that gives Run-time error '13': Type mismatch
And this line is highlighted
.Cells(LRow, varCols(i)) = Format(DateValue(strDate), "dddd
dd\/mm\/yy")

I use Dropbox, not OneDrive, nor do I share files, but I'm sure I have
your code correctly downloaded. (I'll paste it at the end of this post.)

I'm not using the Auto_Open now. I copy tAll_VBA to the clipboard and
use F5 to run your macro. (I've also pasted a copy of that.)

It did somehow run once the first time I tried and I noted that col A
was 'Montag 08/07/19' instead of 'Mon 08/07/19'. And col J (Miles) which
displayed a comma instead of a decimal point. Since then I have not been
able to get past that Type mismatch.

====================

Sub Claus()
'Gets external data from clipboard into variable tAll_VBA
'Then processes it with code from Claus
'Temporarily using a short extract from WalkIndex.xlsm called
'WalkIndex-Claus.xlsm

Dim objData As New MSForms.DataObject
Dim tAll_VBA
Dim strCols As String
Dim strValues As String
'Types for these arrays?
Dim varTmp As Variant
Dim varCols As Variant
Dim varValues As Variant
Dim varMonth() As Variant
Dim varDate As Variant
Dim LRow As Long
Dim i As Integer
Dim j As Integer
Dim strDate As String

objData.GetFromClipboard
tAll_VBA = objData.GetText()
'For testing
'MsgBox tAll_VBA
'Debug.Print tAll_VBA

'Now need to get that into a new row of WI

'This block from Claus
strCols = "A,B,C,H,I,J,K,L,M,N,O,P,R,S,T,U,V,W"
varCols = Split(strCols, ",")
For i = 0 To 11
ReDim Preserve varMonth(i)
varMonth(i) = MonthName(i + 1)
Next
strValues = "3,4,2,10,8,5,6,7,9,11,12,13,14,15,16,17,18,19 "
varValues = Split(strValues, ",")
varTmp = Split(tAll_VBA, Chr(10))
With Sheets("Target")
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
.Rows(LRow).ClearFormats
For i = LBound(varCols) To UBound(varCols)
Select Case varCols(i)
Case "J", "K", "L"
.Cells(LRow, varCols(i)) =
Format(varTmp(varValues(i)), "hh:mm")
Case "A"
strDate = Mid(varTmp(varValues(i)),
InStr(varTmp(varValues(i)), " ") + 1)
varDate = Split(strDate, " ")
For j = 0 To 11
If varDate(1) = varMonth(j) Then
strDate = varDate(0) & "." & j + 1 & "." &
varDate(2)
Exit For
End If
Next
.Cells(LRow, varCols(i)) =
Format(DateValue(strDate), "dddd dd\/mm\/yy")
Case Else
.Cells(LRow, varCols(i)) = varTmp(varValues(i))
End Select
Next
.Range("A" & LRow & ":W" & LRow).HorizontalAlignment = xlCenter
End With
End Sub

====================
20190919MarshGreen-B-r424-m4.4.gpx
Circular walk with Brian, Marsh Green
20190919MarshGreen-B-r424-m4.4
Thursday 19 September 2019
20190919
09:56
11:53
1:56
4.5
2.3
424
242
190
190
Thursday 19 September 2019: Circular walk with Brian, Marsh Green
Start 09:56, End 11:53, Dur'n 1:56 , 4.5 miles, avg. mph 2.3
Gross asc/desc 190 ft, Max 242 ft
C:\Users\terry\Dropbox\FinishedWalks\20190919Marsh Green-B-r424-m4.4.jpg
C:\Users\terry\Dropbox\FinishedWalks\20190919Marsh Green-B-r424-m4.4-PS.jpg
D:\Pictures\PHOTOS\Walks UK\2019\20190919-110750.jpg
30 January 2020

Terry, East Grinstead, UK

I've been to GPSU website and downloaded GPSU to review what/how it does/works
and how touse it via its Help file...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Terry,

Am Fri, 31 Jan 2020 16:45:33 +0000 schrieb Terry Pinnell:

Thanks Claus but that gives Run-time error '13': Type mismatch
And this line is highlighted
.Cells(LRow, varCols(i)) = Format(DateValue(strDate), "dddd
dd\/mm\/yy")

have another look. For me it works fine.

There is only one problem with the system language.
I have to change July to Juli.


Regards
Claus B.
--
Windows10
Office 2016

GS wrote:

Terry, I've addressed some of your comments from various threads of this topic:


I think you have some major misunderstandings.

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

Eh? I see no such 'proposal' from Claus!

Are *you* seriously proposing that I (an obvious VBA novice) discard the
complex and fully working MX Pro macro I've developed over many months
and duplicate it with VBA? Just to complete a few small refinements to
Claus's 99% ready VBA macro?

It is currently using that data from MX Pro and GPSU via a clipboard
copy I've made available in Excel. Its purpose: to accomplish the
relatively trivial step of copying that finished data onto a row of
Excel! My key reason for turning to VBA for that step is, as I said a
few posts ago, for its speed.

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

Do you have any experience of GPSU? And how much experience of writing
macros in MX Pro? My macro quickly generates the text file from the
original GPX using the powerful functionality of GPSU. The GPX is backed
up daily and weekly as well as being kept on Dropbox.

" 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?

'Exactly'? Are you truly interested in a detailed answer to that?

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.

Incredible! Do you realise just how patronising you sound? Most of the
above evangelising, including your comment that I "...don't know of a
better way!" speaks more of your arrogance than my ignorance!

Terry, East Grinstead, UK

Incredible! Do you realise just how patronising you sound? Most of the
above evangelising, including your comment that I "...don't know of a
better way!" speaks more of your arrogance than my ignorance!

Terry,
I did not mean to offend, nor to discount the great progress you've made over
the years! Like I said, I can appreciate the years and expertise you have with
MXP so I'm just offering my years of experience and expertise with Excel
application development 'if you want to go that route'. (Given the tremendous
amount of effort/energy do this project with you would require from me, 'now in
my 28th year living with ALS', I don't offer my services to many much anymore!)

If I didn't have a good understanding of both your project concept as well as
your approach to it.., I would not have offered any assistance at all. I'm
sorry you have taken my meaning in poor light!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

GS wrote:

Incredible! Do you realise just how patronising you sound? Most of the
above evangelising, including your comment that I "...don't know of a
better way!" speaks more of your arrogance than my ignorance!

Terry,
I did not mean to offend, nor to discount the great progress you've made over
the years! Like I said, I can appreciate the years and expertise you have with
MXP so I'm just offering my years of experience and expertise with Excel
application development 'if you want to go that route'. (Given the tremendous
amount of effort/energy do this project with you would require from me, 'now in
my 28th year living with ALS', I don't offer my services to many much anymore!)

If I didn't have a good understanding of both your project concept as well as
your approach to it.., I would not have offered any assistance at all. I'm
sorry you have taken my meaning in poor light!

Garry,

Well, I'm sure your intentions were good - so peace!

My reaction was partly because you seemed to have little grasp of the
background I'd carefully described. I don't think you can have followed
some of my links which I think justify my '99%' comment.

That's not to say that this final step of getting the macro's finished
output into a neat Excel worksheet isn't important to me.

Nor is it to say that I wouldn't love to have been able to do it all in
a real programming language. But I'm not a programmer. I work at a
fairly accomplished level in MX Pro and in humble 'copy/paste/edit' mode
in VBA, Python and Arduino C/C++ (the latter for my electronics hobby).

I'm still struggling to find what I'm doing wrongly with Claus's last
code, but I think I'll put it aside for now and retire to the lounge for
a glass of white ;-)

Best wishes,

Terry, East Grinstead, UK
Friday 31 January 2020, 1831

Hi Terry,

Am Fri, 31 Jan 2020 18:31:33 +0000 schrieb Terry Pinnell:

I'm still struggling to find what I'm doing wrongly with Claus's last
code, but I think I'll put it aside for now and retire to the lounge for
a glass of white ;-)

you get the error when the cipboard is empty.


Regards
Claus B.
--
Windows10
Office 2016

Claus Busch wrote:

Hi Terry,

Am Fri, 31 Jan 2020 18:31:33 +0000 schrieb Terry Pinnell:

I'm still struggling to find what I'm doing wrongly with Claus's last
code, but I think I'll put it aside for now and retire to the lounge for
a glass of white ;-)

you get the error when the cipboard is empty.


Regards
Claus B.

Thanks Claus, I'll follow up on that - maybe after dinner!

I must have changed something. Because as I said I *did* get one single
correct result (flaws apart). I assume you looked at my pasted code and
saw no variation?

Meanwhile, FWIW, the sequence of steps I take, with WalkIndex.xlsm (or
test equivalent) already open, is this:

1. Copy the 21 lines from my test editor. The first line is
20190919MarshGreen-B-r424-m4.4.gpx
and the last is
30 January 2020

2. From your code, the first two lines of which are

Sub Claus()
'Gets external data from clipboard into variable tAll_VBA

I use F5.

3. That gives the debug message.

So, from what you say, somehow the clipboard is getting cleared before
your macro runs?

BTW, can you confirm which version of the variable tAll_VBA you are
using please. And which exact worksheet are you running it on?

Finally (no promises!) how does 'Montag' and the German decimal comma
get into the result?

Best wishes,

Terry, East Grinstead, UK

Hi Terry,

Am Fri, 31 Jan 2020 18:31:33 +0000 schrieb Terry Pinnell:

I'm still struggling to find what I'm doing wrongly with Claus's last
code, but I think I'll put it aside for now and retire to the lounge for
a glass of white ;-)

you get the error when the cipboard is empty.


Regards
Claus B.

I got the same error running with an empty Clipboard! This is my contention for
going with reading the source data directly into an array and processing it
entirely within Excel with no external dependencies!!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

GS wrote:

Hi Terry,

Am Fri, 31 Jan 2020 18:31:33 +0000 schrieb Terry Pinnell:

I'm still struggling to find what I'm doing wrongly with Claus's last
code, but I think I'll put it aside for now and retire to the lounge for
a glass of white ;-)

you get the error when the cipboard is empty.


Regards
Claus B.

I got the same error running with an empty Clipboard! This is my contention for
going with reading the source data directly into an array and processing it
entirely within Excel with no external dependencies!!

Hi Garry,

OK, here's some typical 'source data'
https://www.dropbox.com/s/kwb4yy02rw...m8.3.gpx?raw=1

I'm looking forward to seeing and using your code, delivering all the
output I get from my MX Pro macro, text and images. Using your "very
simple approach"!


Terry, East Grinstead, UK

Claus Busch wrote:

Hi Terry,

Am Fri, 31 Jan 2020 18:31:33 +0000 schrieb Terry Pinnell:

I'm still struggling to find what I'm doing wrongly with Claus's last
code, but I think I'll put it aside for now and retire to the lounge for
a glass of white ;-)

you get the error when the cipboard is empty.


Regards
Claus B.

Hi Claus & Garry,

I methodically reduced the growing clutter of modules and procedures to
a minimum: just your macro and the 'clipboard overhead'. Then I tried
again. Still same result!

Here is my latest worksheet:
https://www.dropbox.com/s/p06jmcvnhe...est.xlsm?raw=1

The data for clipboard remains the same, but for convenience here it is
again:

20190919MarshGreen-B-r424-m4.4.gpx
Circular walk with Brian, Marsh Green
20190919MarshGreen-B-r424-m4.4
Thursday 19 September 2019
20190919
09:56
11:53
1:56
4.5
2.3
424
242
190
190
Thursday 19 September 2019: Circular walk with Brian, Marsh Green
Start 09:56, End 11:53, Dur'n 1:56 , 4.5 miles, avg. mph 2.3
Gross asc/desc 190 ft, Max 242 ft
C:\Users\terry\Dropbox\FinishedWalks\20190919Marsh Green-B-r424-m4.4.jpg
C:\Users\terry\Dropbox\FinishedWalks\20190919Marsh Green-B-r424-m4.4-PS.jpg
D:\Pictures\PHOTOS\Walks UK\2019\20190919-110750.jpg
30 January 2020

Terry, East Grinstead, UK

Terry Pinnell wrote:

Claus Busch wrote:

Hi Terry,

Am Fri, 31 Jan 2020 18:31:33 +0000 schrieb Terry Pinnell:

I'm still struggling to find what I'm doing wrongly with Claus's last
code, but I think I'll put it aside for now and retire to the lounge for
a glass of white ;-)

you get the error when the cipboard is empty.


Regards
Claus B.

Hi Claus & Garry,

I methodically reduced the growing clutter of modules and procedures to
a minimum: just your macro and the 'clipboard overhead'. Then I tried
again. Still same result!

Here is my latest worksheet:
https://www.dropbox.com/s/p06jmcvnhe...est.xlsm?raw=1

The data for clipboard remains the same, but for convenience here it is
again:

20190919MarshGreen-B-r424-m4.4.gpx
Circular walk with Brian, Marsh Green
20190919MarshGreen-B-r424-m4.4
Thursday 19 September 2019
20190919
09:56
11:53
1:56
4.5
2.3
424
242
190
190
Thursday 19 September 2019: Circular walk with Brian, Marsh Green
Start 09:56, End 11:53, Dur'n 1:56 , 4.5 miles, avg. mph 2.3
Gross asc/desc 190 ft, Max 242 ft
C:\Users\terry\Dropbox\FinishedWalks\20190919Mars hGreen-B-r424-m4.4.jpg
C:\Users\terry\Dropbox\FinishedWalks\20190919Mars hGreen-B-r424-m4.4-PS.jpg
D:\Pictures\PHOTOS\Walks UK\2019\20190919-110750.jpg
30 January 2020

Terry, East Grinstead, UK

And both the MsgBox at the start of the macro, AND the clipboard itself
when the macro had finished, still contained the original data.

Terry

Hi Terry,

Am Fri, 31 Jan 2020 20:35:27 +0000 schrieb Terry Pinnell:

Here is my latest worksheet:
https://www.dropbox.com/s/p06jmcvnhe...est.xlsm?raw=1

we don't have your data. Our clipboard is empty.
I pasted your data to another sheet and copy it from there.
Have a look:
https://www.dropbox.com/s/5jljxb1jyr...Test.xlsm?dl=0

If you run it on your language version the comma is changed to period
and the day names are in Englisch.


Regards
Claus B.
--
Windows10
Office 2016

GS wrote:

Hi Terry,

Am Fri, 31 Jan 2020 18:31:33 +0000 schrieb Terry Pinnell:

I'm still struggling to find what I'm doing wrongly with Claus's last
code, but I think I'll put it aside for now and retire to the lounge for
a glass of white ;-)

you get the error when the cipboard is empty.


Regards
Claus B.

I got the same error running with an empty Clipboard! This is my contention
for going with reading the source data directly into an array and
processing it entirely within Excel with no external dependencies!!

Hi Garry,

OK, here's some typical 'source data'
https://www.dropbox.com/s/kwb4yy02rw...m8.3.gpx?raw=1

I'm looking forward to seeing and using your code, delivering all the
output I get from my MX Pro macro, text and images. Using your "very
simple approach"!


Terry, East Grinstead, UK

Could you please also provide the 3 remaining items on my list (when you have
time to do so)?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Claus Busch wrote:

Hi Terry,

Am Fri, 31 Jan 2020 20:35:27 +0000 schrieb Terry Pinnell:

Here is my latest worksheet:
https://www.dropbox.com/s/p06jmcvnhe...est.xlsm?raw=1

we don't have your data. Our clipboard is empty.
I pasted your data to another sheet and copy it from there.
Have a look:
https://www.dropbox.com/s/5jljxb1jyr...Test.xlsm?dl=0

If you run it on your language version the comma is changed to period
and the day names are in Englisch.


Regards
Claus B.

Hi Claus,

I included the data at the bottom of my 16:45 post and mentioned it at
the top:
"I copy tAll_VBA to the clipboard and use F5 to run your macro. (I've
also pasted a copy of that.)"

BINGO - I just successfully ran your macro! ;-)

I've no idea why yours works and mine didn't. It seems the only
difference in our workflow is that you are copying from a single cell of
a worksheet and I'm copying from my text editor. (I'm pretty sure I also
occasionally copied from a worksheet too, but one line per row.)

I've been up since 5:30 am so I'll get back on the case in the morning.

Many thanks for sticking with me on this!

Best wishes,

Terry, East Grinstead, UK

GS wrote:

GS wrote:

Hi Terry,

Am Fri, 31 Jan 2020 18:31:33 +0000 schrieb Terry Pinnell:

I'm still struggling to find what I'm doing wrongly with Claus's last
code, but I think I'll put it aside for now and retire to the lounge for
a glass of white ;-)

you get the error when the cipboard is empty.


Regards
Claus B.

I got the same error running with an empty Clipboard! This is my contention
for going with reading the source data directly into an array and
processing it entirely within Excel with no external dependencies!!

Hi Garry,

OK, here's some typical 'source data'
https://www.dropbox.com/s/kwb4yy02rw...m8.3.gpx?raw=1

I'm looking forward to seeing and using your code, delivering all the
output I get from my MX Pro macro, text and images. Using your "very
simple approach"!


Terry, East Grinstead, UK

Thanks!
This is an XML file that has 4,846 lines of data (including blanks), which will
easily parse with any XML parser utility. Fortunately, this is available to
VB[A] already because many years ago XML became the standard for storing data
as plain text files and processing doesn't have the overheads associated with a
full-blown database.

Sounds good so far. Time will tell!

I will only be proposing a solution for pulling the data into your worksheet
similar to you do now

Just to be su I assume you mean the WalkIndex.xlsm workbook,
worksheet Target. As I reminded you, I no longer make the 'track sheet'
workbooks.

and what GPSU does into its view windows. Although I do
use PSP for processing ScreenCaptures, I'm afraid you'll have to do your map
processes same as you now do. (Assuming the images you refer to are not bundled
with the file data on the clipboard)

Ah, I thought the idea was that you could do it *all* in VBA? Starting
with the GPX and ending with the extra data added to WalkIndex.xlsm?

Is this the GPSU output format for spreadsheets?
Sorry, don't understand that last question. GPSU doesn't have 'an output
format for spreadsheets' per se, but CSV is often used, although not by
me.

Best wishes,
Terry, East Grinstead, UK


Terry, East Grinstead, UK

GS wrote:

GS wrote:

Hi Terry,

Am Fri, 31 Jan 2020 18:31:33 +0000 schrieb Terry Pinnell:

I'm still struggling to find what I'm doing wrongly with Claus's last
code, but I think I'll put it aside for now and retire to the lounge for
a glass of white ;-)

you get the error when the cipboard is empty.


Regards
Claus B.

I got the same error running with an empty Clipboard! This is my
contention for going with reading the source data directly into an array
and processing it entirely within Excel with no external dependencies!!

Hi Garry,

OK, here's some typical 'source data'
https://www.dropbox.com/s/kwb4yy02rw...m8.3.gpx?raw=1

I'm looking forward to seeing and using your code, delivering all the
output I get from my MX Pro macro, text and images. Using your "very
simple approach"!


Terry, East Grinstead, UK

Thanks!
This is an XML file that has 4,846 lines of data (including blanks), which
will easily parse with any XML parser utility. Fortunately, this is
available to VB[A] already because many years ago XML became the standard
for storing data as plain text files and processing doesn't have the
overheads associated with a full-blown database.

Sounds good so far. Time will tell!

I will only be proposing a solution for pulling the data into your worksheet
similar to you do now

Just to be su I assume you mean the WalkIndex.xlsm workbook,
worksheet Target. As I reminded you, I no longer make the 'track sheet'
workbooks.

I asked for the current worksheet template[s] you now use in WalkIndex.xlsm!

and what GPSU does into its view windows. Although I do
use PSP for processing ScreenCaptures, I'm afraid you'll have to do your map
processes same as you now do. (Assuming the images you refer to are not
bundled with the file data on the clipboard)

Ah, I thought the idea was that you could do it *all* in VBA? Starting
with the GPX and ending with the extra data added to WalkIndex.xlsm?

Yes, that is correct. What am I (or you) missing that you ask this? I still
haven't gotten from you the design description and project intent so perhaps
I'm at a disadvantage for fully understanding this project beyond what your
WalkIndex.xlsm reveals.

Is this the GPSU output format for spreadsheets?
Sorry, don't understand that last question. GPSU doesn't have 'an output
format for spreadsheets' per se, but CSV is often used, although not by
me.
Well since WalkIndex.xlsm IS a spreadsheet I'd think the data should be in
GPSU's CSV format since XML parsing carries way more overhead than parsing a
simple delimited text file.

Best wishes,
Terry, East Grinstead, UK


Terry, East Grinstead, UK

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

#2 was posted at 20:24

I have all the download files, this recent one since I posted here.

#3 Please clarify what you mean.

How do you want to interface with, and what are, the expected tasks and/or
functionality of this project. For example, if you want your own custom tab on
the Ribbon for menus then specify what these are to be and their respective
function.

#4 I thought I'd already done that in some detail? In short: Get from a
GPX to all the various outputs I've described, including WalkIndex.xlsm.
Saving an intermediate simple text file in the format I've described, as
I'm doing at present, is an option. The most important output is the
Finished Walk, an example of which I showed earlier. I have much if the
groundwork done on that, but intend to automate it completely.

Well, ..you've done a great deal of explaining what you are trying to get
working in WalkIndex.xlsm macros, but that doesn't explain all of the what/why
you are doing/using a spreadsheet for, nor what specific data you want from the
gpx file. (there's lots more in the file than I see in your xlsm!)

I'll look at the test sample file tmo to see where you've gotten to. Basically,
I'm offering a self-sustained solution to bypass using Clipboard and whatever
other processes you use to put the data there for populating your worksheet.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

GS wrote:

GS wrote:

Hi Terry,

Am Fri, 31 Jan 2020 18:31:33 +0000 schrieb Terry Pinnell:

I'm still struggling to find what I'm doing wrongly with Claus's last
code, but I think I'll put it aside for now and retire to the lounge for
a glass of white ;-)

you get the error when the cipboard is empty.


Regards
Claus B.

I got the same error running with an empty Clipboard! This is my
contention for going with reading the source data directly into an array
and processing it entirely within Excel with no external dependencies!!

Hi Garry,

OK, here's some typical 'source data'
https://www.dropbox.com/s/kwb4yy02rw...m8.3.gpx?raw=1

I'm looking forward to seeing and using your code, delivering all the
output I get from my MX Pro macro, text and images. Using your "very
simple approach"!


Terry, East Grinstead, UK

Could you please also provide the 3 remaining items on my list (when you
have time to do so)?

Hi Garry,

Assuming you mean this list:

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.

#2 was posted at 20:24
#3 Please clarify what you mean.
#4 I thought I'd already done that in some detail? In short: Get from a
GPX to all the various outputs I've described, including WalkIndex.xlsm.
Saving an intermediate simple text file in the format I've described, as
I'm doing at present, is an option. The most important output is the
Finished Walk, an example of which I showed earlier. I have much if the
groundwork done on that, but intend to automate it completely.


Terry, East Grinstead, UK

A copy of the expected Clipboard data for the sample gpx file you posted would
be great help. Thanks!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Terry,

Am Fri, 31 Jan 2020 22:53:54 +0000 schrieb Terry Pinnell:

I included the data at the bottom of my 16:45 post and mentioned it at
the top:
"I copy tAll_VBA to the clipboard and use F5 to run your macro. (I've
also pasted a copy of that.)"

BINGO - I just successfully ran your macro! ;-)

I've no idea why yours works and mine didn't. It seems the only
difference in our workflow is that you are copying from a single cell of
a worksheet and I'm copying from my text editor. (I'm pretty sure I also
occasionally copied from a worksheet too, but one line per row.)

I've been up since 5:30 am so I'll get back on the case in the morning.

have a look:
https://www.dropbox.com/transfer/AAA...D3QOp0C 1y7E8

Into that folder is a text file and a new Workbook. The macro reads the
text file and write it into the workbook.


Regards
Claus B.
--
Windows10
Office 2016

Just to be su I assume you mean the WalkIndex.xlsm workbook,
worksheet Target. As I reminded you, I no longer make the 'track sheet'
workbooks.

So then I'll need the Clipboard content that you suggest exists in text files
somewhere, but you're looking to skip that step in favor of the less secure
Clipboard import.

Even better would be to send the gpx and Clipboard files for the 5 events
listed in your "LatestTest" workbook.

The point of my involvement is to eliminate need to use Clipboard by way of
importing the data you put there directly from its source file; - clearly this
is not the GPX file (my bad for assuming it was somewhere in there) but rather
the input data for WalkIndex.xlsm is from/in some other file you put together
in your text editor when you prepared the Clipboard contents. It is that file
we are referring to (see Claus' recent upload)!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry,

Replies inline.

GS wrote:

GS wrote:

GS wrote:

Hi Terry,

Am Fri, 31 Jan 2020 18:31:33 +0000 schrieb Terry Pinnell:

I'm still struggling to find what I'm doing wrongly with Claus's last
code, but I think I'll put it aside for now and retire to the lounge for
a glass of white ;-)

you get the error when the cipboard is empty.


Regards
Claus B.

I got the same error running with an empty Clipboard! This is my
contention for going with reading the source data directly into an array
and processing it entirely within Excel with no external dependencies!!

Hi Garry,

OK, here's some typical 'source data'
https://www.dropbox.com/s/kwb4yy02rw...m8.3.gpx?raw=1

I'm looking forward to seeing and using your code, delivering all the
output I get from my MX Pro macro, text and images. Using your "very
simple approach"!


Terry, East Grinstead, UK

Thanks!
This is an XML file that has 4,846 lines of data (including blanks), which
will easily parse with any XML parser utility. Fortunately, this is
available to VB[A] already because many years ago XML became the standard
for storing data as plain text files and processing doesn't have the
overheads associated with a full-blown database.

Sounds good so far. Time will tell!

I will only be proposing a solution for pulling the data into your worksheet
similar to you do now

Just to be su I assume you mean the WalkIndex.xlsm workbook,
worksheet Target. As I reminded you, I no longer make the 'track sheet'
workbooks.

I asked for the current worksheet template[s] you now use in WalkIndex.xlsm!

There is no 'template'. Just what you see.

and what GPSU does into its view windows. Although I do
use PSP for processing ScreenCaptures, I'm afraid you'll have to do your map
processes same as you now do. (Assuming the images you refer to are not
bundled with the file data on the clipboard)

Ah, I thought the idea was that you could do it *all* in VBA? Starting
with the GPX and ending with the extra data added to WalkIndex.xlsm?

Yes, that is correct. What am I (or you) missing that you ask this? I still
haven't gotten from you the design description and project intent so perhaps
I'm at a disadvantage for fully understanding this project beyond what your
WalkIndex.xlsm reveals.

I've answered those in the post in which you raised them.

Yes, of course you're at a disadvantage, I'd say a serious one! Just as
I am with this 1% of the project because I'm not a VBA programmer, so
you are with the previous 99% because you don't know GPSU.

But we're now way OT! If you want to discuss GPSU there's a Google group
forum at https://groups.io/g/gpsu/ but it's been pretty quiet for the
last few years and traffic dropped off even more sharply when it moved
to Google after Yahoo dropped its groups.


Is this the GPSU output format for spreadsheets?
Sorry, don't understand that last question. GPSU doesn't have 'an output
format for spreadsheets' per se, but CSV is often used, although not by
me.
Well since WalkIndex.xlsm IS a spreadsheet I'd think the data should be in
GPSU's CSV format since XML parsing carries way more overhead than parsing a
simple delimited text file.

Terry, East Grinstead, UK

Hi Claus,

Wow, you were up late last night!

I'm not sure I have downloaded the correct file, as I'm not too
comfortable with OneDrive (prefer Dropbox). But if I have it right then
your latest looks like you've just added the line:

Sheets("Sheet1").Range("A1").Copy

Convenient for testing the main code but excludes the most important
step, getting EXTERNAL data into Excel.

After breakfast I'm going to focus on the mystery about why the
clipboard method I was testing consistently failed.

Did you try it with MY data? Pasted from here for example. That might
help me isolate the cause:

20190919MarshGreen-B-r424-m4.4.gpx
Circular walk with Brian, Marsh Green
20190919MarshGreen-B-r424-m4.4
Thursday 19 September 2019
20190919
09:56
11:53
1:56
4.5
2.3
424
242
190
190
Thursday 19 September 2019: Circular walk with Brian, Marsh Green
Start 09:56, End 11:53, Dur'n 1:56 , 4.5 miles, avg. mph 2.3
Gross asc/desc 190 ft, Max 242 ft
C:\Users\terry\Dropbox\FinishedWalks\20190919Marsh Green-B-r424-m4.4.jpg
C:\Users\terry\Dropbox\FinishedWalks\20190919Marsh Green-B-r424-m4.4-PS.jpg
D:\Pictures\PHOTOS\Walks UK\2019\20190919-110750.jpg
30 January 2020



Ich hoffe du hattest ein gutes Wochenende.

Best wishes,

Terry, East Grinstead, UK

====================


Claus Busch wrote:

Hi Terry,

Am Fri, 31 Jan 2020 22:53:54 +0000 schrieb Terry Pinnell:

I included the data at the bottom of my 16:45 post and mentioned it at
the top:
"I copy tAll_VBA to the clipboard and use F5 to run your macro. (I've
also pasted a copy of that.)"

BINGO - I just successfully ran your macro! ;-)

I've no idea why yours works and mine didn't. It seems the only
difference in our workflow is that you are copying from a single cell of
a worksheet and I'm copying from my text editor. (I'm pretty sure I also
occasionally copied from a worksheet too, but one line per row.)

I've been up since 5:30 am so I'll get back on the case in the morning.

have a look:
https://www.dropbox.com/transfer/AAA...D3QOp0C 1y7E8

Into that folder is a text file and a new Workbook. The macro reads the
text file and write it into the workbook.


Regards
Claus B.

Yes, of course you're at a disadvantage, I'd say a serious one! Just as
I am with this 1% of the project because I'm not a VBA programmer, so
you are with the previous 99% because you don't know GPSU.

But we're now way OT! If you want to discuss GPSU there's a Google group
forum at https://groups.io/g/gpsu/ but it's been pretty quiet for the
last few years and traffic dropped off even more sharply when it moved
to Google after Yahoo dropped its groups.

Well you are certainly correct speaking about your project, but GPSU's help
file is pretty straight forward about explaining how it works and how to use
it, ..speaking from an application developer's point of view. (VBA isn't the
only language I develop in; - all of my Excel-based apps have equivalent
stand-alone Windows EXEs for those who don't/won't use MS Office. These are
currently being revised to implement a newer spreadsheet control that fully
supports working with the latest Excel file formats.)

Also, I'm very familiar with data logging in more general (broader) usage so
the GPX file you posted (4846 lines) is a rather small file by comparison to
many I've seen and worked with before. That said, your final (nicely done) xlsm
has that same walk as its 1st entry; - the Trkpts field value is only 925; how
was that arrived at from the 4846-line data log with 4828 trkpt lines? (Your
ActualMiles field value is 7.9; the GPX value is 8.3; - I assume you turned the
recorder on before actually starting the walk!)

The context of my offer to you is to populate WalkIndex.xlsm directly from the
source file instead of from Clipboard; - that data source file is what I need
to work with because it contains data not in the GPX file. (I was just curious
about the output format from GPSU's CSV file; - disappointed that's not what
you posted!)


My design intent is as follows:

Use a pre-configured source data template file for listing new walk data;
This will be a proper text file dbase template; - 1st line has the
datafields from Target pre-inserted, as a delimited list. (I normally use
the Pipe character but this is your choice as to which character)
- gives you the option to include multiple walks in a file

Use your final Target sheet as its template (xlsm) for adding new walk data
and displaying historical records;

Enable you to choose the file[s] to import via a file dialog for each session
you add more walk data;
- adds new data to the next empty row

A backup (dbase) file will be maintained in case your xlsm gets broken or
damaged such that it won't open; - you'll be able to recover your data into
a new workbook with 1 mouse click.

(This is a typical feature with other data logger analysis projects I've
done)

Would you like it to not pre-load existing walk data when it opens?
(Typically, users are only working with new data being added, and have
existing data display only on demand? Typically, my users like to be
able to view historical data using filters such as 'Place to Place',
'Date to Date', and the like.)

(My experience with data logging systems is mostly in the trucking
industry for analizing road trips; - place-to-place mileage, elapsed-time,
fuel-consumed, rest-stops, and whatever else the client wants. Data
sources are the logger system output file, trip manifest, and trip record.
Normal usage is direct input from the source paperwork and data logger
file is read to setup the record rows for each place-to-place)

Optionally, enable you to view your maps and other images via InsertPicture
on a separate sheet via existing links to image files (for viewing only);

All features run from menu controls on a custom Ribbon tab that appear when
the workbook opens, disappears when it closes;

As is the case with all my clients, this project will remain your proprietary
property. (Don't worry, there's no fee for this!)

If you decide to proceed, I suggest we continue with this offline as it
ventures way too far OT to continue here. (You'll need to post an email
address!)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Claus Busch wrote:

Hi Terry,

Am Fri, 31 Jan 2020 22:53:54 +0000 schrieb Terry Pinnell:

I included the data at the bottom of my 16:45 post and mentioned it at
the top:
"I copy tAll_VBA to the clipboard and use F5 to run your macro. (I've
also pasted a copy of that.)"

BINGO - I just successfully ran your macro! ;-)

I've no idea why yours works and mine didn't. It seems the only
difference in our workflow is that you are copying from a single cell of
a worksheet and I'm copying from my text editor. (I'm pretty sure I also
occasionally copied from a worksheet too, but one line per row.)

I've been up since 5:30 am so I'll get back on the case in the morning.

have a look:
https://www.dropbox.com/transfer/AAA...D3QOp0C 1y7E8

Into that folder is a text file and a new Workbook. The macro reads the
text file and write it into the workbook.


Regards
Claus B.

====================

Hi Claus,

Following my post early this morning I've made some progress. Could you
please download my queries here in WalkIndexLatestTest-FromClaus.xlsm

https://www.dropbox.com/s/vw5kgq36xy...aus.xlsm?raw=1

Best wishes,

Terry, East Grinstead, UK