Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old March 14th, 2012, 06:11 AM
Friend of Wrox
Points: 429, Level: 7
Points: 429, Level: 7 Points: 429, Level: 7 Points: 429, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
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
Reply With Quote
  #2 (permalink)  
Old March 15th, 2012, 07:04 AM
Authorized User
Points: 448, Level: 7
Points: 448, Level: 7 Points: 448, Level: 7 Points: 448, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
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
Reply With Quote
  #3 (permalink)  
Old March 20th, 2012, 05:14 AM
Friend of Wrox
Points: 429, Level: 7
Points: 429, Level: 7 Points: 429, Level: 7 Points: 429, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
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
Reply With Quote
  #4 (permalink)  
Old March 20th, 2012, 05:50 AM
Authorized User
Points: 448, Level: 7
Points: 448, Level: 7 Points: 448, Level: 7 Points: 448, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
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
Reply With Quote
  #5 (permalink)  
Old March 20th, 2012, 06:36 AM
Friend of Wrox
Points: 429, Level: 7
Points: 429, Level: 7 Points: 429, Level: 7 Points: 429, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
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.
Reply With Quote
  #6 (permalink)  
Old March 20th, 2012, 10:03 AM
Friend of Wrox
Points: 429, Level: 7
Points: 429, Level: 7 Points: 429, Level: 7 Points: 429, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
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!!
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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 06: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



All times are GMT -4. The time now is 07:07 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.