Posts by flee01

  • MapPoint VBA Support

    Re: Mappoint 2006 Vba Support


    Now i see why no one is replying. My question not only does not sound like a question but does not have a question mark. I am going to edit my first entry above to make it more clear.

  • Format Date & Time Cell To Hide Time If 0:00

    Re: Format Date/time - Hide Time If Time Is 12:00am


    One thing to consider. If it is being entered by a formula, the best way out is to filter the formula's results by putting in an if statement like the below.


    Code
    =if([your entire cell contents now without the preceding equal sign]="","",[your entire cell contents now without the preceding equal sign])


    This causes the formula output type to be string data instead of numeric. The formula cannot assume it's result should be zero.


    HTH's

  • MapPoint VBA Support

    Re: Mappoint 2006 Vba Support


    The above code would be of course entered into a module in Excel. The cool idea is being able to get driving distance by simply entering the addresses in to a spreadsheet. Zip code to zip code distances can be off by 20 miles or more in any situation, and it some cases 100's of miles. This mainly happens when mapping 2 between 2 zip codes where one is on a peninsula, and one is not.

  • MapPoint VBA Support

    Re: Mappoint 2006 Vba Support


    If anyone out there has Office 2003 and Mappoint 2006, maybe you could try the below code.


    Automating MapPoint
    In Automation, User Defined Functions, Excel Experts E-Letter by Dick Kusleika
    EEE#21: Excel User Defined Functions That Incorporate MapPoint Functionality
    By David Hager
    ---Note: Excel 2003 and MapPoint 2004 were used in the development of this technique and its use with other versions has not been tested.---
    The integration that Microsoft provides between Excel and MapPoint for importing and exporting data provides a powerful method for visual data analysis. As it turns out, there is another way to obtain information from MapPoint to Excel that is not as well-known.
    User-defined functions (UDF) are used in Excel worksheets is the same way that native Excel functions are used. The VBA code for an Excel UDF is written in a standard module in the Excel VBE and called by entering the function in a formula in a worksheet cell. In order to incorporate MapPoint automation in an Excel UDF, open the Visual Basic Environment (VBE) by using the Alt-F11 keys. Then, under Tools|References check the Microsoft MapPoint 11.0 Object Library (North America).
    A simple example of an Excel UDF that incorporates MapPoint functionality is shown below.

    Code
    Function StraightLineDist(strPoint1, strPoint2) 
  Dim objApp As New MapPoint.Application
  Set objMap = objApp.ActiveMap
 
  Set objLocate1 = objMap.FindResults(strPoint1).Item(1)
  Set objLocate2 = objMap.FindResults(strPoint2).Item(1)
  StraightLineDist = Application.Round(CStr(objLocate1.DistanceTo(objLocate2)), 5)
 
End Function


    It is quite rare to find an example where the VBA code for an Excel UDF successfully instantiates an application. In this case, an instance of MapPoint is created with the statement
    Dim objApp As New MapPoint.Application
    that allows the Excel UDF access to a valuable source of location information.
    The FindResults method works in the same way that the Place/Data tab in the dialog box opened from Edit|Find of the MapPoint main menu does. The Item(1) property returns the best choice based on the available information. As an example, type the following in an Excel worksheet.
    A1: Beaumont, Tx
    A2: Houston, Tx
    A3: =StraightLineDist(A1, A2)
    The result returned in cell A3 is 77.11092, which is the number of miles as the crow flies between Beaumont and Houston Texas as calculated by the DistanceTo method.
    In order to improve performance of the StraightLineDist function, the statement

    Code
    Application.Volatile False


    can be used in the code to limit function calls to only those times when the input data is changed. It might be also advantageous to set the MapPoint objects to nothing in order to remove them from memory.
    The next example returns the distance for a multiple point route and contains an element that is perhaps unique among UDF?s.

    The iMPType variable is the type of route to be returned:
    0 = Travel using the quickest route
    1 = Travel using the shortest route
    2 = Travel on preferred roads as set in the Preferred Roads dialog box
    The ParamArray WPoints() statement allows for an arbitrary number of waypoint function arguments along the route.
    By entering the following formula, the distance for the quickest round trip route from Houston is returned (882.89018 miles).
    Cell formula:[

    Code
    =MPRouteDist(0,A1, A2,A3,A4,A1)

    where:
    A1: Houston, Tx
    A2: Dallas, Tx
    A3: San Antonio, Tx
    A4: Corpus Christi, Tx
    This function actually makes changes on the active MapPoint map (not visible) and a prompt appears asking to save those changes unless the statement

    Code
    objMap.Saved = True


    is included in the code. This writer does not know of another example where an Excel UDF modifies another application. This unusual behavior serendipitously provides a simple way to generate route maps from Excel. If the following formula is entered without the objMap.Saved = True statement in the code, the route map from Houston to Dallas is generated and can be saved. Thus, generation of route maps from MapPoint is now as easy as entering an address in a worksheet cell.
    Cell formula:

    Code
    =MPRouteDist(0,A1, A2)


    In summary, Excel UDFs provide easy access to the rich store of information in MapPoint and these examples just scratch the surface of what is possible. For example, I have made Excel UDFs that draw geometric shapes around MapPoint locations. This and other challenges are left to the reader.

  • If Statement Causing Loop To Fail

    Re: If Statement Causing Loop To Fail


    Add two "End if" statements as shown below.


    Code
    Sheets("Practical & Theory Marks").Range(Cells(Col, Row), Cells(Col, Row + 5)).Copy
            End If
        End If


    I edited your code to make it easier to read. It made it easy to find the error.



    HTHs...

  • Sum Criteria With Non-blank Cells

    Re: Sum Criteria With Nonblank Cells


    You need to change "truetrue" to "truetruetrue". You have to do that each time you add a new condition, unless your new condition needed to be false of course. Then it would be "truefalsetrue".

  • Creating Range Of Bar Codes

    Re: Creating Range Of Bar Codes


    I have found an error with my approach. I am uploading a spreadsheet that shows the corrected info. Keep in mind that I did not do anthing with the barcode or the my company name lines as this does not really involve them.

  • Creating Range Of Bar Codes

    Re: Creating Range Of Bar Codes


    Ok. That was a mental mistake on my part.


    That being the case, each cell that is to contain one of the barcodes except the first one needs to be as follows:
    A7:

    Code
    ="000000"&A4+1


    A10:

    Code
    ="000000"&A7+1


    You would repeat this up to 500 times or as much as you want.

  • Creating Range Of Bar Codes

    Re: Creating Range Of Bar Codes


    Ok. How many barcodes to generate at one time? I ask because the solution is different if you want to do a million at a time vs if you want to do a 1000 at a time.

  • Convert A Column Into Comma Delimited List

    Re: Convert A Column Into Comma Delimited List


    Create your own function to loop thru the values and concatenate them. If you know how to insert a module, put this in one.


    Code
    Function csvRange(myRange As Range)
    Dim csvRangeOutput
    For Each entry In myRange
        csvRangeOutput = csvRangeOutput & entry.Value & ", "
    Next
    csvRange = csvRangeOutput
End Function


    Put this in the cell to grab the first 16 cells of column I:


    Code
    =csvRange(I1:I16)