Excel vba printout method
 

Hello all,

I've went throw all posts on vba excel printout codes and none worked.

I am using excel to create a file and i want to save it in pdf format
but dont want the popup that asks the filename to save under

here is the code that i'm using:

Application.ActivePrinter = "Adobe PDFWriter sur LPT1:"
ActiveWorkbook.SaveAs FileName:="C:\toto.xls"
ActiveWorkbook.PrintOut

I've tried specifying parameter of printout with no success

Help please
Curtis

Excel vba printout method
 

What version of Excel. Did you look at Excel VBA help on the printout
method. In xl2000 and later, you can specify the file name to print to.

--
Regards,
Tom Ogilvy

"Curtis" wrote in message
om...
Hello all,

I've went throw all posts on vba excel printout codes and none worked.

I am using excel to create a file and i want to save it in pdf format
but dont want the popup that asks the filename to save under

here is the code that i'm using:

Application.ActivePrinter = "Adobe PDFWriter sur LPT1:"
ActiveWorkbook.SaveAs FileName:="C:\toto.xls"
ActiveWorkbook.PrintOut

I've tried specifying parameter of printout with no success

Help please
Curtis

Excel vba printout method
 

Yes I've looked at help on .printout usage.
But you cant specify filename.
I'm using excel 97 sr-2 on winnt 4.0





*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Excel vba printout method
 

I'm using excel 97 sr-2 on winnt 4.0

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Excel vba printout method
 

Previously posted by Jim Rech:

Jim Rech on writing a PDF

http://www.deja.com/getdoc.xp?AN=652844427&fmt=text

From: "Jim Rech"
Subject: Disable keyboard for SendKeys
Date: 31 Jul 2000 00:00:00 GMT
Message-ID: <#wktr0w#$GA.196@cppssbbsa04
References:


X-Priority: 3
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400
X-MSMail-Priority: Normal
Newsgroups: microsoft.public.excel.programming

What follows is a note I wrote some time ago on batch printing to a PDF
"printer". It's a lot better than sendkeys.

-------
The PDF driver looks for entries in an INI file and uses them for file names
automatically. If it doesn't find these entries then it prompts the user
for a file name. So your process would have to make these entries before
printing each workbook to the PDF "printer".

BTW, the INI file it looks for is "PDFWritr.ini" in the Windows\System
directory. So...

To write entries to INI files using the Windows API you need to have this
declarations at the top of your module:

Declare Function WritePrivateProfileStringA Lib "kernel32" _
(ByVal SectionStr As String, ByVal ItemName As String, _
ByVal Str2Write As String, ByVal IniFileName As String) As Boolean

Although you probably know the path to Windows\system, for portability it's
a good idea to have finding it be automatic. So include this API
declaration too:

Public Declare Function GetSystemDirectory Lib "kernel32" Alias _
"GetSystemDirectoryA" (ByVal lpBuffer As String, ByVal nSize As Long) As
Long

Include this module level variable to hold the path to the INI file:

Dim PDF_INIPath As String

Now before doing any printing run this to set things up:

Sub InitAutoPDF()
SetINIPath ''Set INI path/name variable
SetPDF_INI_Mode "Auto" ''Write entry to INI to put PDF driver in Auto
mode
End Sub

Now in your main file opening/printing macro your must set the name for the
PDF file to be created as each XLS is printed. This one uses the same name
as the source Excel file but with the extension PDF instead of XLS:

Sub MainPrintRoutine()
Dim WriteFile as String, FileLen as Integer
''Need a loop that opens each XLS and
'' puts its name in the variable WriteFile, and
'' the length of its file name in the variable FileLen
'' Then call this to set name in INI file
WritePDFFileName2Ini Left(WriteFile, FileLen - 3) & "pdf"
End Sub

When you're done run this sub to erase the last file name written to the INI
file and to put the PDF driver back in prompting mode:

Sub ResetManualPDF()
WritePDFFileName2Ini ""
SetPDF_INI_Mode "Prompt"
End Sub

And add these supporting subs called from above:

''Writes target PDF file name into INI
''This must be called prior to each printout
Sub WritePDFFileName2Ini(FName As String)
WritePrivateProfileStringA "Acrobat PDFWriter", "PDFFileName", FName,
PDF_INIPath
End Sub

''Set "CreatePDFExcelMacroShowDialog" entry in INI to passed setting
''The settings are "Prompt" and "Auto"
Sub SetPDF_INI_Mode(Setting As String)
WritePrivateProfileStringA "Acrobat PDFWriter",
"CreatePDFExcelMacroShowDialog", Setting, PDF_INIPath
End Sub

''Sets variable pointing to PDF INI file
Function SetINIPath() As Boolean
Dim Buff As String * 100
Dim RetVal As Long
RetVal = GetSystemDirectory(Buff, 100)
If RetVal 0 Then
PDF_INIPath = Left(Buff, RetVal) & "\PDFWritr.ini"
SetINIPath = True
Else
MsgBox "Cannot find the Windows System Directory"
End If
End Function

BTW, I extracted all this from a working module but changed some things
around for clarity. So I may have mis-typed some things but hopefully you
can fix these things up.

--
Jim Rech
Excel MVP

--
Regards,
Tom Ogilvy

"Curtis Marchand" wrote in message
...


I'm using excel 97 sr-2 on winnt 4.0

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Excel vba printout method
 

You forgot that i'm using winnt 4.0 not windows 95/98
There is no directory windows/system
There is winnt/system
/system32
an there is no ini file PDFWritr.ini in winnt
I searched every local drive.

I would like to apply your theory in win nt if it is possible.

Regards
Curtis

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Excel vba printout method
 

PDFwritr
PDFwriter
Create PDF

I understand Windows 2000 uses the registry - can't say for Window NT 4.0.

You can download the Acrobat SDK for version 5 here.

http://partners.adobe.com/asn/acrobat/download.jsp

it is at the bottom - the verions 6 full install requires being an ASN
member (whatever that is). Anyway, the registry approach is supposed to be
documented in the SDK. I would assume it will clarify what NT 4.0 needs as
well.

--
Regards,
Tom Ogilvy






"Curtis Marchand" wrote in message
...
You forgot that i'm using winnt 4.0 not windows 95/98
There is no directory windows/system
There is winnt/system
/system32
an there is no ini file PDFWritr.ini in winnt
I searched every local drive.

I would like to apply your theory in win nt if it is possible.

Regards
Curtis

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Excel vba printout method
 

Looks like you have to register for the ASN thing anyway - but it doesn't
cost anything.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
PDFwritr
PDFwriter
Create PDF

I understand Windows 2000 uses the registry - can't say for Window NT 4.0.

You can download the Acrobat SDK for version 5 here.

http://partners.adobe.com/asn/acrobat/download.jsp

it is at the bottom - the verions 6 full install requires being an ASN
member (whatever that is). Anyway, the registry approach is supposed to
be
documented in the SDK. I would assume it will clarify what NT 4.0 needs
as
well.

--
Regards,
Tom Ogilvy






"Curtis Marchand" wrote in message
...
You forgot that i'm using winnt 4.0 not windows 95/98
There is no directory windows/system
There is winnt/system
/system32
an there is no ini file PDFWritr.ini in winnt
I searched every local drive.

I would like to apply your theory in win nt if it is possible.

Regards
Curtis

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Excel vba printout method
 

Hi Curtis,
PrToFileName is what your looking for.


Sub test()
Dim Output
Close
Output = "mytest.pdf"
Debug.Print Output
Sheets(1).Activate
Application.ActivePrinter = "Acrobat PDFWriter on LPT1:"
ActiveWindow.SelectedSheets.PrintOut _
Copies:=1, PrintToFile:=True, PrToFileName:="C:\Documents and
Settings\jaf\My Documents\mytest.pdf"

End Sub

--

John

johnf202 at hotmail dot com


"Curtis" wrote in message
om...
Hello all,

I've went throw all posts on vba excel printout codes and none worked.

I am using excel to create a file and i want to save it in pdf format
but dont want the popup that asks the filename to save under

here is the code that i'm using:

Application.ActivePrinter = "Adobe PDFWriter sur LPT1:"
ActiveWorkbook.SaveAs FileName:="C:\toto.xls"
ActiveWorkbook.PrintOut

I've tried specifying parameter of printout with no success

Help please
Curtis

Excel vba printout method
 

Curtis previously stated:
I'm using excel 97 sr-2 on winnt 4.0

PrToFileName was not introduced until xl2000.

--
Regards,
Tom Ogilvy


jaf wrote in message
...
Hi Curtis,
PrToFileName is what your looking for.


Sub test()
Dim Output
Close
Output = "mytest.pdf"
Debug.Print Output
Sheets(1).Activate
Application.ActivePrinter = "Acrobat PDFWriter on LPT1:"
ActiveWindow.SelectedSheets.PrintOut _
Copies:=1, PrintToFile:=True, PrToFileName:="C:\Documents and
Settings\jaf\My Documents\mytest.pdf"

End Sub

--

John

johnf202 at hotmail dot com


"Curtis" wrote in message
om...
Hello all,

I've went throw all posts on vba excel printout codes and none worked.

I am using excel to create a file and i want to save it in pdf format
but dont want the popup that asks the filename to save under

here is the code that i'm using:

Application.ActivePrinter = "Adobe PDFWriter sur LPT1:"
ActiveWorkbook.SaveAs FileName:="C:\toto.xls"
ActiveWorkbook.PrintOut

I've tried specifying parameter of printout with no success

Help please
Curtis

Excel vba printout method
 

Rats.

Curtis,

According to the pdf.xla (Acrobat 5.0) the .ini for winnt is in a winnt
subfolder SystemRoot$ + "\System32\Spool\Drivers\W32X86\"

It DOES NOT always have the name pdfwritr.ini. My XP sys uses the
"2\__pdf.ini"


If (bWinNT) Then
SystemRoot$ = GetPrivateProfileSetting(BaseKey:=HKEY_LOCAL_MACHI NE,
_
Section:="Software\Microsoft\Windows NT\CurrentVersion",
_
Setting:="SystemRoot")
iniFilename = SystemRoot$ + "\System32\Spool\Drivers\W32X86\"
If (bWinNT351) Then
iniFilename = iniFilename + "1\__pdf.ini"
Else
iniFilename = iniFilename + "2\__pdf.ini"
End If
Else
SystemRoot$ = GetPrivateProfileSetting(BaseKey:=HKEY_LOCAL_MACHI NE,
_
Section:="Software\Microsoft\Windows\CurrentVersio n", _
Setting:="SystemRoot")
iniFilename = SystemRoot$ + "\System\PDFWritr.ini"
End If



--

John

johnf202 at hotmail dot com


"Tom Ogilvy" wrote in message
...
Curtis previously stated:
I'm using excel 97 sr-2 on winnt 4.0

PrToFileName was not introduced until xl2000.

--
Regards,
Tom Ogilvy


jaf wrote in message
...
Hi Curtis,
PrToFileName is what your looking for.


Sub test()
Dim Output
Close
Output = "mytest.pdf"
Debug.Print Output
Sheets(1).Activate
Application.ActivePrinter = "Acrobat PDFWriter on LPT1:"
ActiveWindow.SelectedSheets.PrintOut _
Copies:=1, PrintToFile:=True, PrToFileName:="C:\Documents and
Settings\jaf\My Documents\mytest.pdf"

End Sub

--

John

johnf202 at hotmail dot com


"Curtis" wrote in message
om...
Hello all,

I've went throw all posts on vba excel printout codes and none worked.

I am using excel to create a file and i want to save it in pdf format
but dont want the popup that asks the filename to save under

here is the code that i'm using:

Application.ActivePrinter = "Adobe PDFWriter sur LPT1:"
ActiveWorkbook.SaveAs FileName:="C:\toto.xls"
ActiveWorkbook.PrintOut

I've tried specifying parameter of printout with no success

Help please
Curtis

Excel vba printout method
 

Dear Tom,
I'll see if m'I membership was paid this year and get m'I password and
username. Thanks for putting me on the right track. The only way is to
convert to win2000 with xl2000 or write in register filename before
every save. I hope the company paid are membership.

Thanks again.
Regards
Curtis

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!