Hi all,
I have been using this command in VB to export Access tables to Excel. Everything works fine except when the table contains a large Character/Text field- it gets truncated to around 200 chars. Is this a known limitation or I am doing something wrong? DoCmd.TransferSpreadsheet command works correctly but there are some limitations to this command.
Can please somebody help
Thanks
Claude
I want to run a report, I need to provide an option to export the output to an excel file or provide a print view. Where would I put the DoCmd.OutputTo command in the report to acheive this??
thanks
Hi all,
To export a file to MS excel from my form, i used the command
DoCmd.OutputTo acOutputQuery, langName & partName, acFormatXLS, exportPath, False
however, i realised that the format of the excel spreadsheet (excel version 5.0/95) that i have exported is slightly different from the format of the spreadsheet if i had exported by clikcing on FILE, EXPORT...
how can i specify the excel version for the exported file? i wan it to be in the latest excel version else i am unable to perform some of the marcos i have written in the latest excel format?
Thks FT
When I export a report to Excel, a memmo field is truncated to 256 characters. If I export the query behind the report, the memmo field is exported correctly. Is there a way to export an Access 2003 report to Excel and maintain all of the data and report formating in memmo fields?
View 1 Replies View RelatedTwo Solutions to address moving an Access Memo field into Excel when string has > 255 characters. All my 'reports' use Excel VBA (Access Reports are not used). The Excel reports can have 40,000 records. Speed to create the report can be an issue.
Describing 2 Solutions below to address moving Access memo fields with > 255 characters into Excel.After running this code
Code:
720 ObjXL.DisplayAlerts = False
ObjXL.Columns("X:X").Select
ObjXL.Selection.NumberFormat = "@" ' set column to Text
730 ObjXL.Worksheets(intWorksheetNum).Cells(intRowPos, 1).CopyFromRecordset rsNutsAndBolts
The Comments column are limited to 255 characters. So, the CopyFromRecordset (recordsetvariable) creates the 255 character limitation.
The reason? The 255 character limit is because CopyFromRecordset sutomatically uses the Characters property of the Range object. The 255 limit would not be there if the Cell Value property is used to assign the string to that cell.
Dim sRx as String ' String Prescription
sRx = "String with > 255 characters ... you fill in the rest ...."
Cells(1, 1).Value = sRx ' Cell's Value property assignment can be very large
Solution 1:
The record set is still in memory. By using a loop, a cursor can start with record 1 (memo column) and assign that value to the Excel row / column using the .value as shown above. Basically, this moves one memo field at a time, record by record. e.g. Read First recordset in Access, copy to variable, assign value to Excel row/column Then move next on each Access and Excel.
Solution 2:
An Access Memo filed [RxNotes] can have up to 750 characters. Cut it apart into three new fields that end up out in the very right Excel columns AA, AB, AC.
Note1=Mid([RxNotes],1,250)
Note2=Mid([RxNotes],251,250)
Note3=Mid([RxNotes],501,250)
Then using Excel Object - Concat the cells back cell by cell...
X2=CONCATENATE(AA2,AB2,AC2))
Then delete the columns AA, AB, AC to hide the evidence..Neither solution is all that elequent. Read about this and by golly, it made a difference
ConcatComments = "'" & CommentString
Before using the CopyFromRecordset be sure to add a single quote in front of the large string.
Turns out the interface between Access and Excel look for this to prepare Excel immediately for the string to be a string, not something else. Some of my strings had weird print characters that kind of looked like Japenese characters. It seemed random, it always happened if the string was 255 or more characters (ramdonly, not always). The single quote doesn't show up in Excel, but got rid of all the noise.
I'm having an issue where when I attempt to export data from an Access database to an excel spreadsheet using VBA it truncates any field longer than 255 characters to the 255 limit. I'm using
Code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "tableName", "FilePath", True, ""
to export it (obviously with the table name and filepath filled in) but for some reason I cannot get it to export the entirety of the field to excel. I've been doing some digging on various forums around the internet and it seems as though it may be possible to split it into various excel cells then use automation to concatenate the cells. But considering this field I am trying to get not truncated can be up to 40,000 characters theoretically, it doesn't make sense to do it that way.Do you need more information from me? I'm somewhat new to both Access and VBA.
I created a Union Query for several linked Excel tables. Certain fields in the Excel table exceed 256 characters and Access (rightfully so) assigns these fields as "Memo". I have create a report based upon the Union Query; however, it will truncate the "Memo" fields to 255 (or 256 characters).
As a side note, if I create a report based upon a simple query using only one of the linked tables, it does not truncate the field.
Any suggestions on what maybe causing this truncation issue?
hi
Would be glad if somebody could help me on this issue that been nagging a set a databases that we use.
The Access databases that we use are around 200 MB in size..
Its multiuser say maybe around 40-60 users.The database is split to enhance performance.
We use DoCmd.OutputTo ...statement to output the query into Excel sheet.
Example :
DoCmd.OutputTo acOutputQuery, "qryAllPrems", acFormatXLS, , False
The problem that we are facing is that the after the execution of the statment the query ( qryAllPrems) gets wiped out....i.e the entire sql in the query gets wiped out clean ..
It's driving us crazy as to what could be the cause of the problem...and I would appreciate it if somebody could help us..in this regard.
Hello all,
I have always been wondering why this happens, and now I have a problem with it.
When I export a Table/Query from Access to Excel (be it with Right CLick -> Export...or TransferSpreadsheet) there is an apostrophe character (') appended to the front of some/each cell in Excel. You cannot see it immediately, but when you click on the cell, there is this character. WHY? Does anyone else have experience in this? :confused:
Thanks in advance.
Here is what I am trying to do. I have a query with 2 fields. "Time In" & "Time Out". What I would like to happen is this. Whenever a character, let's say a "t", is entered into that field I would like the current time to populate that field. Right now we are actually typing in the time. I have the fields set up as DateTime fields currently.
View 10 Replies View RelatedHi!
I have a field that has to be 20 Characters long. Most of the time data for this field is less than 20 characters, so the remaining has to be filled with spaces to make it 20. e.g. if there is a record xyz1234567, then the rest of the record should have 10 spaces like xyz1234567ssssssssss, meaning if i click on this record then the cursor should not blink after 7 but after the spaces. Hope I am making sense.
Thanks for any input.
I have a field which looks like this - "d123456", I will have to display the field without the "d" in front, meaning I will need to show "123456" Any idea how I can do this? Thanks!
View 4 Replies View RelatedI ran into errors recently when I tried running a large UNION query (about 6,900 news articles) in a desktop Access database, that will eventually be migrated to SQL Server. I've got two tables, each having the same typed fields. I joined then through the following query:
SELECT ID,Name,Body FROM Table1 UNION SELECT ID,Name,Body FROM Table2;
The problem was that in the resultant table containing the conjoined records, one of the fields (Body, a MEMO field) copies only the first 250 characters or so, truncating the rest of the data.
I was thinking this might have something to do with telling Access how to type the data when copying it over. Is there a way to explicitly tell a query the data type of each field to be used so that the data can be copied over properly?
Thanks!
I have a large table (over 20,000 records) where the text fields were all set at 255 - even those requiring a single character entry. Is there a way to determine the highest existing character count for each field so I can set the text fields to a reasonable setting? After a compact and repair will existing records be set to the new setting?
View 1 Replies View RelatedI am trying to find the position of a "," in a data field.
I was trying the SEARCH function I found in Help "SEARCH(',',[emplname])", but I am getting "Undefined function 'SEARCH' in expression." when I run the query.
Any ideas?
Thanks,
Jeff
I need help with a required field that has different character lengths.
I have an input mask that covers the first 7 characters, however, the users are still entering the incorrect policy numbers, I need to put a stop to it.
I have 3 different policy number lengths involved, 7, 8 and 9.
They are different kinds as well, alpha, alpha/numeric and numeric.
Example below:
P###### - 7 characters
02###### - 8 characters
02M##### - 8 characters
NX####### - 9 characters
HX###### - 8 characters
HXL##### - 8 characters
HXM##### - 8 characters
My question is this -- do I have to create 7 different forms or can I program it to accept only the certain (policy#) characters above. Or can I create a query to insert into my form.
Any help is appreciated, Thanks as always.
I need help with a required field that has different character lengths.
I have an input mask that covers the first 7 characters, however, the users are still entering the incorrect policy numbers, I need to put a stop to it.
I have 3 different policy number lengths involved, 7, 8 and 9.
They are different kinds as well, alpha, alpha/numeric and numeric.
Example below:
P###### - 7 characters
02###### - 8 characters
02M##### - 8 characters
NX####### - 9 characters
HX###### - 8 characters
HXL##### - 8 characters
HXM##### - 8 characters
My question is this -- do I have to create 7 different forms or can I program it to accept only the certain (policy#) characters above. Or can I create a query to insert into my form.
Any help is appreciated, Thanks as always.
I have a field called "SC" with a value 13-251. I need to remove the "-" ending up with 13251. I tried using, as a starting point, Mid(String, Start, Length) with no success. I found this in another topic, and thought this might be close to what I was looking for. I sure did not know how to use it. When I selected the run command I got the following response:
Query must have at least one destination.
I tried a few different things like adding an extra field and different pointers but I doubt if I'm even standing in the batters box.
I have a column in my table, and its size is set to 10 characters. In a form, i have a combobox based on that column, which contains the values "Corrective", "Preventive", and "Supplier". both Corrective and Preventive are 10 characters, and fill up the field to its max, however, Supplier is only 8 characters. When I look back in the table data, there are two spaces after Supplier. Basically, I can use the arrow keys to "see" the two blank character spaces. Why is that? Are these extra blank spaces taking up space they shouldn't?
View 3 Replies View RelatedHi guys,
I’m working on a report that I want to show on the web as a Snapshot file. I created a Macro using OutputTo method to export the report. If I type the file name and location on the Output File it works perfectly, but the problem is that I want Macros to read the file name from a combo box since the file name will change every day. Here is the code I got so far.
C:Test””&FORMS!frm_FORM2!cbo_Name&”.snp”
Every time I run this Macro I got the following message:
“The report snapshot was not created because you don’t have enough free disk space for temporary work files.”
Now!, when I type the file name to that specific location Macro does the job perfectly.
Is there any way I can get the File name from a combo box located in FORM2.
Thank you so very much in advanced. Your help is always a blessing.
Mosquetero
Hi,
I am having trouble with the OutputTo function. I woul like to Output a report in html format but I would like Access not to record the file under the same name each time. So if my file is named 1_report.html I would like the second time I run the OutputTo function that it is name 2_report.html. Or as an alternative have the name include data from a form/table (In my case an order number).
I have a sample of the code from the converted macro:
Code:'------------------------------------------------------------' Output order to file''------------------------------------------------------------Function EnvoyerCommande_EnvoyerCommande()On Error GoTo EnvoyerCommande_EnvoyerCommande_Err ' Envoi la commande DoCmd.OutputTo acReport, "EnvoiCommande", "HTML(*.html)", SaveAs, False, ""EnvoyerCommande_EnvoyerCommande_Exit: Exit FunctionEnvoyerCommande_EnvoyerCommande_Err: MsgBox Error$ Resume EnvoyerCommande_EnvoyerCommande_ExitEnd Function
Note: here I have used the 'SaveAs' instead of specifying a location. This is not really what I want as I would prefer no user interraction...
Thanx for any help you can provide
See ya
In my table, I have a field for "notes" and have it set to long text. It works just fine and has all the text visible. When I create a report or a form from this table, it truncates the text in that field. It limits to the 254 characters of old. I am not sure how to fix this. I have tried setting the WillGrow to yes and it still does not work.
View 4 Replies View RelatedHi guys & gals,
I recently came across this really strange behaviour and was wondering if anyone else knows this bug or knows a better workaround than mine.
I've attached a little Events DB, which has a search interface. I've included an export button, which runs a macro that does an OutputTo (xls) of the search query.
Now if you do an export once, everthing is fine. The bug (at least I think it's a bug) occurs if you export a *second* time and *overwrite* the file you created earlier. The search query (which sits under the "Queries") tab is now empty and you get an error message.
My workaround idea was to dump the query to a new table every time you export and then do an OutputTo with this table. Not very elegant and you also have to do the whole error handling (e.g. user presses cancel) manually.
Does anyone have a better idea? Or am I doing somethng completely wrong and this is actually "by design"?!
Thanks in advance for any ideas!
Some of my users do not have access to all of our servers. In trying to automate I've hit one user whose system hangs up when Access tries to write to a server folder she does not have write (or even read) permissions for (Error 52, Bad file name or number). See the "Me.CitationType > 500" line below.
Code:
Private Sub comboStatus_AfterUpdate()
Dim hDate As String
Dim sFile As String
[Code]....
It gets as far as "If Len(Dir(hDate, vbDirectory)) = 0" and then hangs with Error 52. Testing for Dir() sooner doesn't work either.
how to either test for read-write permissions or trap Error 52. "On Error GoTo ErrorHandler" never fires to even test for 52, unless I'm missing something.
I am trying to export the data from a form to a textfile by using the following simple piece of code:
Private Sub Command188_Click()
DoCmd.OutputTo acOutputForm, "CC_MAIN_PROJ"
End Sub
it works, but gives me the data like this:
| SPEC | SIZE_1 | SIZE_2 | C1_s | C2_s | APS1 | APS2 | BPS1 |
what I really want is for the data to be without all the bars "|" and dashes etc. and even without headers. I just want a straight text output, maybe delineated by spaces or commas only.
How can I adjust my required field so that you cannot simply enter a space bar character to circumvent the requirement?
Here is part of the code:
If IsNull([txtRequiredReason]) Then
MsgBox "The Reason field is required"
DoCmd.GoToControl "txtRequiredReason"
Else