[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Using DoCmd.OutputTo acOutputReport and a field is outputting as Text, needs to be Number/Percent

Posted on 2012-04-06
11
Medium Priority
?
1,288 Views
Last Modified: 2012-06-27
When my code executes, it is outputting one of the fields as Text and I need it to output as a Number with a Percent sign and two decimals.  Nothing happens and the field stays as text.  What am I doing wrong?

Below is the code that I am using:

strOutputToPath = "C:\Windows\Temp\Manager_Report" & "_" & Format(Date, "mm-dd-yyyy") & ".XLS"

DoCmd.OutputTo acOutputReport, "rptManager_Report", acFormatXLS, strOutputToPath, False

rtn = MsgBox("Would you like to open the Report now?", vbYesNo)
If rtn = vbYes Then
    Set app1 = CreateObject("Excel.Application")
    app1.Workbooks.Open strOutputToPath
    app1.Visible = True
    app1.Columns("D").NumberFormat = "General"
Else
    DoCmd.CancelEvent
End If

Thanks,

gdunn59
0
Comment
Question by:gdunn59
[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
  • 8
  • 3
11 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 37816931
Try specifying the worksheet:

Dim wb as Excel.Workbook
strOutputToPath = "C:\Windows\Temp\Manager_Report" & "_" & Format(Date, "mm-dd-yyyy") & ".XLS"

DoCmd.OutputTo acOutputReport, "rptManager_Report", acFormatXLS, strOutputToPath, False

rtn = MsgBox("Would you like to open the Report now?", vbYesNo)
If rtn = vbYes Then
    Set app1 = CreateObject("Excel.Application")
    Set wb = app1.Workbooks.Open strOutputToPath
    app1.Visible = True
    wb.Worksheets("YourWorkSheetName").NumberFormat = "General"
Else
    DoCmd.CancelEvent
End If

Open in new window


Also try other number formats such as this to see if it makes a difference:

  wb.Worksheets("YourWorkSheetName").NumberFormat = "@"
0
 
LVL 1

Author Comment

by:gdunn59
ID: 37817020
mbizup:

I'm getting a Compile Error -- Syntax Error on the following line of code:

 Set wb = app1.Workbooks.Open strOutputToPath


Thanks,
gdunn59
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37817063
Try it with parentheses:

 Set wb = app1.Workbooks.Open(strOutputToPath)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Author Comment

by:gdunn59
ID: 37817099
Now I get the following error:

Run-time error '438'
"Object doesn't support this property or method"

On the following line of code:

wb.Worksheets("rptManager_Report").NumberFormat = "General"

I also tried it with "@" and get the same error.

Thanks,
gdunn59
0
 
LVL 1

Author Comment

by:gdunn59
ID: 37817349
Ok, I think the error was because I didn't have the Excel Reference, but since I added that, still nothing happens (no formatting).

Thanks,
gdunn59
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37817365
Okay -

Give some other Experts a chance to respond, but my own approach is generally to use Excel automation, not OutputTo to get data into Excel.  You have a lot more control with that approach (but it is a lot more code-intensive, too).
0
 
LVL 1

Author Comment

by:gdunn59
ID: 37817419
Ok.  Thanks anyway.

gdunn59
0
 
LVL 1

Author Comment

by:gdunn59
ID: 37817448
I tried the following, and do F8 to Step through the code, and it does open the Spreadsheet and selects the column D3, but the format still is not changing.  When I look at the spreadsheet all of the data in the D3 column shows the

Screen-Shot-of-Error-Flag--for-f.docx
0
 
LVL 1

Author Comment

by:gdunn59
ID: 37817458
Sorry, submitted before I was finished with my last posting.

When I look at the spreadsheet all of the data in the D3 column shows the error flag pertaining to the formatting of the column (see the embedded document in my previous posting.  So if I click on this error flag and choose "convert to number" that works, but I need code to be able to do this.

I have changed my code to the following (but still no formatting changes, and I'm not getting any errors):

rtn = MsgBox("Would you like to open the Report now?", vbYesNo)

If rtn = vbYes Then
    Set xlObj = CreateObject("Excel.Application")
    Set wb = xlObj.Workbooks.Open(strOutputToPath)
    xlObj.Visible = True
   
    With xlObj
        .Worksheets(1).Select
        .Columns("D").Select
        .Selection.NumberFormat = "General"
        .Selection.NumberFormat = "0.00%"
    End With
   
'    wb.Worksheets("rptManager_Report").Columns("D").NumberFormat = "@"
Else
    DoCmd.CancelEvent
End If
0
 
LVL 1

Accepted Solution

by:
gdunn59 earned 0 total points
ID: 37817831
I ended up writing code to dump the entire report instead of using docmd.OutPutTo.

I have posted the code I wrote (works like a charm).

Thanks,
gdunn59

Private Sub cmdMgrRpt_Click()
On Error GoTo Err_cmdMgrRpt_Click
Dim stDocName As String
Dim stDocName2 As String
Dim strOutputToPath As String
Dim rtn As String
Dim xlObj As Object
Dim rs As DAO.Recordset
Dim qdf As QueryDef
Dim qdf2 As QueryDef

DoCmd.Echo False
DoCmd.SetWarnings False

strOutputToPath = "C:\Windows\Temp\Manager_Report" & "_" & Format(Now(), "mm-dd-yyyy_hh mm ss") & ".XLS"

templatefile = "C:\Documents and Settings\ab56446\Application Data\Microsoft\Templates\Manager_Report_TEMPLATE.xltx"

Set xlObj = CreateObject("excel.application")

xlObj.Workbooks.Add templatefile

If IsNull(Me.cboReportCateg) Or IsNull(Me.cboCategSelect) Then
    MsgBox "Please make selections from the drop-downs for a Auditor, Department, Employee or Manager", vbOKOnly
    Me.cboReportCateg.SetFocus
    Me.cboReportCateg.Dropdown
ElseIf Me.cboReportCateg = "Manager" Then
    stDocName = "qryManagerReport (for Report)"
    DoCmd.OpenQuery stDocName
    
    With xlObj
        .Worksheets(1).Select
        .Range("A3").Select
        Set qdf = CurrentDb.QueryDefs("qryManagerFinalReport")
        qdf.Parameters("[Forms]![frmReports]![txtBeginDT]") = [Forms]![frmReports]![txtBeginDT]
        qdf.Parameters("[Forms]![frmReports]![txtEndDT]") = [Forms]![frmReports]![txtEndDT]
        
        Set rs = qdf.OpenRecordset
        .Selection.CopyFromRecordset rs
    
'Go back to top of Spreadsheet and Save to Report Name
        .Worksheets(1).Select
        .Range("A1").Select
        .Range("A1") = "For Dates:" & " " & txtBeginDT & " thru " & txtEndDT
        .Range("A3").Select
        .ActiveWorkbook.SaveAs strOutputToPath
    End With
End If

DoCmd.SetWarnings True
DoCmd.Echo True

xlObj.Quit
rs.Close
     
Dim xlBook As Object
Dim xlObj2 As Object
Dim Msg, Style, Title, intResponse, MyString, strPathName2

Msg = "Report Complete.  Would you like to open report now?"   ' Define message.
Style = vbYesNo ' Define buttons.
Title = "Open Report" ' Define title.
' Display message.
intResponse = MsgBox(Msg, Style, Title)
If intResponse = vbYes Then    ' User chose Yes.
    Set xlObj2 = New Excel.Application
    Set xlBook = xlObj2.Workbooks.Open(strOutputToPath)
    DoCmd.Echo False
    xlObj2.Visible = True
    DoCmd.Echo True
Else
    DoCmd.Hourglass False
    DoCmd.SetWarnings True
    Exit Sub
End If

Exit_Err_cmdMgrRpt_Click:
    Exit Sub

Err_cmdMgrRpt_Click:

If Err.Number = 2501 Then   ' You cancelled .....
      ' no action required
Else
   MsgBox Err.Description
End If

Err.Clear
DoCmd.GoToControl "cboCategSelect"
Me.cboCategSelect = Null
Me.cboReportCateg = Null
Me.txtBeginDT = Null
Me.txtEndDT = Null
Me.cboDept = Null
GoTo Exit_Err_cmdMgrRpt_Click

DoCmd.SetWarnings True

End Sub

Open in new window

0
 
LVL 1

Author Closing Comment

by:gdunn59
ID: 37831714
I resolved the issue myself by taking a different approach.

Thanks,
gdunn59
0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

656 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