Solved

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

Posted on 2012-04-06
11
1,244 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
  • 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
 

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
 

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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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
 

Author Comment

by:gdunn59
ID: 37817419
Ok.  Thanks anyway.

gdunn59
0
 

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
 

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
 

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
 

Author Closing Comment

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

Thanks,
gdunn59
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

746 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

15 Experts available now in Live!

Get 1:1 Help Now