Automating a Macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to automate the output of a report as an html file using a macro
triggered by the on time property of a form.
The macro works but need a manual response to the msg box saying that the
file exists and asking for confirmation before overwriting it. Can anyone
suggest some code to accept the message automatically.
 
Stop using a macro, and use VBA instead.

Check whether the file already exists, and delete it if it does:

strFile = "C:\MyFolder\MyFile.html"

If Len(Dir(strFile)) > 0 Then
Kill strFile
End If
 
Doug Thanks for the suggestion.
The current macro saved as code is
DoCmd.OpenReport "repDay1", acViewPreview, "", "", acHidden
DoCmd.OutputTo acReport, "repDay1", "HTML(*.html)",
"C:\WebsiteUploads\repDay1.html", True, "", 0
DoCmd.Close acReport, "repDay1"
I would be gratefull if you can advise me on the amended code to replace the
macro including your delete code.
 
strFile = "C:\WebsiteUploads\repDay1.html"

If Len(Dir(strFile)) > 0 Then
Kill strFile
End If

DoCmd.OpenReport "repDay1", acViewPreview, "", "", acHidden
DoCmd.OutputTo acReport, "repDay1", "HTML(*.html)", _
strFile, True, "", 0
DoCmd.Close acReport, "repDay1"
 
This code ran succesfully on my PC but when I tried it on a second machine
each line generated a different compile error message.
If Len(Dir(strFile)) > 0 Then
Kill strFile
End If

DoCmd.OpenReport "repDay1", acViewPreview, "", "", acHidden
Click to expand...
Compile error "Wrong number of arguments or invalid property assignment"
DoCmd.OutputTo acReport, "repDay1", "HTML(*.html)", _
Click to expand...
Compile error "Syntax error"
strFile, True, "", 0
Click to expand...
Compile error "Expected Sub,Function or property"
DoCmd.Close acReport, "repDay1"
Click to expand...
Both PC,s run XP but the first machine has access V 10 and the second V 9,
is this the cause of the problem , and if so how should the code be changed
to work on the second machine?
 
Could be a problem with the References collection.

References problems can be caused by differences in either the location or
file version of certain files between the machine where the application was
developed, and where it's being run (or the file missing completely from the
target machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module (or open the
Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar. Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)
 
Doug Thanks again for the response.
I checked the references and non were "Missing"
There were 4 defined references ;-
Visual Basic for applications
Microsoft access 9.0 object library
OLE Automation
Microsoft ActiveX Data objects 2.1 library.
I tried your suggestions but it made no difference.
I checked the original machine and the references are exactly the same
except the second library which is
Microsoft Access 10.0 object library.
Can I rewrite the code to be compatible with the second machine or must I
update the version to V10.
Thanks
Dave
 
Looking a little harder at the code you're using, you might have to upgrade
the second machine, or else change your code. (Despite the fact that both
Access 2002 and Access 2003 use the same file format as Access 2000 by
default, the newer versions have functionality in them that didn't exist in
Access 2000, and you'll run into problems in Access 2000 if you're trying to
use that new functionality)

I believe the OpenReport method did change between Access 2000 and Access
2002: I don't believe Access 2000 allows you to specify a WindowMode
(acHidden) like you are. Take a look at the differences between the methods
in Access 2000 and Access 2002:

http://msdn.microsoft.com/library/en-us/off2000/html/acmthactOpenReport.asp
http://msdn.microsoft.com/library/en-us/vbaac10/html/acmthactOpenReport.asp

I'm confused by the errors with the OutputTo method. It's possible Access
2000 doesn't like "HTML(*.html)". (You're probably better off using
acFormatHTML anyhow). Also, according to the Help file, acReport isn't a
valid argument for the OutputTo method: it's supposed to be acOutputReport.
However, I wouldn't have expected compile errors for either of those: I
would have expected runtime errors. As well, Access 2000 only accepts 6
arguments, while Access 2002 accepts 7 (with the last one, encoding, being
optional, so you can simply omit it from your list) However, the error
messages you're showing make it look as though Access 2000 is missing the
continuation character (hence the "Expected Sub, Function or property"
error).

http://msdn.microsoft.com/library/en-us/off2000/html/acmthactOutputTo.asp
http://msdn.microsoft.com/library/en-us/vbaac10/html/acmthactOutputTo.asp

Of course, it's entirely possible that Access still confused from the
OpenReport (since you're using more arguments in it than Access 2000 can
handle), and that's what's causing the compile errors with the OutputTo.

That seems odd code you're running, though. You're trying to open a report
as hidden, then you're trying to output that same report as HTML. Why the
hidden report? AFAIK, the OutputTo is going to open a new copy of the
report, and not use the one that's already open.
 
Back
Top