Hello,

Does someone know why this doesn't work in VBA (Excel 97):

lTemp = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
Set wbTarget = Workbooks.Add
Application.SheetsInNewWorkbook = lTemp

Gr. ROn...

Hi

I can't test it for you but try this instead for a one sheet workbook

Workbooks.Add (1)
Or
Workbooks.Add (xlWBATWorksheet)



--
Regards Ron de Bruin
http://www.rondebruin.nl


"ROn..." wrote in message oups.com...
Hello,

Does someone know why this doesn't work in VBA (Excel 97):

lTemp = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
Set wbTarget = Workbooks.Add
Application.SheetsInNewWorkbook = lTemp

Gr. ROn...

"ROn..." wrote in message
oups.com...
Does someone know why this doesn't work in VBA (Excel 97):

lTemp = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
Set wbTarget = Workbooks.Add
Application.SheetsInNewWorkbook = lTemp

Hi Ron,

This works OK for me in all current versions of Excel. Can you give a
bit more detail about what doesn't work when you try it. By the way, this
line of code will do exactly the same as the above for the special case
where you want a new workbook with a single worksheet:

Set wbTarget = Workbooks.Add(xlWorksheet)

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

Hi Rob,

The error message translated from dutch error msg:

Error 1004

Method 'Sheetsinnewworkbook' from object '_Application' failed

Your option

Set wbTarget = Workbooks.Add(xlWorksheet)

works. Tnx!

Gr. ROn...

BTW: How can i create a workbook with 2 sheets?

Hoi Ron,

You're option also works.

Bedankt,

Gr ROn... ;-)

Hi/Hoi Ron

BTW: How can i create a workbook with 2 sheets?
You must use SheetsInNewWorkbook

I go install a Dutch 97 version now to test it for you

I be back



--
Regards Ron de Bruin
http://www.rondebruin.nl


"ROn..." wrote in message ups.com...
Hi Rob,

The error message translated from dutch error msg:

Error 1004

Method 'Sheetsinnewworkbook' from object '_Application' failed

Your option

Set wbTarget = Workbooks.Add(xlWorksheet)

works. Tnx!

Gr. ROn...

BTW: How can i create a workbook with 2 sheets?

Hi

I try it on a Virtual PC with The Dutch version of Excel 97 and it
is working correct there.

Is there other code in the sub ?


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message ...
Hi/Hoi Ron

BTW: How can i create a workbook with 2 sheets?
You must use SheetsInNewWorkbook

I go install a Dutch 97 version now to test it for you

I be back



--
Regards Ron de Bruin
http://www.rondebruin.nl


"ROn..." wrote in message ups.com...
Hi Rob,

The error message translated from dutch error msg:

Error 1004

Method 'Sheetsinnewworkbook' from object '_Application' failed

Your option

Set wbTarget = Workbooks.Add(xlWorksheet)

works. Tnx!

Gr. ROn...

BTW: How can i create a workbook with 2 sheets?

ROn... wrote:
Does someone know why this doesn't work in VBA (Excel 97):

lTemp = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
Set wbTarget = Workbooks.Add
Application.SheetsInNewWorkbook = lTemp


if you have an activeX control, it's probably this problem:

XL97: Error Changing Application Object from ActiveX Control
http://support.microsoft.com/default...b;en-us;177527

try to set TakeFocusOnClick property to false or "ActiveCell.Activate".

--
HTH,

okaizawa

Hello okaizawa,

This seems to be working, tnx!

Gr. ROn...

Yes this is a old 97 bug


--
Regards Ron de Bruin
http://www.rondebruin.nl


"ROn..." wrote in message ups.com...
Hello okaizawa,

This seems to be working, tnx!

Gr. ROn...