Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create new workbook and new worksheet and close. Worksheet not saved | Excel Programming | |||
Create new workbook and new worksheet and close. Worksheet not sav | Excel Worksheet Functions | |||
Protecting worksheet cause vba code to fail | Excel Programming | |||
Copy & paste cells fr open worksheet then close the worksheet | Excel Programming | |||
Links to 2nd closed worksheet fail when using offset function ?? | Excel Worksheet Functions |