Automatically Overwriting Files

[access_noob]

Hi everyone,

I have set up a temporary macro named "AutoExec" that outputs a predefined query to an HTML file and an excel file. These files need to be updated often so our website is as current as possible. The issue has become the annoyance of these two prompts that pop up that ask the user to decide to overwrite the existing file or not. The answer is ALWAYS yes. The macro works nicely otherwise.

I have tried to insert code that I found online that deletes the read-only files before performing the macro but Access never seems to recognize my function so the macro doesn't work.

Can someone please provide me with some direction on how to solve this issue.

Also, is it possible to convert the macro to VBA using access, insert the necessary code above the DoCmd.OutputTo commands, save that, and have access automatically execute that module?

I apologize for my inexperience and thanks in advance for any help.

The file paths are read only and are as follows:

F:\Quality Control\QC_Web\Database\HPLC Daily Report.html
F:\Quality Control\HPLC\HPLC reporting\HPLC Reporting Database - Official.xlsx

 

[Ranman256]

Before you export the file, delete it via vb....

Killfile "c:\folder\myFile.htm"

 

[sneuberg]

Before you export the file, delete it via vb....

Killfile "c:\folder\myFile.htm"

I think that is

Kill "c:\folder\myFile.htm"


Also http://word.mvps.org/faqs/macrosvba/DeleteFiles.htm might be of interest.

 

[access_noob]

I think that is

Kill "c:\folder\myFile.htm"


Also "link" might be of interest.

I actually took a look at this about a week ago and copied the code to the best of my ability. this is the code I used:

----code below--------

Public Sub KillProperly(Killfile As String)
If Len(Dir$(Killfile)) > 0 Then
SetAttr KillFile, vbNormal
Kill KillFile
End If
End Sub

----code above------

I then entered 2 macros and put them before the two export with formatting commands.

in the two RunCode macros I entered the Function name fields like so,
KillProperly("F:\Quality Control\QC_Web\Database\HPLC Daily Report.html")

and the same for the other file path but Access puts an error that says, "The expression you entered has a function name that Microsoft Access can't find."

What is going on?

 

[access_noob]

also, the folder where my file is located in is read only.

 

[sneuberg]

Yeah I get the same thing. I don't use, understand or want to understand macros. I suggest learning VBA. Anyway if you turn it into a function as follows it works even on a readonly file.

Public Function KillProperly(Killfile As String)

If Len(Dir$(Killfile)) > 0 Then
SetAttr Killfile, vbNormal
Kill Killfile
End If

End Function

 

[access_noob]

Yeah I get the same thing. I don't use, understand or want to understand macros. I suggest learning VBA. Anyway if you turn it into a function as follows it works even on a readonly file.

Public Function KillProperly(Killfile As String)

If Len(Dir$(Killfile)) > 0 Then
SetAttr Killfile, vbNormal
Kill Killfile
End If

End Function

Yes, I would actually prefer to use VBA as well.

I am a little more familiar with Ruby and Python. I want to learn VBA but I seem to have no clue how to make a script play.

Here is what I am doing:

I go to Access 2010, in the "database tools" area I select the "visual Basic" icon. Microsoft Visual Basic for Applications opens and I am presented with a blank editable form with two tabs just above. One tab says "(General)" and next to it the tab says "(Declarations)".

I enter code under the "Option Compare Database" and the "(Declarations)" changes to "KillProperly".

Here is the code:

Option Compare Database
 '------------------------------------------------------------
' AutoExec
'
'------------------------------------------------------------
 Public Function KillProperly(Killfile As String)
 If Len(Dir$(Killfile)) > 0 Then
SetAttr Killfile, vbNormal
Kill Killfile
End If
 End Function
 Function AutoExec()
     DoCmd.OutputTo acOutputQuery, "HPLC Daily Report HTML", "HTML(*.html)", "F:\Quality Control\QC_Web\Database\HPLC Daily Report.html", False, "", , acExportQualityScreen
    DoCmd.OutputTo acOutputQuery, "HPLC Daily Report HTML", "ExcelWorkbook(*.xlsx)", "F:\Quality Control\HPLC\HPLC reporting\HPLC Reporting Database - Official.xlsx", False, "", , acExportQualityPrint
 End Function
 
KillProperly ("F:\Quality Control\QC_Web\Database\HPLC Daily Report.html")
KillProperly ("F:\Quality Control\HPLC\HPLC reporting\HPLC Reporting Database - Official.xlsx")
 DoCmd.OutputTo acOutputQuery, "HPLC Daily Report HTML", "HTML(*.html)", "F:\Quality Control\QC_Web\Database\HPLC Daily Report.html", False, "", , acExportQualityScreen
DoCmd.OutputTo acOutputQuery, "HPLC Daily Report HTML", "ExcelWorkbook(*.xlsx)", "F:\Quality Control\HPLC\HPLC reporting\HPLC Reporting Database - Official.xlsx", False, "", , acExportQualityPrint

Thanks for the help all!

 

[Acropolis]

You will still need a macro called auto exec to call the VBA code that you want to run on start up, so in the auto exec macro, enter the line Run Code (Function Name Here) and that will then call the function you want. You have called it AutoExec in your code above, i would personally change that to something else.

another problem you might be having, is that you said the folder where the files are is read only? That would cause some problem with deleting etc.

HTH