VBA Subindices out of range

Question

VBA Subindices out of range

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (0 votes)

0

I have a problem, to see if you can help me.

I have several xml files, which I'm dealing with one by one. I take the first one I try and I keep his information and when I finish crossing it I go to the next, and so on until I no longer have more files to try.

The problem is that when I try to resize the matrix, it does not leave me, since it gives me a subindex error outside the interval. I do not know how to make the matrix grow every time I try a new file, but keeping the information. I detail what I have:

The variable Matrix () as Variant. I have it declared this way.

While MyFile <> ""
    Workbooks("Fichero.xlsm").Activate
    XMLFileName = Mydir & MyFile
    Oxmlfile.Load (XMLFileName) 
    Set nodes = Oxmlfile.SelectNodes("//AINVOICELIST/*")
    aux_node = nodes.Length  'Longitud del nodo
    n = n + aux_node
    j = 1
    ReDim Preserve Matriz(n, 30)
    n = 0
    For Each node1 In nodes         
      y = 0
      Matriz(n, y) = node1.SelectSingleNode("SC").nodeTypedValue()
      Matriz(n, y + 1) = Fact & node1.SelectSingleNode("I").nodeTypedValue()
      Matriz(n, y + 2) = node1.SelectSingleNode("DATE").nodeTypedValue()
      n = n + 1
    Next

Later I call a function that is responsible for taking the treated file to another folder and start with the next one.

Traslado_fichero (MyFile)
MyFile = Dir(Path & MyExtension)

When I start to try the second file and try to resize the matrix, it is when I get the error. Right here ReDim Preserve Matriz(n, 30) , it shows me the message "Subindice out of range"

Can you help me please? Thank you very much.

vba matrices

asked by TANA 17.07.2017 в 18:43

source

3 answers

How to generate a builder.xml file if you do not have it by default Link with twig in symfony 2

score 0 · Answer 1

The problem is the variable Matriz is a multidimensional matrix, in fact, two-dimensional, and ReDim only resizes the last one of the dimensions.

Considering that it is a two-dimensional matrix, a possible solution is to resize a dimension and then transpose the matrix to resize the other and then re-transpose to return to the original form. This has implications for performance, so if your program seems to be very encouraging, you should rethink about whether the use of a multidimensional matrix is the right structure for what you want to achieve.

Similar question in English

ReDim Preserve to a Multi-Dimensional Array in Visual Basic 6

score 0 · Answer 2

Good morning Ruben, first of all thank you very much for your answer. The truth is that the option that you mention to me, I have not understood it very well, and the truth is that it sounds a bit complicated :) I was thinking about another variant I do not know if it will be possible, I explain, I could try the matrix_1, once treated, copy its content to another matrix_2, empty the matrix_1, tartar another file and once finished, copy the contents of the matrix_1 in the matrix_2 (not deleting what you already have, but adding it) and so on, until you finish tartar all? Could it? or what option would you give me? What other solution is it more acceptable? Thank you very much for your attention. Greetings

score 0 · Answer 3

0

Pardon again,

Could you give me an example with my code of how the transpose would be please, I do not get it, or I do not know where to put it. Thanks

answered by 18.07.2017 в 12:20