Prev: test1
Next: Requery not update until 2nd record?
From: alex on 3 Jul 2008 09:59
Hi experts,

using Access '03...

I need a macro that transfers the results of an .mdb query to a
preexisting .xls doc.

I accomplished most of this by creating a transferspreadsheet macro
and populating the Range argument of the macro.

Problem is, however; I want the macro to create a new spreadsheet
everytime the macro executes (the query is a monthly report) without
user intervention. I tried to code the range arguement with something
like: Date() & "monthly" >> This argument, however, takes all text
literally and the spreadsheet name comes out Date___monthly.

I need to code this argument (Range) with something that will
automatically change the spreadsheet name each time the macro runs,
else, it will overwrite whatever is in the Range argument. Any
suggestions?

alex
From: Ken Snell (MVP) on 3 Jul 2008 14:52
Range argument for the macro would look like this:

=Date() & "_monthly"

Be sure to include the = sign at the beginning of the argument.

--

Ken Snell
<MS ACCESS MVP>


"alex" <sql_aid(a)yahoo.com> wrote in message
news:e0700700-9578-4c60-9bc8-199b6184ecdd(a)w7g2000hsa.googlegroups.com...
> Hi experts,
>
> using Access '03...
>
> I need a macro that transfers the results of an .mdb query to a
> preexisting .xls doc.
>
> I accomplished most of this by creating a transferspreadsheet macro
> and populating the Range argument of the macro.
>
> Problem is, however; I want the macro to create a new spreadsheet
> everytime the macro executes (the query is a monthly report) without
> user intervention. I tried to code the range arguement with something
> like: Date() & "monthly" >> This argument, however, takes all text
> literally and the spreadsheet name comes out Date___monthly.
>
> I need to code this argument (Range) with something that will
> automatically change the spreadsheet name each time the macro runs,
> else, it will overwrite whatever is in the Range argument. Any
> suggestions?
>
> alex


From: alex on 8 Jul 2008 07:49
Thanks Ken...never thought to use =

alex

Ken Snell (MVP) wrote:
> Range argument for the macro would look like this:
>
> =Date() & "_monthly"
>
> Be sure to include the = sign at the beginning of the argument.
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
>
> "alex" <sql_aid(a)yahoo.com> wrote in message
> news:e0700700-9578-4c60-9bc8-199b6184ecdd(a)w7g2000hsa.googlegroups.com...
> > Hi experts,
> >
> > using Access '03...
> >
> > I need a macro that transfers the results of an .mdb query to a
> > preexisting .xls doc.
> >
> > I accomplished most of this by creating a transferspreadsheet macro
> > and populating the Range argument of the macro.
> >
> > Problem is, however; I want the macro to create a new spreadsheet
> > everytime the macro executes (the query is a monthly report) without
> > user intervention. I tried to code the range arguement with something
> > like: Date() & "monthly" >> This argument, however, takes all text
> > literally and the spreadsheet name comes out Date___monthly.
> >
> > I need to code this argument (Range) with something that will
> > automatically change the spreadsheet name each time the macro runs,
> > else, it will overwrite whatever is in the Range argument. Any
> > suggestions?
> >
> > alex
 | 
Pages: 1
Prev: test1
Next: Requery not update until 2nd record?