Export subform to Excel

[healey21]

I have a subform which I filter from a main form, when I export the results through a query I don't always get the same records that are shown in the subform.

Can you export the results of the subform into an Excel workbook rather than use a query or table?

My subform name is frmCombinedSearchEngineFilter

Any assistance would be appriecated

 

[Trevor G]

Healey21,

Look at the macro section there is an outputto which will allow you to output your form content. In VBA it would be:

DoCmd.OutputTo acOutputForm, "qryCat subform", "Excel97-Excel2003Workbook(*.xls)", "m:\Access Files\My Sub Form.xls", True, "", 0, acExportQualityPrint

 

[healey21]

Sadly this is trying to send all the records rather than what is in view after filtering. If I filter down and I view say 1000 records when the output starts is states there are 2 many records to be copied to the clipboard cant do more than 65000.

Any help would be appreciated

The code is indicated below:

DoCmd.OutputTo acOutputForm, "frmCombinedSearchEngineFilter", "Excel97-Excel2003Workbook(*.xls)", CurrentProject.Path & "\" & "Test3.xls", True, "", 0, acExportQualityPrint

 

[Trevor G]

Take a look at his set opf code Healey21, I have look at using a macro to go into a the subform and select a control, then used the runcommands to select all records in view, then used a copy command. I have added some code to open excel, place in a new book and paste it in and autofit the columns

I hope it helps you

Private Sub Command13_Click()
On Error GoTo Command13_Click_Err
Me.frmCombinedSearchEngineFilter.SetFocus
DoCmd.GoToControl "Policy Ref"
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdCopy
Dim xlapp As Excel.Application
Set xlapp = CreateObject("Excel.Application")
With xlapp
.Workbooks.Add
.ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
.Cells.Select
.Cells.EntireColumn.AutoFit
.Visible = True
.Range("a1").Select

End With

Command13_Click_Exit:
Exit Sub
Command13_Click_Err:
MsgBox Error$
Resume Command13_Click_Exit
End Sub

 

[healey21]

Thank you Trevor,

This has worked.

 

[pbaldy]

I realize this was posted a long time ago, but I was having the same issue as the original poster. Using the code provided by Trevor G, I was able to get 99.9% of what I needed. The only difference with my situation is that I'm copying and pasting 3 different subforms into one sheet. Everything works fine for the first and third subform, but for some reason when I setFocus to the second subform, it automatically sets focus to the first cell of the datasheet and not the entire subform. Now the third subform is set up the exact same way as the second, the only difference being the source data. Can anyone tell me why this is happening? I have provided the code below which I edited to fit my needs. The three subforms are "ContactInfo", "StudentsInHH", and "TechInHH".

Private Sub Command44_Click()
On Error GoTo Command44_Click_Err
Me.ContactInfo.SetFocus
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdCopy
Dim xlapp As Object
Set xlapp = CreateObject("Excel.Application")
With xlapp
.Workbooks.Add
.ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
.Cells.Select
.Cells.EntireColumn.AutoFit
.Visible = True
.Range("o1").Select

Me.StudentsInHH.SetFocus
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdCopy
.ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
.Cells.Select
.Cells.EntireColumn.AutoFit
.Visible = True
.Range("r1").Select

Me.TechInHH.SetFocus
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdCopy
.ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
.Cells.Select
.Cells.EntireColumn.AutoFit
.Visible = True
End With
Command44_Click_Exit:
Exit Sub
Command44_Click_Err:
MsgBox Error$
Resume Command44_Click_Exit
End Sub

Take a look at his set opf code Healey21, I have look at using a macro to go into a the subform and select a control, then used the runcommands to select all records in view, then used a copy command. I have added some code to open excel, place in a new book and paste it in and autofit the columns

I hope it helps you

Private Sub Command13_Click()
On Error GoTo Command13_Click_Err
Me.frmCombinedSearchEngineFilter.SetFocus
DoCmd.GoToControl "Policy Ref"
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdCopy
Dim xlapp As Excel.Application
Set xlapp = CreateObject("Excel.Application")
With xlapp
.Workbooks.Add
.ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
.Cells.Select
.Cells.EntireColumn.AutoFit
.Visible = True
.Range("a1").Select

End With

Command13_Click_Exit:
Exit Sub
Command13_Click_Err:
MsgBox Error$
Resume Command13_Click_Exit
End Sub