Solved Previous code not working Search and Export an excel sheet.

[smtazulislam]

try again.

Hello Sir @arnelgp
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,

  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.

 

[arnelgp]

let me clarify.
you are Exportin table CCHI, correct?
and you want to Append (to the last row?) records from "QryTempHealthInsIqamaUpdateResults"?

 

[smtazulislam]

let me clarify.
you are Exportin table CCHI, correct?

YES, this is the WorkSheet name & table name.
And File is "strFile = conPath & "CCHI " & (Format$(Date, "dd")) & " " & UCase(MonthName(Month(Date), True)) & " " & Year(Date)"

let me clarify.
and you want to Append (to the last row?) records from "QryTempHealthInsIqamaUpdateResults"?

this temp query data result want to replace in my sample an Excal file which name is "SAMPLE CCHI"

 

[smtazulislam]

any update !

 

[smtazulislam]

Hello Sir @arnelgp any update !

 

[arnelgp]

on post#23, what do you mean by this:

"this temp query data result want to replace in my sample an Excal file which name is "SAMPLE CCHI"

 

[smtazulislam]

on post#23, what do you mean by this:

"this temp query data result want to replace in my sample an Excal file which name is "SAMPLE CCHI"

When I selected rows from Search Result then It is keep in the Query of "QryTempHealthInsIqamaUpdateResults". Am I right?
So, when I click the export button then this query data will export into path where I direct.
This step you done already.

Here I need some chance.
I create an excel file which name is "Sample CCHI" in same path where the file will export.
I need THIS SAMPLE SHEET taken only FORMAT and insert data that said.

 

[smtazulislam]

why I can't upload the .zip or .rar file

 

[arnelgp]

you have google drive, onedrive, etc.
upload it there and give me the shared link.

 

[arnelgp]

you need to change it to your own folder/and filename.

 

[smtazulislam]

Same. its not replace my sample CCHI file.

 

[arnelgp]

the format will be applied to the Exported sheet not the Sample.

 

[smtazulislam]

Thank you very much, Now work perfectly...
possible after export can open excel file directly...

 

[arnelgp]

add this code after showing the Msgbox:

...
...
MsgBox "Your Table Export Process successfully.", vbOKOnly, "Export completed!"
'show the excel file
Application.FollowHyperlink strFile

 

[smtazulislam]

add this code after showing the Msgbox:

...
...
MsgBox "Your Table Export Process successfully.", vbOKOnly, "Export completed!"
'show the excel file
Application.FollowHyperlink strFile

Not work...

 

[arnelgp]

what is not Working, it is working for me.