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 
0
Utf
12/30/2009 5:50:01 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
561 Views

Similar Articles

[PageSpeed] 40

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" <ekareem@discussions.microsoft.com> skrev i meddelelsen 
news:0F9A3AD0-D44E-469D-B760-704696F15FDE@microsoft.com...
> 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 

0
Per
12/30/2009 6:22:35 PM
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" <ekareem@discussions.microsoft.com> skrev i meddelelsen 
> news:0F9A3AD0-D44E-469D-B760-704696F15FDE@microsoft.com...
> > 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 
> 
> .
> 
0
Utf
12/30/2009 9:15:01 PM
Reply:

Similar Artilces:

Making a specific row number active from a cell entry
Hi, I need to be able to insert a new row into a worksheet at row 'x' where 'x' is determined by the value in a particular cell. So e.g. if the user enters the number '34' into cell A2 I want to be able to wite a short macro that will when run insert a new row into the worksheet at row 34. I just cannot find a way to automate the process of selecting the require row number? Strugling with this for hours - would appreciate any help. This is my first ever online question. Hope it makes sense. Thank you. Hi, Right click your sheet tab, view code an...

ThisWorkbook.Close question
Please take a look at the code below: Public Sub DumbFoo ( ) ThisWorkbook.Close() Sheet1.Range("A1").Value = -1 ThisWorkbook.Save() End Sub Is it safe to assume that the code below the "ThisWorkbook.Close()" line will NOT be executed??? My question is basically: Does all VBA code stop executing once a .Close() function is called??? Thank you! I don't believe the code would execute at all because of the parentheses after Close. It would throw an error an tell you that something is missing, or type mismatch. But, if you did...

slight variation when working with separate worksheets
Please tell me why the following two scenarios show different results: Scenario One: When working with just one worksheet: In cell A1 I have a completely blank cell with no value. In cell A2, I type "=A1" (without quotes). The result shows in A2, naturally, another blank cell. Scenario Two: When I have two worksheets Wrksht1 and Wrksht2: In Wrksht1 in cell A1 I have a completely blank cell. Now I go over to Wrksht2 to cell A1 and type "=Wrksht1!A1" (without quotes). It shows the value 0. Why is the result in A2 of Wrksht2 not showing a completely blank cell like i...

How to Enable Close(cross) button of MB_YESNO messagebox
Hi, I want to enable the close(cross) button in the right corner of YESNO message box. Can anybody tell me how to do this. Klic. "Klic" <rohit.turambekar@gmail.com> wrote in message news:1186120969.045121.287770@i13g2000prf.googlegroups.com... > I want to enable the close(cross) button in the right corner of YESNO > message box. > Can anybody tell me how to do this. Why? If there is a valid answer other than yes or no, then how about a yes/no/cancel message box in which (I would guess) the x button is equivalent to cancel. That would be more standard. Da...

closing
What's the best way to close for the day? Making a backup is a given, but what about the computer? Is it better to shut everything off every day, or is it less wear and tear on the computer to leave it on all the time? Any thoughts on this would be appreciated. Craig That's a matter of preference really. Personally I always turn off my rigs at home when I'm done with them. At the store we leave most of ours on 24/7. We've done it for years with no issues. Our register must be left on as it does automatic backups to a tape drive every night. I'd set your monitors ...

Outlook closes and closes
Hi I have a strange bug. Windows XP Sp2 and outlook from office 20003 with all service packs applied. When I start outlook from windows out of standby postion, outlook immediately after the splash screen closes without reporting any errors in the eventlog or in any other log. If I start outlook the 10th time (or so) it stabilizes and does not close anymore and everything works again. info: My machine is fully patched and I am well known with keeping things healthy. Outlook.exe Version: 11.0.6353.0 Any ideas what to do? Thanks... try the detect and repair in the help menu. >----...

Help
When creating or fowarding an e-mail message using outlook 2000, scrolling through my "recipients" list (contacts) causes the program to close unexpectedly. So, I click "new", click "to", switch to my "contacts" list, and as I scroll through the names, the program just closes. Is there a possibility that one of my contact records is corrupt? I have opened all of my contacts and saved them without an error. This problem developed shortly after the install of Office 2000's calender update, and basic security update. I have since fully u...

Auto-close Quotes when Closing Opportunity
We would like the ability to close an opportunity and have it ask if you want to close all the associated quotes. Right now, it says you can’t close the opp until you close all the open quotes and you have to each one of those one by one. Barring that, if we could select multiple quotes and click on a menu item (Close all selected quotes), that would be most useful. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not ...

Workflow Rule -> Closing Activity
I set up a Manual Workflow Rule for Contacts to close out Holiday Season cards sent. The rule uses a condition to check for Contacts with cards sent and then Creates an Activity to add to each of the appropriate Contacts for future reference. What else do I need to do to close out all of activites rather than 1 at a time? Here is the workflow rule if that helps: if Contact.holidaycardlist = 1 then Letter: Send Holiday Card endif -- Dan W Systems Engineer Denver, CO ...

Lost many worksheets saved in a workbook- how can I retrieve?
I kept inserting sheets into a workbook. Now only a few of the sheets are visible, and I need the rest- how can I get them back? I wasn't prompted that there was a limit... ...

Data file not closed properly #3
I saw several post regarding this, but none had any answers, so I starting a new post. There is also nothing in the KB about this. Since othe people are having the same problem Microsoft should take a look at it. I get the message "The data file 'Personal Folders" was not closed properly" every time I close Outlook 2003. It runs a quick check/fix and starts up fine. I am running XP pro w/ SP-2. Outlook 2003 w/ SP-1 All patches and updates are current. My notebook was just rebuilt for so I could get a clean slate for the office 2003 install, (reformat drive and new ins...

Code to display Worksheet names
Hi - I am looking for a formula/code that will display the worksheet name within which it is placed. For example: If I have a worksheet named "Apples" and in cell A1 within this worksheet I have code that somehow says; ='s worksheet name - Cell A1 would then read as "Apples" Is this possible? Advance thanks, Michael --- Message posted from http://www.ExcelForum.com/ =MID(CELL("filename",A1),FIND("]",CELL("filename",A1)) +1,255) Note: wb must be already saved to work. HTH Jason Atlanta, GA >-----Original Message----- >Hi...

Make CRM Faster: Slow = Practically Unusable
CRM 3.0 is great, however for practical use, it is too slow & cumbersome. Sales staff need a very responsive solution to be able to view/switch records during a phone conversation. Support staff need similar responsiveness. Speed is a BIG issue with this product & almost killed it within our organisation. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion ...

Edit a worksheet command button
Using 2003 - I have inherited a workbook that has various command buttons on the worksheets. The buttons are attached to a macro. I need to edit the buttons but I can't seem to get the button into the edit mode. How do I do that? Glen If they're commandbuttons from the Control toolbox toolbar, then show that toolbar: View|Toolbars|Control Toolbox Then click on the Design mode icon. Do the work and click on that design mode icon once more to get back to normal. "glen.e.mettler@lmco.com" wrote: > > Using 2003 - I have inherited a workbook that has various comma...

Outlook2003 open and closed immediatly
I checked event log, nothing. And this happened when I just reboot my machine and first time tried to open Outlook, still same problem. But I found, when I double click anywhere in the Outlook Windows even it only gave me 0.5 second, then it won't close. Andy -------------------------------------------------------------- >>Look in task manager - processes, and see if multiple >>instances are running. If so, end all of them and try >>starting again. I had similar problem w/user, posted >>above. >>>>>-----Original Message----- >>>>>My O...

New-PSsession failing, but winrs commands work fine.
I'm successfully able to run winrs -r:http://localhost:5985, but when I try to create a new-pssession I get the following error: [localhost] Connecting to remote server failed with the following error message : The configuration XML is not valid. T he provider "Microsoft.PowerShell" supports the =E5=8D=97=E6=85=8D=E5=81=AE= =E7=95=AC=E6=A5=A7=E5=89=AE=E6=B1=A5=E6=85=A5=E6=95=B3=E6=A1=93=E6=B1=A5=E4= =8D=AC=E6=B9=AF=E6=95=B4=E7=91=B8 method but it could not be loaded. Verify that the prov ider exports the =E5=8D=97=E6=85=8D=E5=81=AE=E7=95=AC=E6=A5=A7=E5=89=AE=E6= =B1=A5...

Publish completed with errors, CRM failed
This is a multi-part message in MIME format. ------=_NextPart_000_0026_01C514DB.882B0040 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I have CRM 1.2 server and Database server installed in one system. All = these Days I used to add one or more attributes to CRM using deployment = manager. Now I have requirements for adding few more attributes, hence i = have added few more attributes and trying to do publish customization. I = have select Lead and then seleted target CRM server and said next then = it says "Publish completed ...

Outlook Closes After Closing an Email
When I click the 'X' to close an email after reading it, Outlook closes (or tries to close, but I get a warning because I have templates in my Outbox). This only seems to happen when I close a new email. I have Outlook 2003. Thank you. ...

Outlook 2003 SR1 SEND TO AS ATTACHMENT "The Operation Failed"
Hello All, I have a user running Windowx XP Pro and Office 2003 SR1. When she opens a spreadsheet and clicks File>Send to>Mail Recipient (As Attachment). She recieves an error that says "The Operation Failed" She clicks OK and see another error that says An error has occured, please restart your mail program. A re-install of Office did not fix the problem so in the interest of Time i did a clean install of Windows XP and Office 2003. Restored her data and sent her on her way. She was able to send a few spreadsheets using the Send To feature but when she tried using the one t...

Recall message failed #2
The recall message sometimes fails. The users are on the same Exchange server and using the same version of Outlook. Why would the recall fail? Does the recipient's Outlook have to be open in order for the recall to work? I know that the recipient should not open the recalled message. Thank you. Recall is a notoriously unreliable function... -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** In news:6da101c475cb$58886080$a401280a@phx.gbl, HG wrote: > The ...

Search/Match between 2 x separate Worksheets and populate result in third worksheet
I have 2 x databases that i have copied as a text file and pasted into two separate worksheets in the one workbook. The first worksheet of data has come from a new database which lists all current jobs and quotes for supervisors. The second worksheet of data has come from the financial database which list all current jobs and all old jobs. I wish to produce a search/find which will populate the third worksheet with only current jobs (not old jobs and not quotes), which will also bring all of the finacial data listed in the same row of each successfull match. The first worksheet contains a ...

YE Closing Procedures
The year end closing procedures need to be more efficient and user friendly. For example, the payables and receivable management is date sensitive except for amounts since last close, vendor yearly summary etc. These modules should be fully date sensitive and the ability to enter into multiple years without caveats should exist. Year end is stressful enough ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the b...

Worksheet Not Appearing
When I double-click on an excel file, I can only see the title bar and where the worksheet area is, I see the desktop. If I start excel first and then open the same file, the entire excel screen appears. I updated the video and display drivers to no avail. Our company has over 100 workstations configured the same way and only 2 users are seeing this. And this is occurring only in excel. We are running XP SP2 and Office XP. I'm scratching my head over where to go from here. Any help/suggestion would be much appreciated. Sometimes one of these works: Tools|Options|General|Ignore oth...

reference a chart from another worksheet witha hyperlink
Dear experts, would like to reference a chartsheet form another worksheet of the same workbook (to put an explanation of what each chart in the workbook does, put the name of the chart aside, on which you can click to go directly on that chart). I have done it for some worksheets with the "hyperlink" function, but this does not work for charts... Could you please help me? Many thanks in advance! Best regards, -- Valeria Valeria - You can't hyperlink to a chart sheet, as you've discovered. You can fake it with a simple macro, as described in the Charting FAQ: http...

excel link to work can't get full worksheet
When either importing ole or pasting from an Excel Woorkshhet into a Word doc, only get a portion of the worksheet to show, even if I "copy" the whole spreadsheet. How big is the spreadsheet you are trying to paste? "Tom" wrote: > When either importing ole or pasting from an Excel Woorkshhet into a Word > doc, only get a portion of the worksheet to show, even if I "copy" the whole > spreadsheet. > 158 rows by 5 columns....single spreadsheet/worksheet "FinRazel" wrote: > How big is the spreadsheet you are trying to paste? > ...