MapForums

cadehuff · #1 (**permalink**) 07-29-2008

Hello folks!

Well, I am a beginner using VBA to get data from Excel to MapPoint. Now, I have a Run-Time error popping up when I insert a line to concantenate two cells into a variable and that is used as the name of a pushpin in MapPoint.

Code:

 
 
Dim oApp As MapPoint.Application
Dim objMap As MapPoint.Map
 
Private Sub CreateMap_Click()
'set the variable for the location
Dim oLocation As MapPoint.Location
'set the variable for the PushPin
Dim oPushPin As MapPoint.Pushpin
 
'Open a new instance of MapPoint
Set oApp = CreateObject("MapPoint.Application.NA")
 
'Set oApp as the new map
Set objMap = oApp.NewMap
 
'Set the beginning of the current row
Dim nCurrentRow As Integer
  nCurrentRow = 10
 
'Get the information for this pin! Getting the name first
PinNam = Cells(nCurrentRow, 7) & Cells(nCurrentRow, 8)
 
Do While (PinNam <> 0) 'stop when it reaches a blank line.
 
'read line from spreadsheet and assign to variables
PinLat = Cells(nCurrentRow, 3)
PinLon = Cells(nCurrentRow, 4)
PinRad = Cells(nCurrentRow, 5)
 
'Find the pin location and place a pin then draw a circle
'Set location
Set oLocation = objMap.GetLocation(PinLat, PinLon)
 
'add a pushpin
Set oPushPin = objMap.AddPushpin(AtLocation:=oLocation, Name:=PinNam)
'add a circle with the 35 mile radius
objMap.Shapes.AddShape geoShapeRadius, oLocation, PinRad, PinRad
'Increment the current row
nCurrentRow = nCurrentRow + 1
'Set the pointer to the pin name to the new current row
PinNam = Cells(nCurrentRow, 7) & Cells(nCurrentRow, 8)
 
Loop
 
objMap.Saved = True
'Make sure that the instance of MapPoint is shown, not run in the background
  oApp.Visible = True
Exit Sub
End Sub

The problem started when I started using PinNam as a variable that contained two concantenated cells. If I set PinName to a single cell, everything works just fine. When I change it, I get this:

Run-time error '-2147024809(80070057)': The parameter is incorrect.

The line that is highlighted is:

objMap.Shapes.AddShape geoShapeRadius, oLocation, PinRad, PinRad

If anyone can guide me in correcting the problem, I would appreciate it.

Cade

**davidb** · #2 (**permalink**) 07-29-2008

Hello Cade

I think your do loop may be dodgy.

Try replacing

Do While (PinNam <> 0) 'stop when it reaches a blank line.

with

Do While (PinNam <> "") 'stop when it reaches a blank line.

Rgds

cadehuff · #3 (**permalink**) 07-29-2008

Thanks! My macro no longer displays the frustrating error!

But I have another to add to the mix. Once I made the change, I am now getting a "runtime error '4080': The record name is invalid: it either contains invalid characters, exceeds the character limit, or is an empty string." error.

I have atempted to set the strings to blanks in attempts to make sure that there is no garbage associated to the variable. It was a no go.

I do not know know how the record name is invalid. I have done some searching and I have not found any solutions to this problem yet.

Also, any good resources out there on using VBA with excel and mappoint together? (besides you folks

) I really am looking for a decent book that I can read to get more out of using VBA with excel and mappoint.

And, again, thanks davidb. I am one step closer to getting this working with more than one cell.

Cade

cadehuff · #4 (**permalink**) 07-29-2008

Ok folks! I have found the problem...

When I have this:

PinNam = Cells(nCurrentRow, 7)

It works.

when I have PinNam = Cells (nCurrentRow, 7) & Cells(nCurrentRow, 8 ) & Cells(nCurrentRow, 9)

It works.

when I add a '& " " &' between the cells like so:

PinNam = Cells (nCurrentRow, 7) & " " & Cells(nCurrentRow, 8 ) & " " & Cells(nCurrentRow, 9)

I get a run-time error '4080': The record name is invalid: it either contains invalid characters, exceeds the character limit, or is an empty string.

I have tried having a seperate string that is assigned " " and concantenating that in...no dice.

I am just concantenating cells with spaces in between the data.

I am scratching my head as to why this is a problem. PinNam is a string so it SHOULD work...right? If anyone has a clue as to why this is doing what it is, let me know. I will bow and scrape before your greatness!

**davidb** · #5 (**permalink**) 07-30-2008

Hello again Cade

You want to stop the do loop when it finds an empty record, but it’s finding the 2 spaces you’ve included in your concatenation and consequently looping through again and generating the error. So for example if you started your do loop with

Blankspaces = " " & " "
Do While (PinNam <> Blankspaces) 'stop when it reaches a blank line.

it would work but it’s getting clunky.

I guess my suggestion would be to test for an empty record on a different cell. For example if you had the record number in the first column then your do loop could be

Code:

 nCurrentRow = 10
 nRecord = Cells(nCurrentRow, 1)
 Do While (nRecord <> "") 'stop when it reaches a blank line.
   PinNam = Cells(nCurrentRow, 7) & " " & Cells(nCurrentRow, 8) & " " & Cells(nCurrentRow, 9)
   'read line from spreadsheet and assign to variables
   PinLat = Cells(nCurrentRow, 3)
   PinLon = Cells(nCurrentRow, 4)
   PinRad = Cells(nCurrentRow, 5)
 
   'Find the pin location and place a pin then draw a circle
   'Set location
   Set oLocation = objMap.GetLocation(PinLat, PinLon)
 
   'add a pushpin
   Set oPushPin = objMap.AddPushpin(AtLocation:=oLocation, Name:=PinNam)
   'add a circle with the 35 mile radius
   objMap.Shapes.AddShape geoShapeRadius, oLocation, PinRad, PinRad
   'Increment the current row
   nCurrentRow = nCurrentRow + 1
   nRecord = Cells(nCurrentRow, 1)
Loop

Rgds

cadehuff · #6 (**permalink**) 07-30-2008

Oh great and powerful Oz! I bow and scrape before your greatness!

*bows and scrapes*
*bows and scrapes*
*bows and scrapes*

I did happen to have a column of cells that were numbers. I set this instead of PinNam as the check and it works like a charm. I was able to then move the first PinNam into the loop and get rid of the second one.

Thank you very much for helping me get this pinned down (no pun intended). My pushpins are working without a hitch...so far!

And if you are keeping a tab on this thread, do you have a good reference book I can read up on?

Thanks again davidb!

And for those that are following this...here is my new code!

Code:

'set oApp so we can open MapPoint
Dim oApp As MapPoint.Application
Dim objMap As MapPoint.Map
 
Private Sub CreateMap_Click()
'set the variable for the location
Dim oLocation As MapPoint.Location
'Set the variable for the PushPin
Dim oPushPin As MapPoint.Pushpin
Set oPushPin = Nothing
 
'Open a new instance of MapPoint
Set oApp = CreateObject("MapPoint.Application.NA")
 
'Set oApp as the new map
Set objMap = oApp.NewMap
 
'Set the beginning of the current row
Dim nCurrentRow As Integer
nCurrentRow = 10
PinNum = Cells(nCurrentRow, 1)
Do While (PinNum <> 0) 'stop when it reaches a blank line.
'read line from spreadsheet and assign to variables
PinNam = Cells(nCurrentRow, 7) & "  " & Cells(nCurrentRow, 8) & "  " & Cells(nCurrentRow, 9)
PinLat = Cells(nCurrentRow, 3)
PinLon = Cells(nCurrentRow, 4)
PinRad = Cells(nCurrentRow, 5)
'Find the pin location and place a pin then draw a circle
'Set location
Set oLocation = objMap.GetLocation(PinLat, PinLon)
'add a pushpin
Set oPushPin = objMap.AddPushpin(AtLocation:=oLocation, Name:=PinNam)
'add a circle with the 35 mile radius
objMap.Shapes.AddShape geoShapeRadius, oLocation, PinRad, PinRad
'Increment the current row
nCurrentRow = nCurrentRow + 1
Set oPushPin = Nothing
PinNum = Cells(nCurrentRow, 1)
 
Loop
 
objMap.Saved = True
oApp.Visible = True
Exit Sub
End Sub

I hope that this comes in handy for other folks just starting and needs to do the same thing as I am doing.

Night everyone!

LinkBacks (?) LinkBack to this Thread: http://www.mapforums.com/run-time-error-help-8133.html
Posted By	For	Type	Date
A More Compact Method for Obtaining Lat/Long - MapPoint Articles - MP2K Magazine	This thread	Refback	07-30-2008 02:58 AM
Reverse Geocoding with MapPoint 2002 - MapPoint Articles - MP2K Magazine	This thread	Refback	07-30-2008 02:47 AM
Reverse Geocoding, Another Method - MapPoint Articles - MP2K Magazine	This thread	Refback	07-30-2008 02:46 AM
Reverse Geocoding, Pt. III - MapPoint Articles - MP2K Magazine	This thread	Refback	07-30-2008 02:44 AM

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Run-time error 2147467259 80004005 automation error	ThomasB	MapPoint 2006/2009 Discussion	2	02-27-2007 12:41 PM
Run time Error 16400	bdg	MapPoint 2006/2009 Discussion	4	02-15-2007 11:19 AM
run-time error 16391 with MP control in vb6	cwrude	MapPoint 2006/2009 Discussion	2	08-28-2006 01:14 PM
Run-time Error 16398 using Mappoint 2004 ocx	blueisland	MapPoint 2006/2009 Discussion	0	12-13-2004 01:59 AM
Run-time error 'Method 'UpdateLink' object 'DataSet' failed	Anonymous	MapPoint 2006/2009 Discussion	0	09-18-2003 04:53 PM

MapForums

Run-Time Error help

Run-Time Error help

LinkBacks (?)
LinkBack to this Thread: http://www.mapforums.com/run-time-error-help-8133.html

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Similar Threads

MapForums

Run-Time Error help

Run-Time Error help

LinkBacks (?) LinkBack to this Thread: http://www.mapforums.com/run-time-error-help-8133.html

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Similar Threads

LinkBacks (?)
LinkBack to this Thread: http://www.mapforums.com/run-time-error-help-8133.html