Solved

DoCmd.OutputTo acQuery

Posted on 2008-10-14
11
1,751 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
  • 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

825 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