Export to Replace Existing Spreadsheet - Part 2

[RFJ]

The following is a shortened version of another post and its a problem I'm
also trying to overcome.

I've followed the advice using vba code and the kill statement and it is now
sitting as a module - there were no reported errors in its creation.
However, as someone on the lower part of their vba learning curve I'm now
stuck in terms of how I use that code.

I see I can create a macro using RunCode - and in the Expression Builder I
can find, under functions, a reference to the VBA code.

However, if I run the macro I get the message 'The expression you entered as
a function name that Access can't find."

The "Action Failed" window of the macro shows ExcelUpdateMacro () as the
culprit.

The module is called Converted_ExcelUpdateMacro; the function within the
module is called ExcelUpdateMacro()

Can SKS help out.

TIA

Rob

----
Hi Tom,

Just delete (or rename) the old Excel file before creating a new one.
This is simple if you're using VBA code: just use the Kill statement

Dim strFileSpec As String
...
strFileSpec = "D:\Folder\File.xls"
Kill strFileSpec
....
<cut>

I have an Access macro that exports data to Excel at the end of each month.

Since the workbook from the prior month is there, Access inserts the data as a new worksheet.

I would like to export the data and have it replace the entire workbook

with a NEW workbook (not just a new worksheet) of the same name.

 

[John Nurick]

Hi Rob,

The usual way of letting the user run a VBA procedure is to put a
CommandButton on a form, set the button's OnClick property to [Event
Procedure], and call the procedure in the procedure that's displayed
when you click the [...] button next to OnClick in the property sheet.

The error message you get when you try to call the procedure in a macro
is because you can only call Function procedures from macros, not Sub
procedures. One way round this is to create a dummy function that does
nothing but call the procedure you really want, e.g. (in your existing
module)

Public Function CallMySub() As Long
MySub
End Function

and have the macro do
CallMySub()
instead of
MySub

 

[RFJ]

Excellent and thanks. It's all working now <BG>

Rob

Hi Rob,

The usual way of letting the user run a VBA procedure is to put a
CommandButton on a form, set the button's OnClick property to [Event
Procedure], and call the procedure in the procedure that's displayed
when you click the [...] button next to OnClick in the property sheet.

The error message you get when you try to call the procedure in a macro
is because you can only call Function procedures from macros, not Sub
procedures. One way round this is to create a dummy function that does
nothing but call the procedure you really want, e.g. (in your existing
module)

Public Function CallMySub() As Long
MySub
End Function

and have the macro do
CallMySub()
instead of
MySub

The following is a shortened version of another post and its a problem I'm
also trying to overcome.

I've followed the advice using vba code and the kill statement and it is now
sitting as a module - there were no reported errors in its creation.
However, as someone on the lower part of their vba learning curve I'm now
stuck in terms of how I use that code.

I see I can create a macro using RunCode - and in the Expression Builder I
can find, under functions, a reference to the VBA code.

However, if I run the macro I get the message 'The expression you entered as
a function name that Access can't find."

The "Action Failed" window of the macro shows ExcelUpdateMacro () as the
culprit.

The module is called Converted_ExcelUpdateMacro; the function within the
module is called ExcelUpdateMacro()

Can SKS help out.

TIA

Rob

----
Hi Tom,

Just delete (or rename) the old Excel file before creating a new one.
This is simple if you're using VBA code: just use the Kill statement

Dim strFileSpec As String
...
strFileSpec = "D:\Folder\File.xls"
Kill strFileSpec
...
<cut>

data
as a new worksheet.
with a NEW workbook (not just a new worksheet) of the same name.