dodonohoe
01-09-2013, 03:52 AM
Hi all,
This is a two part question.
1) How can I modify the code below to close the non active file after printing
2) How could I modify the code to print the file without opening it at all
Sub PrintForm()
Dim Month As String
'input box to put a variable (folder name) for the file path
Month = Application.Inputbox("Please enter the file path variable")
'puts the variable name on the macro sheet where all the files to be printed are listed
Cells(1, 7) = Month
rowno = 2
'this will tell the macro if the file type is excel, word or pdf
FileType = Trim(Cells(rowno, 2))
Do Until Cells(rowno, 2) = ""
If FileType = "Excel" Then
'call the excel piece
Inputs (rowno)
End If
If FileType = "PDF" Then
'call the PDF piece (I have not included this sub)
PrintPDFsInputBox (rowno)
End If
rowno = rowno + 1
Loop
End Sub
Sub Inputs(ByVal x As Integer)
'tells the macro that the macro is the active workbbook so that it can go_
'back to the active workbook after opening other workbooks
Dim w As Workbook
Set w = ActiveWorkbook
'dim all my variables, these are put in my the user on the main macro workbook
Dim Variablez As String
Dim MyFolder As String
Dim MyFile As String
Dim FileName As String
Dim Action As String
MyFolder = Cells(x, 3)
FileName = Cells(x, 5)
Action = Cells(x, 6)
Variablez = Cells(1, 7)
MyFile = Dir(MyFolder & Variablez & "\" & FileName)
If MyFile <> "" And Action = "Open" Then
Workbooks.Open MyFile
End If
If MyFile <> "" And Action = "Print" Then
'THIS IS THE AREA I NEED HELP WITH
Workbooks.Open MyFile
Sheets.PrintOut
'I HAD TRIED THIS TO CLOSE THE INACTIVE WORKBOOK (Myfile)
'MyFile.Close
End If
If MyFile = "" Then
MsgBox ("FileName does not exist, please check the file is saved out correctly")
End If
w.Activate
End Sub
This is a two part question.
1) How can I modify the code below to close the non active file after printing
2) How could I modify the code to print the file without opening it at all
Sub PrintForm()
Dim Month As String
'input box to put a variable (folder name) for the file path
Month = Application.Inputbox("Please enter the file path variable")
'puts the variable name on the macro sheet where all the files to be printed are listed
Cells(1, 7) = Month
rowno = 2
'this will tell the macro if the file type is excel, word or pdf
FileType = Trim(Cells(rowno, 2))
Do Until Cells(rowno, 2) = ""
If FileType = "Excel" Then
'call the excel piece
Inputs (rowno)
End If
If FileType = "PDF" Then
'call the PDF piece (I have not included this sub)
PrintPDFsInputBox (rowno)
End If
rowno = rowno + 1
Loop
End Sub
Sub Inputs(ByVal x As Integer)
'tells the macro that the macro is the active workbbook so that it can go_
'back to the active workbook after opening other workbooks
Dim w As Workbook
Set w = ActiveWorkbook
'dim all my variables, these are put in my the user on the main macro workbook
Dim Variablez As String
Dim MyFolder As String
Dim MyFile As String
Dim FileName As String
Dim Action As String
MyFolder = Cells(x, 3)
FileName = Cells(x, 5)
Action = Cells(x, 6)
Variablez = Cells(1, 7)
MyFile = Dir(MyFolder & Variablez & "\" & FileName)
If MyFile <> "" And Action = "Open" Then
Workbooks.Open MyFile
End If
If MyFile <> "" And Action = "Print" Then
'THIS IS THE AREA I NEED HELP WITH
Workbooks.Open MyFile
Sheets.PrintOut
'I HAD TRIED THIS TO CLOSE THE INACTIVE WORKBOOK (Myfile)
'MyFile.Close
End If
If MyFile = "" Then
MsgBox ("FileName does not exist, please check the file is saved out correctly")
End If
w.Activate
End Sub