smtazulislam
Member
- Local time
- Today, 14:47
- Joined
- Mar 27, 2020
- Messages
- 806
Hello Sir @arnelgptry again.
is it possible that If I Format a sample Excel Worksheet same data with columns. Then new data will replace in the sample WorkSheet ?
But File name need and Path should be as before.
Previously your code added here,
Code:
Const conPath As String = "F:\3. EMPLOYEES Department\Employee's INSURANCE\1. BUPA ARABIA\"
Dim strFile As String
Dim varItem As Variant
Dim varSelect As Variant
Dim strDataString As String
strFile = conPath & "CCHI " & (Format$(Date, "dd")) & " " & UCase(MonthName(Month(Date), True)) & " " & Year(Date)
For Each varSelect In Me!lstResults.ItemsSelected
strDataString = strDataString & Me!lstResults.ItemData(varSelect) & ","
Next
If Len(strDataString) Then
strDataString = "SELECT * FROM (" & Me!lstResults.RowSource & ") where EmployeeID In (" & strDataString & ");"
Dim qd As DAO.QueryDef
On Error Resume Next
DoCmd.DeleteObject acQuery, "QryTempHealthInsIqamaUpdateResults"
With CurrentDb
Set qd = .CreateQueryDef("QryTempHealthInsIqamaUpdateResults", strDataString)
.QueryDefs.Append qd
Set qd = Nothing
.QueryDefs.Refresh
End With
Application.RefreshDatabaseWindow
DoCmd.TransferSpreadsheet TransferType:=acExport, _
spreadsheetType:=acSpreadsheetTypeExcel12Xml, _
tableName:="CCHI", _
Filename:=strFile
MsgBox "Your Selected Data has been an export successfully.", vbOKOnly, "Export Completed !"
'Dim xlApp As Object
'Set xlApp = CreateObject("Excel.Application")
'xlApp.Workbooks.Open (strFile)
'xlApp.Visible = True
End If
EDIT :
Any Temp query QryTempHealthInsIqamaUpdateResults data insert in sample Format workSheet.
AND also would like to open this worksheet when EXPORT is completed.