Overwrite existing file when using OutputTo command

  • Thread starter Thread starter Angie Fleming via AccessMonster.com
  • Start date 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.
 
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?
 
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.
 
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!!
 
Did you set warnings off? (prior to your output to commands, select
SetWarnings, and in the bottom settings, Warnings on should be No)
 
Sorry, should have said that. Yes SetWarnings to No is the first command
in the macro and SetWarnings to Yes is the last.
 
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
 
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:
Back
Top