Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Sacramento, California, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Rename Excel worksheet (Acess XP, SP3)

    I use the TransferSpreadsheet method to create spreadsheets for users based on a queries. I would like to be able to rename the default Worksheet name from from within access to a more user friendly one; . I have been able to glean from the lounge help that there is a way to do this through code after setting references to Excel, but don't have a clue as to what the code would be. Examples I found were doing so many other things also that I was lost in trying to extract the worksheet renaming portion of the posts....any help would be most appreciated.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rename Excel worksheet (Acess XP, SP3)

    You can use the Name command. From the help file :

    Dim OldName, NewName
    OldName = "OLDFILE": NewName = "NEWFILE" ' Define file names.
    Name OldName As NewName ' Rename file.

    OldName = "C:MYDIROLDFILE": NewName = "C:YOURDIRNEWFILE"
    Name OldName As NewName ' Move and rename file.
    Francois

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rename Excel worksheet (Acess XP, SP3)

    In addition : You can name your file directly from the docmd function :
    DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "YourQuery", "YourXLSFilename.XLS"
    Francois

  4. #4
    Lounger
    Join Date
    Jan 2001
    Location
    Sacramento, California, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rename Excel worksheet (Acess XP, SP3)

    Thank you so much for the immediate reply! But I'm trying to rename the worksheet (the name of the sheet after the workbook is opened), not the workbook itself. Is this possible?

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rename Excel worksheet (Acess XP, SP3)

    You can temporally change the name of the query, export the query and rename it to the old name.
    Dim Oldname As String
    Oldname = CurrentDb.QueryDefs("OldQueryName").Name
    DoCmd.Rename "NewQueryName", acQuery, "OldQueryName"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "NewQueryName", "YourFileName.xls"
    DoCmd.Rename Oldname, acQuery, "NewQueryName"

    Replace OldQueryName with the name of your query, NewQueryName and YourFileName.xls with the name you want.
    Francois

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Rename Excel worksheet (Acess XP, SP3)

    The easiest way is to give your queries user-friendly names. Otherwise, you will have to use Automation to start Excel, open the workbook, rename the worksheet, save the workbook and quit Excel.

    First, select Tools | References... (in the Visual Basic Editor), and tick the check box for Microsoft Excel 10.0 Object Library. (10.0 is the internal version number for Office XP / 2002). Use code like this:

    Sub ExportAndRename()
    ' Declarations
    Dim strQuery As String
    Dim strFile As String
    Dim strFriendlyName As String
    Dim objXL As New Excel.Application
    Dim objWb As Excel.Workbook

    ' Error handling on
    On Error GoTo ErrHandler

    ' Change these values as needed
    strQuery = "YourQuery"
    strFile = "C:ExcelYourFilename.xls"
    strFriendlyName = "Exported Data"

    ' Export
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strQuery, strFile
    ' Open in Excel
    Set objWb = objXL.Workbooks.Open(strFile)
    ' Rename worksheet
    objWb.Worksheets(strQuery).Name = strFriendlyName

    ExitHandler:
    On Error Resume Next
    ' Close and save
    objWb.Close SaveChanges:=True
    ' Quit Excel
    objXL.Quit
    ' Release object memory
    Set objWb = Nothing
    Set objXL = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  7. #7
    Lounger
    Join Date
    Jan 2001
    Location
    Sacramento, California, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rename Excel worksheet (Acess XP, SP3)

    Incredible! Thanks so much Francois; it is so creative in its simplicity, it's great . . . just what the doctor ordered!. <img src=/S/doctor.gif border=0 alt=doctor width=25 height=33>

  8. #8
    Lounger
    Join Date
    Jan 2001
    Location
    Sacramento, California, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rename Excel worksheet (Acess XP, SP3)

    Thanks so much, Hans. As you (and Francois) have pointed out, much easier to temporarily rename the query, which is what I'm going to do. But I appreciate the code and will go over it and do a test implementation in order to learn from it. I really appreciate all the help guys!

  9. #9
    Lounger
    Join Date
    Dec 2004
    Location
    Fort Collins, Colorado, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rename Excel worksheet (Acess XP, SP3)

    Hans, That's perfectly elegant... but "talk about going around your nose to get to your face!" LOL No offense intended, just having fun [img]/forums/images/smilies/smile.gif[/img]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •