Export to workseet name "Data" instead of (sheet4) (1 Viewer)

[Bunga2017]

Dear mr/mrs,
Please help
With below VBA the data from access wil be export to excel with file name
DataStaf.XLSX worksheet (sheet4)

the data must be export to excel with file name DataStaf.XLSX worksheet (Data)
instead of (sheet4)

DataStaf.xlsx with worksheet :
Data
Sheet2
Sheet3
Sheet4




Private Sub Command0_Click()
ExportToXLS
End Sub


Public Function ExportToXLS()

Dim objExcel As Excel.Workbook
Set objExcel = Workbooks.Add
Dim objSheet As Excel.Worksheet
Set objSheet = objExcel.Worksheets.Add()

Dim rec As Recordset
Set rec = CurrentDb.OpenRecordset("SELECT * FROM tblStaf")

objSheet.Range("A4").CopyFromRecordset rec

Dim sPath As String
sPath = CurrentProject.Path

Dim sFilename_full As String
sFilename_full = sPath & "\DataStaf"
On Error Resume Next
objExcel.Close SaveChanges:=True, FileName:=sFilename_full

Set objExcel = Nothing

End Function


Thank you
Bunga

 

[Ranman256]

objSheet.name = "Data"

but normally you can export data without code in a single statement:

docmd.Transferspreadsheet acExport, acSpreadsheetTypeExcel12, sQry, sFile, true, "data"

(sheet name is the last param)

 

[Bunga2017]

Dear Ranman256,

Thank you is working