Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

DoCmd.OutputTo acQuery

Posted on 2008-10-14
11
Medium Priority
?
1,815 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 93

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 93

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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 93

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 93

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 93

Accepted Solution

by:
Patrick Matthews earned 2000 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

618 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