Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Click Button to Load External Data Into Spreadsheet
Hi Folks,
I'm trying to figure out the easiest way to create a spreadsheet template with an "update" button on it. When clicked, this button should tell Excel to grab data from an external database and place it in specified columns. Furthermore, this Excel template should be able to apply conditional formatting to the new data if it meets certain criteria. Do I need to know VB for this, or can a macro do this work for me? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Click Button to Load External Data Into Spreadsheet
Via Data/Import external data you create a QueryTable. that querytable can be refreshed when you rightclick it and choose refresh from the popup. (alternatively you can put a button next to it to call the refresh method of the QueryTable object) -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam simsjr wrote : Hi Folks, I'm trying to figure out the easiest way to create a spreadsheet template with an "update" button on it. When clicked, this button should tell Excel to grab data from an external database and place it in specified columns. Furthermore, this Excel template should be able to apply conditional formatting to the new data if it meets certain criteria. Do I need to know VB for this, or can a macro do this work for me? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Click Button to Load External Data Into Spreadsheet
yes you can. when you define parameters in your query excel can assign those parameters to cells. when you change the cell, the query will be updated. Paramaters.. In the Filter Data Page of the wizard.. Select : Field1 Include only rows where : Select Equals in 1st Dropdown Type 1 in 2nd dropdown. (difficult to enter the param in this box.. be patient.. Press next,next In the Finish Page.... Select View or Edit query in MSQuery Press finish in MSquery in will now say.. Criteria Field: Field1 Value : '1' edit the '1' to [MyParameter?] no quotes! in the popup it will ask for a value: enter a 1 Choose File/Return Data to Excel. Now you should see : a refedit to choose the destination.. a button to assign the Parameters to cells. search excel help for Customize a Parameter QUery. have fun! -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam simsjr wrote : That's very helpful. Now what if I wanted to have a date field where I can specify date criteria to retrieve certain records. Is there a way to make a cell value feed the query to this regard? "keepITcool" wrote: Via Data/Import external data you create a QueryTable. that querytable can be refreshed when you rightclick it and choose refresh from the popup. (alternatively you can put a button next to it to call the refresh method of the QueryTable object) -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam simsjr wrote : Hi Folks, I'm trying to figure out the easiest way to create a spreadsheet template with an "update" button on it. When clicked, this button should tell Excel to grab data from an external database and place it in specified columns. Furthermore, this Excel template should be able to apply conditional formatting to the new data if it meets certain criteria. Do I need to know VB for this, or can a macro do this work for me? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Click Button to Load External Data Into Spreadsheet
Beautiful. That's exactly what I needed to know.
Except, once Excel populates with the data, I can't seem to change the cell formats to translate numbers like "19800" into a usable time format like "1:30 PM" Any ideas about why this is happening? I'm using Office 97, BTW "keepITcool" wrote: yes you can. when you define parameters in your query excel can assign those parameters to cells. when you change the cell, the query will be updated. Paramaters.. In the Filter Data Page of the wizard.. Select : Field1 Include only rows where : Select Equals in 1st Dropdown Type 1 in 2nd dropdown. (difficult to enter the param in this box.. be patient.. Press next,next In the Finish Page.... Select View or Edit query in MSQuery Press finish in MSquery in will now say.. Criteria Field: Field1 Value : '1' edit the '1' to [MyParameter?] no quotes! in the popup it will ask for a value: enter a 1 Choose File/Return Data to Excel. Now you should see : a refedit to choose the destination.. a button to assign the Parameters to cells. search excel help for Customize a Parameter QUery. have fun! -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam simsjr wrote : That's very helpful. Now what if I wanted to have a date field where I can specify date criteria to retrieve certain records. Is there a way to make a cell value feed the query to this regard? "keepITcool" wrote: Via Data/Import external data you create a QueryTable. that querytable can be refreshed when you rightclick it and choose refresh from the popup. (alternatively you can put a button next to it to call the refresh method of the QueryTable object) -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam simsjr wrote : Hi Folks, I'm trying to figure out the easiest way to create a spreadsheet template with an "update" button on it. When clicked, this button should tell Excel to grab data from an external database and place it in specified columns. Furthermore, this Excel template should be able to apply conditional formatting to the new data if it meets certain criteria. Do I need to know VB for this, or can a macro do this work for me? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Click Button to Load External Data Into Spreadsheet
Excel is flexible.. but the integer 19800 doesn't become a timeserial like 1:30pm ?? all by it's lonesome self.. at least i wouldn't see how <g 1st option: adapt the query to compute and format your data into a date/time (there are many functins you can use inside the qry try /build or zoom in msq. 2nd option: add a formula column next to the query.. check query options so that formulas are extended when the length of the retrieved data varies.. and you're done. (use outlining to hide the columns you dont want to see. how long have you used excel? you have to do some trial and error yourself,mate! that's how we all learned. when stuck: take a breath. rethink, try help. rightclicking sometimes helps to give you a clue.... just try out (or reconnoitre) all options presented... or.. buy a book. you'll be amazed at what you could have done with excel, had you just known :) -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam simsjr wrote : Beautiful. That's exactly what I needed to know. Except, once Excel populates with the data, I can't seem to change the cell formats to translate numbers like "19800" into a usable time format like "1:30 PM" Any ideas about why this is happening? I'm using Office 97, BTW "keepITcool" wrote: |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Click Button to Load External Data Into Spreadsheet
A valid time only (i.e. without a date) is a number between 0 and
0.9999999999, i.e. less than 1. If 19800 represents 1:30 PM, you'll have to develop the formula to translate 19800 to 0.5625 (the latter is equal to 13.5/24). On Thu, 29 Jul 2004 12:25:59 -0700, simsjr wrote: Beautiful. That's exactly what I needed to know. Except, once Excel populates with the data, I can't seem to change the cell formats to translate numbers like "19800" into a usable time format like "1:30 PM" Any ideas about why this is happening? I'm using Office 97, BTW "keepITcool" wrote: yes you can. when you define parameters in your query excel can assign those parameters to cells. when you change the cell, the query will be updated. Paramaters.. In the Filter Data Page of the wizard.. Select : Field1 Include only rows where : Select Equals in 1st Dropdown Type 1 in 2nd dropdown. (difficult to enter the param in this box.. be patient.. Press next,next In the Finish Page.... Select View or Edit query in MSQuery Press finish in MSquery in will now say.. Criteria Field: Field1 Value : '1' edit the '1' to [MyParameter?] no quotes! in the popup it will ask for a value: enter a 1 Choose File/Return Data to Excel. Now you should see : a refedit to choose the destination.. a button to assign the Parameters to cells. search excel help for Customize a Parameter QUery. have fun! -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam simsjr wrote : That's very helpful. Now what if I wanted to have a date field where I can specify date criteria to retrieve certain records. Is there a way to make a cell value feed the query to this regard? "keepITcool" wrote: Via Data/Import external data you create a QueryTable. that querytable can be refreshed when you rightclick it and choose refresh from the popup. (alternatively you can put a button next to it to call the refresh method of the QueryTable object) -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam simsjr wrote : Hi Folks, I'm trying to figure out the easiest way to create a spreadsheet template with an "update" button on it. When clicked, this button should tell Excel to grab data from an external database and place it in specified columns. Furthermore, this Excel template should be able to apply conditional formatting to the new data if it meets certain criteria. Do I need to know VB for this, or can a macro do this work for me? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Click Button to Load External Data Into Spreadsheet
I have Excel 2007 & want to create a command button to "Refresh All" reports
in a workbook. Which command button would be best & can someone help me with the code? -- Thanks, Kevin "keepITcool" wrote: Via Data/Import external data you create a QueryTable. that querytable can be refreshed when you rightclick it and choose refresh from the popup. (alternatively you can put a button next to it to call the refresh method of the QueryTable object) -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam simsjr wrote : Hi Folks, I'm trying to figure out the easiest way to create a spreadsheet template with an "update" button on it. When clicked, this button should tell Excel to grab data from an external database and place it in specified columns. Furthermore, this Excel template should be able to apply conditional formatting to the new data if it meets certain criteria. Do I need to know VB for this, or can a macro do this work for me? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Click Button to Load External Data Into Spreadsheet
You can try this
Application.CommandBars.ExecuteMso ("RefreshAll") But why not add this button that is in the Data tab on the QAT -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "AFSSkier" wrote in message ... I have Excel 2007 & want to create a command button to "Refresh All" reports in a workbook. Which command button would be best & can someone help me with the code? -- Thanks, Kevin "keepITcool" wrote: Via Data/Import external data you create a QueryTable. that querytable can be refreshed when you rightclick it and choose refresh from the popup. (alternatively you can put a button next to it to call the refresh method of the QueryTable object) -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam simsjr wrote : Hi Folks, I'm trying to figure out the easiest way to create a spreadsheet template with an "update" button on it. When clicked, this button should tell Excel to grab data from an external database and place it in specified columns. Furthermore, this Excel template should be able to apply conditional formatting to the new data if it meets certain criteria. Do I need to know VB for this, or can a macro do this work for me? __________ Information from ESET Smart Security, version of virus signature database 3946 (20090318) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 3946 (20090318) __________ The message was checked by ESET Smart Security. http://www.eset.com |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Click Button to Load External Data Into Spreadsheet
Ron,
Thank you for your quick response. The code you provided is what I needed. The reason for the command button is primarily for the end user that does even know what their ribbon tools are for. Check click a button right in front of the face. Also, is it possible to create a form that pops up saying "Please wait while report queries are running". Then go away when their finished? -- Thanks, Kevin "Ron de Bruin" wrote: You can try this Application.CommandBars.ExecuteMso ("RefreshAll") But why not add this button that is in the Data tab on the QAT -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "AFSSkier" wrote in message ... I have Excel 2007 & want to create a command button to "Refresh All" reports in a workbook. Which command button would be best & can someone help me with the code? -- Thanks, Kevin "keepITcool" wrote: Via Data/Import external data you create a QueryTable. that querytable can be refreshed when you rightclick it and choose refresh from the popup. (alternatively you can put a button next to it to call the refresh method of the QueryTable object) -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam simsjr wrote : Hi Folks, I'm trying to figure out the easiest way to create a spreadsheet template with an "update" button on it. When clicked, this button should tell Excel to grab data from an external database and place it in specified columns. Furthermore, this Excel template should be able to apply conditional formatting to the new data if it meets certain criteria. Do I need to know VB for this, or can a macro do this work for me? __________ Information from ESET Smart Security, version of virus signature database 3946 (20090318) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 3946 (20090318) __________ The message was checked by ESET Smart Security. http://www.eset.com |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Click Button to Load External Data Into Spreadsheet
Hi AFSSkier
You can test if it will first finish the refresh before it run other code like this MsgBox "Start" Application.CommandBars.ExecuteMso ("RefreshAll") MsgBox "Ready" If this is true you can show a label or open a small userform -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "AFSSkier" wrote in message ... Ron, Thank you for your quick response. The code you provided is what I needed. The reason for the command button is primarily for the end user that does even know what their ribbon tools are for. Check click a button right in front of the face. Also, is it possible to create a form that pops up saying "Please wait while report queries are running". Then go away when their finished? -- Thanks, Kevin "Ron de Bruin" wrote: You can try this Application.CommandBars.ExecuteMso ("RefreshAll") But why not add this button that is in the Data tab on the QAT -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "AFSSkier" wrote in message ... I have Excel 2007 & want to create a command button to "Refresh All" reports in a workbook. Which command button would be best & can someone help me with the code? -- Thanks, Kevin "keepITcool" wrote: Via Data/Import external data you create a QueryTable. that querytable can be refreshed when you rightclick it and choose refresh from the popup. (alternatively you can put a button next to it to call the refresh method of the QueryTable object) -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam simsjr wrote : Hi Folks, I'm trying to figure out the easiest way to create a spreadsheet template with an "update" button on it. When clicked, this button should tell Excel to grab data from an external database and place it in specified columns. Furthermore, this Excel template should be able to apply conditional formatting to the new data if it meets certain criteria. Do I need to know VB for this, or can a macro do this work for me? __________ Information from ESET Smart Security, version of virus signature database 3946 (20090318) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 3946 (20090318) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 3946 (20090318) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 3946 (20090318) __________ The message was checked by ESET Smart Security. http://www.eset.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Load ws cell data into a textbox via click on chart datapoint | Charts and Charting in Excel | |||
when importing data from external database does not load into wor. | Excel Discussion (Misc queries) | |||
External lookups won't load | Excel Discussion (Misc queries) | |||
VBA Excel - Load Data button | Excel Programming | |||
VBA Excel - Load Data button | Excel Programming |