Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Excel stops responding when executing a macro


  • Please log in to reply
8 replies to this topic

#1 petocities

petocities

  • Members
  • 102 posts
  • OFFLINE
  •  
  • Location:Santiago Chile
  • Local time:07:47 PM

Posted 07 July 2011 - 02:42 PM

Hello everyone.
I'm trying to run a macro which, simply put, asks a web address for some coordinates. I recorded de basic steps, due to my lack of knowledge in VBA: obtain data from a web query, select the data I want (coordinates), copy & paste the values from the query, modify the query, redo. After that i tried to adapt it a little bit, the run it.
The code is here:
Sub coordenadas()
'
' coordenadas Macro
'

'
    Application.ScreenUpdating = False

'   Create query'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://maps.google.com/maps/geo?q=" & ActiveSheet.Range("H2") & ", Santiago, RM&output=csv&sensor=false&gl=cl&key=ABQIAAAALsSOLT7cbh7X-JlSENAAQRQxTqHrN2AcRFuPjSEXWFFzkWy7kxRE5W3I6Fj83RAZ-KMoNNW2fBM03A" _
        , Destination:=Range("$I$1"))
        .Name = _
        "geo?q=" & ActiveSheet.Range("H2") & ", Santiago, RM&output=csv&sensor=false&gl=cl&key=ABQIAAAALsSOLT7cbh7X-JlSENAAQRQxTqHrN2AcRFuPjSEXWFFzkWy7kxRE5W3I6Fj83RAZ-KMoNNW2fBM03A_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

'   Copy&Paste first result '
    Range("$I$1").Select
    Selection.Copy
    Range("F2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
'   Modify query and repeat  '  
    Dim direcciones As Range
    Dim indice
    Set direcciones = Worksheets("Limpio").Range("H3:H43")
    indice = 3
    
    For Each direccion In direcciones
        If direccion.Value = "" Then Exit For
        Range("$I$1").Select
        Application.CutCopyMode = False
        With Selection.QueryTable
            .Connection = _
            "URL;http://maps.google.com/maps/geo?q=" & direccion & ", Santiago, RM&output=csv&sensor=false&gl=cl&key=ABQIAAAALsSOLT7cbh7X-JlSENAAQRQxTqHrN2AcRFuPjSEXWFFzkWy7kxRE5W3I6Fj83RAZ-KMoNNW2fBM03A"
            .WebSelectionType = xlAllTables
            .WebFormatting = xlWebFormattingNone
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
        End With
        Range("$I$1").Select
        Selection.Copy
        Range("F" & indice).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        indice = indice + 1
    Next direccion
    
End Sub

Now, the problem is I need to collect around 10.000 coordinates, and excel just can't handle it. So basically, what I'm in need is for a tweak of my code so it doesn't consume much resources (and stops responding every time i try to run it). Up until now, i've been able to run it for 50 cicles without dropping.

An example of the data I'm using:

Marchant Pereira, 367, providencia, Santiago, Chile
Yaguero, 7777, providencia, Santiago, Chile
Carlos Peña Otaegui, 9745, providencia, Santiago, Chile
Vitacura, 9910, providencia, Santiago, Chile
Apoquindo, 5701, providencia, Santiago, Chile
Manuel Claro Vial, 8035, providencia, Santiago, Chile
Condell, 556, providencia, Santiago, Chile
Nuestra Señora Del Rosario, 1246, providencia, Santiago, Chile
Condell, 560, providencia, Santiago, Chile
Presidente Kennedy, 5735, providencia, Santiago, Chile
Las Hualtatas, 7801, providencia, Santiago, Chile
Noruega, 6520, providencia, Santiago, Chile


I'm sunning on office 2010, windows 7 enterprise edition 32 bits, CPU Pentium 4 CPU 3.0, 2GB RAM

Any/All help and suggestions are greatly appreciated.

Edited by petocities, 07 July 2011 - 02:43 PM.

Posted Image

BC AdBot (Login to Remove)

 


#2 cryptodan

cryptodan

    Bleepin Madman


  • Members
  • 21,868 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Catonsville, Md
  • Local time:10:47 PM

Posted 07 July 2011 - 03:06 PM

Please perform the following, so that we can get the exact specs of your computer. This will better assist us in helping you more.

Publish a Snapshot using Speccy

The below is for those who cannot get online

Please take caution when attaching a text file to your post if you cannot copy/paste the link to your post, you will need to edit it to make sure that your Windows Key is not present.

#3 petocities

petocities
  • Topic Starter

  • Members
  • 102 posts
  • OFFLINE
  •  
  • Location:Santiago Chile
  • Local time:07:47 PM

Posted 07 July 2011 - 03:20 PM

Of course:
Snapshot
Posted Image

#4 cryptodan

cryptodan

    Bleepin Madman


  • Members
  • 21,868 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Catonsville, Md
  • Local time:10:47 PM

Posted 08 July 2011 - 04:43 AM

Your ram is a bit low for Windows 7.

#5 petocities

petocities
  • Topic Starter

  • Members
  • 102 posts
  • OFFLINE
  •  
  • Location:Santiago Chile
  • Local time:07:47 PM

Posted 08 July 2011 - 08:27 AM

Your ram is a bit low for Windows 7.


Well... of course. But regarding the code, do you think improvements could be made for it to become less resource-dependant?
I cannot change my pc just to run 1 macro

EDIT: Post Nº 100! yay!

Edited by petocities, 08 July 2011 - 08:30 AM.

Posted Image

#6 cryptodan

cryptodan

    Bleepin Madman


  • Members
  • 21,868 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Catonsville, Md
  • Local time:10:47 PM

Posted 08 July 2011 - 03:01 PM

You can try doing less pulls.

It could also be your internet connection.

#7 petocities

petocities
  • Topic Starter

  • Members
  • 102 posts
  • OFFLINE
  •  
  • Location:Santiago Chile
  • Local time:07:47 PM

Posted 08 July 2011 - 03:46 PM

You can try doing less pulls.

It could also be your internet connection.


Yeah i guess my code isn't very fit, but I don't know how to improve it since my knowledge of VBA isn't very good.
I tried the following:
Sub coordenadas2()
'
' coordenadas2 Macro
'
'   Application.ScreenUpdating = False
    Dim direcciones As Range
    Dim indice
    Set direcciones = Worksheets("Limpio").Range("H2:H52")
    indice = 2
    
    For Each direccion In direcciones
        If direccion.Value = "" Then Exit For
        Sheets("Limpio").Select
        Range("I1").Select
        Application.CutCopyMode = False
        With Selection.QueryTable
            .Connection = _
            "URL;http://maps.google.com/maps/geo?q=" & direccion.Value & ", Santiago, RM&output=csv&sensor=false&gl=cl&key=ABQIAAAALsSOLT7cbh7X-JlSENAAQRQxTqHrN2AcRFuPjSEXWFFzkWy7kxRE5W3I6Fj83RAZ-KMoNNW2fBM03A"
            .WebSelectionType = xlAllTables
            .WebFormatting = xlWebFormattingNone
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
        End With
        Range("$I$1").Select
        Selection.Copy
        Range("F" & indice).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        indice = indice + 1
    Next direccion
    
End Sub


I get an 1004 error at the start of the With block, but can't figure out how to fix it.
Again, all help appreciated
Posted Image

#8 cryptodan

cryptodan

    Bleepin Madman


  • Members
  • 21,868 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Catonsville, Md
  • Local time:10:47 PM

Posted 08 July 2011 - 04:05 PM

You could also try a comma separated value file instead of Excel?

#9 petocities

petocities
  • Topic Starter

  • Members
  • 102 posts
  • OFFLINE
  •  
  • Location:Santiago Chile
  • Local time:07:47 PM

Posted 11 July 2011 - 09:15 AM

You could also try a comma separated value file instead of Excel?


I will give it a try.
Thank you!

Although, any insights with the code I posted and the error it gives?

Edited by petocities, 11 July 2011 - 11:50 AM.

Posted Image




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users