View Single Post
  #11   Report Post  
Posted to microsoft.public.excel.programming
Claus Busch Claus Busch is offline
external usenet poster
 
Posts: 3,872
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