DoCmd.OutputTo acQuery

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?
ShawnGrayAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Patrick MatthewsConnect With a Mentor Commented:
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
 
Patrick MatthewsCommented:
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
 
Patrick MatthewsCommented:
That code would follow your OutputTo statement.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
ShawnGrayAuthor Commented:
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
 
Patrick MatthewsCommented:
What line does the debugger jump to?
0
 
ShawnGrayAuthor Commented:
No errors to debug.  But it doesn't change the name of the worksheet tab.
0
 
Patrick MatthewsCommented:
ShawnGray,

Please post the routine in its entirety.

Regards,

Patrick
0
 
ShawnGrayAuthor Commented:
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
 
ShawnGrayAuthor Commented:
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
 
ShawnGrayAuthor Commented:
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
 
ShawnGrayAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.