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

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