VBA Access array and transpose question

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

BuglerX

Thread Starter
Joined
Nov 20, 2011
Messages
3
*Attached is an access datase in 7Z format. Hold down shift when running as there is a form that runs on startup.*

I have a database that I use to generate excel spreadsheets from sql statements based on date. For some reason It woks with some data but not other data. Here is the scenario:

Choose dates 11/6/2011 to 11/9/2011. Exports perfectly.
Choose dates anything after 11/10/2011 and it crashes with unable to set the FormulaArray property of the Range class.

Here is a snippet
Code:
Set objExcel = CreateObject("Excel.Application")
    objExcel.Workbooks.Add
    Set objResultsWorkbook = objExcel.Workbooks(objExcel.Workbooks.Count)
    Set objResultsSheet = objResultsWorkbook.Worksheets("Sheet1")
    Set objResultsRange = objResultsSheet.Range("A2:E" & 2 + UBound(varResults, 2))
    objResultsRange.FormulaArray = objExcel.Transpose(varResults)
    objResultsSheet.Range("A1").Value = "Item Sold"
Second line from bottom.objResultsRange.FormulaArray = objExcel.Transpose(varResults) is where it is crashing on dates after 11/9/2011

I'm thinking it's an item in 1 of the fields I'm pulling that is crashing the transpose array. Are there any restrictions to characters in an array?

All suggestions are GREATLY appreciated. I need help on this pronto. Can someone take a look and let me know.
 

Attachments

BuglerX

Thread Starter
Joined
Nov 20, 2011
Messages
3
FormulaArray and Application.Transpose is the culprit. I just can't figure out how to debug. varResults shows in Debugger Local window, but the FormulaArray property will not populate with transpose. I could use some help on this.

Anybody have any suggestions?

Bugler
 

BuglerX

Thread Starter
Joined
Nov 20, 2011
Messages
3
I found out what the problem is. I have a series of ==== in my data array varResults. FormulaArray does not like the === I guess. Next Question:

How do I pass a series of ==== through to the array?
 

OBP

Joined
Mar 8, 2005
Messages
19,895
BuglerX, welcome to the Forum.
Replace them in the original Access Query.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top