Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 8
Default Close Worksheet and Making Worksheet Invisible - Both fail

Hi,
I use this (pardon the variable names...)

infile = "C:\Temp\Garb-Tests\Test1\solTrip.xlsx"
insheet = "Trip"
'---------------------------------------------------------------------
Set o = Workbooks.Open(infile).Worksheets(insheet)
....
Reading data from this works. However, this causes the sheet to open, but I
don't want that so I do:

o.Visible = false

When I do this, ths sheet still opens but it opens without any data!
Also, when I try to close this workbook using:

o.Close (false, false)

I get a runtime error 438 - Object does not support....

I also tried to close using this:

o.Close savechanges:=False

but that did not work either.

Any help for the close and the visible issues?

Thanks.

EK
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,533
Default Close Worksheet and Making Worksheet Invisible - Both fail

Hi


Your problem is that 'o' becomes a worksheet object, not as a workbook
object.

You can turn off screenupdating if you just need to read/write data and
close o again...

Sub test()
infile = "C:\Temp\Garb-Tests\Test1\solTrip.xlsx"
insheet = "Trip"
Dim o As Workbook
Dim sh As Worksheet
'---------------------------------------------------------------------
Application.ScreenUpdating = False
Set o = Workbooks.Open(infile) '.Worksheets(insheet)
Set sh = Worksheets(insheet)

'get data
o.Close False, False
Application.ScreenUpdating = True
End Sub


Hopes this helps.
....
Per

"ekareem" skrev i meddelelsen
...
Hi,
I use this (pardon the variable names...)

infile = "C:\Temp\Garb-Tests\Test1\solTrip.xlsx"
insheet = "Trip"
'---------------------------------------------------------------------
Set o = Workbooks.Open(infile).Worksheets(insheet)
...
Reading data from this works. However, this causes the sheet to open, but
I
don't want that so I do:

o.Visible = false

When I do this, ths sheet still opens but it opens without any data!
Also, when I try to close this workbook using:

o.Close (false, false)

I get a runtime error 438 - Object does not support....

I also tried to close using this:

o.Close savechanges:=False

but that did not work either.

Any help for the close and the visible issues?

Thanks.

EK


  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 8
Default Close Worksheet and Making Worksheet Invisible - Both fail

Hi Jessen,
Thanks for the tip.
You are correct. Following your point, Also,

o.Parent.Close

woked.

Thanks again :)



"Per Jessen" wrote:

Hi


Your problem is that 'o' becomes a worksheet object, not as a workbook
object.

You can turn off screenupdating if you just need to read/write data and
close o again...

Sub test()
infile = "C:\Temp\Garb-Tests\Test1\solTrip.xlsx"
insheet = "Trip"
Dim o As Workbook
Dim sh As Worksheet
'---------------------------------------------------------------------
Application.ScreenUpdating = False
Set o = Workbooks.Open(infile) '.Worksheets(insheet)
Set sh = Worksheets(insheet)

'get data
o.Close False, False
Application.ScreenUpdating = True
End Sub


Hopes this helps.
....
Per

"ekareem" skrev i meddelelsen
...
Hi,
I use this (pardon the variable names...)

infile = "C:\Temp\Garb-Tests\Test1\solTrip.xlsx"
insheet = "Trip"
'---------------------------------------------------------------------
Set o = Workbooks.Open(infile).Worksheets(insheet)
...
Reading data from this works. However, this causes the sheet to open, but
I
don't want that so I do:

o.Visible = false

When I do this, ths sheet still opens but it opens without any data!
Also, when I try to close this workbook using:

o.Close (false, false)

I get a runtime error 438 - Object does not support....

I also tried to close using this:

o.Close savechanges:=False

but that did not work either.

Any help for the close and the visible issues?

Thanks.

EK


.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Create new workbook and new worksheet and close. Worksheet not saved Patrick Molloy Excel Programming 0 July 25th 09 07:00 PM
Create new workbook and new worksheet and close. Worksheet not sav Patrick Djo Excel Worksheet Functions 0 July 20th 09 07:10 PM
Protecting worksheet cause vba code to fail Ken Warthen[_2_] Excel Programming 7 April 23rd 09 05:42 PM
Copy & paste cells fr open worksheet then close the worksheet Sin Excel Programming 1 October 2nd 06 02:20 PM
Links to 2nd closed worksheet fail when using offset function ?? Jordan795 Excel Worksheet Functions 1 June 21st 05 01:43 AM


All times are GMT +1. The time now is 12:11 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"