Exporting filtered access Datasheet to Excel spreadsheet - No truncation

[Jerry Maiapu]

I have been working on a project and one of the things my client wanted was to have a nice formatted duplicate excel spreadsheet of a manually filtered sub form datasheet with all records exported including memo.

I at first struggled but have come up with the following code which I wish to share as I know that people will need it for their database knowledge and projects.

One of the main issues faced currently on exporting is “truncation” of memo fields from access to excel.
However, this will not be experience with my code, because an instance of excel application object is created and the data on the datasheet is basically copied and pasted on the spreadsheet.

I have experimented with 500 words (approx. more 2000 characters in memo field) and all exported and intact in cell.

Hope you will love using my code.

Note: I have tried with “DoCmd.TransferSpreadsheet” and “DoCmd.OutputTo” but has caused a nightmare for me.

But there are different experiences and people might work around these 2 procedures, did not work well for me.
Happy programming!

Cheers!

Jerry

Option Compare Database
'*******************************************
'Author: Jerry Maiapu
'email: [e-mail]
'Please do not remove the author's name
'This is code basically copies filtered records from a subform dataseet to an excel spreadsheet
'There are a few basic formating applied once exported to Excel
'Decided to share this as I have seen many people asked questions in reagrds to access to excel data export
'Note that with this code, memo fields will not be trunculated..
Option Explicit
Private Sub export_Click()
Me.usn_subform.SetFocus                'line 1: Selects the subform
Me.usn_subform!Item.SetFocus           'Line 2: sets the focus in the first field/record in the subform
DoCmd.RunCommand acCmdSelectAllRecords  'Select all the records-ie including filtred records
DoCmd.RunCommand acCmdCopy              'Copy the selected record
Dim xlapp As Object
 
Set xlapp = CreateObject("Excel.Application") 'create an excel application object
With xlapp
.Workbooks.Add 'add new workbook in the excel
.ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False ' Line 10
'Line 10: paste the copied records,not as a link please
.Cells.Select 'now select all cells in excel
.Cells.Entirecolumn.WrapText = True 'wrap text in all cells
.columns("N:N").ColumnWidth = 60
.columns("M:M").ColumnWidth = 90
.columns("O:O").ColumnWidth = 90
.columns("A:L").ColumnWidth = 18
.columns("A:A").ColumnWidth = 8
.columns("G:I").ColumnWidth = 10
.columns("R:R").ColumnWidth = 13
'the above does this: More spaces needed in columns N&M  while less space needed in A & G to I
 
.Cells.rows.AutoFit 'applying auto fit feature for rows
.selection.AutoFilter 'Apply autor filter
 
'***************************************************************************************
'Now loop through the rows starting from row 1 to 19 which is A1 to S1 and apply formating as below
Dim i As Integer
For i = 1 To 19
.Cells(1, i).Font.Bold = True
.Cells(1, i).Font.ColorIndex = 3
.Cells(1, i).Interior.ColorIndex = 37
 
Next 'end of loop
'****************************************************************************************
 
.worksheets(1).Cells(2, 2).Activate ' make cell B2 as the active cell
.activewindow.freezepanes = True 'Now freezepanes from the active cell B2
.Visible = True
.range("a1").Select 'If for some reason if other cells are selected please select A1 as am now done.
 
End With
 export_Click_Exit:
Exit Sub
 export_Click_Err:
MsgBox Error$
Resume export_Click_Exit
End Sub

You just need to create a button on the main/parent form (in this case is export) and simply copy and paste the code above.

Remember to change the subform name, the subform field name and the cmd button name as per underlined in code.

[nazero]

I have searched online for a while for this piece of information and never found a solution for one of my projects. Thank you Mr. for this helpful article.

It did helped me so much.

[lemon]

Exporting custom filtered query and datasheet subform was one of the frequently asked questions on the forums and I appreciate your valuable input in sharing this.

I have seen a lot of hints and articles on this particular matter of subject (truncation) but never really found one solution because i had to put pieces together to solve the puzzle.

This is indeed is a helpful article and code.

Thank you so much for sharing Sir.

[nazero]

I am definitely sharing this link on many of the forums.

10Q

[lavenderchan]

This code works great. However, I need to add this sheet to a template and I am having a hard time trying to modify it to copy to a worksheet in an existing workbook file. I was hoping you could help me.