Change Excel Sheet Names

[qwerty1]

So I have 5 querys exported to an excel file (C:/File.xlsx) using transferspreadsheet.


The sheet names are named the same as the query names.

Is it possible to change the sheet names (in the file C:\File.xlsx) to what I want them to be named in access vba? Either after I export, or possibly change the query name before it is exported (all done in vba).
Like a line I would add after transferspreadsheet like oldsheetname.name = newsheetname.
Also need to find out how to change field names in the excel file to what I want in vba.

Looked everywhere, cant find an answer.
Really easy question, I just never had to do it before.

[rivereridanus]

I don't usually do it in vba, but I know for a fact that you can do that in the macro builder. The property is "Output File", and you just write the file name that I want. So when I output tblOrders to Excel 2003-97, I write My Orders.xls in the Output File property. If you are already using vba to do the export instead of macros, maybe there is a property with the same name?

[help_me_with_access]

qwerty,

I doubt that stuff like that is available in the macro builder. it shouldn't be really, because tab names are properties of a file INSIDE the excel application. the xl file name itself is "closer" to windows operations and doesn't require manipulating properties inside of another application.

I would think you'd have multiple options here. first of all, ADO 3.x, 4.x, and 5.x libraries have these capabilities using simple recordset coding. but as for the way you're doing things now I would say 2 options might be the easiest:

first, rename your objects and fieldnames in acc or create temporary objects with the desired names, export, then delete if not needed. that would solve the issue immediately.

or, if you don't like that option you can always spend time manipulating the file through an instance of excel via the visual basic medium. e.g. - "coding"

[rivereridanus]

Oh, you're right, sorry. I misread the request. The macro builder thing only works to rename the entire file- not the tabs.

[help_me_with_access]

Oh, you're right, sorry. I misread the request. The macro builder thing only works to rename the entire file- not the tabs.

Microsoft may get there at some point, although I'm doubting it. Right now their bread and butter seems to be .NET everything. especially the WCF...they're pushing that heavily. doesn't mean that it does anything different or special at all, but they had to move and do 'something'.