How to copy data from Access query to excel spreadsheet as numbers and not text?

I actually have some vba that copies the results of a query and pastes it into an excel worksheet. Just want to know if I can get it to paste the numbers in as numbers and not text. We have to manually convert it (which is not a huge issue, but a nuisance) in excel after it has been copied over. I have tried to change the numers to general number format in the query but it doesn't matter.. I also tried manually copying the results of the query outside of the VBA and still it comes across as text.

Here is the vba that runs the query and copies the results;

Option Compare Database

Private Sub cmdSendtoExcel_Click()
    DoCmd.OpenForm ("frmFAIPrintLoadingN")
    Form_frmFAIPrintLoadingN.LoadingStatus "Preparing Data...", 10
    Form_frmFAIPrintLoadingN.LoadingStatus "Preparing Data...", 20
    Form_frmFAIPrintLoadingN.LoadingStatus "Preparing Data...", 30
    Form_frmFAIPrintLoadingN.LoadingStatus "Preparing Data...", 40
    Form_frmFAIPrintLoadingN.LoadingStatus "Getting Data (This may take a moment)...", 50
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    
    Dim objXL As Object      'Excel.Application
    Dim objWbk As Object   'Excel.Workbook
    Dim objSht As Object     'Excel.Worksheet
    
    Set db = CurrentDb
    strSQL = "SELECT * FROM [qryIndirectLaborJE]"
    If Me.FilterOn And Len(Me.Filter) > 0 Then
        strSQL = strSQL & " WHERE " & Me.Filter
    End If
    If Len(Me.OrderBy) > 0 Then
        strSQL = strSQL & " ORDER BY " & Me.OrderBy
    End If

    Set rs = db.OpenRecordset(strSQL, , dbFailOnError)
    
    Set objXL = CreateObject("Excel.Application")
    objXL.Visible = True
    Set objWbk = objXL.Workbooks.Add
    Set objSht = objWbk.Sheets(1)
    
    objSht.Range("A1").CopyFromRecordset rs
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Set objSht = Nothing
    Set objWbk = Nothing
    Set objXL = Nothing
    Form_frmFAIPrintLoadingN.LoadingStatus "Closing...", 60
    Form_frmFAIPrintLoadingN.LoadingStatus "Closing...", 70
    Form_frmFAIPrintLoadingN.LoadingStatus "Closing...", 80
    Form_frmFAIPrintLoadingN.LoadingStatus "Closing...", 90
    Form_frmFAIPrintLoadingN.LoadingStatus "Complete.", 100
    
    'MsgBox "Done!"
    
End Sub

Open in new window



Here is the sql for the query itself;
SELECT 1 AS Line, '5302-00-' & Left([AdjDept1],2) AS GLAcct, "" AS Description1, Format(Round16(Sum(qryIndirectLabor.[Round]),2),"General Number") AS Debit, "" AS Credit, Eval('[Forms]![frmIndirectLabor]![txtGLDesc]') AS Description
FROM qryIndirectLabor
GROUP BY 1, '5302-00-' & Left([AdjDept1],2), "", Eval('[Forms]![frmIndirectLabor]![txtGLDesc]'), "", qryIndirectLabor.AdjDept1;

Open in new window


Thanks for the input!
LVL 2
Jarred MeyerProduction ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
try this
dim rowCnt as integer


 objSht.Range("A1").CopyFromRecordset rs


rowcnt=objSht.usedrange.rows.count

objSht.range("c2:c" & rowcnt).numberformat="#,###.00"
   
    rs.Close
0
Frank WhiteCommented:
This doesn't seem to be an issue with your query data, but rather Excel assuming the data contained in the recordset is raw text data when copying it into the cells, and automatically setting the cells to text format in order to accomodate.

To counter this:

Before or after the line "objSht.Range("A1").CopyFromRecordset rs" in your VBA sub, try adding objSht.Cells.NumberFormat = "General" or objSht.Cells.NumberFormat = "Number". This should set the entire worksheet to the corresponding format. Beware performance issues. You might want to use the previous suggestion to set the format of only the relevant cells/rows if there's too much delay in using .Cells or if you want to retain the format of some other cells.

Note that the suggested "#,###.00" format string is US-number-specific, and will not adapt to different locale settings, being hardcoded to that particular format.
0
Jarred MeyerProduction ManagerAuthor Commented:
Thank both of you for your responses.. I gave both of them a try and didn't seem to get it to work.. Just keeps coming over as text.

Here is the example of the data after it came over to excel;
excel
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jarred MeyerProduction ManagerAuthor Commented:
Maybe I need to take a step back to query that i generating the data. When I run this it is doing the same thing, the results are coming across as text, even if I try formatting them as a number. (The [Line] field oddly enough is coming across as a number and exports to excel as a number as well..)

Here is a screenshot of the results of the query;
query
And here is that SQL;
SELECT
1 AS Line,
'5302-00-' & Left([AdjDept1],2) AS GLAcct,
"" AS Description1,
Format(Round16(Sum(qryIndirectLabor.[Round]),2),"#,###.00") AS Debit,
"" AS Credit,
Eval('[Forms]![frmIndirectLabor]![txtGLDesc]') AS Description
FROM qryIndirectLabor
GROUP BY 1, '5302-00-' & Left([AdjDept1],2), "", "", Eval('[Forms]![frmIndirectLabor]![txtGLDesc]'), qryIndirectLabor.AdjDept1

UNION Select
2 as Line,
'1173-00-00' as GLAcct,
'' as Description1,
'' As Debit,
Round16(Sum(qryIndirectLabor.[Round]),2) as Credit,
Eval('[Forms]![frmIndirectLabor]![txtGLDesc]') AS Description
FROM qryIndirectLabor;


Perhaps if I can get this to display correctly then it will export correctly??


Thanks again for the help!
0
Dale FyeCommented:
The Format function in your query is causing this to convert to text.  Try:

SELECT 1 AS Line,
            '5302-00-' & Left([AdjDept1],2) AS GLAcct,
            "" AS Description1,
            Round16(Sum(qryIndirectLabor.[Round]),2) AS Debit,
            "" AS Credit,
            Eval('[Forms]![frmIndirectLabor]![txtGLDesc]') AS Description
FROM qryIndirectLabor
GROUP BY 1, '5302-00-' & Left([AdjDept1],2), "", Eval('[Forms]![frmIndirectLabor]![txtGLDesc]'), "", qryIndirectLabor.AdjDept1;

I'm also unsure what the Round16 function is and what data type it returns, but I would start with that.
0
Rey Obrero (Capricorn1)Commented:
in the design view of the query

remove Format on the Debit column

Debit: Round16(Sum(qryIndirectLabor.[Round]),2)

do  a right click on the column then select Property
in the Format property select either  Standard, Fixed or Currency
0
Rey Obrero (Capricorn1)Commented:
change what i posted

objSht.range("c2:c" & rowcnt).numberformat="#,###.00"

to

objSht.range("D:D").numberformat="#,###.00"
0
Jarred MeyerProduction ManagerAuthor Commented:
So I tried all of the above and am still getting the result to return as text.. It must have something to do with the Round16 function that is running on the sum.

Here is the vba for that;
Public Function Round16(ByVal v As Double, Optional ByVal lngDecimals As Long = 0) As Double
' by Filipe Lage, fclage-NO~SPAM@kiss-ezlink.com, 20050322
  Round16 = CDbl(Format$(v * 10 ^ lngDecimals, "0")) / 10 ^ lngDecimals
End Function
0
Dale FyeCommented:
so, when you removed the format from the query, does the query still show the Debit column coming back as a string?  Or is this only occurring when exported to Excel
0
Jarred MeyerProduction ManagerAuthor Commented:
Sorry I'm just getting back to you on this..

When I run the query without exporting it, it still shows the columns coming back as a string.
0
Dale FyeCommented:
Still working with just the query, try wrapping the Round16 function call in cdbl, like:

SELECT 1 AS Line,
            '5302-00-' & Left([AdjDept1],2) AS GLAcct,
            "" AS Description1,
            cdbl(Round16(Sum(qryIndirectLabor.[Round]),2)) AS Debit,
            "" AS Credit,
            Eval('[Forms]![frmIndirectLabor]![txtGLDesc]') AS Description
FROM qryIndirectLabor
GROUP BY 1, '5302-00-' & Left([AdjDept1],2), "", Eval('[Forms]![frmIndirectLabor]![txtGLDesc]'), "", qryIndirectLabor.AdjDept1;

After running the query, when you go back to the query design view, highlight the "Debit" column and look at the properties of the column.  Is there a "Decimal Places" property displayed?
0
Jarred MeyerProduction ManagerAuthor Commented:
I added cdbl and when I break out the first half of the union query into it's own query and click on the properties of Debit there is a Decimal places property there.. If I run this query by itself without the Union then the numbers do come across as numbers.. But when I put it back into the Union query then they go back to being text again.

So when I run it like;
SELECT
1 AS Line,
'5302-00-' & Left([AdjDept1],2) AS GLAcct,
"" AS Description1,
cdbl(Round16(Sum(qryIndirectLabor.[Round]),2)) AS Debit,
"" AS Credit,
Eval('[Forms]![frmIndirectLabor]![txtGLDesc]') AS Description
FROM qryIndirectLabor
GROUP BY 1, '5302-00-' & Left([AdjDept1],2), "", "", Eval('[Forms]![frmIndirectLabor]![txtGLDesc]'), qryIndirectLabor.AdjDept1


It will show up with numbers.


But when I run it like;
SELECT
1 AS Line,
'5302-00-' & Left([AdjDept1],2) AS GLAcct,
"" AS Description1,
cdbl(Round16(Sum(qryIndirectLabor.[Round]),2)) AS Debit,
"" AS Credit,
Eval('[Forms]![frmIndirectLabor]![txtGLDesc]') AS Description
FROM qryIndirectLabor
GROUP BY 1, '5302-00-' & Left([AdjDept1],2), "", "", Eval('[Forms]![frmIndirectLabor]![txtGLDesc]'), qryIndirectLabor.AdjDept1

UNION Select
2 as Line,
'1173-00-00' as GLAcct,
'' as Description1,
'' As Debit,
cdbl(Round16(Sum(qryIndirectLabor.[Round]),2)) as Credit,
Eval('[Forms]![frmIndirectLabor]![txtGLDesc]') AS Description
FROM qryIndirectLabor;



Then it shows up as text
0
Dale FyeCommented:
Then replace this line:

'' as Debit

from the second part of the union with

NULL as Debit

and try it again.  And do the same thing with the "Credit" column in the top half of the union.  Replace the double quotes "" with NULL
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
use

Null as Debit  instead of      '' as Debit

and

Null as Credit   instead of      '' as Credit
0
Dale FyeCommented:
My guess is that will work.

If so, then you should also be able to drop the cdbl() function call that you have surrounding the Round16() function call.
0
Jarred MeyerProduction ManagerAuthor Commented:
Yup.. that was it.. Didn't even hit me. I did have to change it to 0 AS Debit and 0 AS Credit for some reason though or my second union sum wouldn't show up.. Nonetheless though, that did the trick:)

Thanks a bunch!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.