Solved

DoCmd.OutputTo acQuery

Posted on 2008-10-14
11
1,782 Views
Last Modified: 2013-11-27
I'm using:

DoCmd.OutputTo acQuery, "Query Name", "MicrosoftExcel(*.xls)", "c:\Alloc Report Mgd.xls", True, ""

The workbook tab is named after the query that I'm sending to Excel.
Can this tab name be changed?
0
Comment
Question by:ShawnGray
[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
  • 6
  • 5
11 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22716556
ShawnGray said:
>>Can this tab name be changed?

Yes.

Dim xlApp As Object, xlWb As Object

Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open("c:\Alloc Report Mgd.xls")
With xlWb
    .Worksheets("Query Name").Name = "some other name"
    .Save
    .Close
End With
Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22716564
That code would follow your OutputTo statement.
0
 

Author Comment

by:ShawnGray
ID: 22716684
Something doesn't work.  Here's the actual code with your stuff.
Do you see anything wrong?

strFile = "c:\Alloc Report.xls"
DoCmd.OutputTo acQuery, "3PR Calc Backup Sum Global Total", "MicrosoftExcel(*.xls)", strFile, True, ""

Dim xlApp As Object, xlWb As Object

Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open("c:\Alloc Report.xls")
With xlWb
    .Worksheets("3PR Calc Backup Sum Global Tota").Name = "test"
    .Save
    .Close
End With
Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22717066
What line does the debugger jump to?
0
 

Author Comment

by:ShawnGray
ID: 22720925
No errors to debug.  But it doesn't change the name of the worksheet tab.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22722083
ShawnGray,

Please post the routine in its entirety.

Regards,

Patrick
0
 

Author Comment

by:ShawnGray
ID: 22722211
Thats it.  Nothing fancy.  Just kicking out a query to XL.

Private Sub Command267_Click()

strFile = "c:\Alloc Report.xls"
DoCmd.OutputTo acQuery, "3PR Calc Backup Sum Global Total", "MicrosoftExcel(*.xls)", strFile, True, ""

Dim xlApp As Object, xlWb As Object

Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open("c:\Alloc Report.xls")
With xlWb
    .Worksheets("3PR Calc Backup Sum Global Tota").Name = "test"
    .Save
    .Close
End With
Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing

End Sub
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 22722613
ShawnGray,

I'm at a loss.  The little sample in the snippet below passed its test.  The code you tried should have either
worked or thrown an error.  I'm baffled as to why it did neither...

Regards,

Patrick
Sub ztest()
    
    Dim xlApp As Object, xlWb As Object
    
    Const QryName As String = "tblTeams"
    
    DoCmd.OutputTo acOutputTable, QryName, acFormatXLS, "c:\qw.xls"
    
    Set xlApp = CreateObject("Excel.Application")
    Set xlWb = xlApp.Workbooks.Open("c:\qw.xls")
    With xlWb
        .Worksheets(QryName).Name = "test"
        .Save
        .Close
    End With
    Set xlWb = Nothing
    xlApp.Quit
    Set xlApp = Nothing 
End Sub

Open in new window

0
 

Author Comment

by:ShawnGray
ID: 22725102
After running the routine I get a message from Excel stating that the file already exists, would I like to replace it.  I deleted the file, reran the routine, and got the same message.  Told it yes but the tab name still won't change. VB gives a run-time error 2308 'the file already exists".
0
 

Author Comment

by:ShawnGray
ID: 22725644
Looks as if when XL states that the file exist do I want to overwrite; I select a different file name and the tab changes.
0
 

Author Comment

by:ShawnGray
ID: 22725775
If I delete the file, start from scratch.  It actually writes two files.  One to the C: (where I have told it to go) and the other to the Y: (my default location when opening XL).  The 2nd file has the correct tab name.  Why would it not follow the correct file location.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

718 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