Export query result (datasheet) on command button to ms excel

[Sandy1992]

Dear friends,

I am using A2010. I am trying to export my filtered datasheet to excel. Tried TransferSpreadsheet() but nothing helped.

Lastly I tried below code but excel only displays 1 column.

  Dim strOriginalRecordSource As String
    Dim strNewRecordSource As String
 
    strOriginalRecordSource = Me.RecordSource
    strNewRecordSource = "SELECT * FROM Tasks WHERE ([EmployeeId] =" & Me.cboEmployee & ")"
 
    Me.RecordSource = strNewRecordSource
 
    DoCmd.OutputTo acOutputForm, Searchbyempid, , , True
 
    Me.RecordSource = strOriginalRecordSource
 
    DoCmd.OutputTo acOutputForm, Searchbyempid, , , True
 

Also, please check the below code which i used earlier,

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, "Tasks", Employee.xlsx, True

Thanks in advance .

Looking forward for a solution :)

[Sandy1992]

Also, in excel the instead of field name in A1 Column, Combobox name is displayed. But the values under it are correct

[Sandy1992]

Well, I done it myself.

But their is a problem in it.

It displays data for the first time. but it displays the previous searched data even though the option in combobox is changed.

The result is been reflected in the datasheet but not in excel. So I need to close the form and reopen it if I want to change the data.

Please find the below code and suggest if something is inappropriate.

DoCmd.OutputTo acOutputForm, "Tasks SubForm", acFormatXLS, "Tbl1XLS.xls", True
 

Actually the main problem was, I filtered the subform based on VBA code on after update event.

    Dim Myemp As String
    Myemp = "select * from Tasks where ([EmployeeId] =" & Me.cboEmployee & ")"
    Me.Tasks_subform.Form.RecordSource = Myemp
    Me.Tasks_subform.Requery
 

Any help is appreciated :)

[Sandy1992]

This is for the first time my query is not resolved on this forum. I feel helpless. Its a simple question though. If I am not clear to my points please ask any types of questions, I will try my best to answer them, as I really need to resolve it.

Thanks in advance.

[NeoPa]

@Sandy.

Most people will look at the thread and see three responses, so no need to look further.

Your question is full of changes and extra details. The rules are one question per thread as that is most helpful to everybody - especially you, the questioner. Only someone with a lot of time to spare will look at this because it will take them a long time to read all that you've posted and longer to make real sense out of it.

You need to do the work of formulating the question FIRST. Make it as simple as you can. Don't expect the readers to have to do the work to make it make sense.

If you must add comments later, which means you haven't considered the question fully beforehand but will happen often because doing it fully is actually very hard, then add the comment into the first post as an edit. If you keep adding new posts then it will look like a busy thread and we'll all assume you already have someone helping you.