PDA

View Full Version : If file is not in use or open then execute macro



Riaaz66
10-26-2010, 06:34 AM
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:
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

And here is code B:
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"

Hope someone can help me.

Regards,

RM

Bob Phillips
10-26-2010, 06:59 AM
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?

Riaaz66
10-26-2010, 08:38 AM
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

Riaaz66
10-27-2010, 07:56 AM
Hey,

Can someone help me with my problem, please?

Kenneth Hobs
10-27-2010, 05:22 PM
See: http://www.xcelfiles.com/IsFileOpenAPI.htm