PDA

View Full Version : Solved: VBA Excel Close Non Active File



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

Aflatoon
01-09-2013, 03:56 AM
Dim wbk as Workbook
Set wbk = Workbooks.Open(MyFile)
wbk.Sheets.PrintOut
wbk.Close False

for example.

dodonohoe
01-09-2013, 04:21 AM
Hi Aflatoon,

I'm getting an error "compile error: duplicate declaration in current scope"

I have indicated where I have put your code, perhaps I have put it in the wrong place

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 wbk As Workbook
Set wbk = Workbooks.Open(MyFile)



'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

'AFLATOONS CODE
Dim wbk As Workbook
Set wbk = Workbooks.Open(MyFile)

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

'AFLATOONS CODE
'Dim wbk As Workbook
'Set wbk = Workbooks.Open(MyFile)

wbk.Sheets.PrintOut
wbk.Close False


'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

Aflatoon
01-09-2013, 04:34 AM
Why did you add:
Dim wbk As Workbook
Set wbk = Workbooks.Open(MyFile)
twice? It should only be there once.

dodonohoe
01-09-2013, 04:50 AM
Apologies Aflatoon, just saw my own silly mistake. Your code works perfectly.

dodonohoe
01-09-2013, 05:12 AM
Hi Aflatoon,

Last question, if I wanted to print the file without opening it could I use something similar to what you advised above. I tried a number of combinations but without success.

Thanks

Aflatoon
01-09-2013, 06:27 AM
You can't print a file without opening it to the best of my knowledge. Certainly not an Excel file.

BrianMH
01-09-2013, 07:52 AM
You can print it with out opening it but then you are going to need to start using the windows API.

Aflatoon
01-09-2013, 08:11 AM
Even if you use something like ShellEexcute, the file will be opened, printed and then closed - or did you have another API function in mind?

dodonohoe
01-09-2013, 08:37 AM
Hi Aflatoon,

I am going to Mark this thread as solved. Thanks again for your help.

Des

BrianMH
01-09-2013, 08:42 AM
I was thinking send to printer but your right it ends up being opened by the default program anyway.

dodonohoe
01-09-2013, 08:43 AM
Thank you also for your time Brian