Solved

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

Posted on 2012-04-06
11
1,276 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

688 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