Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ThisWorkbook
Hi guys,
I have a sheet which periodically through the day takes a snapshot of financial prices. This works fine as long as this is the active sheet, if it isn't I get a vba error. I've been using a laptop as a spare standalone machine for this sheet, as I need to be using excel throughout the same period. Can I make the code focus to the actual workbook that contains the code and do what it needs to do in the background? I'm thinking along the lines of ThisWorkbook.sheets("import").range("m3") etc, which I'm hoping will do the job? Will this do it? Ron |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ThisWorkbook
Surely, if you are competent enough to ask a question that specific and that
accurate (in terms of OM and syntax), you are competent enough to try and find out yourself? -- HTH RP (remove nothere from the email address if mailing direct) "Ron" wrote in message 10.205... Hi guys, I have a sheet which periodically through the day takes a snapshot of financial prices. This works fine as long as this is the active sheet, if it isn't I get a vba error. I've been using a laptop as a spare standalone machine for this sheet, as I need to be using excel throughout the same period. Can I make the code focus to the actual workbook that contains the code and do what it needs to do in the background? I'm thinking along the lines of ThisWorkbook.sheets("import").range("m3") etc, which I'm hoping will do the job? Will this do it? Ron |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ThisWorkbook
"Bob Phillips" wrote in
: Surely, if you are competent enough to ask a question that specific and that accurate (in terms of OM and syntax), you are competent enough to try and find out yourself? Bob, I'm unsure whether to take that as a compliment or a slap on the wrist for asking a dumb question. I'm trying to teach myself vba with a couple of books, practical examples and the help of the good people on here. I'm far from confident of my abilities as yet, but maybe I should be a bit more confident. Thanks for the reply anyway. Ron |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ThisWorkbook
Ron,
A bit of both, but you learn far more by trying it yourself. Go for it, it won't bite :-). Keep us in reserve for when you try something which, to your mind, should work and it doesn't, or you are truly stumped. Just asking '... will this work?' is a bit dumb (gently said ;-), as you can easily find out. Bob "Ron" wrote in message 10.205... "Bob Phillips" wrote in : Surely, if you are competent enough to ask a question that specific and that accurate (in terms of OM and syntax), you are competent enough to try and find out yourself? Bob, I'm unsure whether to take that as a compliment or a slap on the wrist for asking a dumb question. I'm trying to teach myself vba with a couple of books, practical examples and the help of the good people on here. I'm far from confident of my abilities as yet, but maybe I should be a bit more confident. Thanks for the reply anyway. Ron |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ThisWorkbook
Hi Bob,
You just knew I'd be back ;) I tried many variations of Thisworkbook, and to be honest had a nightmare. In the end I've settled for the first line in the timed code to be ThisWorkbook.Activate At least this gives the relevant workbook focus to run the code ok. At the moment I'm happy with this until I find alternate usage of ThisWorkbook My main aim was to run the code in the background without having to give the workbook focus, I'm sure this is possible but at the moment it's stumping me. Ron |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ThisWorkbook
Ron
What is it you're trying to do? You can certainly do almost anything without giving the workbook focus. For instance, if you want to copy a range, you would use ThisWorkbook.Sheets("import").Range("a1").Copy Be specific about what you are doing and you'll have an answer in no time. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com Ron wrote: Hi Bob, You just knew I'd be back ;) I tried many variations of Thisworkbook, and to be honest had a nightmare. In the end I've settled for the first line in the timed code to be ThisWorkbook.Activate At least this gives the relevant workbook focus to run the code ok. At the moment I'm happy with this until I find alternate usage of ThisWorkbook My main aim was to run the code in the background without having to give the workbook focus, I'm sure this is possible but at the moment it's stumping me. Ron |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
ThisWorkbook
Application.ScreenUpdating = False
set bk = Activeworkbook ThisWorkbook.Activate ' code you have bk.Activate Application.ScreenUpdating = True Will disguise the fact you have activated the workbook if your code is fast. to work on a workbook in the background qualify everything from the Thisworkbook.Object down thisworkbook.Worksheets("Sheet1").Range("A1").Valu e = 6 or with thisworkbook.Worksheets("Sheet1") .Range("A1").Value = 6 .Range("B9").Value = 11 .Range("A1:C30").Sort Key1:=.Range("C1") End with -- Regards, Tom Ogilvy "Ron" wrote in message 10.205... Hi Bob, You just knew I'd be back ;) I tried many variations of Thisworkbook, and to be honest had a nightmare. In the end I've settled for the first line in the timed code to be ThisWorkbook.Activate At least this gives the relevant workbook focus to run the code ok. At the moment I'm happy with this until I find alternate usage of ThisWorkbook My main aim was to run the code in the background without having to give the workbook focus, I'm sure this is possible but at the moment it's stumping me. Ron |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
ThisWorkbook
Ron,
You could also try specifying explicitly which workbook: Workbooks("YourWorkbookName.xls").Sheets("import") .Range ("m3") Regards, Lonnie M. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
ThisWorkbook
Thanks for the continuing help guys.
I've pasted one of the routines below that I'm trying to do in the background. Im getting a 'Select method of Range class failed' error at the first line of code, ThisWorkbook.Worksheets("WEB QUERY").Range("A51:d100").Select Here's the code in full... Sub PasteMarket() ' copies the current market and pastes it lower down ThisWorkbook.Worksheets("WEB QUERY").Range("A51:d100").Select Selection.Insert Shift:=xlDown ThisWorkbook.Worksheets("WEB QUERY").Range("A1:d49").Select Selection.Copy ThisWorkbook.Worksheets("WEB QUERY").Range("A51").Select Selection.PasteSpecial paste:=xlPasteValuesAndNumberFormats, Operation: = _ xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False ThisWorkbook.Worksheets("WEB QUERY").Range("l2").Copy ThisWorkbook.Worksheets("WEB QUERY").paste Destination: =ThisWorkbook.Worksheets("WEB QUERY").Range("d1") End Sub I'm sure also that you guys could trim that code down somewhat, but as I say I'm trying to teach myself this, so please forgive any amateurish lines of code. Thanks guys, Ron |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
ThisWorkbook
Ron
Im getting a 'Select method of Range class failed' error at the first line of code, ThisWorkbook.Worksheets("WEB QUERY").Range("A51:d100").Select You can't select a range on a worksheet that's not active. That's what the error means. You could do: ThisWorkbook.Activate ThisWorkbook.Worksheets("Web query").Select ThisWorkbook.Worksheets("Web query").Range("A51:D100").Select which makes sure that the workbook and worksheet you want are active. But you shouldn't do that. You should strive to not use Select or Activate unless it's a necessary part of your code and it rarely is. Here's the code in full... Sub PasteMarket() With ThisWorkbook.Worksheets("WEB QUERY") ' copies the current market and pastes it lower down .Range("A51:D100").Insert Shift:=xlDown ThisWorkbook.Worksheets("WEB QUERY").Range("A51:d100").Select Selection.Insert Shift:=xlDown .Range("A1:D49").Copy .Range("A51").PasteSpecial Paste:=xlPasteValuesAndNumberFormats 'skip the rest of the arguments because they're the default values anyway Application.CutCopyMode = False ThisWorkbook.Worksheets("WEB QUERY").Range("A1:d49").Select Selection.Copy ThisWorkbook.Worksheets("WEB QUERY").Range("A51").Select Selection.PasteSpecial paste:=xlPasteValuesAndNumberFormats, Operation: = _ xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False .Range("I2").Copy Destination:=.Range("D1") ThisWorkbook.Worksheets("WEB QUERY").Range("l2").Copy ThisWorkbook.Worksheets("WEB QUERY").paste Destination: =ThisWorkbook.Worksheets("WEB QUERY").Range("d1") End With End Sub Everytime you have a Something.Select followed by a Selection.DoSomething, you can usually make it one line like Something.DoSomething. Here's some more reading on the subject http://www.dicks-blog.com/archives/2...-and-activate/ I'm sure also that you guys could trim that code down somewhat, but as I say I'm trying to teach myself this, so please forgive any amateurish lines of code. In a couple of years you'll look back at the code you wrote with disgust. At least that's what I do. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use: ThisWorkbook module | Excel Programming | |||
ThisWorkbook.Path | Excel Programming | |||
Calling Sub(s) from ThisWorkbook | Excel Programming | |||
ThisWorkbook variables... | Excel Programming | |||
ThisWorkbook Macros | Excel Programming |