Solved

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

Posted on 2012-03-21
16
262 Views
Last Modified: 2012-03-26
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!
0
Comment
Question by:SeyerIT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 4
  • +1
16 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37747669
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
 
LVL 3

Expert Comment

by:DaFranker
ID: 37747672
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
 
LVL 2

Author Comment

by:SeyerIT
ID: 37752317
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Author Comment

by:SeyerIT
ID: 37752353
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37752377
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37752425
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37752441
change what i posted

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

to

objSht.range("D:D").numberformat="#,###.00"
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 37752539
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37752585
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
 
LVL 2

Author Comment

by:SeyerIT
ID: 37765640
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37765729
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
 
LVL 2

Author Comment

by:SeyerIT
ID: 37765781
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
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 37765871
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37765882
use

Null as Debit  instead of      '' as Debit

and

Null as Credit   instead of      '' as Credit
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37765898
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
 
LVL 2

Author Comment

by:SeyerIT
ID: 37765966
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question