Pass the '=' sign in an array to excel range

[BuglerX]

Hello again all. First see this website /forums/showthread.php?t=218472 thread for the beginning of my trouble.

I managed to find out the above problem was the data in my array was causing problems with the code. In the array was a series of ==== signs. When I changed the data to all 'a' (aaaaa) the code worked perfectly. My next question is I need to keep the === in the data, How would I pass the array to have forumula.Array see the === as string I guess?

Need your expertise. If you pull the file you will see what I am talking about.

Thanks again.

 

[vbaInet]

Format the Cells (or Range) you are pasting into as Text before pasting.

 

[BuglerX]

vbaINet Thanks for your input. I am going to need a little more help. see the code below and see how I will be able to accomplish this

strQuery = "SELECT [Detail].[Menu Item] AS ItemSold, Sum([Detail].[Quantity]) AS TotalQty, Sum([Detail].[Item Price]) AS TotalSale, "
    strQuery = strQuery & "[Detail].[Department Name] AS DeptName, [Detail].[Category Name] AS CategoryName "
    strQuery = strQuery & "FROM [Detail] "
    strQuery = strQuery & "WHERE (((CDate([Date])) >= #" & datStartDate & "# And (CDate([Date])) <= #" & datEndDate & "#)) "
    strQuery = strQuery & "GROUP BY [Detail].[Department Name], [Detail].[Category Name], [Detail].[Menu Item] "
    strQuery = strQuery & "ORDER BY [Detail].[Department Name], [Detail].[Category Name], [Detail].[Menu Item];"
    
    Set rstResults = CurrentDb.OpenRecordset(strQuery)
    varResults = rstResults.GetRows(2 ^ 15)
    rstResults.Close
    Set rstResults = Nothing
    
    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"
    objResultsSheet.Range("B1").Value = "Total Qty"
    objResultsSheet.Range("C1").Value = "Total Sale"
    objResultsSheet.Range("D1").Value = "Department Name"
    objResultsSheet.Range("E1").Value = "Category Name"
    objResultsSheet.Range("A:E").Columns.AutoFit
    objExcel.Visible = True
    Set objResultsRange = Nothing
    Set objResultsSheet = Nothing
    Set objResultsWorkbook = Nothing
    Set objExcel = Nothing

So varREsults is the result set of a query. First element in the array is Item Sold. This is the only set of data I need to force to string as Total Qty and Total Sale are numbers. Any suggestions on how to do this using the above code?

Thank you for your time.

 

[vbaInet]

So where is it throwing the error?