Help me automate a macro

[Timothy.Rybak@gmail.com]

I have an Excel report that I have to run every day. It requires one
piece of manually entered data. I have to type in the date in the
format yyyymmdd (ex. 20060713). If I could figure out a way to get
this data into the program automatically, then I could set the macro to
run on a schedule and I wouldn't have to do it every morning.

Any help is appreciated.

TIm

[Bob Phillips]

Just use

=TEXT(TODAY(),"yyyymmdd")

in a cell, or even just

=TODAY()

and format as "yyyymmdd"

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

<Timothy.Rybak@gmail.com> wrote in message
news:1152879347.103976.319720@p79g2000cwp.googlegroups.com...
> I have an Excel report that I have to run every day. It requires one
> piece of manually entered data. I have to type in the date in the
> format yyyymmdd (ex. 20060713). If I could figure out a way to get
> this data into the program automatically, then I could set the macro to
> run on a schedule and I wouldn't have to do it every morning.
>
> Any help is appreciated.
>
> TIm
>

[Duncan]

Timothy,

How is the data entered into the spreadsheet?

is it just typed into a cell?

Duncan


Timothy.Rybak@gmail.com wrote:

> I have an Excel report that I have to run every day. It requires one
> piece of manually entered data. I have to type in the date in the
> format yyyymmdd (ex. 20060713). If I could figure out a way to get
> this data into the program automatically, then I could set the macro to
> run on a schedule and I wouldn't have to do it every morning.
>
> Any help is appreciated.
>
> TIm

[Norman Jones]

Hi Tim,

If I understand you, try:

'=============>>
Public Sub Tester()
Dim res

res = InputBox(Prompt:="Enter date", _
Default:=Format(Date, "yyyymmdd"))

If IsDate(res) Then res = Format(res, "yyyymmdd")

'your code

End Sub
'<<=============



---
Regards,
Norman



<Timothy.Rybak@gmail.com> wrote in message
news:1152879347.103976.319720@p79g2000cwp.googlegroups.com...
>I have an Excel report that I have to run every day. It requires one
> piece of manually entered data. I have to type in the date in the
> format yyyymmdd (ex. 20060713). If I could figure out a way to get
> this data into the program automatically, then I could set the macro to
> run on a schedule and I wouldn't have to do it every morning.
>
> Any help is appreciated.
>
> TIm
>

[Timothy.Rybak@gmail.com]

I should have given a bit more info, sorry. The data is currently
being entered via an input box. I was envisioning a way to do it with
no input box, using only variables to get at the date.

Tim




Norman Jones wrote:
> Hi Tim,
>
> If I understand you, try:
>
> '=============>>
> Public Sub Tester()
> Dim res
>
> res = InputBox(Prompt:="Enter date", _
> Default:=Format(Date, "yyyymmdd"))
>
> If IsDate(res) Then res = Format(res, "yyyymmdd")
>
> 'your code
>
> End Sub
> '<<=============
>
>
>
> ---
> Regards,
> Norman
>
>
>
> <Timothy.Rybak@gmail.com> wrote in message
> news:1152879347.103976.319720@p79g2000cwp.googlegroups.com...
> >I have an Excel report that I have to run every day. It requires one
> > piece of manually entered data. I have to type in the date in the
> > format yyyymmdd (ex. 20060713). If I could figure out a way to get
> > this data into the program automatically, then I could set the macro to
> > run on a schedule and I wouldn't have to do it every morning.
> >
> > Any help is appreciated.
> >
> > TIm
> >

[Norman Jones]

Hi Tim,

> I was envisioning a way to do it with
> no input box, using only variables to get at the date.


Dim myDate As Date
Dim Rng As Range

Set Rng = Ramge("A1")

myDate = Format(Rng.Value, "yyyymmdd")


---
Regards,
Norman

[JMB]

Or application.text(date,"yyyymmdd") in your code.


"Timothy.Rybak@gmail.com" wrote:

> I have an Excel report that I have to run every day. It requires one
> piece of manually entered data. I have to type in the date in the
> format yyyymmdd (ex. 20060713). If I could figure out a way to get
> this data into the program automatically, then I could set the macro to
> run on a schedule and I wouldn't have to do it every morning.
>
> Any help is appreciated.
>
> TIm
>
>

[Bob Phillips]

VBA has its own,. Format.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"JMB" <JMB@discussions.microsoft.com> wrote in message
news:87FBCAA9-B744-42C1-AA41-24F02752BA81@microsoft.com...
> Or application.text(date,"yyyymmdd") in your code.
>
>
> "Timothy.Rybak@gmail.com" wrote:
>
> > I have an Excel report that I have to run every day. It requires one
> > piece of manually entered data. I have to type in the date in the
> > format yyyymmdd (ex. 20060713). If I could figure out a way to get
> > this data into the program automatically, then I could set the macro to
> > run on a schedule and I wouldn't have to do it every morning.
> >
> > Any help is appreciated.
> >
> > TIm
> >
> >

[Timothy.Rybak@gmail.com]

application.text(date,"yyyymmdd") is AWESOME! A single line of code
that does exactly what I need.

Now, the last piece of the puzzle is how to run the macro at a given
time each day. Would it be better to set the macro to autorun when
opened and then create a windows scheduled task to open the file every
day, or is there some way to leave the file open and have the macro
automatically kick off at a give time each day?

Tim

JMB wrote:
> Or application.text(date,"yyyymmdd") in your code.
>
>
> "Timothy.Rybak@gmail.com" wrote:
>
> > I have an Excel report that I have to run every day. It requires one
> > piece of manually entered data. I have to type in the date in the
> > format yyyymmdd (ex. 20060713). If I could figure out a way to get
> > this data into the program automatically, then I could set the macro to
> > run on a schedule and I wouldn't have to do it every morning.
> >
> > Any help is appreciated.
> >
> > TIm
> >
> >

[DS-NTE]

I would go for the windows scheduled task option, don't think its an good
idea to have the file open all the time - you may forget to open it every
day or you may close it by mistake...


knut
<Timothy.Rybak@gmail.com> skrev i melding
news:1152883699.704447.213000@h48g2000cwc.googlegroups.com...
> application.text(date,"yyyymmdd") is AWESOME! A single line of code
> that does exactly what I need.
>
> Now, the last piece of the puzzle is how to run the macro at a given
> time each day. Would it be better to set the macro to autorun when
> opened and then create a windows scheduled task to open the file every
> day, or is there some way to leave the file open and have the macro
> automatically kick off at a give time each day?
>
> Tim
>
> JMB wrote:
>> Or application.text(date,"yyyymmdd") in your code.
>>
>>
>> "Timothy.Rybak@gmail.com" wrote:
>>
>> > I have an Excel report that I have to run every day. It requires one
>> > piece of manually entered data. I have to type in the date in the
>> > format yyyymmdd (ex. 20060713). If I could figure out a way to get
>> > this data into the program automatically, then I could set the macro to
>> > run on a schedule and I wouldn't have to do it every morning.
>> >
>> > Any help is appreciated.
>> >
>> > TIm
>> >
>> >
>

[Bob Phillips]

As I said, you don't need a call to a worksheet function

Format(date,"yyyymmdd")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

<Timothy.Rybak@gmail.com> wrote in message
news:1152883699.704447.213000@h48g2000cwc.googlegroups.com...
> application.text(date,"yyyymmdd") is AWESOME! A single line of code
> that does exactly what I need.
>
> Now, the last piece of the puzzle is how to run the macro at a given
> time each day. Would it be better to set the macro to autorun when
> opened and then create a windows scheduled task to open the file every
> day, or is there some way to leave the file open and have the macro
> automatically kick off at a give time each day?
>
> Tim
>
> JMB wrote:
> > Or application.text(date,"yyyymmdd") in your code.
> >
> >
> > "Timothy.Rybak@gmail.com" wrote:
> >
> > > I have an Excel report that I have to run every day. It requires one
> > > piece of manually entered data. I have to type in the date in the
> > > format yyyymmdd (ex. 20060713). If I could figure out a way to get
> > > this data into the program automatically, then I could set the macro
to
> > > run on a schedule and I wouldn't have to do it every morning.
> > >
> > > Any help is appreciated.
> > >
> > > TIm
> > >
> > >
>

[JMB]

I had forgotten. Thanks for reminding. BTW -how is the paper on using class
modules coming? Sorry to get off-topic, but was curious.


"Bob Phillips" wrote:

> VBA has its own,. Format.
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "JMB" <JMB@discussions.microsoft.com> wrote in message
> news:87FBCAA9-B744-42C1-AA41-24F02752BA81@microsoft.com...
> > Or application.text(date,"yyyymmdd") in your code.
> >
> >
> > "Timothy.Rybak@gmail.com" wrote:
> >
> > > I have an Excel report that I have to run every day. It requires one
> > > piece of manually entered data. I have to type in the date in the
> > > format yyyymmdd (ex. 20060713). If I could figure out a way to get
> > > this data into the program automatically, then I could set the macro to
> > > run on a schedule and I wouldn't have to do it every morning.
> > >
> > > Any help is appreciated.
> > >
> > > TIm
> > >
> > >
>
>
>

[JMB]

As Bob pointed out, VBA has its own function to format (which is ironically
called format -you'd think that would be an easy one to remember!).

Format(Date, "yyyymmdd")

If you want to leave it open, you could use OnTime (check VBA help for
details) to run the macro. You could put this in a Workbook_Open event
handler and leave the workbook open (at least until it runs). I've never
needed the Windows Task Scheduler, so I don't have an opinion on which would
be more functional.


"Timothy.Rybak@gmail.com" wrote:

> application.text(date,"yyyymmdd") is AWESOME! A single line of code
> that does exactly what I need.
>
> Now, the last piece of the puzzle is how to run the macro at a given
> time each day. Would it be better to set the macro to autorun when
> opened and then create a windows scheduled task to open the file every
> day, or is there some way to leave the file open and have the macro
> automatically kick off at a give time each day?
>
> Tim
>
> JMB wrote:
> > Or application.text(date,"yyyymmdd") in your code.
> >
> >
> > "Timothy.Rybak@gmail.com" wrote:
> >
> > > I have an Excel report that I have to run every day. It requires one
> > > piece of manually entered data. I have to type in the date in the
> > > format yyyymmdd (ex. 20060713). If I could figure out a way to get
> > > this data into the program automatically, then I could set the macro to
> > > run on a schedule and I wouldn't have to do it every morning.
> > >
> > > Any help is appreciated.
> > >
> > > TIm
> > >
> > >
>
>