Reopen Error in Excel Macro

[petes]

Hi Friends!!

I have this code which copy's the data from "Name.xlsx" to "Master.xslx"
I run this macro from Master.xslx file...

Sub mast()

If Dir("C:\test\Name.xlsx") <> "" Then
Workbooks.Open "C:\test\Name.xlsx"
Worksheets("Emp").Select
ActiveSheet.Cells.Select
Selection.Copy
Windows("Master").Activate
Worksheets("Sheet1").Select
Range("A1").Select
Selection.PasteSpecial
Else
MsgBox "File doesn't exist."
End If
End Sub

I need to alter this code so that, in case if the file "Name.xlsx" is already open, it should automatically save and close...

But right now, the error is dsipalying as

----Name.xlsx is already open. Reopening will cause any changes you made to be discarded . Do you want to reopen Name.xlsx------

Basically i am trying to get rid off this error by automatically saving the opened file before it gets closed.

Thanks Pals!!!

 

[VoG]

Sorry, my error. Try

Sub mast()
If Dir("C:\test\Name.xlsx") <> "" Then
    If Not BookOpen("Name.xlsx") Then
        Workbooks.Open "C:\test\Name.xlsx"
    Else
        Workbooks("test.xlsx").Activate
    End If
    Worksheets("Emp").Cells.Copy
    Workbooks("Master.xlsx").Sheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues
Else
    MsgBox "File doesn't exist."
End If
End Sub

Function BookOpen(wbName As String) As Boolean
On Error Resume Next
BookOpen = Len(Workbooks(wbName).Name)
End Function

 

[petes]

Actually, when i click the Name.xslx file and got to VB editor and run the macro, i don't see any error... however, when i click the Master.xslx file and go to VB editor and run the macro or if i run the Macro from Master.xlsx file itself, the error pops up....

Note: code is in Master.xlsx file....

 

[petes]

Even this code has some minor problems..... Ok, let me make you clear about my requirement....

1) I have two Excel Files (Name.xlsx and Master.xslx)
2) Name.xlsx has a sheet named "EMP"
3) Master.xlsx has a sheet named "Sheet1"
4) The source for both the files is "C:\test"

I need a macro in Master.xlsx, so that if i run a macro the following events should happen

1) It should Open the Name.xslx file from the source and copy the entire contents from sheet named EMP and paste it to sheet1 of Master.xlsx which is already open.

Constraint: Even though the Name.xlsx is already open, it should not display any error when i run the macro...

I really appreciate your effort in this chains..

 

[VoG]

What "minor problems"?

 

[petes]

I dont have a workbook named "test.xlsx"... your code has that

 

[VoG]

Typo

Sub mast()
If Dir("C:\test\Name.xlsx") <> "" Then
    If Not BookOpen("Name.xlsx") Then
        Workbooks.Open "C:\test\Name.xlsx"
    Else
        Workbooks("Name.xlsx").Activate
    End If
    Worksheets("Emp").Cells.Copy
    Workbooks("Master.xlsx").Sheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues
Else
    MsgBox "File doesn't exist."
End If
End Sub

Function BookOpen(wbName As String) As Boolean
On Error Resume Next
BookOpen = Len(Workbooks(wbName).Name)
End Function

 

[petes]

Great... it works!!! Thanks a lot buddy.... really appreciate your patience