hi all,
I'm using Access 2007 and when I'm trying to export a query by using DoCmd.transferspreadsheet. I get my memo field truncated into 255 character.
I searched the web for answers and I did see many, but nothing helped me.
Is there any simple solution to get rid off this pain in the...
just pack of facts for your knowledge:
1. I can see full text(memo) in my query.
2. when I try to export query by GUI it works like a charm.
3. my export isn't simple, it exports a sheet by value.(so I can't really use GUI-export).
4.I've tried use DoCmd.OutputTo (and it worked fine) but it didn't worked out with the sheets separation (3).
thanks for any kind of help
Uriya.
@Uriya:
Let's suppose that you have a fictitious Field named [Notes] in your Database, and that it is a MEMO Field. The following Query will split that Field into 5 Segments with Field Names Notes1...Notes5. This Query can now be Exported to Excel, where the Fields can then be reassembled via the CONCATENATE() Function which can be found in the Help Files. -
SELECT Mid([Notes],1,250) AS Notes1,
-
Mid([Notes],251,501) AS Notes2,
-
Mid([Notes],501,250) AS Notes3,
-
Mid([Notes],751,250) AS Notes4,
-
Mid([Notes],1001,250) AS Notes5
-
FROM tblTest;
-
11 8093 NeoPa 32,556
Expert Mod 16PB
It's a known limitation. What do you want? I would have thought this this would be understood after reading about it as you have.
I know little of your design but all I could suggest would be that you stopped using the field as a Memo.
This is a well known problem with Exporting MEMO Fields in a Query to Excel. There are a couple of things you can try to circumvent this behavior: - Export to Excel 2000 Format, namely:
- DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryNotes", CurrentProject.Path & "\Notes.xls"
- Break the Field into Segments, perform the Export, and then reassemble in Excel, namely:
-
'Divide Field into 250 Character 'Chunks'
-
Notes1: Mid([MEMO Field],1,250)
-
Notes2: Mid([MEMO Field],251,501)
-
Notes3: Mid([MEMO Field],501,250)
-
Notes4: Mid([MEMO Field],751,250)
-
Notes5: Mid([MEMO Field],1001,250)
-
'etc...
-
- In Excel, reassemble:
-
=CONCATENATE(B2,C2,D2,E2,F2)
thanks for reply ADezii, NeoPa.
NeoPa- I have to use memo cuz it need to contain more then 255 chars.
ADezii- the first solution you gave much more possible for me (im quite a novice). but I do an export with - DoCmd.TransferSpreadsheet
and using acSpreadsheetTypeExcel9.
and it doesnt work...
NeoPa 32,556
Expert Mod 16PB
Have you considered ADezii's second option? Whereby you output multiple fields for the single Memo field in your table.
If this really is your requirement then it may be worth the extra complication of such a solution. Application Automation should enable you to merge these fields into a single column in the spreadsheet after the TransferSpreadsheet command has completed.
I admit I have no knowledge why Excel 2000 might handle it differently from other versions, but if ADezii says it's worth a try then it probably is.
well I don't mine to try ADezii's second options but I don't know how to integrate it with my code(cuz I don't no VBA so well)...I don't mine to share it so you could help me...
NeoPa 32,556
Expert Mod 16PB
You'll need to be clearer than that and ask for help properly. Expecting this to be done for you is not a good approach. There are threads already explaining this but you need to ask for specific help if you want assistance (Assistance <> doing it for you).
Neopa, I'm sorry that you think that Im expecting someone will do the work for me. I've never tried to even imply this. :)
the only thing Im trying to do is to find any solution here. I don't have a lot of experience with VB as I mentioned. so yes I would like to get some comprehension to help solve this issue
thanks for replying.
Uriya
@Uriya:
Let's suppose that you have a fictitious Field named [Notes] in your Database, and that it is a MEMO Field. The following Query will split that Field into 5 Segments with Field Names Notes1...Notes5. This Query can now be Exported to Excel, where the Fields can then be reassembled via the CONCATENATE() Function which can be found in the Help Files. -
SELECT Mid([Notes],1,250) AS Notes1,
-
Mid([Notes],251,501) AS Notes2,
-
Mid([Notes],501,250) AS Notes3,
-
Mid([Notes],751,250) AS Notes4,
-
Mid([Notes],1001,250) AS Notes5
-
FROM tblTest;
-
NeoPa 32,556
Expert Mod 16PB Uriya:
I've never tried to even imply this
Nevertheless, you managed to without trying.
As I said in an earlier post there are threads which explain how you should ask questions and if you'd read any of them (instead of simply denying you'd got anything wrong) you would have read how you should be posting the work you have already done in order to : - Indicate you have already done or attempted some work on your own.
- Give the experts a context within which to work. There are often many ways to handle a problem and knowing which you are thinking about makes any answer more appropriate to the question.
- Give various details often missing from the explanation such as object names. A properly asked question wouldn't absolutely need this, but very few questions are asked well enough for that. This is no exception.
Uriya:
4.I've tried use DoCmd.OutputTo (and it worked fine) but it didn't worked out with the sheets separation (3).
If you show us this code I'm sure we could deduce where you're coming from and help you to formulate something more appropriate from it. We can also help you with instruction when we have a better feel for exactly what you do and don't understand.
ADezii, I've tried what you've suggested but I didn't know how to do it right (I added it in this post)...
I did a little research and I find out the transferspreadsheet does not export to excel 2007 (*.xlsx) and outputto method can't do multiple worksheets...
there is any workaround for exporting access 2007 queries into excel 2007?
my code : -
Option Compare Database
-
Option Explicit
-
-
Sub cmdExport_Click()
-
Dim strEnts As String, strSQL As String, strFName As String, skelFN As String
-
Dim cdb As DAO.Database
-
Dim varEnt As Variant
-
Set cdb = CurrentDb
-
-
On Error GoTo Error_cmdExport
-
-
strFName = CurrentProject.Path & "\Hoveret_Milgot.xlsx"
-
skelFN = CurrentProject.Path & "\skel\skel.xlsx"
-
strEnts = ""
-
FileCopy skelFN, strFName
-
-
strSQL = "SELECT DISTINCT [entitled] FROM [scholl]"
-
With cdb.OpenRecordset(strSQL, dbOpenSnapshot)
-
Call .MoveFirst
-
Do While Not .EOF
-
strEnts = strEnts & "," & !entitled
-
Call .MoveNext
-
Loop
-
Call .Close
-
End With
-
strEnts = Mid(strEnts, 2)
-
-
For Each varEnt In Split(strEnts, ",")
-
If Len(CStr(varEnt)) = 0 Then
-
GoTo Continue1
-
End If
-
Call DoCmd.CopyObject(, varEnt, acQuery, "query1")
-
-
Set cdb = CurrentDb
-
With cdb.QueryDefs(varEnt)
-
strSQL = Replace(.SQL, ";", "") & "WHERE [entitled] = '%E'"
-
.SQL = Replace(strSQL, "%E", varEnt)
-
Application.RefreshDatabaseWindow
-
-
End With
-
Call DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel12Xml, varEnt, strFName, True)
-
'DoCmd.OutputTo acOutputQuery, varEnt, acFormatXLSX, strFName, False
-
-
DoCmd.DeleteObject acQuery, varEnt
-
-
Set cdb = Nothing
-
Continue1:
-
Next varEnt
-
-
Call MsgBox("finished", vbOKOnly, "Excel export")
-
Exit Sub
-
-
Error_cmdExport:
-
MsgBox "Error {" & Err & "}" & vbNewLine & Err.Description, vbOKOnly Or vbExclamation, "cmdExport"
-
-
Title:="cmdExport")
-
End Sub
-
what my code does is it get every entitled (distinct) in scholler table, and by it create a query (that contain some fields (including MEMO type)) and then export it (or trying to) into xlsx file in different sheet.
got it fixed!!!!
after I used ADezii's solution, I find that in my select I get a lot of HTML tags (such as <div>, <font> ,etc) so I went to my MEMO feild settings and change 'Text pattern' from HTML to SIMPLE
and it works. now I can export with TransferSpreadsheet more then 255 words.
thank all for the support,
Uriya
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Shyguy |
last post by:
I want to create two buttons on a form. One would allow the user to
Copy the contents of the current records memo field, the other would
allow them to print. I set up a report based on the memo...
|
by: GorDon |
last post by:
Hi,
I have a report based on a query. The query grabs a memo field from
my main table, yet when I display the memo field in the report it
truncates the memo field (the field needs to hold more...
|
by: user_5701 |
last post by:
Hello, I'm getting an error with a Docmd.Transferspreadsheet line of
code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel2000,
"tblTest", pathAndFilename, True
The above line...
|
by: lesperancer |
last post by:
I've read all the posts, but nothing works
I've got a table with an ID field and a memo field
one record in the table, with the memo field having 322 chars
I open the table, and then use the...
|
by: aleksandra_83 |
last post by:
Hello,
I have searched all over google groups and internet, but I have not
found a solution to this problem that has worked. The solutions I found
helped me single out the line that is causing...
|
by: Taffman |
last post by:
I've searched this goup for an answer to this, there are many
discussions that come close but non that I can find that actually
addresses this particular problem.
I'm exporting queries to Excel....
|
by: KMLEditor |
last post by:
Reports may show only 255 characters of a memo field.
1) On the report, remove the formatting from the memo field textbox if
any.
2) On the underlaying query, make sure that Unique Values is set...
|
by: johnvonc |
last post by:
I am using Access 2003, and trying to Export query results in code to
a csv file.
I am using DoCmd.TransferText. When I run the query, there are two
memo fields: Description and...
|
by: blammo04 |
last post by:
My problem is that Access freezes up whenever I try to use the docmd.transferspreadsheet method, I can use the docmd.outputto and it works fine but I need to be able to use the transferspreadsheet...
|
by: Van Fitz |
last post by:
I am importing an excel worksheet into an existing access 2000 table using TransferSpreadsheet. Im using a timestamp field as well in this table. This is going well, however, once the data is...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |