Home PageFacebookRSS News Feed
PocketGPS
Web
SatNav,GPS,Navigation
Pocket GPS World - SatNavs | GPS | Speed Cameras: Forums

Pocket GPS World :: View topic - Excel VBA: Please recommend learning resource
 Forum FAQForum FAQ   SearchSearch   UsergroupsUsergroups   ProfileProfile   Log in for private messagesLog in for private messages   Log inLog in 

Excel VBA: Please recommend learning resource
Goto page 1, 2  Next
 
Post new topic   Reply to topic    Pocket GPS World Forum Index -> Lost Satellite Fix - Off Topic Area
View previous topic :: View next topic  
Author Message
Privateer
Pocket GPS Moderator
Pocket GPS Moderator


Joined: 30/12/2002 17:36:20
Posts: 4912
Location: Oxfordshire, England, UK

PostPosted: Thu Aug 31, 2017 6:32 pm    Post subject: Excel VBA: Please recommend learning resource Reply with quote

Hi,

I keep my POI data in an Excel spreadsheet, I manually extract the data (delete and merge columns,etc) and convert to .csv file in order to convert to TomTom file.

Having just bought a Garmin PDA I'd like to keep to one master spreadsheet per POI category but expand the number of columns needed to take advantage of the extra information I can place in a Garmin POI.

I'm guessing that I could partially or fully automate the data extraction from the master POI list to a csv for both TomTom and Garmin and possibly other SatNav formats by Excel VBAs.

One other thing to consider is that I'm using Mac Excel instead of Windows Excel so it would be nice to write universal code for both Mac and Windows.

What resources would you recommend?

Are the Dummies guides any good?

Regards,
_________________
Robert.
iPhone 6s Plus, iOS 14.0.1: iOS CamerAlert v2.0.7
TomTom GO Mobile iOS 2.3.1; TomTom (UK & ROI and Europe) iOS apps v1.29
Garmin Camper 770 LMT-D
Back to top
View user's profile Send private message Send e-mail
M8TJT
The Other Tired Old Man
The Other Tired Old Man


Joined: Apr 04, 2006
Posts: 10118
Location: Bexhill, South Sussex, UK

PostPosted: Thu Aug 31, 2017 6:59 pm    Post subject: Reply with quote

I've found Dummys guides to be quite good, but have not specifically read one on VBA.
What you are trying to do sounds pretty straightforward for a VBA project, but the problem is that you need to be a black belt from the start as most of the stuff in books just tells you how to do it, not why or the best way etc.
If you want any help, or pointers etc. drop me an email Robert (I think you have my address from some tome ago). If not, PM.
Back to top
View user's profile Send private message
M8TJT
The Other Tired Old Man
The Other Tired Old Man


Joined: Apr 04, 2006
Posts: 10118
Location: Bexhill, South Sussex, UK

PostPosted: Thu Aug 31, 2017 6:59 pm    Post subject: Reply with quote

For tome, read time. WTF can't we edit posts here?
Back to top
View user's profile Send private message
pcaouolte
Frequent Visitor


Joined: Dec 27, 2006
Posts: 998
Location: South Lincs, UK.

PostPosted: Thu Aug 31, 2017 7:44 pm    Post subject: Reply with quote

Excel VBA for Dummies is excellent. The 4th edition is updated for office 2016.
_________________
Paul
Back to top
View user's profile Send private message
Privateer
Pocket GPS Moderator
Pocket GPS Moderator


Joined: 30/12/2002 17:36:20
Posts: 4912
Location: Oxfordshire, England, UK

PostPosted: Thu Aug 31, 2017 9:02 pm    Post subject: Reply with quote

Thanks M and Paul,

Whilst I'd like the challenge of doing the coding myself, I firmly believe in NOT reinventing the wheel so if I could take you up on your kind offer of help, M, then that'd be great.

Thanks for also saying what I want to do is achieveable via VBA.

Paul, Thanks for the advice on the Dummies VBA guide. I've found most Dummies books to be brilliant but have come across the occasional one that didn't work for me.

I just hope that Mac VBA is similar enough to Windows VBA. I'll try the kindle version first.

My only concern is the only coding I have ever been happy doing is sequential if then goto sort of thing. I've tried object coding and couldn't get my head around it and I think that's how VBA is.

Apologies for typos, I'm using my phone to post whilst at work! Embarassed

Regards,
_________________
Robert.
iPhone 6s Plus, iOS 14.0.1: iOS CamerAlert v2.0.7
TomTom GO Mobile iOS 2.3.1; TomTom (UK & ROI and Europe) iOS apps v1.29
Garmin Camper 770 LMT-D
Back to top
View user's profile Send private message Send e-mail
Kremmen
Pocket GPS Verifier
Pocket GPS Verifier


Joined: Mar 03, 2006
Posts: 7036
Location: Reading

PostPosted: Fri Sep 01, 2017 4:29 am    Post subject: Reply with quote

Quite often it's easier to 'record a macro', look at the code it's generated and fiddle with that, rather than try and write from scratch.
_________________
Satnav:
Garmin 2599 LMT-D (Indoor test rig)
DashCam:
Viofo A119 V3
Car Average MPG :
Back to top
View user's profile Send private message
M8TJT
The Other Tired Old Man
The Other Tired Old Man


Joined: Apr 04, 2006
Posts: 10118
Location: Bexhill, South Sussex, UK

PostPosted: Fri Sep 01, 2017 7:27 am    Post subject: Reply with quote

I do that all the time to get the basic operation, but it doesn't help a lot when you need to set up a repetitive loop and I feel that will be required a few times to do what Robert needs to do. Very Happy
I'm pretty good at using VBA to shuffle data around in a spreadsheet, but Kremmen is your man if the subsequent data needs to be turned into a GPX file for POILoader.
You have a PM Robert.
Back to top
View user's profile Send private message
Kremmen
Pocket GPS Verifier
Pocket GPS Verifier


Joined: Mar 03, 2006
Posts: 7036
Location: Reading

PostPosted: Fri Sep 01, 2017 8:23 am    Post subject: Reply with quote

The issue Robert may have is that Microsoft's DOM Document API that creates the XML/GPX file won't run on a Mac.

Still, we'll do what we can.
_________________
Satnav:
Garmin 2599 LMT-D (Indoor test rig)
DashCam:
Viofo A119 V3
Car Average MPG :
Back to top
View user's profile Send private message
Privateer
Pocket GPS Moderator
Pocket GPS Moderator


Joined: 30/12/2002 17:36:20
Posts: 4912
Location: Oxfordshire, England, UK

PostPosted: Fri Sep 01, 2017 11:33 am    Post subject: Reply with quote

Thanks M and K,

As K says, the first problem will be whether Mac Excel 2016 is capable of doing what I need to do. My backup plan will be to take my old laptop that was running slow, wipe its hard drive and instal a clean version of Windows 10 on it. However that is my last resort and will take a lot of time to do.

The first part of the problem will require mentoring from M as I'll want to work on moving columns of data, possibly concatenating data into new columns and creating new .csv files, which use the correct text encoding so that diacritical marks (i.e. accents on letters) work correctly. This will allow words like café to be in the POI name but you can search for them as "cafe".

Once I can create a .csv file then I'll want to explore all of the data fields that Garmins can use. Incorporate columns for that data in the master spreadsheet and then turn the data into a .gpx file. That's where K's expertise will be invaluable.

Is the above possible, yes I think so. Will it be difficult, yes absolutely with no doubt. Thumbs Up

Regards,
_________________
Robert.
iPhone 6s Plus, iOS 14.0.1: iOS CamerAlert v2.0.7
TomTom GO Mobile iOS 2.3.1; TomTom (UK & ROI and Europe) iOS apps v1.29
Garmin Camper 770 LMT-D
Back to top
View user's profile Send private message Send e-mail
Kremmen
Pocket GPS Verifier
Pocket GPS Verifier


Joined: Mar 03, 2006
Posts: 7036
Location: Reading

PostPosted: Fri Sep 01, 2017 12:33 pm    Post subject: Reply with quote

Once we know what data you are looking to see on the Garmin we can investigate.

Just one Q: Why do you need the diacritical letters ? For example Cafe would be the easy option because as we know you need to change the Windows font or it won't play ball.

If Mac Excel runs basic Macros then it's possible to manually, but long winded, to build a GPX file. Probably what they call a GPXX file may be an option.

I seem to recall that Garmin supply a GPX file with 3 of their head offices in there as POi's that contain other info like phone numbers.
_________________
Satnav:
Garmin 2599 LMT-D (Indoor test rig)
DashCam:
Viofo A119 V3
Car Average MPG :
Back to top
View user's profile Send private message
Privateer
Pocket GPS Moderator
Pocket GPS Moderator


Joined: 30/12/2002 17:36:20
Posts: 4912
Location: Oxfordshire, England, UK

PostPosted: Fri Sep 01, 2017 1:30 pm    Post subject: Reply with quote

Kremmen wrote:
Once we know what data you are looking to see on the Garmin we can investigate.

No problem, I can send you the .xlsx file.

Kremmen wrote:
Just one Q: Why do you need the diacritical letters ? For example Cafe would be the easy option because as we know you need to change the Windows font or it won't play ball.

Agree about the word café/cafe but the POI that I'm working on is for Jersey in the Channel Islands where there is a large French (Norman) influence with spelling. Therefore a lot of the places of interest have diacritical marks in their names.

In addition to getting things right, if I wrote the tools then I'd like to make them available to the PGPSW community, and some people would probably also need to use diacritical marks as well.

Kremmen wrote:
If Mac Excel runs basic Macros then it's possible to manually, but long winded, to build a GPX file. Probably what they call a GPXX file may be an option.

If we got to a point where a .csv file cold be made that could be used by POI Loader then that would probably be sufficient.

Kremmen wrote:
I seem to recall that Garmin supply a GPX file with 3 of their head offices in there as POi's that contain other info like phone numbers.

As a TomTom user, any POI is an "enhanced" POI! Twisted Evil As a newbie Garmin user I don't really know what data I can include within a Garmin POI so stuff like name; telephone; email; URL; opening times; etc would be useful if it's possible to do so.

Regards,
_________________
Robert.
iPhone 6s Plus, iOS 14.0.1: iOS CamerAlert v2.0.7
TomTom GO Mobile iOS 2.3.1; TomTom (UK & ROI and Europe) iOS apps v1.29
Garmin Camper 770 LMT-D
Back to top
View user's profile Send private message Send e-mail
Kremmen
Pocket GPS Verifier
Pocket GPS Verifier


Joined: Mar 03, 2006
Posts: 7036
Location: Reading

PostPosted: Fri Sep 01, 2017 3:06 pm    Post subject: Reply with quote

I'm sure we can crack this.
_________________
Satnav:
Garmin 2599 LMT-D (Indoor test rig)
DashCam:
Viofo A119 V3
Car Average MPG :
Back to top
View user's profile Send private message
Kremmen
Pocket GPS Verifier
Pocket GPS Verifier


Joined: Mar 03, 2006
Posts: 7036
Location: Reading

PostPosted: Fri Sep 01, 2017 3:09 pm    Post subject: Reply with quote

https://support.garmin.com/faqSearch/en-US/faq/content/l1V4EeOfq2AQXs2matNO89

Looks like just one CSV comment field though
_________________
Satnav:
Garmin 2599 LMT-D (Indoor test rig)
DashCam:
Viofo A119 V3
Car Average MPG :
Back to top
View user's profile Send private message
Kremmen
Pocket GPS Verifier
Pocket GPS Verifier


Joined: Mar 03, 2006
Posts: 7036
Location: Reading

PostPosted: Fri Sep 01, 2017 3:13 pm    Post subject: Reply with quote

However:

http://www.poi-factory.com/garmin-csv-file-format

(As M said, no edit facility here is a pain)
_________________
Satnav:
Garmin 2599 LMT-D (Indoor test rig)
DashCam:
Viofo A119 V3
Car Average MPG :
Back to top
View user's profile Send private message
pcaouolte
Frequent Visitor


Joined: Dec 27, 2006
Posts: 998
Location: South Lincs, UK.

PostPosted: Fri Sep 01, 2017 3:28 pm    Post subject: Reply with quote

Recent versions of Excel (on the PC, don't know about the MAC) can save xml files directly with file - save as. You need to set up a schema to do this but it might be a way to save a gpx file directly from excel. I'll have a play after work.
_________________
Paul
Back to top
View user's profile Send private message







Posted: Today    Post subject: Pocket GPS Advertising

Back to top
Display posts from previous:   
Post new topic   Reply to topic    Pocket GPS World Forum Index -> Lost Satellite Fix - Off Topic Area All times are GMT + 1 Hour
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Make a Donation



CamerAlert Database

Click here for the PocketGPSWorld.com Speed Camera Database

Download Speed Camera Database
22.034 (27 Mar 24)



WORLDWIDE SPEED CAMERA SPOTTERS WANTED!

Click here to submit camera positions to the PocketGPSWorld.com Speed Camera Database


12mth Subscriber memberships awarded every week for verified new camera reports!

Submit Speed Camera Locations Now


CamerAlert Apps



iOS QR Code






Android QR Code







© Terms & Privacy


GPS Shopping