Exporting to Excel Problems

[J. Trucking]

Hello All,

I have a split database for which some of the users are running Office
2007 and some are running 2003. I have one part of the DB which
automatically exports data from Access into a couple of pre-
constructed Excel Templates. It works awesome on the computers with
2007 but there seems to be some issues when running in 2003. I have
included the code below which was constructed with the help of some
very knowledgable people whom I give all of the credit to. I have
taken out the error handling to save space.

Sub exportmonthlybreakdown()

Dim objXLApp As Object
Set objXLApp = CreateObject("Excel.Application")
Dim objXLBook As Object
Dim obj As Object
Dim strFilter As String
Dim strInputFileName As String
Dim conpath As String

Dim db As DAO.Database

MsgBox ("This process will export data to Microsoft Excel. Please be
patient as this may take a few minutes. You will be prompted to save
the Excel file. You will then be notified when the export is
complete.")

Set db = CurrentDb

conpath = CurrentProject.Path

If Len(Dir(conpath & "\HourBreakdown.xls")) > 0 Then
Kill conpath & "\HourBreakdown.xls"
End If

If Len(Dir(conpath & "\HourBreakdown1.xls")) > 0 Then
Kill conpath & "\HourBreakdown1.xls"
End If

Set objXLBook = objXLApp.Workbooks.Open(conpath &
"\HourBreakdown.xlt")

If Val(Application.Version) < 12 Then

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)",
"*.xls")
strInputFileName =
ahtCommonFileOpenSave_(OpenFile:=False,Filter:=strFilter,Flags:=ahtOFN_OVERWRITEPROMPT
Or ahtOFN_READONLY)
objXLBook.SaveAs (strInputFileName)
objXLBook.Close
DoCmd.TransferSpreadsheet_
acExport,acSpreadsheetTypeExcel9,
"qryMonthlyHourBreakdown",strInputFileName,True, "ExportedData"

Else

strFilter = ahtAddFilterItem(strFilter, "Excel 2000-2003 Workbook
(*.xls)", "*.xls")
strInputFileName = ahtCommonFileOpenSave(OpenFile:=False,
Filter:=strFilter, Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
objXLBook.SaveAs (strInputFileName), FileFormat:=56
objXLBook.Close
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12,
"qryMonthlyHourBreakdown", strInputFileName, True, "ExportedData"

End If

MsgBox ("Export Complete.")

FinishWork:
On Error Resume Next
Set db = Nothing
Set objXLBook = Nothing
Set objXLApp = Nothing

ExitHere:
Exit Sub
Resume FinishWork
End Sub

I am sorry if the code that is supposed to be on one line got copied
onto two lines. I have two problems with the above code. The first
problem is when the user selects the location to save their file using
the API file open/save box and hits 'Save', the system asks them if
they would like to save 'HourBreakdown1.xls' even though thats not
what they named their file. Do I need to add in another line to kill
that instant of Excel? The second problem is when the user exports
their Excel file and goes to open it, the computer claims that the
file is 'Not Recognizable'. I am guessing that this is because the
user doesn't have the compatibility pack installed but I am not too
sure. I say this because they can open the Excel templates before the
data gets copied over. Once the template is populated with the data
and saved as a different name, it seems to take the same effect as an
uncompatible Excel file. Any advice would be greatly appreciated.

John

 

[a a r o n . k e m p f]

I think that ADO generally works better for Excel

 

[J. Trucking]

THanks for the reply. What's required to make that ADO? Is it a lot
of work?

 

[Aaron Kempf knows nothing]

I think that DAO generally works better for Excel

 

[BruceM]

Scan through Aaron's postings. He recommends ADO and SQL Server in all
cases, no matter the situation. In any case, don't expect a detailed reply
from him.

 

[So Sorry For Poor Aaron]

THanks for the reply. What's required to make that ADO? Is it a lot
of work?

Yes, it is a lot of work to move from DAO to ADO, and there is just no
advantage -- DAO is the native language of Jet and ACE databases, the default
database engines of Access in different versions; it has more extensive
capabilities, fewer errors, and better performance than ADO with the default
database engines for Access. Access MDB or ACCDB with DAO and OdBC drivers
are the configuration now recommended by the Access team for Access client
database applications.

But aaron had tried for so long to bully his way to winning arguments
depending on the benefits of Access ADPs, now supported just for backward
compatibility, and not updated with new features as are ACCDBs and MDBs, and
he finds it impossible to confess that he has ever been wrong. Thus, you will
still see him interjecting himself into every possible message thread trying
to lure people into using ADP and ADO, but he doesn't know those, nor SQL
Server, well enough to give sound, practical, detailed advice about them --
he just parrots the old marketing material.

In addition to what BruceM told you, Aaron has been exposed as a liar...
over and over, he's denied pleading guilty to cyberstalking (he threatened
the life and threatened arson against a person) and has claimed to be a
Microsoft-certified database expert. But in a message thread that has since
been (conveniently for aaron) removed from Microsoft's server, but remains on
mirrored servers elsewhere, links were posted to Washington Superior Court
records showing he did plead guilty and to a site where you can look up
Microsoft-certificate holders from which aaron's name was conspicuous by its
absence.

He holds a grudge against Access MVPs because they have so often
demonstrated he is wrong in his rants against Access and for SQL Server and
against Jet's DAO and for ADO, against Access MDB/MDE/ACCDB/ACCDE because it
is a better approach than the one he touts for creating client applications
to server database, and against Microsoft because they ran his sorry ass off
from their campus when they found him violating the rules of contract
employment -- before he decided that was not beneficial to him, you should
have read his rants about how they "took his notebook computer" and "wouldn't
give it back to him for months".

Oh, if there were only a way to do what Microsoft did and "run aaron's sorry
ass off" from the newsgroups as well, what disruption we would avoid, and how
much more pleasant they would be.

So, So Sorry for Poor, Poor, Pitiful Aaaron

 

[david]

If you are doing a simple export to excel, you can use a
make-table query or an append query, in ADO or DAO,
instead of using the Access DoCmd methods.

You need to specify the target database (in this case,
the xls file) using either the IN clause or the extended
table name syntax:

table IN "" [excel; DATABASE=C:\DBASE\DATA\SALES;]

or

[excel; DATABASE=C:\DBASE\DATA\SALES;].table

I've probably got those examples wrong: I don't have anything
in front of me. I don't know if it is different in ADO SQL than
it is DAO SQL. The DoCmd methods are wrappers around
the underlying Jet Database methods, which are normally faster
and more light-weight.

(david)

 

[J. Trucking]

Thanks for the help everyone.

 

[a a r o n . k e m p f]

Jet is dead.. no other company-- in the history of the world- has ever
embraced DAO because it is a laughable technology.

its' not my fault that it MIGHT take an hour to change a moderately
complex Jet database from DAO to ADO.

the performance implications are tremendous.
Move to ADO and then ADP for everything.

A simple, lightweight language for any relational engine.

-Aaron

 

[a a r o n . k e m p f]

Jet is dead.. no other commercial software company-- in the history of
the world- has ever embraced DAO because it is a laughable technology.

Meanwhile everyone from Adobe to IBM use ADO.

its' not my fault that it MIGHT take an hour to change a moderately
complex Jet database from DAO to ADO.

the performance implications are tremendous.
Move to ADO and then ADP for everything.

A simple, lightweight language for any relational engine.

-Aaron

 

[a a r o n . k e m p f]

ADP is not 'just supported for backwards compatability'.

Jet databases didn't have a single improvement in a decade-- and they
were kept around for 'backwards compatability only'.

if you look under FILE, NEW, you will see that Projects are the most
popular format by far.

-aaron

 

[a a r o n . k e m p f]

and for the record, I quit Microsoft because my car was stolen and I
was pissed off that they didn't have cameras in the parking lot.

Jet and DAO does not have fewer errors and better performance.
Jet doesn't even support working against SQL Server.

if you're stuck with an albatross-- it's because they kids want you to
be stuck with their same crappy databases-- because misery loves
company.

I reccomend you find someone on CraigsList to migrate your mission
critical database to SQL Server.

anything else is just a waste of time and money

 

[a a r o n . k e m p f]

a) I don't need to prove to anyone that I'm certified. I am. Screw
you.
b) i didn't stalk anyone-- I didn't threaten anyone.
c) I didn't plead guilty to what they charged me with. If i plead
guilty to something else-- it's none of your friggin business

so no-- i'm not full of crap in _ANY_ regard.

 

[a a r o n . k e m p f]

and re:
but he doesn't know those, nor SQL
Server, well enough to give sound, practical, detailed advice about
them --
he just parrots the old marketing material.

for the record-- I _DARE_ you to name one thing that I've -EVER- said
wrong about SQL Server.
It's just a much much much better platform.

WHy do you think that SQL Server is the worlds most popular database?
It's _EASIER_ to use!!!!
It's _FASTER_!!!!
It's _MORE_RELIABLE!!!!
It's _JUST_AS_FREE!!!!

 

[Graham R Seach]

The problem people have with you here Aaron, is not so much what you say
about SQL Server. but (a) the constant stream of misinformation you spew
about Access, (b) your monotonic matra that rarely has anything to do with
helping the people who come here, and (c) your rude/bad attitude to those
very people. Access and SQL Server are two different beasts; Access doesn't
seek to compete with SQL Server - never did. Understand and accept that and
you'll be a lot happier.

You have reached an unenviable level of ridicule in these groups because you
are incredibly rude and inconsiderate to the people who come here for help,
and you make sweeping statements about Access and Jet that often have no
basis in fact. When challenged to provide credible support for your claims,
you either (a) take a scattergun approach with even more unsupported claims
in an attempt to obfuscate the fact that you were wrong, or (b) you dump the
thread altogether; never to return.

By all means, Aaron, stay in these groups, but please (a) be honest, (b) be
helpful, (c) be accurate, (d) be realistic, and (e) be polite. That's all we
ask.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


message
and re:
but he doesn't know those, nor SQL
Server, well enough to give sound, practical, detailed advice about
them --
he just parrots the old marketing material.

for the record-- I _DARE_ you to name one thing that I've -EVER- said
wrong about SQL Server.
It's just a much much much better platform.

WHy do you think that SQL Server is the worlds most popular database?
It's _EASIER_ to use!!!!
It's _FASTER_!!!!
It's _MORE_RELIABLE!!!!
It's _JUST_AS_FREE!!!!

 

[posted_by_anonymous]

By all means, Aaron, stay in these groups, but please
(a) be honest, (b) be helpful, (c) be accurate, (d) be
realistic, and (e) be polite. That's all we ask.

How sad that the "only" things you ask are things that aaron's nature
absolutely prevents him from doing -- perhaps with outstanding psychiatric
help and good medications, he might do some of them. But, he would have to
know something to be helpful and accurate.

Anony Mous

 

[posted_by_anonymous]

Jet is dead.. no other company -- in the history
of the world- has ever embraced DAO because
it is a laughable technology.

An advocate of Joseph Goebbels' Big Lie Technique, are you, aaron? Tell it
often enough and somebody's bound to be stupid enough to believe you. DAO is
the native language of Jet.

its' not my fault that it MIGHT take an hour to
change a moderately complex Jet database
from DAO to ADO.

Only someone as stupid as you, or someone who's believed your lies, would
bother to convert a Jet database from DAO to ADO.

the performance implications are tremendous.
Move to ADO and then ADP for everything.

Yes, indeed, they are. ADO is a wrapper, using OLEdb, that still has to go
through DAO to use a Jet database. Thus you have DAO which you could access
directly, plus the overhead of ADO on top. ADO has already been superceded
by ADO.NET which is a different thing entirely, different object model...

A simple, lightweight language for any relational engine.

Not at all, but aaron is a simple lightweight, who thinks bluff and bluster
can overcome the facts. What a sad excuse for a human being.

 

[posted_by_anonymous]

ADP is not 'just supported for backwards compatability'.

Dying, no longer recommended. All development is aimed at ACE (direct
descendant of Jet), ACCDB/DE/DR, and, yes... Jet. The Access team assumed
responsibility for Jet to further develop it.

Jet databases didn't have a single improvement in a decade -- and they
were kept around for 'backwards compatability only'.

Goebbels again. Big Lie, repeated ad nauseum.

if you look under FILE, NEW, you will see that
Projects are the most popular format by far.

There's no information whatsoever about "popularity" there.

Anony Mous

 

[posted_by_anonymous]

and for the record, I quit Microsoft because
my car was stolen and I was pissed off that
they didn't have cameras in the parking lot.

His car may have been stolen, but why was he whining about them taking his
notebook computer and not giving it back for two months? They ran his sorry
ass off, as "So Sorry" said, pure and simple.

Jet and DAO does not have fewer errors
and better performance. Jet doesn't even
support working against SQL Server.

Jet works with ODBC. ODBC works with any ODBC-compliant database. Jet works
with ODBC works with MS SQL Server, and dozens of other databases. DAO has
better performance with Jet than ADO has with Jet because ADO has more layers
and more overhead.

I reccomend you find someone on CraigsList
to migrate your mission critical database to
SQL Server.

You want to "migrate" your database to SQL Server? Hire yourself a
competent DBA (not a numbnuts numbskull like aaron) to create your tables
there. Then link them and copy your Access data. Use the linked SQL Server
tables. It'll just work. Go to any one of many sites to get hints on
improving performance, if you wish.

anything else is just a waste of time and money

Listening to aaron kempf, liar, disruptive poster, and publicly recorded
criminal is a waste of time and money.

Anony Mous

 

[posted_by_anonymous]

a) I don't need to prove to anyone that I'm
certified. I am. Screw you.

You've got your words confused, aaron. "Certifiable" doesn't mean the same
as "certified". No wonder you can't get a decent job. People hiring
"certified" help insist on proof -- it's not there for you. You make the
claims but you can't back them up.

b) i didn't stalk anyone-- I didn't threaten anyone.

Yes, you did. It's right there in the court records. There are copies of
your posts.

c) I didn't plead guilty to what they charged me
with. If i plead guilty to something else-- it's
none of your friggin business

Oh, Daddy's high-powered attorney plea-bargained you down from felony to
misdemeanor and you claim you didn't plead guilty. It's right there in the
public records. It establishes that your character does not support belief in
your claims here. Of course, it's our business to know whether we are
listening to a criminal or not when he asks us to believe what he says, just
because he says it.

so no-- i'm not full of crap in _ANY_ regard.

Actually, yes, you are full of crap in _EVERY_ regard. You are a pitiable
excuse for a human being.

Anony Mous