Modify Transferspreasheet command query name. 1

[CptCrusty1]

I'm using the following line:


[blue]DoCmd.TransferSpreadsheet acExport, 8, "qrySEL_FCL_REV_List_NoNoMod", "C:\Documents and Settings\sroark\My Documents\Demands\FC REV\Conv_AutoRefer.XLS", True, ""[/blue]

which runs the query and deposits it into a spreadsheet of my naming choice. Is there a way to change the name of the query so that it shows up as a different name on the spreadsheet tab? The names belong to my predecessor and don't make a lot of sense to the user community.

Thanks
Crusty.

I live to work and I work to live.

 

[LittleSmudge]

The query qrySEL_FCL_REV_List_NoNoMod must exist as a query in the database.

So just go and change the name of the query to whatever you like and make the same change in the TransferSpreadsheet code.

If you are concerned that qrySEL_FCL_REV_List_NoNoMod is used in other places in the app that will break if you change it ( and it is too daunting a task to track them all down ) then make a copy of the query with the new name and refer to the new copy in the TransferSpreadsheet code.


'ope-that-'elps.


G LS

 

[CautionMP]

CptCrusty1,
I know that sometimes you can't just 'rename' the queries so this might be another alternative for you. Once you have output the query to Excel you can use this routine to rename the worksheet. Usage is:

[tt]RenameSpreadSheetTab "C:\Documents and Settings\sroark\My Documents\Demands\FC REV\Conv_AutoRefer.XLS", "qrySEL_FCL_REV_List_NoNoMod", "New tab name"[/tt]

[navy]Public[/navy] Static [navy]Sub [/navy] RenameSpreadSheetTab(FileName [navy]As String[/navy], ExportedQueryName [navy]As String[/navy], NewSheetName [navy]As String[/navy])
On [navy]Error Goto[/navy] RenameSpreadSheetTab_Error
[navy]Dim[/navy] appExcel [navy]As Object[/navy]
[navy]Dim[/navy] objWorkbook [navy]As Object[/navy]
[navy]Dim[/navy] objWorksheet [navy]As Object[/navy]
[navy]Dim[/navy] blnSpawnedExcel [navy]As Boolean[/navy]

[navy]Set[/navy] appExcel = GetObject(, "Excel.Application")
[navy]Set[/navy] objWorkbook = appExcel.Workbooks.Open(FileName)
[navy]Set[/navy] objWorksheet = objWorkbook.WorkSheets(ExportedQueryName)
objWorksheet.Name = NewSheetName
objWorkbook.Save

Clean_up:
[navy]Set[/navy] objWorksheet = [navy]Nothing[/navy]
objWorkbook.Close
[navy]Set[/navy] objWorkbook = [navy]Nothing[/navy]
[navy]If[/navy] blnSpawnedExcel [navy]Then[/navy]
  appExcel.Quit
[navy]End If[/navy]
[navy]Set[/navy] appExcel = [navy]Nothing[/navy]
[navy]Exit Sub [/navy]

RenameSpreadSheetTab_Error:
[navy]Select Case[/navy] Err.Number
  [navy]Case[/navy] 429
    [navy]Set[/navy] appExcel = CreateObject("Excel.Application")
    blnSpawnedExcel = [navy]True[/navy]
    [navy]Resume Next[/navy]
  [navy]Case Else[/navy]
    Debug.Print Err.Number, Err.Description
    [navy]Resume[/navy] Clean_up
[navy]End Select[/navy]
[navy]End Sub [/navy]

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[JerryKlmns]

LittleSmudge 's suggestion of "do not touch that query" and use a copy of it, could be a new query with your favorite name with this SQL statement

SELECT * FROM qrySEL_FCL_REV_List_NoNoMod

any modifications in qrySEL_FCL_REV_List_NoNoMod shall be included in your export with no extra work.

 

[LittleSmudge]

So there you have it CptCrusty1

A cheap and chearful solution - made even better by Jerry's addition to it, or the technically advanced solution that really gets to the heart of the problem.

Thanks CautionMP - that's a nice solution duly rec'ed.




G LS

 

[CptCrusty1]

Wow. Lots of 'elp. Actually, all of the suggestions offered were considered, however, the database already has over 300 queries and I'm trying to reduce the number as it is. Also, the Excel option is good as well. I had considered that buy time is a concern. I might just have to stick with what I got and come back when I have time. Good thing these links are active for a while.

I appreciate all the good responses...

Thanks.
Crusty.

I live to work and I work to live.

 

[JerryKlmns]

CptCrusty1

Since you do have "enough" queries there is the option of instead pushing data to excel, to pull data from access in the excel enviroment using VBA. I think the method is QueryTable and if you search forum707 you 'll find many posts.