Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
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 |
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
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 |
#23
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
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 |
#24
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
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 |
#25
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
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 |
#26
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
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 |
#27
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
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 |
#28
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
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 |
#29
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
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 |
#30
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
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 |
#31
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
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 |
#32
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
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 |
#33
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
#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 |
#34
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
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 |
#35
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
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 |
#36
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
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 |
#37
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
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 |
#38
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
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. |
#39
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
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 |
#40
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
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 |
Reply |
|
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable series multi line chart? | Charts and Charting in Excel | |||
Macro to validate is cell is not empty and copy the line | Excel Programming | |||
Copy row to next empty line | Excel Programming | |||
Copy and Paste in the first empty available line. | Excel Programming | |||
copy/paste excel to texteditor -> multi-line text cells gain unwanted double-quotes | Excel Discussion (Misc queries) |