1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

VBA Access array and transpose question

Discussion in 'Software Development' started by BuglerX, Nov 20, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. BuglerX

    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.
     

    Attached Files:

  2. BuglerX

    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
     
  3. BuglerX

    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?
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    BuglerX, welcome to the Forum.
    Replace them in the original Access Query.
     
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 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1027776

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice