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