VBA DoCmd.TransferSpreadsheet acExport... Sort Order

  • Permalink
  • submit to reddit
  • Email
  • Follow


I have a table whose fields are sorted in a certain order.  Is there a
way to retain or specify the sort order when using
DoCmd.TransferSpreadsheet acExport... to export to a spreadsheet?
Thanks, Sheldon Potolsky

0
Reply SHPsalm139 (70) 7/26/2007 5:46:15 PM

See related articles to this posting


Sheldon wrote:
> I have a table whose fields are sorted in a certain order.  Is there a
> way to retain or specify the sort order when using
> DoCmd.TransferSpreadsheet acExport... to export to a spreadsheet?
> Thanks, Sheldon Potolsky
> 

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Instead of transferring the table, use a query to retrieve the data in
the sorted order you want and transfer the query.
-- 
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup.  I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRqkE8YechKqOuFEgEQIvpgCgwYQi9bk0AF/rI4LRrKY7u3xmt70AoN65
xA5A2ILnLD/I9qv5uT9l7ype
=HFPW
-----END PGP SIGNATURE-----
0
Reply me2968 (1473) 7/26/2007 8:31:59 PM

On Jul 26, 4:31 pm, MGFoster <m...@privacy.com> wrote:
> Sheldon wrote:
> > I have a table whose fields are sorted in a certain order.  Is there a
> > way to retain or specify the sort order when using
> > DoCmd.TransferSpreadsheet acExport... to export to a spreadsheet?
> > Thanks, Sheldon Potolsky
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Instead of transferring the table, use a query to retrieve the data in
> the sorted order you want and transfer the query.
> --
 Would the syntax be the same except for substituting the SELECT?
query name instead of the table name?
e.g. Would
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"tblAbsenceNotesSent", InputFileHSMS & "AllLetters.xls", True
become
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query1",
InputFileHSMS & "AllLetters.xls", True
Sheldon Potolsky


0
Reply SHPsalm139 (70) 7/27/2007 5:29:49 PM
comp.databases.ms-access 42289 articles. 10 followers. Post

2 Replies
454 Views

Similar Articles

[PageSpeed] 12


  • Permalink
  • submit to reddit
  • Email
  • Follow


Reply:

Similar Artilces:

Sort order of a sorted array
With the PHP command 'sort' I can sort an array. However, it doesn't exactly sort the array I'd like to: <html> <body> <?php $my_array[0] = array("AA", "11"); $my_array[1] = array("AA", "22"); $my_array[2] = array("aa", "22"); $my_array[3] = array("AB", "11"); $my_array[4] = array("ab", "11"); $my_array[5] = array("ab", "22"); $my_array[6] = array("�b", "11"...

Sort order of a non-sorted dataset?
Dear all, Take the following example: DATA chk; DO patid=1 TO 20; DO visid=1 TO 3; var1=RANUNI(0); var2=RANUNI(5); OUTPUT; END; END; RUN; PROC CONTENTS DATA=chk; RUN; Now this dataset is not explicity sorted, as shown in the PROC CONTENTS. But you can "see" that it is sorted (inherently) by patid and visid. Does anyone know of a way to find out what variables (if any) a Not sorted dataset is sorted by? If it's not possible, just let me know and I'll stop thinking about it... Thanks, Dan One way is to tell SAS that it is sorted by (patid a...

execution order and sorted order in simulink
Hi, I am new to simulink. Is execution order and sorted order are same in simulink. Thanks in advance Naga. On Feb 29, 5:35 am, "Nagaraju boya" <naga...@mathworks.com> wrote: > Hi, > > I am new to simulink. > Is execution order and sorted order are same in simulink. > > Thanks in advance > Naga. No. At a guess, sort order represents the order in which the blocks were created in a model. Execution order is self explanatory. -Chris checker <checkeraz69@yahoo.com> wrote in message <cfcb1dd3-8c41-463f-95f5-751630ca8cc4@s8g2000prg.googlegroups...

[B,IX] = sort(A,...)
Dear community, I want to use the sort function to sort a (nested) list. General information can be found below. http://www.python.org/doc/2.4.2/lib/typesseq-mutable.html http://wiki.python.org/moin/HowTo/Sorting http://www.python.org/doc/2.4.4/whatsnew/node12.html I want to solve the following problem. Given a list I do not only want to retrieve the sorted list but also the position of the original elements (IX below). The example is taken from Matlab syntax: http://www.mathworks.com/access/helpdesk/help/techdoc/ref/sort.html '[B,IX] = sort(A,...) also returns an array of indice...

Re: Sort order of a non-sorted dataset?
_NULL_, I guess the question I'm trying to get at is: Is there a way to find these SORTEDBY variables without manually typing them in? Say, if I have 400 datasets and there are all inherently sorted, is there a way to find the SORTEDBY variables? Let me know if you need more information. Thanks for the tip, Dan On Wed, 10 May 2006 10:52:33 -0400, data _null_; <datanull@GMAIL.COM> wrote: >One way is to tell SAS that it is sorted by (patid and visid), using >the SORTEDBY data set option. The SORTED flag is not set but the >sortedby variables are. > >You can still...

Sorted views (was: Sort order change from Mainframe to Windows)
Howard Schreier's post in the "Sort order change from Mainframe to Windows" thread contains a very interesting aspect regarding views and sort: > Howard Schreier <some lines snipped ... RB> > Boston, with an upper case "B", would appear before > "atlanta" in a simple sort. > > So create a view with "hidden" sort keys: > > proc sql; > create view temp_in_report_order as > select * > from temp > order by anydigit(substr(region,1,1) ) > , upcase(region) > ,...

Re: Sort order of a non-sorted dataset? #8
This may be one of the "Why do you want to do that?" questions but why should that stop me. The ERROR: BY variables are not properly sorted on data set ... does not cause syntax check mode so we can systematically look for a BY variable then add it to a list and keep looking for BY variables that sort within the list. Until we have looked at all the variables. If you know there are variables that are defiantly NOT keys they can be dropped with the DROP= data set option, and shorten the searching. I realize my program is rather naive. View with a FIXED font. options noovp; data ...

Re: Sort order of a non-sorted dataset? #4
Here is a data step approach. Certainly it can be updated to handle varying data structures: data work.unsorted; input var1 var2 var3; datalines; 2 3 6 3 4 5 1 5 2 9 7 1 ; run; data _null_; retain old_var1 old_var2 old_var3 var1_cnt var2_cnt var3_cnt ; set work.unsorted end=eof; if var1 ge old_var1 then var1_cnt + 1; else var1_cnt + (-1); if var2 ge old_var2 then var2_cnt + 1; else var2_cnt + (-1); if var3 ge old_var3 then var3_cnt + 1; else var3_cnt + (-1); old_var1 = var1; old_var2 = var2; old_var3 = var3; if eof then ...

Re: Sort order of a non-sorted dataset? #5
Daniel - To expand on Clinton's suggestion, you might create two temporary arrays based on the _numeric_ and _character_ implicit lists. These would span your dataset. Then generalize Clinton's method, except rather than keeping track of all variables across the whole dataset, only continue testing those that continue to have only ordered values. Generally you should expect to find your best candidates after only a few lines of comparisons. hth Paul Choate DDS Data Extraction (916) 654-2160 > -----Original Message----- > From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.ED...

Re: Sort order of a non-sorted dataset? #3
One way is to tell SAS that it is sorted by (patid and visid), using the SORTEDBY data set option. The SORTED flag is not set but the sortedby variables are. You can still do BY processing on the data as is without "knowing". SAS is happy to process your BY statements either way. data chk(sortedby=patid visid); do patid=1 to 20; do visid=1 to 3; var1=ranuni(0); var2=ranuni(5); output; end; end; run; proc contents noprint data=chk out=work.cont; run; proc print; var name sortedby; run; On 5/10/06, Daniel Boisvert <...

Re: Sorted views (was: Sort order change from Mainframe to
Robert: Experts in the database field advise against relying on ordering of the = yield of views. Date cites an example. An ordering by one attribute does = not have one possible result for any one source dataset. As a result, = tests of queries may succeed in one context, given an ordered view, but = fail in another context given that same view. More generally, since SQL = does not require pre-ordering of tuples, nor guarantee that processing = will maintain a sort order, it usually makes more sense to order data = within data display procedures than in a view. I can think of = exceptional si...

Re: Sort order of a non-sorted dataset? #6
And then try to handle major and minor sorts (this would take some time to figure out). A final wrinkle would be ascending/descending. Depending on the size of the data and the number of data sets, all of this could take some time. Being able to make educated guesses based on the source of the data and how it was created will help cut down on the variables to be checked. One additional question: Does it really matter if you know the sort order? Using the unsorted option on BY statements and some additional coding may get you through, as might the parallel CLASS statement. Clint -----Origina...

Re: Sort order of a non-sorted dataset? #7 1553540
Actually my first was reaction was, "open up the file and look!" :) Paul Choate DDS Data Extraction (916) 654-2160 > -----Original Message----- > From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of > Rickards, Clinton (GE Consumer Finance) > Sent: Wednesday, May 10, 2006 10:05 AM > To: SAS-L@LISTSERV.UGA.EDU > Subject: Re: Sort order of a non-sorted dataset? > > And then try to handle major and minor sorts (this would take some time to > figure out). A final wrinkle would be ascending/descending. Depending on > the size of the data an...

Sort order...
I'm attempting to sort my mail so that the last (most recent) message is at the top of my message list. I've attempted to configure this a number of ways, logging out each time in order that the change would take affect. Unfortunately nothing seems to work. I assumed selecting "Sort by" Arrival would put the last message in, at the top of the list. I also tried "Start At", last, and all the reciprocal options, but nothing appears to work. I set "Sort by" from the GUI page, Configure-Message List, then saved it, exited, went back in. Any ideas? ...

DoCmd.TransferSpreadsheet
How do you specify a specific worksheet? I know this works if the worksheet in question is the 1st, but suppose I am interested in importing several worksheets from the same workbook. There does appear to be a way to specify the worksheet by name. Simple, type the spreadsheet name follow by a bang (!)in the range parameter of the transfer spreadsheet command...Use the following syntax as an example to refer to a seperate sheet(this is for an import): DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "YourTableName", [FileName], True, "YourExcelSpreadsheetName!A43:L60&quo...

ORDER BY does not sort
Hi -- I have three tables: one for authors, one for books, and a link table linking the author_id with the book_id. I want to list first each author and then their books, excluding authors that don't yet have books entered. I did this: // Only want to list authors that actually have books in the library $query1 = " SELECT DISTINCT author_id FROM source_author"; $author_ids = mysql_query($query1) or die ("Error in query: $query1 . " . mysql_error()); if (mysql_num_rows($author_ids) > 0) { while(list($a_id) = mysql_fetch_row($author_ids)) { // Get...

sort ordering
is there a list of the sort order used by FM; I need to set a field with a guaranteeded low sort order ("Z" is ok, but clunky). It also appears that if you have a space then char, FM sorts on the Char, wheras other apps, (excel) sorts on the space. -- ------------------------------------------------- Captain Guy s/v Island Time (352#277) AICW 845.5 386-689-5088 On Mon, 03 Apr 2006 17:11:12 GMT, Guy wrote: > is there a list of the sort order used by FM; Yes - it's the indexed sort order. Thus it will depend on the storage/indexing options that you set on this fiel...

Sort Order
Using FM 7.0v3 How can I sort records alphabeticly but have blank fields listed last Gus In article <dsWdnd_qO8hM5QjYnZ2dnUVZ_h2pnZ2d@adelphia.com>, "Old Foggy" <OldFoggy@Foggy.com> wrote: > Using FM 7.0v3 > How can I sort records alphabeticly but have blank fields listed last The easiest way would be to create a new Calculation field that copies the original field's value or if the original field is blank uses something that will alway gets sorted to the end. eg. SortField = If (IsEmpty(OriginalField), "zzzzzzzzz", ...

DoCmd.OutputTo and/or DoCmd.TransferSpreadsheet
Using Access 97. I have experience using VBA code in an MS Access to create a spreadsheet...I know how to use DoCmd.OutputTo (with an acFormatXLS argument) and I know how to use DoCmd.TransferSpreadsheet. In the stuff I've done, however, the spreadsheet that gets created always contains exactly one worksheet within it. Up until now, this has been sufficient because the VBA code has always produced exactly one result set, and that result set logically goes into one worksheet within a spreadsheet. Now I would like the application to produce two result sets and have those result sets go i...

Re: Sort order of a non-sorted dataset? #7 665957
Hi all, Thanks for the responses. I'll give it a try now. Clinton, unfortunately it DOES matter. The data sets are for FDA esubmission and have to be sorted. Thanks a ton, Dan Daniel, can find the variable(s) in datset that is sorted by using proc contents.this procedure will give sort information. hope this helps Daniel Boisvert wrote: > Hi all, > > Thanks for the responses. I'll give it a try now. > > Clinton, unfortunately it DOES matter. The data sets are for FDA > esubmission and have to be sorted. > > Thanks a ton, > Dan Hi all, I had the...

Sort order
Hi, I've a table with some rows. With 'select * from tab order by sp1' I've got the following: sp1 --- a1 a2 aa ab Is it possible to change the sort order to get this: sp1 --- aa ab a1 a2 Regards Michael You can create a database with User Defined Collation Sequence. In DB2 v8, we have a sample program in sqllib/samples/c/dbcreate.c. The sample program is in sqllib/samples/c/ebcdicdb.c. Sherman "hobbit" <michael.spoden@web.de> wrote in message news:7a88ec99.0307070737.5299cf25@posting.google.com... > Hi, > >...

Sort order
I was on "vacation" at my brother's for 9 days, trying to fix his computer up for him and a new job, and I dl'd a new copy of Eud6. I only had an Inbox, didn't create other mailboxes. I usually had it sorted by date, but when I dl'd new mail, the new mail seemed to become interspersed with the old mail. It seemed that because the dates on spam and viruses vary, are often from hours or days ago, they were mixed in according to their date-times. In the past, version 3, no matter what, new emails were at the end of my Inbox (or other mails). Is version 6 d...

Sort order
Hi I'm not a developper but only a end user for a system build with Paradox. I want ton know how paradox sort data in database. Actually my data supposed to be sort on a text field but upper and lower case in this field dont sorting correctly. Why "AZ" came before "ac"? Do I have to change BDE langdriver for native paradox driver? It actually set to Ascii Ansi. thank you sorry for bad english Ma Dalton wrote: > Hi > I'm not a developper but only a end user for a system build with > Paradox. I want ton know how paradox sort ...

Sort order
Hi community! A little problem appeared. DB with lv_lv.1257 is used. I am sorting by NCHAR field. But latin letter Y acquired not between latin letter X and latin letter Z (as I've expected and as Excel sorts), but between latin letter I and latin letter I with macron. Does anybody knows where can find any standart for sort order in different code pages? Why such difference in Excel and Informix exists? Leonid. -- ���������� �������� ���� ������������ http://pasts.delfi.lv sending to informix-list it code be a bug with the lv_lv.1257 locale if you believe it is...