VBA problem to open a file which is allready opened

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

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" <blaksnm@jjuc.no> wrote in message
news:1144393964.158167.57270@j33g2000cwa.googlegroups.com...
> 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
>

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

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)

"Snoopy" <blaksnm@jjuc.no> wrote in message
news:1144400764.766822.221180@u72g2000cwu.googlegroups.com...
> 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
>

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)

"Snoopy" <blaksnm@jjuc.no> wrote in message
news:1144400764.766822.221180@u72g2000cwu.googlegroups.com...
> 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
>

Thanks
I'l try

Thanks
I'l try

You've got more replies at your other thread.

Snoopy wrote:
>
> 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

--

Dave Peterson