By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,813 Members | 2,269 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Exporting filtered access Datasheet to Excel spreadsheet - No truncation

Jerry Maiapu
100+
P: 259
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



Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. '*******************************************
  3. 'Author: Jerry Maiapu
  4. 'email: jmaiapu@atlantisgoose.com
  5. 'Please do not remove the author's name
  6. 'This is code basically copies filtered records from a subform dataseet to an excel spreadsheet
  7. 'There are a few basic formating applied once exported to Excel
  8. 'Decided to share this as I have seen many people asked questions in reagrds to access to excel data export
  9. 'Note that with this code, memo fields will not be trunculated..
  10. Option Explicit
  11. Private Sub export_Click()
  12. Me.usn_subform.SetFocus                'line 1: Selects the subform
  13. Me.usn_subform!Item.SetFocus           'Line 2: sets the focus in the first field/record in the subform
  14. DoCmd.RunCommand acCmdSelectAllRecords  'Select all the records-ie including filtred records
  15. DoCmd.RunCommand acCmdCopy              'Copy the selected record
  16. Dim xlapp As Object
  17.  
  18. Set xlapp = CreateObject("Excel.Application") 'create an excel application object
  19. With xlapp
  20. .Workbooks.Add 'add new workbook in the excel
  21. .ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False ' Line 10
  22. 'Line 10: paste the copied records,not as a link please
  23. .Cells.Select 'now select all cells in excel
  24. .Cells.Entirecolumn.WrapText = True 'wrap text in all cells
  25. .columns("N:N").ColumnWidth = 60
  26. .columns("M:M").ColumnWidth = 90
  27. .columns("O:O").ColumnWidth = 90
  28. .columns("A:L").ColumnWidth = 18
  29. .columns("A:A").ColumnWidth = 8
  30. .columns("G:I").ColumnWidth = 10
  31. .columns("R:R").ColumnWidth = 13
  32. 'the above does this: More spaces needed in columns N&M  while less space needed in A & G to I
  33.  
  34. .Cells.rows.AutoFit 'applying auto fit feature for rows
  35. .selection.AutoFilter 'Apply autor filter
  36.  
  37. '***************************************************************************************
  38. 'Now loop through the rows starting from row 1 to 19 which is A1 to S1 and apply formating as below
  39. Dim i As Integer
  40. For i = 1 To 19
  41. .Cells(1, i).Font.Bold = True
  42. .Cells(1, i).Font.ColorIndex = 3
  43. .Cells(1, i).Interior.ColorIndex = 37
  44.  
  45. Next 'end of loop
  46. '****************************************************************************************
  47.  
  48. .worksheets(1).Cells(2, 2).Activate ' make cell B2 as the active cell
  49. .activewindow.freezepanes = True 'Now freezepanes from the active cell B2
  50. .Visible = True
  51. .range("a1").Select 'If for some reason if other cells are selected please select A1 as am now done.
  52.  
  53. End With
  54. export_Click_Exit:
  55. Exit Sub
  56. export_Click_Err:
  57. MsgBox Error$
  58. Resume export_Click_Exit
  59. 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.
Jul 14 '12 #1
Share this Article
Share on Google+
4 Comments


P: 2
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.
Apr 25 '14 #2

P: 1
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.
Apr 25 '14 #3

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

10Q
Apr 25 '14 #4

P: 1
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.
Mar 23 '17 #5