I Despise microsft access

[ViolentGreen 0]

I need to export a report from Access2002 from a visual basic sub routine. I am using

 

DoCmd.OutputTo acOutputReport, "Table", acFormatXLS, "File"

 

The problem is that it appears to save it in a pre Excel97 format. The issue here is that it is not displaying the sums. Any click on a sum field causes a memory error.

 

If i manually export to excel97-2002 format everything works. If I export to the excel5-95 I get the above problem.

 

I am wondering if anyone knows how to force it to save in the latest format. I don't know much about VBA or Access.

 

Thanks, either way.

 

 

On a side note, if a application is going to use SQL, it should stick with the standard.

[ViolentGreen 0]

Quote:

P.S.=> Have you look into patches to Office XP itself? I am assumign you are using that model since it is Access 2002... they do fix stuff in those Service Packs... apk

Well, it's on my machine at work and it's not worth going though all the channels to get it patched.

[AndyFair 0]

Since Access doesn't really format its Excel export files, have you thought of exporting text (CSV) instead?

 

Just a thought?

AndyF

[ViolentGreen 0]

Quote:

Since Access doesn't really format its Excel export files, have you thought of exporting text (CSV) instead?

Just a thought?
AndyF

Actually, I need it in the .xls format.

How about this... Is it possible to export it as a csv, open the file in excel and run excell-specific code from inside the access sub?

[ViolentGreen 0]

Quote:

Quote:

Actually, I need it in the .xls format.

Excel can import that .csv type... easily, Comma Separated Value type file... most files are just this anyhow, but not text file but instead binary data... still, just streams of characters with separators... even MS OLE "compound documents", they just have more types of separators for organization etc.

I need the actual .xls filetype though. Clients are picky.

Quote:

Quote:

How about this... Is it possible to export it as a csv, open the file in excel and run excell-specific code from inside the access sub?

You should have .csv import in Excel as is... (only hassle? I don't know how well reports export out fields-wise, & also if they will import properly into an excel sheet outta Access... still, just fields though, & you can manually 'massage' the Excel sheet as needed in Cells after the import, compound documents or not!)



APK

I wasn't very clear. What I am asking is if it is possible to do all the above from inside an access sub. The whole process of opening of opening the file in excell and running code there and then closing the file all inside of the access sub.

[AndyFair 0]

Yes, it is possible to control other Office applications from within Access. Can't remember off the top of my head how to do it, but it can be done.

So in theory, the flow of code should be something like:

 

Export query as CSV

Start Excel

Import CSV

Save as most recent Excel format

Close Excel

 

I'll take a look at work tomorrow and get back to you (don't have Access installed at home at the mo)

 

Rgds

AndyF

[AndyFair 0]

OK, just had a quickie read up (at work now, I have Access apps wot I wrote that use OLE automation all over the place)...anyway, the important line is:

 

Code:

Set xlObj = CreateObject("Excel.Application")

 

Once this is done, you use standard Excel VBA commands and prefix them with xlObj - for example:

 

Code:

xlObj.Workbooks.Open FileName:="Filename.csv"xlObj.ActiveWorkbook.SaveAs FileName:="Filename.xls", FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

 

Hope this helps.

Rgds

Andy

[ViolentGreen 0]

Quote:

OK, just had a quickie read up (at work now, I have Access apps wot I wrote that use OLE automation all over the place)...anyway, the important line is:

Code:

Set xlObj = CreateObject("Excel.Application")

Once this is done, you use standard Excel VBA commands and prefix them with xlObj - for example:

Code:

xlObj.Workbooks.Open FileName:="Filename.csv"xlObj.ActiveWorkbook.SaveAs FileName:="Filename.xls", FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

Hope this helps.
Rgds
Andy

That looks to be exactly what I need. I'll let you know how it works.

Thanks!

[ViolentGreen 0]

On further inspection, it will not let me save as a CSV file.

 

My current thoughts are opening the report and trying to save it from there. I doubt that I will have much luck on that. Time to scour the net again for VB info...

[AndyFair 0]

Quote:

On further inspection, it will not let me save as a CSV file

Sorry, my bad - I thought you were exporting a query not a report. I have to say that I have never thought of exporting a report as a spreadsheet before.

Like Alec said, do you have to export the design of the report? Because if not, it would be easier to export the query that sits behind the report. The only difficulty would be if you have any sublevels and subtotals in your report...

Alec, I'm not sure whether a patch would help here (but I'm willing to be proved wrong!) - the Access development team always seem to be a couple of steps behind the rest of the Office development team. I don't know whether it's that MS don't consider Access as a mainstream application or what, but it always seems to be the case - the only thing that seems to change between versions is the file format!!

Rgds
AndyF

[ViolentGreen 0]

Quote:

Neither have I, which is why I asked him if it is ONLY fields from results calculations he has to export out... to me, that makes ALOT more sense then doing the ENTIRE report (design & all).

Quote:

Like Alec said, do you have to export the design of the report? Because if not, it would be easier to export the query that sits behind the report. The only difficulty would be if you have any sublevels and subtotals in your report...

Sorry, I miss stuff in long posts. Actually, i need the whole thing, format and all. The calculations are the fields that are causing program crashes.

Quote:

He can manually massage that in Excel cells calculations I imagine, don't you agree? Just like doing them in Access almost & same formula set iirc pretty much is offered in the way of prebuilt functions too...

Sorry, I have no clue what you are talking about here.

Quote:

I tell you, I would check it out myself though, never leave any stone unturned & it alone may cure what ails him TOTALLY, since apparently Access 2k only exports out to Excel 97 format...

What bugs me?

How come Excel 2k does not import in Excel 97 properly?? That's an option to check out as well... has he tried this?

I believe that 97 is the same format as 2000. I am using access 2002. It exports fine (manually) to the 97-2002 format but does not to the 95 format. This is why I believe it is exporting to the 95 format.

Quote:

* He ought to do a File Open As #1 for Output VB code module & drive it outta Access via a Macro!

Export it into a format he can export the fields he needs in a format HE designs for import later in Excel... not hard to do either, just a bit more manual & old-school.

Then, there'd be NO doubts here on what gets put out etc. & since he designs the output file, he has TOTAL control & understanding of it as well. You know, & I know... this helps ALOT & is sometimes worth the doing of yourself: Design from the "ground-up"...

Sorry, once again I have not Idea what you are talking about.
Somehow I missed the VBA help files on the install and have very primitave knowledge of the language so most of my info is comming off the internet. Any idea how many bad results come by doing a query with the words "access" and "database"?

I am just going to export the query then run excell macros to manually create the sub levels and totals.

I really appreciate all the help you all provided. It just occured to me that Im using excel97 and access2002. Maybe the problem lies there. I don't know and I am beyond caring. Again, thanks for your help. That bit of code to run excel code from within access will work wonders for me in hte future.

[AndyFair 0]

Quote:

I am just going to export the query then run excell macros to manually create the sub levels and totals.

You don't have to run Excel macros, you can do it all from Access using the method I talked about above - then it stays a one-step process (click a button in Access) rather than a 2-step process (click a button in Access, run a macro in Excel)

The easiest way to do this is to record your macro in Excel, then copy the code and paste it into Access (surround it by a file open at the beginning, then a file save at the end), and prefix each line with a reference to Excel object.

Hope this helps
Rgds
AndyF