Home |
Search |
Today's Posts |
#41
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, That is an interesting alternative to the external clipboard approach. (I used a similar method, but with tTtrackName.xlsm as my source.) But your macro would need the varying file name tTrackName.txt, which I assume that would have to be passed via the clipboard anyway? The actual text files I create look like this one for 20190714Fowey to Polperro-r925-m8.3.txt are more extensive. https://www.dropbox.com/s/n300ncvxmu...m8.3.txt?raw=1 My MX macro then parses and summarises the file as tAll_VBA, which is then used by your macro. (Version N - I'm losing track)! I'm very happy with the external clipboard approach, not only because it capitalises on the work I've already finished but also because it's almost finished, thanks to your patient efforts! Best wishes, Terry, East Grinstead, UK |
#42
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
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. GS wrote: 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!) I was going to mention that when I sent the outputs but will cover it here. At some time a year or three ago my GPS device (iPhone 6S+) started to generate 5x to 10x times the number of track points as it (and earlier devices) had done previously. That bumps up distances a bit, so I used GPSU's feature to reduce them. My macro offers the option at the outset and the filename (trackname.gpx) is altered to reflect the change. That prompts another point. I name my GPX files like this at the outset: 20190714Fowey to Polperro-u4828-m8.3.gpx yyyymmdd (I've not so far walked through a midnight and don't anticipate that!); description; -u4828 = an unedited track with that many trackpoints; -m8.3 = 8.3 miles (rounded) So 20190714Fowey to Polperro-r925-m7.9 is the more realistic reduced version. 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!) The only data not in (or obtainable from) the GPX that comes immediately to mind is tTrackDescr, a brief label entered by me when prompted at the start of the macro: 'Fowey to Polperro' or, in current test versions, 'Fowey to Polperro, SWCP 2019'. Remind me of any I've missed. 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.) Definitely want *all* the data in that one file please. That's causing me headaches because of the format changes due to my switching to an 'all text' output from my MX macro. (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!) The only weakness is that an important part of my macro is the generation of a smoothed elevation profile image from GPSU (as well as its altitude data), which is the basis of the Profile Summary (PS) the macro creates later. If I understand you correctly your VBA macro will neither deliver that nor the PS. Disappointing that "something way beyond MXP's step-up-from-novice approach!" falls a bit short here Garry! Otherwise it would prove a useful complement, and a fun project. But do give it up without any concern if it proves unexpectedly arduous/time consuming - unless you're enjoying the challenge - as frankly I'm pretty happy with what I have! Email to t.pinnellATbtinternetDOTcom Best wishes, Terry, East Grinstead, UK |
#43
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
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!) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#44
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
Hi Terry,
Am Sat, 01 Feb 2020 14:12:28 +0000 schrieb Terry Pinnell: That is an interesting alternative to the external clipboard approach. (I used a similar method, but with tTtrackName.xlsm as my source.) But your macro would need the varying file name tTrackName.txt, which I assume that would have to be passed via the clipboard anyway? The actual text files I create look like this one for 20190714Fowey to Polperro-r925-m8.3.txt are more extensive. https://www.dropbox.com/s/n300ncvxmu...m8.3.txt?raw=1 My MX macro then parses and summarises the file as tAll_VBA, which is then used by your macro. (Version N - I'm losing track)! I'm very happy with the external clipboard approach, not only because it capitalises on the work I've already finished but also because it's almost finished, thanks to your patient efforts! have another look in DropBox Regards Claus B. -- Windows10 Office 2016 |
#45
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
Claus Busch wrote:
Hi Terry, Am Sat, 01 Feb 2020 14:12:28 +0000 schrieb Terry Pinnell: That is an interesting alternative to the external clipboard approach. (I used a similar method, but with tTtrackName.xlsm as my source.) But your macro would need the varying file name tTrackName.txt, which I assume that would have to be passed via the clipboard anyway? The actual text files I create look like this one for 20190714Fowey to Polperro-r925-m8.3.txt are more extensive. https://www.dropbox.com/s/n300ncvxmu...m8.3.txt?raw=1 My MX macro then parses and summarises the file as tAll_VBA, which is then used by your macro. (Version N - I'm losing track)! I'm very happy with the external clipboard approach, not only because it capitalises on the work I've already finished but also because it's almost finished, thanks to your patient efforts! have another look in DropBox Regards Claus B. Sorry Claus, I don't follow? Look at what? You've not given a Dropbox link. Terry, East Grinstead, UK |
#46
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
Hi Terry,
Am Sun, 02 Feb 2020 18:42:52 +0000 schrieb Terry Pinnell: Sorry Claus, I don't follow? Look at what? You've not given a Dropbox link. https://www.dropbox.com/transfer/AAA...Xj13dfF Ev0Aw Regards Claus B. -- Windows10 Office 2016 |
#47
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
Claus Busch wrote:
Hi Terry, Am Sun, 02 Feb 2020 18:42:52 +0000 schrieb Terry Pinnell: Sorry Claus, I don't follow? Look at what? You've not given a Dropbox link. https://www.dropbox.com/transfer/AAA...Xj13dfF Ev0Aw Regards Claus B. YES!! Thank you. This version also runs on the full worksheet, so I'm a happy bunny (or whatever the german equivalent is). (I never did solve that with the clipboard versions, which always ran OK with the test worksheets but never with the main one.) And late last night I realised I'd forgotten an easy solution to the problem I raised about the varying source text filename. In my MX macro I'll simply save a TEMPORARY file Source.txt, as well as tTrackName.txt. Which is basically what I did before when I was saving those complex 'tracksheets'. So that brings us up from 99% to 99.5% finished. But I'd still like to get U,V and W to hyperlinks please Claus - when you've had a decent break from my stuff ;-) Best wishes, Terry, East Grinstead, UK |
#48
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
Claus Busch wrote:
Hi Terry, Am Sun, 02 Feb 2020 19:32:34 +0000 schrieb Terry Pinnell: And late last night I realised I'd forgotten an easy solution to the problem I raised about the varying source text filename. In my MX macro I'll simply save a TEMPORARY file Source.txt, as well as tTrackName.txt. Which is basically what I did before when I was saving those complex 'tracksheets'. So that brings us up from 99% to 99.5% finished. But I'd still like to get U,V and W to hyperlinks please Claus - when you've had a decent break from my stuff ;-) have another look in DropBox. Now you get real hyperlinks. Regards Claus B. Thanks Claus but no new link? Best wishes, Terry, East Grinstead, UK |
#49
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
Hi Terry,
Am Sun, 02 Feb 2020 21:10:03 +0000 schrieb Terry Pinnell: Thanks Claus but no new link? https://www.dropbox.com/transfer/AAA..._NUv6WQ Vki98 Regards Claus B. -- Windows10 Office 2016 |
#50
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy multi-line variable to first empty row
Claus Busch wrote:
Hi Terry, Am Sun, 02 Feb 2020 21:10:03 +0000 schrieb Terry Pinnell: Thanks Claus but no new link? https://www.dropbox.com/transfer/AAA..._NUv6WQ Vki98 Regards Claus B. Hi Claus, Terrific, thanks so much! Great work on this, Claus, warmly appreciated! Best wishes, Terry, East Grinstead, UK |
#51
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
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) |