preview a report in excel

spa...@comcast.net

unread,

Oct 30, 2012, 1:26:39 PM10/30/12

to

I have done a lot of saving reports to excel but I can't figure out
how to open a report in excel to preview it.
this is the way I sent the report and save it.

DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.OutputTo acOutputQuery, stDocName, acFormatXLS, XFile, False

how can I just preview the excel output without saving it or even
having a prompt for saving in excel.

Bob Barrows

unread,

Oct 30, 2012, 4:56:52 PM10/30/12

to

It's not possible of course. Excel is not part of Access - you are writing
data to a file ...

All you can do is consider the first OpenQuery call to be the preview.

Bob Barrows

unread,

Oct 30, 2012, 4:58:13 PM10/30/12

to

I should have added that Excel won't open a file unless it exists. You have
to save the data to a file before opening the file in Excel.

The Frog

unread,

Oct 31, 2012, 8:34:52 AM10/31/12

to

You could copy the results of a query to an array with getrows for
example, then pump the array into an Excel worksheet. That's kind of
like a raw dump rather than a report though, but worth a mention.

--
Cheers

The Frog

Douglas J Steele

unread,

Oct 31, 2012, 9:46:03 AM10/31/12

to

Or you could automate Excel and use the CopyFromRecordset method, rather
than requiring the use of an array .

"The Frog" wrote in message
news:almarsoft.1121...@news.aioe.org...

sparks

unread,

Nov 5, 2012, 10:05:02 AM11/5/12

to

Thanks for your help, I am now saving and preview mode and they seem
to not get it.
Then I asked what they expected.
OH its not the view its the data.
WHAT does that have to do with saving the data or just viewing it.
I can write a routine that saves it, previews it and on close deletes
the file.
No thats not it.

They have cut and pasted text into a note field.
when you export it the data may or may not all copy to excel on
export.

They are cutting and pasting from some online database, then pasting
it into a note field in access, and it is full of funky punctuations.

The export routine gets confunsed on this mess and they expected
access to automatically clean it up for them.

1) how does this have anything to do with the preview and not what
they expected.
2) how do they expect access to reformat this mess and keep it at the
same time.
I tried to explain to them they are copying a bunch or returns and
tabs etc and just pasting this into a note field and saying ok access
reformat this stuff.
I advised them to paste it into word or something and removing the
funky syntax.
That is not our job.

Sorry for asking about something they were sure was the problem with
out asking more from them. They seem to know everything but can't
explain it.

overall I hate Secretaries OH excuse me Administrative Assistants

Gene Wirchenko

unread,

Nov 5, 2012, 1:52:35 PM11/5/12

to

On Mon, 05 Nov 2012 09:05:02 -0600, sparks @comcast.net wrote:

>Thanks for your help, I am now saving and preview mode and they seem
>to not get it.
>Then I asked what they expected.
>OH its not the view its the data.
>WHAT does that have to do with saving the data or just viewing it.
>I can write a routine that saves it, previews it and on close deletes
>the file.
>No thats not it.
>
>They have cut and pasted text into a note field.
>when you export it the data may or may not all copy to excel on
>export.
>
>They are cutting and pasting from some online database, then pasting
>it into a note field in access, and it is full of funky punctuations.
>
>The export routine gets confunsed on this mess and they expected
>access to automatically clean it up for them.

That seems reasonable to me.

>1) how does this have anything to do with the preview and not what
>they expected.
>2) how do they expect access to reformat this mess and keep it at the
>same time.

Two columns: original and reformatted?

>I tried to explain to them they are copying a bunch or returns and
>tabs etc and just pasting this into a note field and saying ok access
>reformat this stuff.
>I advised them to paste it into word or something and removing the
>funky syntax.
>That is not our job.

Well, it could be their job, but only if you fail to reformat the
data.

>Sorry for asking about something they were sure was the problem with
>out asking more from them. They seem to know everything but can't
>explain it.

They know what is happening, but they do not know why? I am not
surprised. Knowing why is your job. As I see it: As far as they are
concerned, they have a flaky tool, and you should repair it.

>overall I hate Secretaries OH excuse me Administrative Assistants

They probably pick up on that.

Sincerely,

Gene Wirchenko

The Frog

unread,

Nov 5, 2012, 4:09:15 PM11/5/12

to

Hi Sparks,
I had a similar situation long ago. I ended up using a regex to clean
up the garbage users were dumping in. When I got the regex right the
users stopped whining and the app 'just worked'. You got a sample of
the garbage they're feeding your app?

--
Cheers

The Frog

sparks

unread,

Nov 14, 2012, 11:21:22 AM11/14/12

to

That seemed to take care of it.

They were cutting some weird stuff out of pdf documents and it looked
fine to them. So paste it in the box.
There were some weird tabs and recurring returns. one line had
something that textpad determined was a return anyways and it ended up
being 96 lines of test they saw as 3.

The Frog

unread,

Nov 14, 2012, 2:58:35 PM11/14/12

to

On Wed, 14 Nov 2012 10:21:22 -0600, sparks <@comcast.net> wrote:
> That seemed to take care of it.

Excellent news. Glad to hear it solved the issue. Input validation
with regex can be a wonderfully effective solution to an otherwise
awkward problem.

--
Cheers

The Frog