Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old January 31st 20, 12:11 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,762
Default Copy multi-line variable to first empty row

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

  #12   Report Post  
Old January 31st 20, 10:23 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2015
Posts: 192
Default Copy multi-line variable to first empty row

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
  #13   Report Post  
Old January 31st 20, 12:20 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2015
Posts: 192
Default Copy multi-line variable to first empty row

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
  #14   Report Post  
Old January 31st 20, 01:27 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,762
Default Copy multi-line variable to first empty row

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
  #15   Report Post  
Old January 31st 20, 05:14 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,138
Default Copy multi-line variable to first empty row

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


  #16   Report Post  
Old January 31st 20, 05:45 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2015
Posts: 192
Default Copy multi-line variable to first empty row

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
  #17   Report Post  
Old January 31st 20, 05:56 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,138
Default Copy multi-line variable to first empty row

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
  #18   Report Post  
Old January 31st 20, 06:13 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,762
Default Copy multi-line variable to first empty row

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
  #19   Report Post  
Old January 31st 20, 06:26 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2015
Posts: 192
Default Copy multi-line variable to first empty row

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
  #20   Report Post  
Old January 31st 20, 06:41 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,138
Default Copy multi-line variable to first empty row

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Variable series multi line chart? cherman Charts and Charting in Excel 1 February 17th 10 10:29 PM
Macro to validate is cell is not empty and copy the line Steff[_3_] Excel Programming 2 December 10th 07 10:16 PM
Copy row to next empty line [email protected] Excel Programming 9 November 9th 07 02:19 PM
Copy and Paste in the first empty available line. Etienne Excel Programming 5 August 3rd 06 03:49 PM
copy/paste excel to texteditor -> multi-line text cells gain unwanted double-quotes niz Excel Discussion (Misc queries) 1 October 14th 05 02:06 PM


All times are GMT +1. The time now is 08:52 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017