Overwrite existing file when using OutputTo command

  • Thread starter Angie Fleming via AccessMonster.com
  • Start date
A

Angie Fleming via AccessMonster.com

I have a macro set up to run daily that appends new records into my dbase
then I have 5 reports set to run and output to rtf file. This rtf file is
a link on a daily reports web page I have. I ran into this problem the 2nd
day the macro ran. After the report runs I get the "file exists, do you
want to overwrite" msg box. I need a way to add a step in the mac that
will answer yes to this msg box so the rpts can post everyday without
manual intervention.
 
A

Angie Fleming via AccessMonster.com

I'm not sure how to do that. I'm creating the macro in access and don't
know visual basic. The command line I am using is OutPut to and I have the
path and file name set. Because the output file is linked to a web page,
it has to output to same file name each day (for example daily.rft). I get
the error message after the report runs and tries to output to daily.rft
again, I'm asked if I want to overwrite file. Since daily.rft is not in
access how would I delete that file before running the access report?
 
S

SusanV

When you output the file, instead of using a mapped drive like
g:\share\filename.rtf, output to \\server\share\filename.rtf. After making
the change in your macro, do a compact and repair and it should stop
prompting you about the overwrite. Run it twice manually to check.
 
A

Angie Fleming via AccessMonster.com

I've tried this and it still does not work, I get the same msg box. I'v
also tried to add the sendkey cmd and that doesn't seem to work. I
converted a copy of the mac to visual basic and this is how it looks:
DoCmd.OutputTo acReport, "rptAgent", "RichTextFormat(*.rtf)", "\\server\
Share\wwwroot\agent06.rtf", False, ""
SendKeys "{TAB}{ENTER}", False

Is there something I can put in here to either delete the rtf file before
the output to is run, or is the send keys line correct?
THANKS!!
 
S

SusanV

Did you set warnings off? (prior to your output to commands, select
SetWarnings, and in the bottom settings, Warnings on should be No)
 
A

Angie Fleming via AccessMonster.com

Sorry, should have said that. Yes SetWarnings to No is the first command
in the macro and SetWarnings to Yes is the last.
 
S

Steve Schapel

Angie,

Sorry, this is one of the things that can't be managed in a macro.
SetWarnings is not relevant to this situation. There is no option but
to use a VBA procedure instead of a macro here. The code to do what you
need will look like this...

Dim OutputFile As String
OutputFile = "\\server\Share\wwwroot\agent06.rtf"
If Len(Dir(OutputFile)) Then
Kill OutputFile
End if
DoCmd.OutputTo acOutputReport, "rptAgent", acFormatRTF, OutputFile
 
Joined
Jan 19, 2014
Messages
1
Reaction score
0
I tried using this kill command with a vba script that cycles thru a combobox to run a specific query on each record before exporting to an excel file with the name of the record and it somehow mixes the names up. For example:

Private Sub Upload_Piezo_excel_to_C_Click()
On Error GoTo Upload_Piezo_excel_to_C_Click_Err
Dim pname As String 'path name for the three output files
Dim fname() As String 'file name w/o extension sourced from combo box
Dim i As Long
Dim test As String

pname = "C:\drv\\LeLv\LeLv_Piezometers\"

i = 0

With Forms!LeLv_Piezo_VnU.Controls!cmbo_Piezo
For i = 0 To .ListCount - 1
'MsgBox .ItemData(i)
test = pname & .ItemData(i) & ".xlsx"
'MsgBox test

Dim KillFile As String
KillFile = test
'Check that file exists
If Len(Dir$(KillFile)) > 0 Then
'First remove readonly attribute, if set
SetAttr KillFile, vbNormal
'Then delete the file
Kill KillFile
End If

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "LeLv_Piezo", test, False

.Value = .ItemData(.ListIndex + 1)
Next i
.Value = .ItemData(0)
End With


Upload_Piezo_excel_to_C_Click_Exit:
Exit Sub

Upload_Piezo_excel_to_C_Click_Err:
MsgBox Error$
Resume Upload_Piezo_excel_to_C_Click_Exit

End Sub
 
Last edited:

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top