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

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)

wrote in message
oups.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,

How is the data entered into the spreadsheet?

is it just typed into a cell?

Duncan


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

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



wrote in message
oups.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

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



wrote in message
oups.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

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

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


" 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

VBA has its own,. Format.

--
HTH

Bob Phillips

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

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


" 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

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.


" 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

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
skrev i melding
oups.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.


" 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

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)

wrote in message
oups.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.


" 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

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" wrote in message
...
Or application.text(date,"yyyymmdd") in your code.


" 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

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.


" 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.


" 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