Check if target workbook is already opened before start copying data between two workbooks

[Soma88]

Hi everyone,

So I have a macro which copies certain data to an other workbook. It works fine but I have this issue that every time the macro begins to run (somebody is migrating data to the main excel file) it should be checked first if the main file was opened/is open by someone else on the network to avoid confusion and data loss.

I have tried the following code:

Private Sub CommandButton1_Click()


Dim dt As String
Dim SO As String




Worksheets("migrate").Select
dt = Range("A2")
SO = Range("B2")
Ret = IsFileOpen("D:\MainTracker.xls")


If Ret = False Then
Set myData = Workbooks.Open("D:\MainTracker.xls")
Worksheets("ProjectName").Select
Worksheets("ProjectName").Range("F1").Select
With Worksheets("Projectname")
RowCount = .Range("F" & .Rows.Count).End(xlUp).Row
End With
With Worksheets("Projectname").Range("F1")
.Offset(RowCount, 0) = dt
.Offset(RowCount, 1) = SO
End With
myData.Save
Else
MsgBox "not now"
End If
End Sub

I highlighted the parts which are causing troubles.(I'm quite sure that I am not using IsFileOpen right.)

Any toughts how to solve this issue?

Thank you very much in advance!

 

[Andrew Poulsom]

Do you have a function named IsFileOpen? If so please post it. If not here's one:

Macro Code to Check Whether a File Is Already Open