locked
Error in a Array string RRS feed

  • Question

  • Last week I've asked about how to import specific data from a web source and it worked.

    https://social.msdn.microsoft.com/Forums/office/en-US/2596b1b6-2c5b-4232-9285-fbdeb446df85/import-specific-multiple-data-from-web-sources-into-excel-at-once?forum=exceldev

    Now I have another issue.

    Sub SIF()
    
        Dim sConnect As String
        Dim rng As Range
        Dim i As Variant
        Dim oQT As QueryTable
        
        sConnect = "URL;" & "http://makoo.eu/tools/userInfo/index.php?user_id="
        
        Worksheets.Add
        
        
        
        arr = Array(24, 67, 145, 739, 891)
        
        For Each i In arr
            Set rng = Range("a1").Offset(WorksheetFunction.CountA(Range("a:a")) + 1)
            
            Set oQT = ActiveSheet.QueryTables.Add(sConnect & i, rng)
            
            With oQT
                 .WebSelectionType = xlEntirePage
                 .Refresh False
            End With
            
            oQT.Delete
               
        Next i
    
    End Sub
    

    This is the script with 5 values in an array I used to make an example.
    My need is to insert tens of thousands of values in the array, but when I try to do so, I receive this error:

    Sub SIF()
    
        Dim sConnect As String
        Dim rng As Range
        Dim i As Variant
        Dim oQT As QueryTable
        
        sConnect = "URL;" & "http://makoo.eu/tools/userInfo/index.php?user_id="
        
        Worksheets.Add
        
        
        
        arr = Array()
    40435361, 40780801, 40269829, 40323843, 40004349, 40282997, 40009610, 40003135, 40978948, 40028356, 40587623, 40670543, 40365400, 40579501, 40014098, 40294482, 40583509, 40807822, 40848687, 40026871, 40382663, 40731685, 40122607, 40257525, 40535863, 40511911, 40397739, 40743651, 40827149, 40012941, 40132873, 40160437, 40133248, 40099730, 40791823, 40134723, 40116325, 40735503, 40161457, 40001473, 40624546, 40001181, 40013977, 40423984, 40412025, 40348962, 40166137, 40389818, 40914699, 40312104, 40097645, 40115937, 40752679, 40418010, 40454593, 40023938, 40174628, 40002741, 40249502, 40427583, 40364842, 40022225, 40000231, 40819543, 40111282, 40127162, 40313077, 40078200, 40585520, 40326819, 40335823, 40179061, 40175633, 40654059, 40337102, 40488002, 40250076, 40117565, 40175342, 40291069, 40371110, 40128040, 40010636, 40041441, 40600791, 40014636, 40304122, 40971270, 40013472, 40305751, 40419697, 40143601, 40652210, 40367509, 40167503, 40174882, 40738258, 40401226, 40248661, 40258505, 40000282, 40027235, 401
    40316, 40285682, 40704143, 40112186, 40000699, 40199829, 40745255, 40251521, 40300893, 40406956, 40252307, 40030596, 40343685, 40568952, 40749950, 40338140, 40439181, 40475818, 40007549, 40713217, 40096115, 40594208, 40803655, 40152983, 40262137, 40341226, 40312593, 40126211, 40129991, 40017653, 40594743, 40004414, 40000698, 40398377, 40001251, 40652855, 40180877, 40876285, 40436403, 40175315, 40422461, 40629551, 40066679, 40843503, 40659038, 40000501, 40350318, 40466260, 40000324, 40201987, 40014333, 40012977, 40151102, 40896323, 40013901, 40510456, 40054773, 40348773, 40690550, 40005703, 40268088, 40153846, 40341438, 40004354, 40006749, 40012325, 40737706, 40000483, 40254889, 40969562, 40489631, 40250023, 40792045, 40405584, 40022489, 40500652, 40817221, 40269798, 40298569, 40402080, 40892538, 40033164, 40904406, 40007825, 40636377, 40008642, 40009044, 40356858, 40315231, 40013099, 40433175, 40375899, 40310250, 40029758, 40752500, 40885998, 40000847, 40143439, 40019371, 40307537, 40027423, 40813628, 401273
    70, 40278016, 40281572)
        
        For Each i In arr
            Set rng = Range("a1").Offset(WorksheetFunction.CountA(Range("a:a")) + 1)
            
            Set oQT = ActiveSheet.QueryTables.Add(sConnect & i, rng)
            
            With oQT
                 .WebSelectionType = xlEntirePage
                 .Refresh False
            End With
            
            oQT.Delete
               
        Next i
    
    End Sub
    

    All the values went "red" and it gives me a "syntax error" when I try to run the script. I don't know why there is this element 

    Array()

    And the values start on a new row.

    How can I do to solve this problem? (if it's solvable)

    Thanks in advance.

    Andrea.

    Wednesday, October 18, 2017 10:31 AM

Answers

  • Try this - put your values into cells A1:A20, and keep that sheet active:

    42012299
    42216974
    42137354
    42239143
    42068883
    42287770
    42437251
    42210408
    42124485
    42232139
    42406324
    42166170
    42009763
    42069738
    42422345
    42360664
    42105657
    42032126
    42073111
    42198243


    Then use this code

    Sub SIF2()

        Dim sConnect As String
        Dim rng As Range
        Dim cell As Range
        Dim oQT As QueryTable
        Dim rngResult As Range
        Dim shtResult As Worksheet
        
        sConnect = "URL;" & "http://makoo.eu/tools/userInfo/index.php?user_id="
        
        Set rng = ActiveSheet.Range("A1:A20")
        Set shtResult = Worksheets.Add(After:=ActiveSheet)
            
        For Each cell In rng
            Debug.Print (cell.Value)
            
            Set rngResult = shtResult.Cells(shtResult.Rows.Count, "A").End(xlUp)(3)
        
            Set oQT = shtResult.QueryTables.Add(sConnect & cell.Value, rngResult)
            
            With oQT
                 .WebSelectionType = xlEntirePage
                 .Refresh False
            End With
            
            oQT.Delete
               
        Next cell

    End Sub


    Wednesday, October 25, 2017 3:01 PM

All replies

  • I would just remove the extra ) <return> (

    arr = Array()
    40435361, 40780801, ....., 40281572)

    Should be this

    arr = Array(40435361, 40780801,....., 40281572)

    And if the array is too big, split it into and loop through a 2D arrays instead of the 1D array you are using.
    Wednesday, October 18, 2017 8:07 PM
  • Hi DraperyOfConsciousness,

    I find that there is some syntax mistake in your array.

    also VBE not able to handle this much long array like you try to write above.

    I suggest you to properly declare an array with the size and try to assign the value using index key of an array.

    Sub demo()
    
    Dim myArr(1 To 3) As Variant
    myArr(1) = "string1"
    myArr(2) = "string2"
    myArr(3) = "string3"
    End Sub

    or

    Sub demo()
    Dim myArr() As Variant
    myArr = Array("string1", "string2", "string3")
    End Sub

    Reference:

    VBA - Arrays

    Excel VBA Array: The Complete Tutorial for Beginners

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, October 19, 2017 2:25 AM
  • >I would just remove the extra ) <return> (

    That's what I thought, but VBA didn't allow me to do that.

    Thursday, October 19, 2017 7:15 PM
  • Thank you!

    Dim myArr(1 To 3) As Variant

    The "1 To 3" is arbitrary as I wish? For example, if I'd need "1 to 100000"?

    myArr(1) = "string1"

    And inside the "string1" I can put the value (or more than one value)?

    At last, may I ask, how can I insert this codes in the main script (the first that I wrote)

    Thanks in advance!

    Andrea.

    Thursday, October 19, 2017 7:23 PM
  • Hi DraperyOfConsciousness,

    you had asked,"And inside the "string1" I can put the value (or more than one value)?

    At last, may I ask, how can I insert this codes in the main script (the first that I wrote)"

    you cannot put more then one value at one particular index in an array.

    you need to only store one value at one index then to add next value , you need to add value at next index.

    you just replace your current array with the array I suggested above.

    but here, if we think practically then it is little complex, confusing and tedious to store 10000 values in array manually in code.

    you can try to store this value in worksheet and then you can pass the range in your code.

    it will be much easier to handle 10000 values.

    you can also manipulate it with your code as per your requirement.

    you can also loop through them and directly use it wherever you want to use and do some process on it.

    or you can also loop through values and add them in an array.

    so here I think that it is better to store these values in sheet and use it instead of storing them in array using VBA code.

    let us know if you have any further question. we will try to provide you further suggestions , if needed.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, October 20, 2017 2:49 AM

  • you can try to store this value in worksheet and then you can pass the range in your code.

    it will be much easier to handle 10000 values.

    you can also manipulate it with your code as per your requirement.

    you can also loop through them and directly use it wherever you want to use and do some process on it.

    or you can also loop through values and add them in an array.

    so here I think that it is better to store these values in sheet and use it instead of storing them in array using VBA code.

    Thank you and sorry for this late answer.

    With the suggests you gave me I could build a sheet with n values in a column and then instruct VBA to look for web sources based on the values in the sheet?

    May I ask how can I implement this code in the script?

    Thanks in advance.

    Andrea.

    Monday, October 23, 2017 6:25 PM
  • Hi DraperyOfConsciousness,

    you can try to loop through the range of worksheet, same as you can loop through array.

    then you can use that value in your code and perform your operation.

    below is a simple example in which I try to store 10 values in sheet and try to loop through that range and print the value in immediate window.

    you can the values in sheet.

    code:

    Sub demo()
    Dim rng As Range, cell As Range
    Set rng = Range("A1:A10")
    For Each cell In rng
        Debug.Print (cell.Value)
    Next cell
    End Sub
    
     

    Output:

    so like this you can try to use the values stored in the sheet.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 24, 2017 12:55 AM
  • I tried to implement this code in the main script and it came out something like this, with a cell range from A1 to A20 in sheet 1

    Sub SIF()
    
        Dim sConnect As String
        Dim rng As Range, cell As Range
        Dim oQT As QueryTable
        
        sConnect = "URL;" & "http://makoo.eu/tools/userInfo/index.php?user_id="
        
        Worksheets.Add
        
        
        
        Set rng = Range("A1:A20")
        
        For Each cell In rng
            Debug.Print (cell.Value)
            
            Set rng = Range("a1").Offset(WorksheetFunction.CountA(Range("a:a")) + 1)
            
            Set oQT = ActiveSheet.QueryTables.Add(sConnect & i, rng)
            
            With oQT
                 .WebSelectionType = xlEntirePage
                 .Refresh False
            End With
            
            oQT.Delete
               
        Next cell
    
    End Sub

    But the results are these

    Apart all those codes from "<b>Fatal error</b>:", the "User-ID:0 and Friend-ID:0" are wrong, but should be based on this field (Cell range <g class="gr_ gr_633 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Style replaceWithoutSep" data-gr-id="633" id="633">A1:A20</g>)

    42012299
    42216974
    42137354
    42239143
    42068883
    42287770
    42437251
    42210408
    42124485
    42232139
    42406324
    42166170
    42009763
    42069738
    42422345
    42360664
    42105657
    42032126
    42073111
    42198243

    Which each value represents the "User-ID" field.

    How can I tell the script to look into the range I defined?

    Thanks in advance.

    Andrea.

    Wednesday, October 25, 2017 9:20 AM
  • Try this - put your values into cells A1:A20, and keep that sheet active:

    42012299
    42216974
    42137354
    42239143
    42068883
    42287770
    42437251
    42210408
    42124485
    42232139
    42406324
    42166170
    42009763
    42069738
    42422345
    42360664
    42105657
    42032126
    42073111
    42198243


    Then use this code

    Sub SIF2()

        Dim sConnect As String
        Dim rng As Range
        Dim cell As Range
        Dim oQT As QueryTable
        Dim rngResult As Range
        Dim shtResult As Worksheet
        
        sConnect = "URL;" & "http://makoo.eu/tools/userInfo/index.php?user_id="
        
        Set rng = ActiveSheet.Range("A1:A20")
        Set shtResult = Worksheets.Add(After:=ActiveSheet)
            
        For Each cell In rng
            Debug.Print (cell.Value)
            
            Set rngResult = shtResult.Cells(shtResult.Rows.Count, "A").End(xlUp)(3)
        
            Set oQT = shtResult.QueryTables.Add(sConnect & cell.Value, rngResult)
            
            With oQT
                 .WebSelectionType = xlEntirePage
                 .Refresh False
            End With
            
            oQT.Delete
               
        Next cell

    End Sub


    Wednesday, October 25, 2017 3:01 PM
  • Hi DraperyOfConsciousness,

    code is working correctly but here you have issue with your logic.

    you are not properly understanding that what code is performing.

    I gave you the example code which only print the values to immediate window as it is.

    what your code is doing that fetch the values from website and store it in to column A.

    then my code is printing that value as it is.

    so if we see the code then it is working correctly because it is just written to print the value stored in range.

    further , you need to write more code and implement the logic to find your desire value from the whole string that you stored in each cell.

    so you need to think that how can you find and fetch your desire value from the value below.

    currently , I am not sure that which value you want to find exactly from the string you stored in a cell.

    but I guess that you want to find the number like 42012299. so I mark it yellow.

    or if the value is not there in the cell, then you need to inform us that from where you load this value in cell?

    you can try to confirm which value you want to find from the string.

    then we will try to help you to develop a code example that can find that value.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, October 26, 2017 12:52 AM
  • Hi DraperyOfConsciousness,

    is your issue solved?

    I find that you did not done any follow up after I post my last reply.

    if your issue is solved then I suggest you to post the solution and mark it as an answer.

    if your issue is still exist then I suggest you to refer the suggestion given by me.

    if then also you have any further question then let me know about that.

    we will try to provide further suggestions to solve the issue.

    thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 8, 2017 5:07 AM
  • Sorry for the missed response. I thought I marked the reply from Bernie Deitrick, but I actually didn't.

    The issue is solved.

    Thank you for your time.

    Best regards.

    Andrea.

    Wednesday, November 8, 2017 11:04 AM