VBA problem to open a file which is allready opened

[Snoopy]

Hey guys
After a horrible nights sleep, caused by spooky VBA macro-gosts that
are harassing me and chasing me through the bright and flowering fields
of Visual Basic and into the dark woods of soul-searching lack of
VBA-qualifications, I have no other choice than ask You to conjur one
of these small devils in mind, by helping me solve my VBA-problem:

I try - and manage (hurray!!) - to create a macro that copy a specified
range from one workbook, then open a specified new one and finaly paste
the data (pluss - of course - do some other stuff) into this
predefinied workbook/sheet.
My problem is that my macro has rather bad manners when I try to run
the macro in the case where this specified workbook is allready opened.
How can I design my macro to check out and possibly close the file in
case it is opened, and open if it is not?

Proberbly an easy task (for a VBAxorsist), but still...so is swimming -
if one can.

If one of You feel the calling to give me peace in mind, I will be
greatfully happy and thankful for ever.

Regards
Snoopy


I have listet the beginning of my VBA-macro:

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Workbooks.Open Filename:= _
"Y:\\UnderlagDummy.xls"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select

 

[Bob Phillips]

Try this

Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Application.CutCopyMode = False
Selection.Copy
On Error Resume Next
Set owb = Workbooks("UnderlagDummy.xls")
On Error GoTo 0
If owb Is Nothing Then
Set owb = Workbooks.Open(Filename:= _
"Y:\\UnderlagDummy.xls")
Else
owb.Activate
End If
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Range("A1").Select



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Snoopy]

Thanks for Your consideration
The macro partial You sugested unhappyly did not quite do the job as
the debugger stops at "If owb Is Nothing Then" without opening the
file.

Regards
Snoopy

 

[Bob Phillips]

That's odd, it works okay here.

Try adding

Dim oWB as Workbook

earlier in the code.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Dave Peterson]

You've got more replies at your other thread.

Hey guys
After a horrible nights sleep, caused by spooky VBA macro-gosts that
are harassing me and chasing me through the bright and flowering fields
of Visual Basic and into the dark woods of soul-searching lack of
VBA-qualifications, I have no other choice than ask You to conjur one
of these small devils in mind, by helping me solve my VBA-problem:

I try - and manage (hurray!!) - to create a macro that copy a specified
range from one workbook, then open a specified new one and finaly paste
the data (pluss - of course - do some other stuff) into this
predefinied workbook/sheet.
My problem is that my macro has rather bad manners when I try to run
the macro in the case where this specified workbook is allready opened.
How can I design my macro to check out and possibly close the file in
case it is opened, and open if it is not?

Proberbly an easy task (for a VBAxorsist), but still...so is swimming -
if one can.

If one of You feel the calling to give me peace in mind, I will be
greatfully happy and thankful for ever.

Regards
Snoopy

I have listet the beginning of my VBA-macro:

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Workbooks.Open Filename:= _
"Y:\\UnderlagDummy.xls"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select

 

[Snoopy]

Thanks
I'l try

 

[Snoopy]

Thanks
I'l try