End Macro, if workbook can't be opened

[TheIntern]

Hey everyone,

I designed a Macro to copy data from another workbook that can be chosen each time.

Sub copydatafromotherworkbook()
Dim wkbk As Workbook
Dim myFileName As Variant
Set wkbk = Workbooks.Open(Filename:=myFileName)
.....

It works all right, but if the source workbook is already open or I press cancel instead of choosing a workbook an error occurs in the last line I stated here. tried to solve it like that but it doesn't work:

If myFileName = False Then
Exit Sub
End If

Any ideas? Thanks for your help,

The Intern

 

[Ruddles]

I believe it's:-

If myFileName = [COLOR=red][B]"[/B][/COLOR]False[COLOR=red][B]"[/B][/COLOR] Then
Exit Sub
End If

Place a breakpoint at the Exit Sub statement and examine the contents of myFileName when you cancel out of the dialog box.

 

[TheIntern]

Thanks for your help so far! Unfortunately the quotation marks don't change the situation. When I place a breakpoint on the Exit Sub the macro still runs further, so I think the

If myFileName = "False" Then
Exit Sub
End If

is still not working...is there any other approach?

 

[Andrew Poulsom]

Where are you choosing the workbook? I don't see anything assigned to myFileName.

 

[TheIntern]

To be honest, I got this formula from the internet as well^^
When I start the macro:

Dim wkbk As Workbook
Dim myFileName As Variant
myFileName = Application.GetOpenFilename

a menu appear, where you can open an excel workbook, just as if you press the home button and than open.

 

[Andrew Poulsom]

Try:

    myFileName = Application.GetOpenFilename
    If myFileName = False Then Exit Sub

You can use the functions here to check that the file exists and that the workbook isn't already open:

http://spreadsheetpage.com/index.php/tip/some_useful_vba_functions/

 

[Mr.Daines]

why not try

'Checks if master in use
Set wb = Workbooks.Open("your workbook")
While wb.ReadOnly
    wb.Close
    Set wb = Nothing
    Set wb = Workbooks.Open("your workbook")
    DoEvents
Wend

 

[TheIntern]

Thanks a lot!!
After including

Dim x As Workbook
    On Error Resume Next
    Set x = Workbooks(wbname)
    If Err = 0 Then WorkbookIsOpen = True _
        Else WorkbookIsOpen = False

from the proposed website, it finally works!