Newbie macro problem

[Support]

scenario: need to run a query at a certain time each day and save results
as text file in particualr location. db is access97

Solution: I have both acc97 and 2002 installed. I have created a macro
that does the following (in both programs):
1) OpenQuery (opens specified query)
2) OutputTo (outputs text file to particular location)
3) SetWarnings = No
4) Close

I have found a great little utility called HAL (Handy Access Launcher) which
can schedule macros etc so I have the automation sorted.
My problems are twofold:
1) even with setwarnings = no, I am still being proompted to overwrite the
existing text file - how can I get around this?
2) I have borders marked in the exported text file similar to this:
-------------------------------------------------------------
| Location | PLU | Stock |
-------------------------------------------------------------
| 4 | 1631603 | -1 |
-------------------------------------------------------------
I would like to get rid of them - is this possible?
For further info, here is the vba code (which I cannot do as yet)
'------------------------------------------------------------
' Macro1
'
'------------------------------------------------------------
Function Macro1()
On Error GoTo Macro1_Err

' Open Lcation 4 Query
DoCmd.OpenQuery "qryLocation4Stock", acViewNormal, acReadOnly
' Output to Text file
DoCmd.OutputTo acQuery, "qryLocation4Stock", "MS-DOSText(*.txt)",
"C:\Documents and Settings\robhackney.TKCSALES.000\My Documents\test.csv",
False, "", 0
DoCmd.SetWarnings False
DoCmd.Close , ""


Macro1_Exit:
Exit Function

Macro1_Err:
MsgBox Error$
Resume Macro1_Exit

End Function

Thanks for any help
Cheers

 

[Steve Schapel]

Support,

First of all, the OpenQuery action in you macro can be removed, it
doesn't achieve anything. Therefore, if the OpenQuery is deleted, there
is no need for the Close either, so you can delete that too.
SetWarnings only applies to system messages, so will be ineffective in
the aspect of overwriting a file, so you can also remove it. So that
cuts your 4 action macro down to a single action macro, just the
OutputTo. Well, I wouldn't use OutputTo for this. The TransferText
macro is more applicable to what you are doing here. Run through the
export "manually" first, via the File|Save as/Export menu, so you can
specify the delimiters between fields (presumably you will use a comma
for a csv file), and then when you get to the last screen of the Export
wizard, click on Advanced button, and there you will be able to name and
save this setup as a Specification. You enter this in the Specification
Name argument of the TransferText action in your macro design.

 

[Support]

Many thanks Steve that worked great on my test database. Just need to test
on my live db...