Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #51   Report Post  
Old February 3rd 20, 07:24 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

GS wrote:

Hi Garry,

Looks good - and impressively ambitious!

Some comments inline.

I'm still not quite there with Claus's latest macro. The baffling
problem is that I can run it OK on several test versions of WalkIndex
but not on that full file itself.

Because of that I have not yet had time to finish the outputs you asked
for but hope to do so tomorrow.


No rush! I'm following Claus' progress with code to read in data your source
file instead of Clipboard. He does good work so hang in there!

I'll try to generate a CSV from the GPX I have for Fowley-Polperro. Also, after
seeing your Clipboard source file example I've created a vertical list template
using value pairs (fieldname=value) similar to what you do now, but in an INI
file format as an alternative for use with single walk events. (Don't want to
overwhelm you with too much new stuff all at once!)


Hi Garry,

I've not yet got your email address so I'm posting here as I prepared
this yesterday as planned. If you have queries or need further
information please use the email address I sent you.

As requested for your VBA project, here's a list of my macro functions.

1. Inputs a GPX to GPSU. Like the example I sent you.

2. Prompts for short description of walk, tTrackDescr.

3. Offers option to reduce number of trackpoints.

4. If reduced, allows renaming of track and saves with tFileName =
tTrackName

5. Smooth's altitude data using filter = 4 and outputs an elevation
profile as a JPG. Here's an example:
https://www.dropbox.com/s/2lj3ipwh07...Elev.jpg?raw=1

6. Outputs summary report. Like the example discussed in this thread.
Here's an example:
https://www.dropbox.com/s/n300ncvxmu...m8.3.txt?raw=1
(That should also help you as it includes the variable names assigned at
that point.

7. Sends calls to PaintShop Pro to enhance the GPSU elevation profile
and save it. Here's an example:
https://www.dropbox.com/s/4tveyo50n9...9-PS.jpg?raw=1
Assigns the location as tPSlink,

8. Prompts user to choose a photo/video taken during the walk, from the
appropriate folder automatically presented (based on the first eight
characters of tTrackName). Assigns the location as tPhotoLink.

9. A finished walk map may have been prepared at this stage, partially
assisted by the macro. Whether or not it exists, a location is assigned
as tFWlink.

10. A new entry is added to WalkIndex.xlsm. Details already provided.

Good luck!

Best wishes,

Terry, East Grinstead, UK


  #52   Report Post  
Old February 3rd 20, 06:40 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,797
Default Copy multi-line variable to first empty row

Hi Terry,

Am Mon, 03 Feb 2020 18:18:13 +0000 schrieb Terry Pinnell:

Could you take a look at this please to see if you can spot where the
unwanted spaces are coming from?
https://www.dropbox.com/s/rf6q4q7r82...aces.jpg?raw=1

Source.txt looks OK to me. Here's the file I used for that last run:
https://www.dropbox.com/s/mr78694twn...urce.txt?raw=1


there were 1 leading space and a trailing char(13)
Have a look:
https://www.dropbox.com/transfer/AAA...AyjTIJxkeCuAQE


Regards
Claus B.
--
Windows10
Office 2016
  #53   Report Post  
Old February 3rd 20, 09:28 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,797
Default Copy multi-line variable to first empty row

Hi Terry,

Am Mon, 03 Feb 2020 21:23:17 +0000 schrieb Terry Pinnell:

Here's a copy of my current sweetly running version. It's just yours
plus some minor alignment statements and a bit of code at the end to
turn 'Sunday' into 'Sun' etc (which saves me doing that in MX Pro).


if you want the short version of the weekday change the line that writes
the weekday in the cell to:
Case "A"
.Cells(LRow, varCols(i)) = Format(myDate, "ddd dd\/mm\/yy")


Regards
Claus B.
--
Windows10
Office 2016
  #54   Report Post  
Old February 4th 20, 03:41 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,797
Default Copy multi-line variable to first empty row

Hi Terry,

Am Mon, 03 Feb 2020 21:37:06 +0000 schrieb Terry Pinnell:

I was just coming back to say that my code was careless anyway: I would
get Tues, Wednes, Thurs, Satur!


the format ddd will bring you Mon, Tue, Wed and so on.
If you want the days like the examples above you must use string
functions and the the whole cell is a string and no more a real date.

In that "ddd dd\/mm\/yy" why is the '\' needed?


The backslash is a marker that the following character is shown in the
format.

Have a try. I did some improvments:

Sub CopyTextToWI_ClausPlus()
Dim strCols As String
Dim strValues As String
Dim strData As String
Dim strRep As String
Dim strTmp As String
Dim varTmp As Variant
Dim varCols As Variant
Dim varValues As Variant
Dim LRow As Long
Dim i As Integer
Dim myDate As Date
Dim objReadFile As Object
Dim FSO As Object

'Modify Path and file name for your device
Const FN = "C:\Users\terry\Dropbox\FinishedWalks\Source.t xt"
Set FSO = CreateObject("Scripting.Filesystemobject")
Set objReadFile = FSO.opentextfile(FN)
strData = objReadFile.readall
objReadFile.Close
strRep = Chr(13) & Chr(10) & Chr(13) & Chr(10)
strData = Replace(strData, strRep, Chr(13) & Chr(10))
MsgBox strData
strCols = "A,B,C,H,I,J,K,L,M,N,O,P,R,S,T"
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(strData, Chr(13) & Chr(10))
strTmp = Trim(Split(varTmp(4), " = ")(2))
myDate = DateSerial(CInt(Left(strTmp, 4)), CInt(Mid(strTmp, 5, 2)), _
CInt(Right(strTmp, 2)))

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(Trim(Split(varTmp(CInt(varValues(i))), " = ")(2)), "hh:mm")
Case "A"
.Cells(LRow, varCols(i)) = _
Format(myDate, "ddd dd\/mm\/yy")
Case Else
.Cells(LRow, varCols(i)) = _
Trim(Split(varTmp(CInt(varValues(i))), "=")(2))
End Select
Next
i = 17
.Hyperlinks.Add _
anchor:=.Cells(LRow, i + 5), _
Address:=Trim(Split(varTmp(i), "=")(2)), _
TextToDisplay:="FW"
.Hyperlinks.Add _
anchor:=.Cells(LRow, i + 4), _
Address:=Trim(Split(varTmp(i + 1), "=")(2)), _
TextToDisplay:="PS"
.Hyperlinks.Add _
anchor:=.Cells(LRow, i + 6), _
Address:=Trim(Split(varTmp(i + 2), "=")(2)), _
TextToDisplay:=Mid(Split(varTmp(i + 2), "=")(2), _
InStrRev(Split(varTmp(i + 2), "=")(2), "\") + 1)

.Range("A" & LRow & ":W" & LRow).HorizontalAlignment = xlCenter
.Range("A" & LRow).HorizontalAlignment = xlLeft
.Range("C" & LRow).HorizontalAlignment = xlLeft
.Range("R" & LRow & ":T" & LRow).HorizontalAlignment = xlLeft

End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016
  #55   Report Post  
Old February 4th 20, 05:01 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

Thanks Claus, that's much tidier!

Best wishes,

Terry, East Grinstead, UK

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

Claus Busch wrote:

Hi Terry,

Am Mon, 03 Feb 2020 21:37:06 +0000 schrieb Terry Pinnell:

I was just coming back to say that my code was careless anyway: I would
get Tues, Wednes, Thurs, Satur!


the format ddd will bring you Mon, Tue, Wed and so on.
If you want the days like the examples above you must use string
functions and the the whole cell is a string and no more a real date.

In that "ddd dd\/mm\/yy" why is the '\' needed?


The backslash is a marker that the following character is shown in the
format.

Have a try. I did some improvments:

Sub CopyTextToWI_ClausPlus()
Dim strCols As String
Dim strValues As String
Dim strData As String
Dim strRep As String
Dim strTmp As String
Dim varTmp As Variant
Dim varCols As Variant
Dim varValues As Variant
Dim LRow As Long
Dim i As Integer
Dim myDate As Date
Dim objReadFile As Object
Dim FSO As Object

'Modify Path and file name for your device
Const FN = "C:\Users\terry\Dropbox\FinishedWalks\Source.t xt"
Set FSO = CreateObject("Scripting.Filesystemobject")
Set objReadFile = FSO.opentextfile(FN)
strData = objReadFile.readall
objReadFile.Close
strRep = Chr(13) & Chr(10) & Chr(13) & Chr(10)
strData = Replace(strData, strRep, Chr(13) & Chr(10))
MsgBox strData
strCols = "A,B,C,H,I,J,K,L,M,N,O,P,R,S,T"
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(strData, Chr(13) & Chr(10))
strTmp = Trim(Split(varTmp(4), " = ")(2))
myDate = DateSerial(CInt(Left(strTmp, 4)), CInt(Mid(strTmp, 5, 2)), _
CInt(Right(strTmp, 2)))

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(Trim(Split(varTmp(CInt(varValues(i))), " = ")(2)), "hh:mm")
Case "A"
.Cells(LRow, varCols(i)) = _
Format(myDate, "ddd dd\/mm\/yy")
Case Else
.Cells(LRow, varCols(i)) = _
Trim(Split(varTmp(CInt(varValues(i))), "=")(2))
End Select
Next
i = 17
.Hyperlinks.Add _
anchor:=.Cells(LRow, i + 5), _
Address:=Trim(Split(varTmp(i), "=")(2)), _
TextToDisplay:="FW"
.Hyperlinks.Add _
anchor:=.Cells(LRow, i + 4), _
Address:=Trim(Split(varTmp(i + 1), "=")(2)), _
TextToDisplay:="PS"
.Hyperlinks.Add _
anchor:=.Cells(LRow, i + 6), _
Address:=Trim(Split(varTmp(i + 2), "=")(2)), _
TextToDisplay:=Mid(Split(varTmp(i + 2), "=")(2), _
InStrRev(Split(varTmp(i + 2), "=")(2), "\") + 1)

.Range("A" & LRow & ":W" & LRow).HorizontalAlignment = xlCenter
.Range("A" & LRow).HorizontalAlignment = xlLeft
.Range("C" & LRow).HorizontalAlignment = xlLeft
.Range("R" & LRow & ":T" & LRow).HorizontalAlignment = xlLeft

End With
End Sub


Regards
Claus B.



  #56   Report Post  
Old February 12th 20, 01:30 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

My macro is now working sweetly, thanks to Claus, using an intermediate
text file to transfer the variables from the external source, my macro
written with Macro Express Pro.

I've just come across the SaveSetting and GetSetting VBA functions to
save and recover data from the registry. MX Pro also has similar
commands. So would this be a viable and possibly superior alternative
method?

Terry, East Grinstead, UK


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 09:29 PM
Macro to validate is cell is not empty and copy the line Steff[_3_] Excel Programming 2 December 10th 07 09:16 PM
Copy row to next empty line [email protected] Excel Programming 9 November 9th 07 01: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 11:53 AM.

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