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

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

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

.