Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 14th, 2012, 06:11 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default Array Loop skipping loop in VBA

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
 
Old March 15th, 2012, 07:04 AM
Authorized User
 
Join Date: Jan 2011
Posts: 86
Thanks: 1
Thanked 12 Times in 12 Posts
Default

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
 
Old March 20th, 2012, 05:14 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

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
 
Old March 20th, 2012, 05:50 AM
Authorized User
 
Join Date: Jan 2011
Posts: 86
Thanks: 1
Thanked 12 Times in 12 Posts
Default

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
 
Old March 20th, 2012, 06:36 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Hi

Its now doing the loop however it is now only performing the loop for 3 of teh 4 items in the array.
 
Old March 20th, 2012, 10:03 AM
Friend of Wrox
 
Join Date: Jan 2011
Posts: 103
Thanks: 7
Thanked 0 Times in 0 Posts
Default

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!!





Similar Threads
Thread Thread Starter Forum Replies Last Post
For loop with Floating Point Loop Counter - pp. 150 shivesh BOOK: Ivor Horton's Beginning Visual C++ 2010 1 March 28th, 2012 09:31 AM
Help with Array and Loop ublend Classic ASP Professional 2 November 21st, 2007 07:50 AM
loop array problem dbrine Java Basics 2 June 15th, 2007 06:49 AM
Problem with loop and array katlees MySQL 1 July 5th, 2006 11:24 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.