Solved

DoCmd.OutputTo acQuery

Posted on 2008-10-14
11
1,738 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
 
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
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: 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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,…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now