Hi,

Im trying to open a non excel file ( Textpad kind of file) from excel and
then saving it as excel type file using the following code ( I made the code
by recording the macro).

I added DisplayAlerts statements at the appropriate places so that
overwriting could be done .

Inspite of the above if dev11112 already exists then I get a message saying
that "A file named '......' already exists in that location. Do you want to
replace it?

Is there something wrong/incorrect in my displayalerts statement?

Workbooks.OpenText Filename:= _
"C:\Documents and Settings\abc\Desktop\dev11112.lst", Origin:=437,
StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True

DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\abc\Desktop\dev11112.xls",
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

ActiveWorkbook.Close

DisplayAlerts = True

--
Thanks a lot,
Hari
India

Hi Hari,

Try changing:


DisplayAlerts = False

to

Application. DisplayAlerts = False


---
Regards,
Norman



"Hari" wrote in message
...
Hi,

Im trying to open a non excel file ( Textpad kind of file) from excel and
then saving it as excel type file using the following code ( I made the
code
by recording the macro).

I added DisplayAlerts statements at the appropriate places so that
overwriting could be done .

Inspite of the above if dev11112 already exists then I get a message
saying
that "A file named '......' already exists in that location. Do you want
to
replace it?

Is there something wrong/incorrect in my displayalerts statement?

Workbooks.OpenText Filename:= _
"C:\Documents and Settings\abc\Desktop\dev11112.lst", Origin:=437,
StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True

DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\abc\Desktop\dev11112.xls",
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

ActiveWorkbook.Close

DisplayAlerts = True

--
Thanks a lot,
Hari
India

Hi Hari,

Just to add, you would similarly need to amend the closing:

DisplayAlerts = True

to

Application. DisplayAlerts = True


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Hari,

Try changing:


DisplayAlerts = False

to

Application. DisplayAlerts = False


---
Regards,
Norman

Hi Norman,

Thanks a ton for your kind help.

Thanks again,
Hari
India


"Norman Jones" wrote in message
...
Hi Hari,

Just to add, you would similarly need to amend the closing:

DisplayAlerts = True

to

Application. DisplayAlerts = True


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Hari,

Try changing:


DisplayAlerts = False

to

Application. DisplayAlerts = False


---
Regards,
Norman

Hi Norman,

Just from understanding point of view....

When I previously wrote it as -- DisplayAlerts = True -- how does excel
interpret the statement. Is NOT writing Application. NOT considered as an
error. Does that mean DisplayAlerts = True is also a valid statement on its
own and if so what does it do.

Thanks a lot,
Hari
India

"Norman Jones" wrote in message
...
Hi Hari,

Just to add, you would similarly need to amend the closing:

DisplayAlerts = True

to

Application. DisplayAlerts = True


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Hari,

Try changing:


DisplayAlerts = False

to

Application. DisplayAlerts = False


---
Regards,
Norman

Hi Hari,

Try runnining this small demo:

Sub Tester()
Debug.Print "Opening State", Application.DisplayAlerts
DisplayAlerts = False
Debug.Print "After DisplayAlerts = False command", _
Application.DisplayAlerts

DisplayAlerts = True
Debug.Print "Closing State", Application.DisplayAlerts

End Sub

The immediate window reports:


Opening State True
After DisplayAlerts = False command True
Closing State True

As you see, if DisplayAlerts is used without the required Application
qualifier, no error is observed but neither is any response.

That is why Excel was throwing up the overwrite alert message. If you rerun
the code (using the qualified construct), no alert should be observed.

For the record, I have not run your code or otherwise examined it. I did,
however, run a test repoducing a file overwrite event.

---
Regards,
Norman



"Hari" wrote in message
...
Hi Norman,

Just from understanding point of view....

When I previously wrote it as -- DisplayAlerts = True -- how does excel
interpret the statement. Is NOT writing Application. NOT considered as an
error. Does that mean DisplayAlerts = True is also a valid statement on
its
own and if so what does it do.

Thanks a lot,
Hari
India

"Norman Jones" wrote in message
...
Hi Hari,

Just to add, you would similarly need to amend the closing:

DisplayAlerts = True

to

Application. DisplayAlerts = True


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Hari,

Try changing:


DisplayAlerts = False

to

Application. DisplayAlerts = False


---
Regards,
Norman

Norman didn't talk about what displayalerts is.

Sub TesterAA()

Debug.Print "Opening State", Application.DisplayAlerts
DisplayAlerts = False
Debug.Print "After DisplayAlerts = False command", _
Application.DisplayAlerts
DisplayAlerts = "Funny Bunny"
Debug.Print "DisplayAlerts is a " & _
TypeName(DisplayAlerts) & " with a value of " & _
DisplayAlerts
DisplayAlerts = True
Debug.Print "Closing State", Application.DisplayAlerts

End Sub

produces:

Opening State True
After DisplayAlerts = False command True
DisplayAlerts is a String with a value of Funny Bunny
Closing State True

---------

so displayalerts = "anything" creates a new variant variable and assigns it
a value.

If you put

Option Explicit

at the top of the module and compile, you will be notified that
DisplayAlerts is undefined.

--
Regards,
Tom Ogilvy

"Norman Jones" wrote in message
...
Hi Hari,

Try runnining this small demo:

Sub Tester()
Debug.Print "Opening State", Application.DisplayAlerts
DisplayAlerts = False
Debug.Print "After DisplayAlerts = False command", _
Application.DisplayAlerts

DisplayAlerts = True
Debug.Print "Closing State", Application.DisplayAlerts

End Sub

The immediate window reports:


Opening State True
After DisplayAlerts = False command True
Closing State True

As you see, if DisplayAlerts is used without the required Application
qualifier, no error is observed but neither is any response.

That is why Excel was throwing up the overwrite alert message. If you
rerun
the code (using the qualified construct), no alert should be observed.

For the record, I have not run your code or otherwise examined it. I did,
however, run a test repoducing a file overwrite event.

---
Regards,
Norman



"Hari" wrote in message
...
Hi Norman,

Just from understanding point of view....

When I previously wrote it as -- DisplayAlerts = True -- how does excel
interpret the statement. Is NOT writing Application. NOT considered as
an
error. Does that mean DisplayAlerts = True is also a valid statement on
its
own and if so what does it do.

Thanks a lot,
Hari
India

"Norman Jones" wrote in message
...
Hi Hari,

Just to add, you would similarly need to amend the closing:

DisplayAlerts = True

to

Application. DisplayAlerts = True


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Hari,

Try changing:


DisplayAlerts = False

to

Application. DisplayAlerts = False


---
Regards,
Norman

Hi Tom and Norman,

Your "creates a new variant variable and assigns it a value." is a superb
one. Its crystal clear for me now.

Actually I ran Norman's and your code and ( my VBE was not set to option
explicit and hence did not get any compile error , variable not defined --
now, I have changed my settings to require variable definition - thanx for
that too) I did not get any output of debug.print.

Is debug.print supposed to show me some result/output. Is it similar to
msgbox. I got no messageboxes saying "DisplayAlerts is a String with a
value of Funny Bunny. Neither do i get anything called "Opening State True"
etc.

I thought debug.print might have fired off printouts (hard copies) to my
printer (a big fool am!!) but found no such printouts.

Before doing the above I consulted VBA help but couldnt understand the terse
description "The Debug object sends output to the Immediate window at
runtime."
When i click on the runtime blue link above I get a message saying "A run
time error has occurred, do you wish to debug"

--
Thanks a lot,
Hari
India
"Tom Ogilvy" wrote in message
...
Norman didn't talk about what displayalerts is.

Sub TesterAA()

Debug.Print "Opening State", Application.DisplayAlerts
DisplayAlerts = False
Debug.Print "After DisplayAlerts = False command", _
Application.DisplayAlerts
DisplayAlerts = "Funny Bunny"
Debug.Print "DisplayAlerts is a " & _
TypeName(DisplayAlerts) & " with a value of " & _
DisplayAlerts
DisplayAlerts = True
Debug.Print "Closing State", Application.DisplayAlerts

End Sub

produces:

Opening State True
After DisplayAlerts = False command True
DisplayAlerts is a String with a value of Funny Bunny
Closing State True

---------

so displayalerts = "anything" creates a new variant variable and assigns
it
a value.

If you put

Option Explicit

at the top of the module and compile, you will be notified that
DisplayAlerts is undefined.

--
Regards,
Tom Ogilvy

"Norman Jones" wrote in message
...
Hi Hari,

Try runnining this small demo:

Sub Tester()
Debug.Print "Opening State", Application.DisplayAlerts
DisplayAlerts = False
Debug.Print "After DisplayAlerts = False command", _
Application.DisplayAlerts

DisplayAlerts = True
Debug.Print "Closing State", Application.DisplayAlerts

End Sub

The immediate window reports:


Opening State True
After DisplayAlerts = False command True
Closing State True

As you see, if DisplayAlerts is used without the required Application
qualifier, no error is observed but neither is any response.

That is why Excel was throwing up the overwrite alert message. If you
rerun
the code (using the qualified construct), no alert should be observed.

For the record, I have not run your code or otherwise examined it. I
did,
however, run a test repoducing a file overwrite event.

---
Regards,
Norman



"Hari" wrote in message
...
Hi Norman,

Just from understanding point of view....

When I previously wrote it as -- DisplayAlerts = True -- how does
excel
interpret the statement. Is NOT writing Application. NOT considered as
an
error. Does that mean DisplayAlerts = True is also a valid statement
on
its
own and if so what does it do.

Thanks a lot,
Hari
India

"Norman Jones" wrote in message
...
Hi Hari,

Just to add, you would similarly need to amend the closing:

DisplayAlerts = True

to

Application. DisplayAlerts = True


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Hari,

Try changing:


DisplayAlerts = False

to

Application. DisplayAlerts = False


---
Regards,
Norman

In the VBE go to the View menu and click Immediate window so it has a check
next to it. It will appear as another window in the VBE.

Debug Print
outputs to the immediate window. It is just another debugging tool. You
can also type a complete VBA statement in it and hit enter and it will
immediately execute

For instance, If I wanted to delete all shapes on my worksheet, but didn't
want to select them all, I could go to the immediate window in the VBE and
type

Activesheet.Shapes.SelectAll : Selection.Delete

and hit enter. and it would clear my sheet.

It is also good for testing in complex strings where you are concatenating
variables. For example, I just posted this. (which I copied from the
immediate window)

arr = Array("My","Your")
i = 1
? arr(i)
Your
set cntRef = Range("A1:A10")
set cntRef2 = Range("B1:B10")
? "=SUMPRODUCT(--('" & Arr(i) & _
"Billable FT'!" & CntRef.Address & """0""),--('" & Arr(i) & _
"Billable FT'!" & CntRef2.Address & "))"
=SUMPRODUCT(--('YourBillable FT'!$A$1:$A$10"0"),--('YourBillable
FT'!$B$1:$B$10))


first I had to create some variables to simulate what the poster was doing.
so I created an array with

arr = Array("My","Your")

then I assigned a value to the variable i

i = 1

In each case I typed those in and hit enter.

Then I wanted to see what was stored in arr(i) so I did
? arr(i)
and hit enter. It returned Your (the array is zero based).

Eventually I tested the string concatenation with the variables to see if it
produced the desired string.

It appears to.

So that was just as an example.

--
Regards,
Tom Ogilvy





"Hari" wrote in message
...
Hi Tom and Norman,

Your "creates a new variant variable and assigns it a value." is a superb
one. Its crystal clear for me now.

Actually I ran Norman's and your code and ( my VBE was not set to option
explicit and hence did not get any compile error , variable not defined --
now, I have changed my settings to require variable definition - thanx for
that too) I did not get any output of debug.print.

Is debug.print supposed to show me some result/output. Is it similar to
msgbox. I got no messageboxes saying "DisplayAlerts is a String with a
value of Funny Bunny. Neither do i get anything called "Opening State
True"
etc.

I thought debug.print might have fired off printouts (hard copies) to my
printer (a big fool am!!) but found no such printouts.

Before doing the above I consulted VBA help but couldnt understand the
terse
description "The Debug object sends output to the Immediate window at
runtime."
When i click on the runtime blue link above I get a message saying "A run
time error has occurred, do you wish to debug"

--
Thanks a lot,
Hari
India
"Tom Ogilvy" wrote in message
...
Norman didn't talk about what displayalerts is.

Sub TesterAA()

Debug.Print "Opening State", Application.DisplayAlerts
DisplayAlerts = False
Debug.Print "After DisplayAlerts = False command", _
Application.DisplayAlerts
DisplayAlerts = "Funny Bunny"
Debug.Print "DisplayAlerts is a " & _
TypeName(DisplayAlerts) & " with a value of " & _
DisplayAlerts
DisplayAlerts = True
Debug.Print "Closing State", Application.DisplayAlerts

End Sub

produces:

Opening State True
After DisplayAlerts = False command True
DisplayAlerts is a String with a value of Funny Bunny
Closing State True

---------

so displayalerts = "anything" creates a new variant variable and assigns
it
a value.

If you put

Option Explicit

at the top of the module and compile, you will be notified that
DisplayAlerts is undefined.

--
Regards,
Tom Ogilvy

"Norman Jones" wrote in message
...
Hi Hari,

Try runnining this small demo:

Sub Tester()
Debug.Print "Opening State", Application.DisplayAlerts
DisplayAlerts = False
Debug.Print "After DisplayAlerts = False command", _
Application.DisplayAlerts

DisplayAlerts = True
Debug.Print "Closing State", Application.DisplayAlerts

End Sub

The immediate window reports:


Opening State True
After DisplayAlerts = False command True
Closing State True

As you see, if DisplayAlerts is used without the required Application
qualifier, no error is observed but neither is any response.

That is why Excel was throwing up the overwrite alert message. If you
rerun
the code (using the qualified construct), no alert should be observed.

For the record, I have not run your code or otherwise examined it. I
did,
however, run a test repoducing a file overwrite event.

---
Regards,
Norman



"Hari" wrote in message
...
Hi Norman,

Just from understanding point of view....

When I previously wrote it as -- DisplayAlerts = True -- how does
excel
interpret the statement. Is NOT writing Application. NOT considered
as
an
error. Does that mean DisplayAlerts = True is also a valid statement
on
its
own and if so what does it do.

Thanks a lot,
Hari
India

"Norman Jones" wrote in message
...
Hi Hari,

Just to add, you would similarly need to amend the closing:

DisplayAlerts = True

to

Application. DisplayAlerts = True


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Hari,

Try changing:


DisplayAlerts = False

to

Application. DisplayAlerts = False


---
Regards,
Norman

Hi Tom,

Norman didn't talk about what displayalerts is.
Well spotted - as a prestidigitator I would starve!

Thank you for dragging the response from the banal into the genuinely
useful.



---
Regards,
Norman



"Tom Ogilvy" wrote in message
...
Norman didn't talk about what displayalerts is.

Sub TesterAA()

Debug.Print "Opening State", Application.DisplayAlerts
DisplayAlerts = False
Debug.Print "After DisplayAlerts = False command", _
Application.DisplayAlerts
DisplayAlerts = "Funny Bunny"
Debug.Print "DisplayAlerts is a " & _
TypeName(DisplayAlerts) & " with a value of " & _
DisplayAlerts
DisplayAlerts = True
Debug.Print "Closing State", Application.DisplayAlerts

End Sub

produces:

Opening State True
After DisplayAlerts = False command True
DisplayAlerts is a String with a value of Funny Bunny
Closing State True

---------

so displayalerts = "anything" creates a new variant variable and assigns
it
a value.

If you put

Option Explicit

at the top of the module and compile, you will be notified that
DisplayAlerts is undefined.

--
Regards,
Tom Ogilvy

Hi Tom,

That was lots of new stuff for me. Till now F8 and (set breakpoints) were
the only debugging tool I was using and now you have led me to Immediate
window (very cool stuff).Thanx a lot for the detailed explanation you have
given regarding how to use this feature.
--
Regards,
Hari
India

"Tom Ogilvy" wrote in message
...
In the VBE go to the View menu and click Immediate window so it has a
check
next to it. It will appear as another window in the VBE.

Debug Print
outputs to the immediate window. It is just another debugging tool.
You
can also type a complete VBA statement in it and hit enter and it will
immediately execute

For instance, If I wanted to delete all shapes on my worksheet, but didn't
want to select them all, I could go to the immediate window in the VBE and
type

Activesheet.Shapes.SelectAll : Selection.Delete

and hit enter. and it would clear my sheet.

It is also good for testing in complex strings where you are concatenating
variables. For example, I just posted this. (which I copied from the
immediate window)

arr = Array("My","Your")
i = 1
? arr(i)
Your
set cntRef = Range("A1:A10")
set cntRef2 = Range("B1:B10")
? "=SUMPRODUCT(--('" & Arr(i) & _
"Billable FT'!" & CntRef.Address & """0""),--('" & Arr(i) &
_
"Billable FT'!" & CntRef2.Address & "))"
=SUMPRODUCT(--('YourBillable FT'!$A$1:$A$10"0"),--('YourBillable
FT'!$B$1:$B$10))


first I had to create some variables to simulate what the poster was
doing.
so I created an array with

arr = Array("My","Your")

then I assigned a value to the variable i

i = 1

In each case I typed those in and hit enter.

Then I wanted to see what was stored in arr(i) so I did
? arr(i)
and hit enter. It returned Your (the array is zero based).

Eventually I tested the string concatenation with the variables to see if
it
produced the desired string.

It appears to.

So that was just as an example.

--
Regards,
Tom Ogilvy





"Hari" wrote in message
...
Hi Tom and Norman,

Your "creates a new variant variable and assigns it a value." is a
superb
one. Its crystal clear for me now.

Actually I ran Norman's and your code and ( my VBE was not set to option
explicit and hence did not get any compile error , variable not
defined --
now, I have changed my settings to require variable definition - thanx
for
that too) I did not get any output of debug.print.

Is debug.print supposed to show me some result/output. Is it similar to
msgbox. I got no messageboxes saying "DisplayAlerts is a String with a
value of Funny Bunny. Neither do i get anything called "Opening State
True"
etc.

I thought debug.print might have fired off printouts (hard copies) to my
printer (a big fool am!!) but found no such printouts.

Before doing the above I consulted VBA help but couldnt understand the
terse
description "The Debug object sends output to the Immediate window at
runtime."
When i click on the runtime blue link above I get a message saying "A
run
time error has occurred, do you wish to debug"

--
Thanks a lot,
Hari
India
"Tom Ogilvy" wrote in message
...
Norman didn't talk about what displayalerts is.

Sub TesterAA()

Debug.Print "Opening State", Application.DisplayAlerts
DisplayAlerts = False
Debug.Print "After DisplayAlerts = False command", _
Application.DisplayAlerts
DisplayAlerts = "Funny Bunny"
Debug.Print "DisplayAlerts is a " & _
TypeName(DisplayAlerts) & " with a value of " & _
DisplayAlerts
DisplayAlerts = True
Debug.Print "Closing State", Application.DisplayAlerts

End Sub

produces:

Opening State True
After DisplayAlerts = False command True
DisplayAlerts is a String with a value of Funny Bunny
Closing State True

---------

so displayalerts = "anything" creates a new variant variable and
assigns
it
a value.

If you put

Option Explicit

at the top of the module and compile, you will be notified that
DisplayAlerts is undefined.

--
Regards,
Tom Ogilvy

"Norman Jones" wrote in message
...
Hi Hari,

Try runnining this small demo:

Sub Tester()
Debug.Print "Opening State", Application.DisplayAlerts
DisplayAlerts = False
Debug.Print "After DisplayAlerts = False command", _
Application.DisplayAlerts

DisplayAlerts = True
Debug.Print "Closing State", Application.DisplayAlerts

End Sub

The immediate window reports:


Opening State True
After DisplayAlerts = False command True
Closing State True

As you see, if DisplayAlerts is used without the required
Application
qualifier, no error is observed but neither is any response.

That is why Excel was throwing up the overwrite alert message. If
you
rerun
the code (using the qualified construct), no alert should be
observed.

For the record, I have not run your code or otherwise examined it. I
did,
however, run a test repoducing a file overwrite event.

---
Regards,
Norman



"Hari" wrote in message
...
Hi Norman,

Just from understanding point of view....

When I previously wrote it as -- DisplayAlerts = True -- how does
excel
interpret the statement. Is NOT writing Application. NOT
considered
as
an
error. Does that mean DisplayAlerts = True is also a valid
statement
on
its
own and if so what does it do.

Thanks a lot,
Hari
India

"Norman Jones" wrote in message
...
Hi Hari,

Just to add, you would similarly need to amend the closing:

DisplayAlerts = True

to

Application. DisplayAlerts = True


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Hari,

Try changing:


DisplayAlerts = False

to

Application. DisplayAlerts = False


---
Regards,
Norman

Isn't that a bit fastuous?


Just kidding of course! Your better than the word for the day.

--
Regards,
Tom Ogilvy

"Norman Jones" wrote in message
...
Hi Tom,

Norman didn't talk about what displayalerts is.
Well spotted - as a prestidigitator I would starve!

Thank you for dragging the response from the banal into the genuinely
useful.



---
Regards,
Norman



"Tom Ogilvy" wrote in message
...
Norman didn't talk about what displayalerts is.

Sub TesterAA()

Debug.Print "Opening State", Application.DisplayAlerts
DisplayAlerts = False
Debug.Print "After DisplayAlerts = False command", _
Application.DisplayAlerts
DisplayAlerts = "Funny Bunny"
Debug.Print "DisplayAlerts is a " & _
TypeName(DisplayAlerts) & " with a value of " & _
DisplayAlerts
DisplayAlerts = True
Debug.Print "Closing State", Application.DisplayAlerts

End Sub

produces:

Opening State True
After DisplayAlerts = False command True
DisplayAlerts is a String with a value of Funny Bunny
Closing State True

---------

so displayalerts = "anything" creates a new variant variable and assigns
it
a value.

If you put

Option Explicit

at the top of the module and compile, you will be notified that
DisplayAlerts is undefined.

--
Regards,
Tom Ogilvy