473,889 Members | 1,692 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Exporting filtered access Datasheet to Excel spreadsheet - No truncation

Jerry Maiapu
259 Contributor
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.Transfer Spreadsheet” 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
4 37143
nazero
2 New Member
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
lemon
1 New Member
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
nazero
2 New Member
I am definitely sharing this link on many of the forums.

10Q
Apr 25 '14 #4
lavenderchan
1 New Member
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

Sign in to post your reply or Sign up for a free account.

Similar topics

1
1582
by: Kaos99 | last post by:
Is there anyway of colour coding values in a report for given criteria and then exporting this formating into an Excel spreadsheet. What I have is a table full of details that are being changed daily. I have a report to list these changes to give to a customer on a monthly basis but as there are 500+ changes per month, this is somewhat cumbersome. What I was planning to do, was to have certain fields that have change in a given month...
2
2036
by: cjay85 | last post by:
I wish to direct reports that I have created in Access to Excel enabling me to produce a graphical output from Excel. The report is a simple query regarding responses to a letter mailing. There are two reports 'Reply Recieved' and 'Reply Not Recieved'. In an ideal world I would like to be able to create a graph showing the percentage of replys recieved against non-replys. Many Thanks
1
5749
by: garry.oxnard | last post by:
Can anyone help me to solve a problem which involves switching from Access to Excel (then back to Access) programatically please? I have an Excel template which, on open, also opens an Access database - containing a list of addresses. Sequence of events is = (1) Excel template opens in its default XXX.xls filename. (2) Code runs to save the spreadsheet as XXX.xls. (3) User clicks a button to open an Access database containing an
0
1592
by: Slicemahn | last post by:
Hi Everyone! I have a spreadsheet in which I would need to update daily and have the new record added to a table in Access. Any ideas on writing code to update the d-base with the new data? Many thanks. C
2
7660
by: nofear | last post by:
I used to export my reports as snapshot but now I have to export them to Excel When I export my report to a Excel Spreadsheet the report header and footer are not included Only the data gets exported. How do I format a Access report for Excel the right way?
7
4962
by: tasmontique | last post by:
Hi All, I have finally succeeded in exporting to a preformated excel spreadsheet. I have one tiny setback. One of the sheets I am exporting to must be password protected. When I do this and export I am prompted for the password. This defeats the point.
2
19332
by: atlbearcat | last post by:
Here's one that's been bugging me for about a week now... I have a form that allows users to filter records, simple enough. But I want to give them the option to export the filtered records to Excel. I don't want to use the docmd.outputTo due to it won't filter the records, it puts all of the records in the file. I've looked around and found some code (actually that I'm already using), but the problem is that it outputs EVERYTHING on the...
1
3482
by: stingaway | last post by:
Have an end user who prefers to see everything in Excel, neatly grouped. I know how to export to Excel - but is there a way to export a query from Access directly into Excel with groupings without having to manually establish groups inside the excel spreadsheet? (ie selecting rows of data groupings and hitting shift-alt-right or selecting the 'group' button) Example: Query data looks like Group1 Info Info Info Group1 Info Info Info
14
2980
by: fkbodley | last post by:
I am trying to upload a filtered form to excel. Using code someone posted to me I tried to make this work. But like all great code for a newbee.. IT DOSENT WORK! Can some one help me out. Code: Private Sub Excel_Click() 'Be sure to set your References vis Tools on the Menu Bar to 'Microsoft Excel 10.0 Object Library or to what ever 'version excel you are running Dim rs As Recordset Dim intMaxCol As Integer Dim intMaxRow As...
4
9194
by: Dave Smith | last post by:
Iíve read a lot of different article on the internet about being able to export a table from MS Access to Ms Excel. Problem Iím having is that I donít know which one would work best for me, so I thought since Iíve gotten such great advice from this form I will ask. I have a query that updates a table in access. What I would like to have happen in the export from access to excel Currently I have a number of queries that updates number...
0
9969
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9810
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11203
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10443
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9612
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development projectóplanning, coding, testing, and deploymentówithout human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7999
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Duprť who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7151
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
4650
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4251
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.