If file is not in use or open then execute macro

Hi,

I have to macro codes. One opens an excel file and checks if a file is in use (Code A). The other one is opening an excel file (without checking)(Code B).
What I want is to combine these two macro's but I do not know how.
The macro should before opening the excel file, first check if it is in use or open. If not then stop otherwise continue to open the excel file.

Here is code A:
[VBA]Sub OpenExcelFile()

Dim vFile As Variant

'Sheets("Sheet1").Select
'Showing Excel Open Dialog Form
vFile = Application.GetOpenFilename("XLS Files (*.xls)," & _
"*.xls", 1, "Select Data File", "Open", False)
'If Cancel then exit
If TypeName(vFile) = "Boolean" Then
Exit Sub
End If
'Empty clipboard
Application.CutCopyMode = False

'Open selected file
Workbooks.Open vFile

ChDir _
"C:\TEST\"
ActiveWorkbook.SaveAs Filename:="C:\TEST\TESTDATA.xls" _
, FileFormat:=xlXLsSpreadsheet, ReadOnlyRecommended:=False, CreateBackup _
:=False

Columns("A:AM").Select
Selection.Copy
Windows("DATA_TESTING.xls").Activate
Sheets("Sheet1").Select
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
End Sub[/VBA]

And here is code B:
[VBA]Sub OpenExample()
Application.DisplayAlerts = False
On Error GoTo fileInUse
Workbooks.Open Filename:="C:\TESTDATA.xls", _
notify:=False, ReadOnly:=False
Application.DisplayAlerts = True
'Macro statements
Exit Sub
fileInUse:
MsgBox "The file is in use"[/VBA]

Hope someone can help me.

Regards,

RM

Can I double-check the spec?

You want to:

- get the user to pick a file using the GetOpenFilename dialog
- check if that selected file is already open in this instance of Excel
- do the SaveAs
- do the copy/paste

Is that about right?

Hi Xld,

Yes.

If the "file-to-open" is in use, then abort the "OpenExcelFile" macro. So, don't "save as" and don't "copy/paste". I think when the file is in use, a message box should appear with the message "this process is aborted - file to open is in use".

Thanks in advance,

RM

Hey,

Can someone help me with my problem, please?

See: http://www.xcelfiles.com/IsFileOpenAPI.htm