Application.DisplayAlerts = False - Not Working

[carusoloredana]

1) In the code below I am trying to open an excel file, convert it
into pdf, and close the excel file. When I run this macro I keep
getting prompted to replace the current pdf file and I want to
overwrite that as the pdf file needs to be replaced everyday. The
Application.DisplayAlerts are not doing anything. Any suggestions to
stop this prompt?

2) Also, I would like to save the pdf without going through the save as
prompt. Initially, I had a code that would open the excel file and just
save it as pdf in a predetermined folder. But when I would go to open
the pdf file I would get an error from adobe saying that the file
format was incorrect. Any ideas?

Any help is greatly appreciated. Thank you in advance.

This is the code I am using now:

ChDir "C:\Documents and Settings\43202328\Desktop\PDF
Ratesheets\InternetRates\"
Workbooks.Open Filename:="C:\Documents and
Settings\43202328\Desktop\PDF Ratesheets\InternetRates\CABROK.XLS"


On Error Resume Next
For i = 1 To 9
Application.ActivePrinter = "Adobe PDF on Ne0" & i & ":"
If Err.Number = 0 Then Exit For
Err.Clear
Next

Application.ActivePrinter = "Adobe PDF on Ne0" & i & ":"
Windows("CABROK.xls").Activate
Application.DisplayAlerts = False
ActiveWorkbook.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF on Ne0" & i & ":", Collate:=True
Windows("CABROK.xls").Activate
Application.DisplayAlerts = True
ActiveWindow.Close


End Sub

 

[Jim Rech]

For the first issue you might try using the Kill function to delete the file
just before you recreate it.

--
Jim
| 1) In the code below I am trying to open an excel file, convert it
| into pdf, and close the excel file. When I run this macro I keep
| getting prompted to replace the current pdf file and I want to
| overwrite that as the pdf file needs to be replaced everyday. The
| Application.DisplayAlerts are not doing anything. Any suggestions to
| stop this prompt?
|
| 2) Also, I would like to save the pdf without going through the save as
| prompt. Initially, I had a code that would open the excel file and just
| save it as pdf in a predetermined folder. But when I would go to open
| the pdf file I would get an error from adobe saying that the file
| format was incorrect. Any ideas?
|
| Any help is greatly appreciated. Thank you in advance.
|
| This is the code I am using now:
|
| ChDir "C:\Documents and Settings\43202328\Desktop\PDF
| Ratesheets\InternetRates\"
| Workbooks.Open Filename:="C:\Documents and
| Settings\43202328\Desktop\PDF Ratesheets\InternetRates\CABROK.XLS"
|
|
| On Error Resume Next
| For i = 1 To 9
| Application.ActivePrinter = "Adobe PDF on Ne0" & i & ":"
| If Err.Number = 0 Then Exit For
| Err.Clear
| Next
|
| Application.ActivePrinter = "Adobe PDF on Ne0" & i & ":"
| Windows("CABROK.xls").Activate
| Application.DisplayAlerts = False
| ActiveWorkbook.PrintOut Copies:=1, ActivePrinter:= _
| "Adobe PDF on Ne0" & i & ":", Collate:=True
| Windows("CABROK.xls").Activate
| Application.DisplayAlerts = True
| ActiveWindow.Close
|
|
| End Sub
|

 

[carusoloredana]

Jim,

Thanks- That works great.

The thing I cant figure out now is how to close the acrobat window
after the pdf is created. I cant seem to find a code for that. Any
ideas?

 

[Jim Rech]

There is nothing in Excel for closing another application. It probably can
be done through a Windows API call but I don't have example code.

--
Jim
| Jim,
|
| Thanks- That works great.
|
| The thing I cant figure out now is how to close the acrobat window
| after the pdf is created. I cant seem to find a code for that. Any
| ideas?
|