• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

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!
0
SeyerIT
Asked:
SeyerIT
  • 6
  • 5
  • 4
  • +1
1 Solution
 
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
 
SeyerITAuthor 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
SeyerITAuthor 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
 
SeyerITAuthor 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
 
SeyerITAuthor 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
 
SeyerITAuthor 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
 
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
 
SeyerITAuthor 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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 6
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now