Hi Ken,
I don't know of a way to delete a file using a macro
command directly (if anyone else does hopefully they will
respond to this post). But, you could easily write a
simple little function in VBA to do this if you want.
Then, you could call the function from the macro using
the RunCode Action. I assume that you meant that you
would want to do this after the import, not before, so
you would want to call this function after the import.
It is a little dangerous to delete the file without
checking the import though, unless you have another copy
or a way of easily recreating it.
To create a custom function, you just create a new module
(go to the modules section and click new), then from the
menu choose Insert|Procedure from the menu and then
choose Function and Public for the options and type the
function name that you want.
Access will then insert the function header line and end
lines for you. All you have to do then is type a line in
the area between with the code:
Kill "C:\YourFolderPath\KenCSV.csv"
Where you would of course substitute the actual path and
filename.
The function would look something like this:
Public Function DeleteFile()
Kill "C:\YourFilePath\KenCSV.csv"
End Function
The above function would always delete the same file.
Not sure what the Kill statement will do if it does not
find the file (it may generate an error). To call the
function you would just use the function name and empty
()'s such as DeleteFile().
You could make the function a little more flexible by
designing it to accept the filename to be deleted as a
variable, and adding an error handler, such as:
Public Function DeleteFile(DelPathAndFileName)
On Error Resume Next
Kill DelPathAndFileName
ExitLine:
End Function
Note that to call this function, you would have to
include the path and filename in quotes between the ()'s,
such as:
DeleteFile("C:\YourFilePath\KenCSV.csv").
When you are done writing the function close the vb
window and you will be prompted for the module name. Name
the module whatever you want. I usually put my custom
functions in a module titled just that -
CustomFunctions. You can insert many functions or
standard procedures in a single module.
Also, for future general reference regarding custom
functions, they are often used to return values to
queries, or objects in forms or reports similar to the
built in functions. To use them in that regard, you have
to assign a value to a variable matching the function
name. Your custom functions are listed under the
database name under functions in the query builder (just
below the list of built in functions).
The custom function listed above is a different way of
using a function, in that it is not used to return a
value. It is only a function rather than a standard
procedure because as far as I know macros will only allow
you to call functions rather than procedures. I could be
wrong about this though, since I don't really use macros
all that much. I often just use VBA to do what I want.
For instance, in your case, I would have probably just
had a button on a form, with code that runs when
clicked. The TransferText command and Kill statement
could all then be contained in the standard procedure for
the buttons click event.
In the long run if you want to do a lot of automation
with Access you should probably start getting used to at
least the basics of VBA. It really isn't that difficult
to learn, and there are a lot of people willing to help
with code for specific tasks in this user group.
Hopefully this will help.
Ted Allen
-----Original Message-----
Ted,
That was perfect. My only issue now is how to delete
the CSV file prior to running the macro. I wrote a Batch
file to do it, but is is a little ackward and was hoping
Access could delete a file in a macro.
