Welcome to MapForums!

Register, sign in, or use Facebook Connect above to join in and participate in the forum.

When you are signed in, this message, the ads in this row, and the red-underlined link ads all go away.

Subscribe to receive our newsletter.
Subscribe Unsubscribe
Results 1 to 8 of 8

Gotta be a faster way to export to excel?

This is a discussion on Gotta be a faster way to export to excel? within the MapPoint Desktop Discussion forums, part of the Map Forums category; Hello everybody again. I've been playing around with the VBA macro to export to excel, which it is doing now, ...

  1. #1
    alexramo is offline Member Green Belt
    Join Date
    Sep 2008
    Posts
    63

    Gotta be a faster way to export to excel?

    Hello everybody again.

    I've been playing around with the VBA macro to export to excel, which it is doing now, however it is very slow. It is going through each record in a shape one at a time, through each field one at a time and copying and pasting into excel, one at a time.

    What I am wondering is, what is a faster way of doing this? Mappoint has the built in feature of exporting to excel, and it is very fast.

    Is there a better way to extract all the records in a shape as a range or an array and export them all at once?

    here is a bit of the code that I am currently using:

    Dim objfield As MapPoint.Field
    Dim strfieldnames As String


    ' Worksheets("sheet3").Select
    Range("a35").Select
    ActiveCell.Offset(1, 0).Select
    Dim objshapers As MapPoint.Recordset
    Dim objrs As MapPoint.Recordset
    Dim objrecord As MapPoint.DataRange
    Dim lngcountrecrds As Integer
    Set objrs = objDS.QueryAllRecords

    objShape.Select
    Dim x As Integer
    x = 1

    Dim vals As String

    objRecordset.MoveFirst
    Do Until objRecordset.EOF
    For Each objfield In objRecordset.Fields
    'On Error Resume Next

    If IsNull(objfield.Value) Then
    vals = " "
    Else
    vals = CStr(objfield.Value)

    End If

    Dim lastrow As Integer


    ActiveCell.Select
    ActiveCell.Value = vals
    ActiveCell.Offset(0, 1).Select
    If x = objMap.DataSets(1).Fields.Count Then
    Range("A35").Select
    'lastrow is the last row with data

    lastrow = ActiveCell.SpecialCells(xlLastCell).Row
    Cells(lastrow, 1).Select

    ActiveCell.Offset(1, 0).Select
    x = 0

    End If

    x = x + 1
    Next objfield
    objRecordset.MoveNext
    Loop

    Looking for ay input or help. Thank you very much.

    -Alex

  2. #2
    alexramo is offline Member Green Belt
    Join Date
    Sep 2008
    Posts
    63

    Re: Gotta be a faster way to export to excel?

    Anybody want to take a stab at it, anybody???

  3. #3
    alexramo is offline Member Green Belt
    Join Date
    Sep 2008
    Posts
    63

    Re: Gotta be a faster way to export to excel?

    Maybe the first post is confusing, here is some truncated code to show where I'm getting stuck with another attempt:

    Dim objApp As MapPoint.Application
    Dim objMap As MapPoint.Map
    Dim objShape As MapPoint.Shape
    Dim objDataSet As MapPoint.DataSet
    Dim objDataSets As MapPoint.DataSets
    Dim objRecords As MapPoint.Recordset
    Dim lngCount As Integer, NDataSets As Integer, NCount As Integer, NRow As Integer
    Dim strDataSet() As String, Total As Integer
    Dim objfield As MapPoint.Field
    Dim objRecordset As MapPoint.Recordset
    Dim lngShapeCount As Long
    Dim countofshapes As Integer
    Dim MPrecordSet As ADODB.Recordset
    Dim SIAccessConnection As ADODB.Connection

    Set objMap = GetObject(, "MapPoint.Application.NA.16").ActiveMap
    Set objDataSet = objMap.DataSets(1)
    Set SIMappointConnection = New ADODB.Connection
    Set SIMappointConnection = CreateObject("ADODB.Connection")
    countofshapes = objMap.Shapes.Count


    For lngShapeCount = 1 To countofshapes
    Set objShape = objMap.Shapes(lngShapeCount)
    objShape.Select
    Set objRecords = objDataSet.QueryShape(objShape)
    Set objRecordset = objDataSet.QueryShape(objShape)
    ' Set MPrecordSet = objRecordset
    ActiveSheet.Range("a16").Offset(1, 0).CopyFromRecordset objRecords
    error message:
    class does not support automation or does not support expected interface


    also tried:
    objRecordset.MoveFirst
    Do Until objRecordset.EOF
    For Each objfield In objRecordset.Fields
    'add to array/recordset/??????
    Next objfield
    objRecordset.MoveNext
    Loop


    Set MPrecordSet = objRecordset
    error message:
    Type Mismatch

  4. #4
    davidb is offline Senior Member Blue Belt
    Join Date
    Oct 2003
    Location
    London
    Posts
    290

    Re: Gotta be a faster way to export to excel?

    Instead of cutting and pasting each field couldn't you use something like

    For i = 1 To objFields.Count
    Sheets("Exported Data").Cells(kCount + 1, i).Value = objFields(i).Value
    Next i

    where kCount is your record count. That would be much quicker. I'm probably missing something but you asked for stabs!!
    David
    UK mapping and map analysis services at www.broomanalysis.plus.com

  5. #5
    alexramo is offline Member Green Belt
    Join Date
    Sep 2008
    Posts
    63

    Re: Gotta be a faster way to export to excel?

    Thank you David.
    I guess I did say it wrong. I'm not copying and pasting, I'm doing something similar to your code:
    Dim vals As String
    objRecordset.MoveFirst
    Do Until objRecordset.EOF
    For Each objfield In objRecordset.Fields

    If IsNull(objfield.Value) Then
    vals = " "
    Else
    vals = CStr(objfield.Value)

    End If

    Dim lastrow As Integer


    ActiveCell.Select
    ActiveCell.Value = vals
    ActiveCell.Offset(0, 1).Select
    If x = objMap.DataSets(1).Fields.Count Then
    Range("A35").Select
    'lastrow is the last row with data

    lastrow = ActiveCell.SpecialCells(xlLastCell).Row
    Cells(lastrow, 1).Select

    ActiveCell.Offset(1, 0).Select
    x = 0
    'Next x
    End If

    x = x + 1
    Next objfield
    objRecordset.MoveNext



    I think this is similar to what you were suggesting, right?

    But it still brings over one records at a time, doesn't it?

    -Thank you again.
    -alex

  6. #6
    Eric Frost's Avatar
    Eric Frost is offline Administrator Black Belt
    Join Date
    Jul 1992
    Posts
    5,094
    Blog Entries
    4

    Re: Gotta be a faster way to export to excel?

    Code:
    Range("A35").Select
    'lastrow is the last row with data
    
    lastrow = ActiveCell.SpecialCells(xlLastCell).Row
    Cells(lastrow, 1).Select
    
    ActiveCell.Offset(1, 0).Select
    x = 0


    This may not be the issue at all, but I wonder if you rewrote it so you don't have the .Select, use .Offset, or use .SpecialCells if it might go a little faster..

    You should be able to use a counter easily to keep track of the row.

    linerow = linerow + 1
    ~ Order MapPoint MapPoint 2013 Here and Get Free Access to the MapForums Downloads ~
    ~~
    ~ Upgrade to Get Access to the MapForums Downloads ~

  7. #7
    alexramo is offline Member Green Belt
    Join Date
    Sep 2008
    Posts
    63

    Re: Gotta be a faster way to export to excel?

    Yes,
    I am going to try yours and Davids suggestion now. I think I didn't use a counter, because I was having a hard time getting my count of records, and cycling through it.

    I am also having a hard time with Davids suggestion of
    objfields.count
    I am getting an error message, what kind of object or property do i need to set to get this count?

    And just to make sure I understand, do I need to cycle through the fields and then at the end of my fields count, go to the next record and cycle through the fields again?

    Isnt this what I am doing now, but with the activecell.offset command and the lastcell.select?

    Could that be slowing me down that much?

    Is there still no other way to copy a whole range or record set?

  8. #8
    EWong is offline Junior Member White Belt
    Join Date
    Dec 2009
    Posts
    4

    Re: Gotta be a faster way to export to excel?

    back from the dead threads...


    Off the cuff
    -- dont use "active cells"
    -- dont use cell "off sets"
    use a specified cell location that you control via counters that you increment



    -- hide the Excel spread sheet (run the VBA from say Word, run mappoint and excel as non visible)

    I'm doing something simliar right now so I borrowed the code to read/learn

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Trying to export to excel via VBA
    By ae3145 in forum MapPoint Desktop Discussion
    Replies: 1
    Last Post: 10-16-2008, 10:43 PM
  2. Export to Excel
    By lovetann in forum MapPoint Desktop Discussion
    Replies: 4
    Last Post: 02-19-2008, 04:02 PM
  3. Export to excel
    By Dazzer in forum MapPoint Desktop Discussion
    Replies: 0
    Last Post: 10-23-2003, 05:29 AM
  4. export to excel
    By gsully in forum MapPoint Desktop Discussion
    Replies: 0
    Last Post: 05-08-2003, 10:39 AM
  5. Update Mappoint by GPS faster then 15 seconds, how?
    By Anonymous in forum MapPoint Desktop Discussion
    Replies: 2
    Last Post: 01-11-2003, 10:15 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127