To: Albert D. Kallal

[Guest]

Albert

Thanks for the assistance with the Access 2000/2002 ADP Project issue of Feb 3. Your suggestion worked flawlessly. Now, the end users are asking if the: "DoCmd.OutputTo acOutputServerView, "qryViewByMachineReworkedEmailData"
acFormatXLS, "MachineParts.XLS" command can output the data to an Excel97-2002 format instead of the Excel3/Office95 that it's currently outputting the data to?

Can you assist here too

Thanks,.....

 

[Albert D. Kallal]

Hum, off the top of my head, I don't know of a solution. I did not realize
that it exports to a "older" version of Excel. This was likely done for
compatibility reasons. Gee, MS can't win!..you try and make it compatible
with older stuff...but the newer users are not happy!. (it also likely got
the guys off the hook having to write code for each version!)

I suppose you could launch excel, open, and then save the spreadsheet in the
new format. (I talking about using automaton here).

Workbooks.Open FileName:="C:\zoo.xls"
ActiveWorkbook.SaveAs FileName:="C:\zoo2.xls", FileFormat:=xlNormal

However, you are able to open and use those spreadsheets. You might have to
live with this.

I suppose you could also dump the use outputto, and use 100% automation to
insert cell by cell. This approach would only be workable if the amount of
data you send out is not too large (say, just 100 or so rows).


However, just checking the KB, it does confirm that the output is Excel 5
format.

 

[Paul Overway]

Docmd.TransferSpreadsheet allows you to specify the spreadsheet format.

 

[Albert D. Kallal]

Yes, but unfortunately for some strange reason TransferSpreadhseet DOES NOT
work with a ADP projects. (and, to be fair..this thread should have
continued on the original post since you could have known we are taking
about a adp project).

TransferSpreadnsheet does work on a table in a ADP project, but, for some
really strange reason...it does not work with a query (well ok..a view,
since we are using a ADP project).

So, the original poster COULD send the query to a temp table, and then use
tranferSpreadsheet on that table. Of course, this means that the "order" of
the data cannot be set. However, if the data is sorted from the query/view
to the temp table, I would bet the order does remain....but this would have
to be tested, and is not a given.

 

[Guest]

Albert

Thanks, again for trying to resolve the matter of output to. I kind of figured that I could output to a Temp table, then, pump it out to excel. As you mentioned, the sorting would have to be set in the query/view level prior to this. I'll give it a whirl and let you know the results

Have a very good day or Safe Weekend