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
Reply With Quote
