Eddie
04-09-2008, 04:14 PM
I have the following code that places the serial number (pulled from the file name) in a cell and then prints a selection of a test report in PDF.
Sub PrintSelection()
'creates strings to be set
Dim SerialDate As String
Dim SerialNum As String
Dim FullFileName As String
'sets the FullFileName variable value to the name of the file
FullFileName = ActiveWorkbook.Name
'takes the first four numbers from the FullFileName string and assigns them to the SerialDate string
SerialDate = Mid(FullFileName, 1, 4)
'takes the next four numbers from the FullFileName string and assigns them to the SerialNum string
SerialNum = Mid(FullFileName, 5, 4)
'Selects cell H8 and then enters the serial number based off of the file name in the format "SN: ####-####"
Range("H8").Select
ActiveCell.FormulaR1C1 = "SN:" & SerialDate & "-" & SerialNum
'Selects the cells that are to be printed
Range("A1:L107").Select
'Prints selection to PDF printer
Application.ActivePrinter = "Adobe PDF on Ne04:"
ActiveWindow.Selection.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF on Ne04:", Collate:=True
Pause (1.5)
SendKeys SerialDate & "-" & SerialNum, True
End Sub
Public Sub Pause(NbSec As Single)
Dim Finish As Single
Finish = Timer + NbSec
DoEvents
Do Until Timer >= Finish
Loop
End Sub
I cannot get the code to name the file automatically once the save screen comes up. The code in question is bolded and underlined. Is it possible to have the file named automatically? Thanks!
Sub PrintSelection()
'creates strings to be set
Dim SerialDate As String
Dim SerialNum As String
Dim FullFileName As String
'sets the FullFileName variable value to the name of the file
FullFileName = ActiveWorkbook.Name
'takes the first four numbers from the FullFileName string and assigns them to the SerialDate string
SerialDate = Mid(FullFileName, 1, 4)
'takes the next four numbers from the FullFileName string and assigns them to the SerialNum string
SerialNum = Mid(FullFileName, 5, 4)
'Selects cell H8 and then enters the serial number based off of the file name in the format "SN: ####-####"
Range("H8").Select
ActiveCell.FormulaR1C1 = "SN:" & SerialDate & "-" & SerialNum
'Selects the cells that are to be printed
Range("A1:L107").Select
'Prints selection to PDF printer
Application.ActivePrinter = "Adobe PDF on Ne04:"
ActiveWindow.Selection.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF on Ne04:", Collate:=True
Pause (1.5)
SendKeys SerialDate & "-" & SerialNum, True
End Sub
Public Sub Pause(NbSec As Single)
Dim Finish As Single
Finish = Timer + NbSec
DoEvents
Do Until Timer >= Finish
Loop
End Sub
I cannot get the code to name the file automatically once the save screen comes up. The code in question is bolded and underlined. Is it possible to have the file named automatically? Thanks!