dodonohoe
01-28-2013, 03:36 AM
Hi all,
I have seen a couple of variations but nothing I could get to work. Best I could do was handle the errors when the file is already open. Basically if a file is already open I would like to A) Either leave it open and do nothing and B) Print it and leave it open.
Sub Inputs(ByVal x As Integer)
'This sub is for the excel piece
'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 by the user on the main macro workbook
Dim Variablez As String
Dim MyFolder As String
Dim MyFile As String
Dim wbk As Workbook
Dim FileName As String
Dim Action As String
MyFolder = Trim(Cells(x, 3))
FileName = Trim(Cells(x, 4))
Action = Trim(Cells(x, 5))
Variablez = Trim(Cells(1, 6))
MyFile = Dir(MyFolder & Variablez & "\" & FileName)
'This piece opens the file and if its already open it doesn't throw an error
If MyFile <> "" And FileName <> "" And Action = "Open" Then
Cells(x, 6) = "This report is open"
On Error Resume Next: Workbooks.Open MyFolder & Variablez & "\" & MyFile: On Error GoTo 0
End If
'NEED TO CHANGE THE CODE BELOW SO THAT IF THE FILE IS ALREADY OPEN DONT THROW ERROR BUT PRINT BUT DON'T CLOSE OTHERWISE OPEN PRINT AND CLOSE
If MyFile <> "" And FileName <> "" And Action = "Print" Then
Cells(x, 6) = "This report has been printed"
On Error Resume Next: Set wbk = Workbooks.Open(MyFolder & Variablez & "\" & MyFile): On Error GoTo 0
wbk.Sheets.PrintOut
wbk.Close True
End If
w.Activate
End Sub
I have seen a couple of variations but nothing I could get to work. Best I could do was handle the errors when the file is already open. Basically if a file is already open I would like to A) Either leave it open and do nothing and B) Print it and leave it open.
Sub Inputs(ByVal x As Integer)
'This sub is for the excel piece
'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 by the user on the main macro workbook
Dim Variablez As String
Dim MyFolder As String
Dim MyFile As String
Dim wbk As Workbook
Dim FileName As String
Dim Action As String
MyFolder = Trim(Cells(x, 3))
FileName = Trim(Cells(x, 4))
Action = Trim(Cells(x, 5))
Variablez = Trim(Cells(1, 6))
MyFile = Dir(MyFolder & Variablez & "\" & FileName)
'This piece opens the file and if its already open it doesn't throw an error
If MyFile <> "" And FileName <> "" And Action = "Open" Then
Cells(x, 6) = "This report is open"
On Error Resume Next: Workbooks.Open MyFolder & Variablez & "\" & MyFile: On Error GoTo 0
End If
'NEED TO CHANGE THE CODE BELOW SO THAT IF THE FILE IS ALREADY OPEN DONT THROW ERROR BUT PRINT BUT DON'T CLOSE OTHERWISE OPEN PRINT AND CLOSE
If MyFile <> "" And FileName <> "" And Action = "Print" Then
Cells(x, 6) = "This report has been printed"
On Error Resume Next: Set wbk = Workbooks.Open(MyFolder & Variablez & "\" & MyFile): On Error GoTo 0
wbk.Sheets.PrintOut
wbk.Close True
End If
w.Activate
End Sub