2

I have 6 worksheets, each has a subcategory of data (it is important they are in separate worksheets). I am loading up the data into arrays because there are thousands of rows, then printing them out in a specific format to a .txt file.

Sub ExcelToXML()

Dim headers(), data(), attributes1(), attributes2(), attr$, r&, c&
Dim rowCount As Long
Dim columnCount As Long
Dim FF As Worksheet
Dim FOPR As Worksheet
Dim R1 As Long
Dim C1 As Long

Set FF = Worksheets("Fairy")
Set FOPR = Worksheets("Opera")

rowCount = (FF.Range("A1048576").End(xlUp).Row) 'Only one defined as rowCount should be consistent

ffcolumncount = (FF.Range("XFD1").End(xlToLeft).Column) 
FOPRcolumnCount = FOPR.Range("XFD1").End(xlToLeft).Column


' load the headers and data to an array '
FFheaders = Cells(1, 1).Resize(1, ffcolumncount).Value
FFdata = Cells(1, 1).Resize(rowCount, ffcolumncount).Value

FOPRheaders = Cells(1, 1).Resize(1, FOPRcolumnCount).Value
FOPRdata = Cells(1, 1).Resize(rowCount, FOPRcolumnCount).Value


' set the size for the attributes based on the columns per child, dynamic
ReDim attributes1(1 To ffcolumncount)
ReDim attributes2(1 To FOPRcolumnCount)

' open file and print the header two main parents
Open "C:\desktop\ToGroup.xml" For Output As #1 'file path is here, going to change to save prompt
Print #1, "<Parent>"
Print #1, "  <Child>"

' iterate each row non inclusive of headers 
For r = 2 To UBound(FFdata)

  ' iterate each column '
  For c = 1 To UBound(FFdata, 2)
    ' build each attribute '
    attr = FFheaders(1, c) & "=""" & FFdata(r, c) & """"
    attributes1(c) = FFheaders(1, c) & "=""" & FFdata(r, c) & """"

    Next

       For R1 = 2 To UBound(FOPRdata)

         For C1 = 1 To UBound(FOPRdata, 2)

             attr = FOPRheaders(1, c) & "=""" & FOPRdata(r, c) & """"
             attributes2(c) = FOPRheaders(1, c) & "=""" & FOPRdata(r, c) & """"
                Next

I cut it off at the prining and at 2 for next loops. (Not actually sure if the for..next loops are structured properly). Anyways, my question is, am I redimensioning wrong? It gives me 'subscript out of range' error on the second attribute. Is the line

ReDim attributes2(1 To FOPRcolumnCount)

the issue? As it may be dimensioning the array in the original worksheet. Perhaps I should define the arrays in separate or worksheet models? Can I and how would I reference them? Is there a way to make the array specifically refer to a worksheet?

Appreciate any input. It's really hard not having anyone around who can provide a second opinion.

Community
  • 1
  • 1
J.H
  • 181
  • 5
  • 14
  • Whenever you have an error you do not know which line is causing this error then just (1) comment out everything in your sub and then (2) un-comment one stack at a time (one loop at a time) and run the code each time to see if it works. If you are sure that the error occurs at the above mentioned line then the error can be only the size of `FOPRcolumnCount`. According to [this article](https://msdn.microsoft.com/en-us/library/b388cb5s(v=vs.90).aspx) each dimension is limited to 2 ^ 31. So, unless `FOPRcolumnCount` is bigger than ~1.2 billion this line shouldn't be a problem. – Ralph May 12 '16 at 20:39
  • Interesting technique! I will be sure to keep that in mind next time. – J.H May 18 '16 at 09:46

1 Answers1

0

Try replacing 'FOPRcolumnCount' with an actual value. If it solves your problem then the issue is with how 'FOPRcolumnCount' is calculated, which I think is where your problem lies. It's hard to tell from your example, but it appears you are trying to find the right most column on row#1 ; there are easier ways of doing that. Same with rowCount.

I notice you haven't declared it as a variable. Always declare your variables; put "Option Explicit" at the top of your module to force you to declare all variables.

W5ALIVE
  • 381
  • 3
  • 9
  • Actually, that is really embarassing. I am supposed to have Option Explicit enabled by default. I had tested this code on only one column and forgot to rename the variables when I added more...I will try your suggestions, thank you. What are the other options of counting rows that you speak of? I found this to be reliable in that it requires little maintenance when new columns / rows are inevitably added. – J.H May 18 '16 at 09:46
  • You're better off using a named equation in the Excel Name Manager and then link to that equation in your VBA code. Use the answer to this equation to find the last non-empty cell in a row or column http://stackoverflow.com/questions/5441885/last-non-empty-cell-in-a-column/37185137#37185137 For example, if you know your data contains only numbers, use this equation: =MATCH(1E+306,$1:$1,1) Put that equation in the Excel name manager, and name it FOPRcolumnCount Next put this in your VBA script: dim FOPRcolumnCount as long Set FOPRcolumnCount = FOPR.range("FOPRcolumnCount") – W5ALIVE May 19 '16 at 12:23