Array Loop skipping loop in VBA

jeskit · March 14th, 2012, 06:11 AM

Hi,

I am trying to create a piece of code which does two array loops. The first loop I need to open multiple queries and export them to Excel and then the second loop should open them all in excel. However when I run the code it gets to the first line of the loop and skips over it then does the same on the second loop.

So far my code looks like:

Code:

Dim FileArray As Variant
Dim OpenFileArray As Variant
Dim FileName As String
Dim objApp As Excel.Application
Dim objBook As Excel.Workbook
Dim objSheet As Excel.Worksheet

'Export files to excel
FileArray = Array("BDC - Apps", "BDC - Comms", "BDC - IFAs", "IFA Details")
For i = UBound(FileArray) To LBound(FileArray)
    DoCmd.OpenQuery Queryname:=FileArray
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, FileArray, "Y:\" & FileName & "\" & FileArray & ".xls"
    DoCmd.Close acForm, FileArray, acSavePrompt
Next i

'Open Exported Files
OpenFileArray = Array("BDC - Apps", "BDC - Comms", "BDC - IFAs", "IFA Details", "MeetingNo", "BDC Meeting Record")
For i = UBound(FileArray) To LBound(FileArray)
        Set objBook = Workbooks.Open("Y:\" & FileName & "\" & OpenFileArray & ".xls") 'open NewContractNumbers spreadsheet
        Set objApp = objBook.Parent
        objApp.Visible = True 'Make the worksheet visible
Next i

I am not very good with Array loops as I dont have much experience building them so it is most likely that I have written them wrong.

Can anyone explain why the loops are skipping?

Thanks

Jeskit

disel2010 · March 15th, 2012, 07:04 AM

At first sight, looks like your reference the array not the individual array elements..
I'd try changing that first. Your first array would become the following:

Code:

For i = UBound(FileArray) To LBound(FileArray)
    DoCmd.OpenQuery Queryname:=FileArray(i)
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, FileArray(i), "Y:\" & FileName & "\" & FileArray(i) & ".xls"
    DoCmd.Close acForm, FileArray(i), acSavePrompt
Next i

jeskit · March 20th, 2012, 05:14 AM

Hi

Thank you for replying. I tried your suggestion and added the (i) to the arrays however it is still skipping the loop. My code is below and I have highlighted the line gets to before it jumps to the next loop

Code:

Dim FileArray As Variant
Dim OpenFileArray As Variant
Dim FileName As String
Dim objApp As Excel.Application
Dim objBook As Excel.Workbook
Dim objSheet As Excel.Worksheet

FileArray = Array("BDC - Apps", "BDC - Comms", "BDC - IFAs", "IFA Details")
For i = UBound(FileArray) To LBound(FileArray)
    DoCmd.OpenQuery Queryname:=FileArray(i)
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, FileArray, "Y:\" & FileName & "\" & FileArray(i) & ".xls"
    DoCmd.Close acForm, FileArray(i), acSavePrompt
Next i

OpenFileArray = Array("BDC - Apps", "BDC - Comms", "BDC - IFAs", "IFA Details", "MeetingNo", "BDC Meeting Record")
For i = UBound(FileArray) To LBound(FileArray)
        Set objBook = Workbooks.Open("Y:\" & FileName & "\" & OpenFileArray(i) & ".xls") 
        Set objApp = objBook.Parent
        objApp.Visible = True 
Next i

disel2010 · March 20th, 2012, 05:50 AM

Hi,

there seem to be two remaining issues with your first loop:

1) The first issue is because you go from high to low array index...
Because by default a "for .. next" loop increases each time you call the "next", this goes wrong.
If you have a need to go from high to low, you could specify that the for loop decreases with "step minus 1" as follows:

Code:

 
For i = (UBound(FileArray)-1) To LBound(FileArray) step -1
	.......
Next

2) The second issue is because vbscript arrays are zero-based and thus your highest array index is actually Ubound(array) minus one, so the loop becomes as follows:

Code:

 
For i = (UBound(FileArray)-1) To LBound(FileArray) step -1
	.......................
Next

This should do the trick for you first loop.
Your second loop should be adapted in the same way, and I believe you meant to refer to OpenFileArray(i) and not FileArray(i) there.

Success

jeskit · March 20th, 2012, 06:36 AM

Hi

Its now doing the loop however it is now only performing the loop for 3 of teh 4 items in the array.

jeskit · March 20th, 2012, 10:03 AM

I have figured it out, I need to add ,"" to teh end of the array so it minuses that instead of the one of the entries, works great now thanks!!