I have a VBA that has an statement:
Filename = "" & WO & "_" & Tract & "_" & Supplier & "_" & Dates & "_" & Time
& ""
I want to type the time in the excel cell €œ=now()€� instead of typing:
Dates = 20050923 (September 23, 2005)
Time = 1145 ( 11:45AM)

Every time I type in the cell =Now() I have an error message that says €œthe
file cannot be found€�
Does anybody knows how to do it?

Thanks.
Maperalia

Every time you type in the cell =Now()
you get date+time
so & Time is never solely 1145

"maperalia" wrote in message
...

I have a VBA that has an statement:
Filename = "" & WO & "_" & Tract & "_" & Supplier & "_" & Dates & "_" &
Time
& ""
I want to type the time in the excel cell "=now()" instead of typing:
Dates = 20050923 (September 23, 2005)
Time = 1145 ( 11:45AM)

Every time I type in the cell =Now() I have an error message that says
"the
file cannot be found"
Does anybody knows how to do it?

Thanks.
Maperalia

I think the problem may lie in the underlying value of the date or
time. Dates and times are stored as Longs. So when you think you're
retrieving the "1145 AM" value from the cell, you're actually
retrieving a date serial number. Try changing it as follows:

- For the Dates cell, set the formula to:
=TEXT(TODAY(),"yyyymmdd").

- For the Time cell, set the formula to:
=TEXT(TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()) ),"hhmm AM/PM")

Then in the VBA code get the
Dates = ActiveSheet.Range("A1").Text 'change to actual cell
Time = ActiveSheet.Range("B1").Text 'change to actual cell

The =TEXT() formula isn't absolutely necessary. If you grab the
Range.Value property, you'll likely get some serial number for the date
and time. But if you use Range.Text, you'll get the text as displayed.

Using the =TEXT() formula in the cells is just an added insurance
policy. It let's you use the Range.Value or Range.Text and still get
the correct result.


HTH,

Nick Hebb
BreezeTree Software
http://www.breezetree.com

I think the problem may lie in the underlying value of the date or
time. Dates and times are stored as Longs. So when you think you're
retrieving the "1145 AM" value from the cell, you're actually
retrieving a date serial number. Try changing it as follows:

- For the Dates cell, set the formula to:
=TEXT(TODAY(),"yyyymmdd").

- For the Time cell, set the formula to:
=TEXT(TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()) ),"hhmm AM/PM")

Then in the VBA code get the
Dates = ActiveSheet.Range("A1").Text 'change to actual cell
Time = ActiveSheet.Range("B1").Text 'change to actual cell

The =TEXT() formula isn't absolutely necessary. If you grab the
Range.Value property, you'll likely get some serial number for the date
and time. But if you use Range.Text, you'll get the text as displayed.

Using the =TEXT() formula in the cells is just an added insurance
policy. It let's you use the Range.Value or Range.Text and still get
the correct result.


HTH,

Nick Hebb
BreezeTree Software
http://www.breezetree.com

So Dates and Time are variables that refer back to cells on a worksheet.

(I think I'd stay away from Time as a variable name.)

dim myDate as string
dim myTime as string
dim myDateTime as string

mydate = format(worksheets("sheet1").range("a1").value,"yyy ymmdd")
mytime = format(worksheets("sheet1").range("a2").value,"hhm m")

or if you have =now() in that cell:
mydatetime = format(worksheets("sheet1").range("a3").value,"yyy ymmdd_hhmm")

Then you could use:

Filename _
= "" & WO & "_" & Tract & "_" & Supplier & "_" & myDate & "_" & myTime

or
Filename = "" & WO & "_" & Tract & "_" & Supplier & "_" & mydatetime

=====
You've got to remove those slashes from the date and colons from the time.


maperalia wrote:

I have a VBA that has an statement:
Filename = "" & WO & "_" & Tract & "_" & Supplier & "_" & Dates & "_" & Time
& ""
I want to type the time in the excel cell €œ=now()€� instead of typing:
Dates = 20050923 (September 23, 2005)
Time = 1145 ( 11:45AM)

Every time I type in the cell =Now() I have an error message that says €œthe
file cannot be found€�
Does anybody knows how to do it?

Thanks.
Maperalia

--

Dave Peterson

Dave;
Thanks for your response. I put the information but I got the following
error message:

Run-time error '6'
over flow

Then is hightligthing in the statement:

Filename = "" & WO & "_" & Tract & "_" & Supplier & "_" & mydatetime

Could you please tell me how to fix it please?

Regards.
Maperalia

"Dave Peterson" wrote:

So Dates and Time are variables that refer back to cells on a worksheet.

(I think I'd stay away from Time as a variable name.)

dim myDate as string
dim myTime as string
dim myDateTime as string

mydate = format(worksheets("sheet1").range("a1").value,"yyy ymmdd")
mytime = format(worksheets("sheet1").range("a2").value,"hhm m")

or if you have =now() in that cell:
mydatetime = format(worksheets("sheet1").range("a3").value,"yyy ymmdd_hhmm")

Then you could use:

Filename _
= "" & WO & "_" & Tract & "_" & Supplier & "_" & myDate & "_" & myTime

or
Filename = "" & WO & "_" & Tract & "_" & Supplier & "_" & mydatetime

=====
You've got to remove those slashes from the date and colons from the time.


maperalia wrote:

I have a VBA that has an statement:
Filename = "" & WO & "_" & Tract & "_" & Supplier & "_" & Dates & "_" & Time
& ""
I want to type the time in the excel cell €œ=now()€� instead of typing:
Dates = 20050923 (September 23, 2005)
Time = 1145 ( 11:45AM)

Every time I type in the cell =Now() I have an error message that says €œthe
file cannot be found€�
Does anybody knows how to do it?

Thanks.
Maperalia

--

Dave Peterson

One of those variables isn't large enough to hold what you're trying to put into
it.

It might be as simple as changing:

dim WO as Integer
to
dim WO as Long

(but that's just a guess--without seeing the code and knowing the data, it's
difficult to say.)

maperalia wrote:

Dave;
Thanks for your response. I put the information but I got the following
error message:

Run-time error '6'
over flow

Then is hightligthing in the statement:

Filename = "" & WO & "_" & Tract & "_" & Supplier & "_" & mydatetime

Could you please tell me how to fix it please?

Regards.
Maperalia

"Dave Peterson" wrote:

So Dates and Time are variables that refer back to cells on a worksheet.

(I think I'd stay away from Time as a variable name.)

dim myDate as string
dim myTime as string
dim myDateTime as string

mydate = format(worksheets("sheet1").range("a1").value,"yyy ymmdd")
mytime = format(worksheets("sheet1").range("a2").value,"hhm m")

or if you have =now() in that cell:
mydatetime = format(worksheets("sheet1").range("a3").value,"yyy ymmdd_hhmm")

Then you could use:

Filename _
= "" & WO & "_" & Tract & "_" & Supplier & "_" & myDate & "_" & myTime

or
Filename = "" & WO & "_" & Tract & "_" & Supplier & "_" & mydatetime

=====
You've got to remove those slashes from the date and colons from the time.


maperalia wrote:

I have a VBA that has an statement:
Filename = "" & WO & "_" & Tract & "_" & Supplier & "_" & Dates & "_" & Time
& ""
I want to type the time in the excel cell €œ=now()€� instead of typing:
Dates = 20050923 (September 23, 2005)
Time = 1145 ( 11:45AM)

Every time I type in the cell =Now() I have an error message that says €œthe
file cannot be found€�
Does anybody knows how to do it?

Thanks.
Maperalia

--

Dave Peterson


--

Dave Peterson

Dave;
Thank you very much for your advise. The program is working
WONDERFUL!!!!!!!!!.

I really appreciate your helping me in this matter.

Best regards.
Maperalia

"Dave Peterson" wrote:

One of those variables isn't large enough to hold what you're trying to put into
it.

It might be as simple as changing:

dim WO as Integer
to
dim WO as Long

(but that's just a guess--without seeing the code and knowing the data, it's
difficult to say.)

maperalia wrote:

Dave;
Thanks for your response. I put the information but I got the following
error message:

Run-time error '6'
over flow

Then is hightligthing in the statement:

Filename = "" & WO & "_" & Tract & "_" & Supplier & "_" & mydatetime

Could you please tell me how to fix it please?

Regards.
Maperalia

"Dave Peterson" wrote:

So Dates and Time are variables that refer back to cells on a worksheet.

(I think I'd stay away from Time as a variable name.)

dim myDate as string
dim myTime as string
dim myDateTime as string

mydate = format(worksheets("sheet1").range("a1").value,"yyy ymmdd")
mytime = format(worksheets("sheet1").range("a2").value,"hhm m")

or if you have =now() in that cell:
mydatetime = format(worksheets("sheet1").range("a3").value,"yyy ymmdd_hhmm")

Then you could use:

Filename _
= "" & WO & "_" & Tract & "_" & Supplier & "_" & myDate & "_" & myTime

or
Filename = "" & WO & "_" & Tract & "_" & Supplier & "_" & mydatetime

=====
You've got to remove those slashes from the date and colons from the time.


maperalia wrote:

I have a VBA that has an statement:
Filename = "" & WO & "_" & Tract & "_" & Supplier & "_" & Dates & "_" & Time
& ""
I want to type the time in the excel cell €ŀœ=now()€ÂÂ� instead of typing:
Dates = 20050923 (September 23, 2005)
Time = 1145 ( 11:45AM)

Every time I type in the cell =Now() I have an error message that says €ŀœthe
file cannot be found€ÂÂ�
Does anybody knows how to do it?

Thanks.
Maperalia

--

Dave Peterson


--

Dave Peterson