singhg8

Hi All,


Process: I have the below code which gets the base SQL statement and defines the working query. Based on this it then generates & exports Excel spreadsheet into a defined folder (wkfolder).

Objective: The exported workbook name is fine but the sheet-name comes up as "RunReport". I wish to change the sheetname to "UpdateData". Is this possible please?


QryDef = CurrentDb.QueryDefs("MyQuery").SQL

CurrentDb.QueryDefs("RunReport").SQL = QryDef

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "RunReport", wkFolder & "1002 Update 01_First Letter.xls"

singhg8

Is there anyone who can help with my query please? It is quite urgent!!!

Flyguy

Seems that the sheetname is the same as the table or query name.
Most easy seems to change the table/query name.

Otherwise you have to use Excel automation to open the created Excel file and change the name of the worksheet.

singhg8

I have tried changing the query name in the below statement but it doesn't seem to work. The file will be generated automatically every week.

CurrentDb.QueryDefs("RunReport").SQL = QryDef

I am new to VBA and learning the code language. Can you please elaborate how can we exactly use excel automation from the access vba itself?

Flyguy

singhg8

Thanks Flyguy. I will try