Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query
Range("A7").QueryTable.Refresh BackgroundQuery:=False
Can someone please explain why this is = False. This line of code is for refreshing a query so I do not understand why it is False. Every time I try to refresh this query on a computer that is not the development pc I get an error which points to this line of code after I have entered the parameters. I have created a new data source on this pc to point to the same file as the original pc and called it the same. Is there something else I am not doing? -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query
hi,
if the background guery is set to false then code execution is halted until the refresh is complete. otherwise if there is additional code after the refresh command that may try to manipulate the query data and background query is set to true(default is true), the additional code will execute and start manipulating query data before is has refreshed. always best to let the MSQ complete the refresh. regards FSt1 "Leanne M (Aussie)" wrote: Range("A7").QueryTable.Refresh BackgroundQuery:=False Can someone please explain why this is = False. This line of code is for refreshing a query so I do not understand why it is False. Every time I try to refresh this query on a computer that is not the development pc I get an error which points to this line of code after I have entered the parameters. I have created a new data source on this pc to point to the same file as the original pc and called it the same. Is there something else I am not doing? -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query
Ok thank you for that info. The refresh keeps failing on this line so
thought it may be something but turns out to be nothing. -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "FSt1" wrote: hi, if the background guery is set to false then code execution is halted until the refresh is complete. otherwise if there is additional code after the refresh command that may try to manipulate the query data and background query is set to true(default is true), the additional code will execute and start manipulating query data before is has refreshed. always best to let the MSQ complete the refresh. regards FSt1 "Leanne M (Aussie)" wrote: Range("A7").QueryTable.Refresh BackgroundQuery:=False Can someone please explain why this is = False. This line of code is for refreshing a query so I do not understand why it is False. Every time I try to refresh this query on a computer that is not the development pc I get an error which points to this line of code after I have entered the parameters. I have created a new data source on this pc to point to the same file as the original pc and called it the same. Is there something else I am not doing? -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query
What kind of error are you seeing when you run it. Maybe that cell isn't
associated with a querytable? Maybe turning off all the background refreshing querytables would work??? Option Explicit Sub testme() Dim QT As QueryTable Dim wks As Worksheet For Each wks In ThisWorkbook.Worksheets For Each QT In wks.QueryTables QT.Refresh BackgroundQuery:=False Next QT Next wks End Sub This should be a one time thing--that setting will stay that way until you (or your code) changes it. Leanne M (Aussie) wrote: Range("A7").QueryTable.Refresh BackgroundQuery:=False Can someone please explain why this is = False. This line of code is for refreshing a query so I do not understand why it is False. Every time I try to refresh this query on a computer that is not the development pc I get an error which points to this line of code after I have entered the parameters. I have created a new data source on this pc to point to the same file as the original pc and called it the same. Is there something else I am not doing? -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query
Hi Dave,
The error I am currently getting reads - ODBC Excel Driver Login Failed - Failure creating File and I can click OK to this. When I do it allows me to select the workbook that is my database so I do this and then it works. I have had other errors but at the moment as I am getting this one I am not seeing the others to let you know what they are. Should I try the code you have or has this information changed things. What do you mean by 'it should be a one time thing'? Do you mean it should be placed in workbook open code or that I should be able to add it in - see if it solves the problem and then remove it? Sorry to ask so many questions but I really need detailed instructions - I have tried to follow instructions given to me recently and am starting to realise that I know less and less about what I am doing!! Thanks -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "Dave Peterson" wrote: What kind of error are you seeing when you run it. Maybe that cell isn't associated with a querytable? Maybe turning off all the background refreshing querytables would work??? Option Explicit Sub testme() Dim QT As QueryTable Dim wks As Worksheet For Each wks In ThisWorkbook.Worksheets For Each QT In wks.QueryTables QT.Refresh BackgroundQuery:=False Next QT Next wks End Sub This should be a one time thing--that setting will stay that way until you (or your code) changes it. Leanne M (Aussie) wrote: Range("A7").QueryTable.Refresh BackgroundQuery:=False Can someone please explain why this is = False. This line of code is for refreshing a query so I do not understand why it is False. Every time I try to refresh this query on a computer that is not the development pc I get an error which points to this line of code after I have entered the parameters. I have created a new data source on this pc to point to the same file as the original pc and called it the same. Is there something else I am not doing? -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query (Off Topic)
G'day Leanne
Your living in the UK for it's tropical climate and frosty cold beer, right!.LOL Kind regards Mark Melbourne, Australia. PS. Collingwood beat Essendon in the ANZAC Day match. Go Pies! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query
You are now officially over my head. I have no idea what that error message
means! But I meant that this setting shouldn't ever change on its own. You should be able to set it once (manually or via code) and it should stay that way. Maybe someone who knows ODBC and its error messages will chime in and set us straight???? Leanne M (Aussie) wrote: Hi Dave, The error I am currently getting reads - ODBC Excel Driver Login Failed - Failure creating File and I can click OK to this. When I do it allows me to select the workbook that is my database so I do this and then it works. I have had other errors but at the moment as I am getting this one I am not seeing the others to let you know what they are. Should I try the code you have or has this information changed things. What do you mean by 'it should be a one time thing'? Do you mean it should be placed in workbook open code or that I should be able to add it in - see if it solves the problem and then remove it? Sorry to ask so many questions but I really need detailed instructions - I have tried to follow instructions given to me recently and am starting to realise that I know less and less about what I am doing!! Thanks -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "Dave Peterson" wrote: What kind of error are you seeing when you run it. Maybe that cell isn't associated with a querytable? Maybe turning off all the background refreshing querytables would work??? Option Explicit Sub testme() Dim QT As QueryTable Dim wks As Worksheet For Each wks In ThisWorkbook.Worksheets For Each QT In wks.QueryTables QT.Refresh BackgroundQuery:=False Next QT Next wks End Sub This should be a one time thing--that setting will stay that way until you (or your code) changes it. Leanne M (Aussie) wrote: Range("A7").QueryTable.Refresh BackgroundQuery:=False Can someone please explain why this is = False. This line of code is for refreshing a query so I do not understand why it is False. Every time I try to refresh this query on a computer that is not the development pc I get an error which points to this line of code after I have entered the parameters. I have created a new data source on this pc to point to the same file as the original pc and called it the same. Is there something else I am not doing? -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert hard coded query criteria to Parameter Query | Excel Discussion (Misc queries) | |||
Excel 2007 / MS Query - editing existing query to another sheet | Excel Discussion (Misc queries) | |||
Importing Data via Web Query - Can values be passed to query? | Excel Discussion (Misc queries) | |||
Save data retreived from query without saving query | Excel Discussion (Misc queries) | |||
Problem with .Background Query option of ODBC Query | Excel Programming |