Solved

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

Posted on 2012-03-21
16
252 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
  • 6
  • 5
  • 4
  • +1
16 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
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
 
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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 119

Expert Comment

by:Rey Obrero
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

759 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now