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

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

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
gdunn59
Asked:
gdunn59
  • 8
  • 3
1 Solution
 
mbizupCommented:
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
 
gdunn59Author Commented:
mbizup:

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

 Set wb = app1.Workbooks.Open strOutputToPath


Thanks,
gdunn59
0
 
mbizupCommented:
Try it with parentheses:

 Set wb = app1.Workbooks.Open(strOutputToPath)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
gdunn59Author Commented:
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
 
gdunn59Author Commented:
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
 
mbizupCommented:
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
 
gdunn59Author Commented:
Ok.  Thanks anyway.

gdunn59
0
 
gdunn59Author Commented:
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
 
gdunn59Author Commented:
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
 
gdunn59Author Commented:
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
 
gdunn59Author Commented:
I resolved the issue myself by taking a different approach.

Thanks,
gdunn59
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 8
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now