Solved

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

Posted on 2012-04-06
11
1,267 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
 
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

828 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