Excel VBA: Please recommend learning resource

Privateer

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

M8TJT · Posted: Thu Aug 31, 2017 6:59 pm Post subject:

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.

M8TJT · Posted: Thu Aug 31, 2017 6:59 pm Post subject:

For tome, read time. WTF can't we edit posts here?

pcaouolte · Posted: Thu Aug 31, 2017 7:44 pm Post subject:

Excel VBA for Dummies is excellent. The 4th edition is updated for office 2016.
_________________
Paul

Privateer · Posted: Thu Aug 31, 2017 9:02 pm Post subject:

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

Kremmen · Posted: Fri Sep 01, 2017 4:29 am Post subject:

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.
_________________
DashCam:
Viofo A119 V3

M8TJT · Posted: Fri Sep 01, 2017 7:27 am Post subject:

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.

Kremmen · Posted: Fri Sep 01, 2017 8:23 am Post subject:

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.
_________________
DashCam:
Viofo A119 V3

Privateer · Posted: Fri Sep 01, 2017 11:33 am Post subject:

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

Kremmen · Posted: Fri Sep 01, 2017 12:33 pm Post subject:

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.
_________________
DashCam:
Viofo A119 V3

Privateer · Posted: Fri Sep 01, 2017 1:30 pm Post subject:

Kremmen · Posted: Fri Sep 01, 2017 3:06 pm Post subject:

I'm sure we can crack this.
_________________
DashCam:
Viofo A119 V3

Kremmen · Posted: Fri Sep 01, 2017 3:09 pm Post subject:

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

Looks like just one CSV comment field though
_________________
DashCam:
Viofo A119 V3

Kremmen · Posted: Fri Sep 01, 2017 3:13 pm Post subject:

However:

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

(As M said, no edit facility here is a pain)
_________________
DashCam:
Viofo A119 V3

pcaouolte · Posted: Fri Sep 01, 2017 3:28 pm Post subject:

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

	PocketGPS
	Web