Click Button to Load External Data Into Spreadsheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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 :
 
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 :
 
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 said:
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?
Click to expand...
Click to expand...
 
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 :
 
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).

Beautiful. That's exactly what I needed to know.

Except, once Excel populates with the data, I can't seem to change the cell
Click to expand...
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 said:
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?


:


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?
Click to expand...
Click to expand...
Click to expand...
 
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?
 
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 said:
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




__________ Information from ESET Smart Security, version of virus signature database 3946 (20090318) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 3946 (20090318) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
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?
 
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 said:
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




__________ Information from ESET Smart Security, version of virus signature database 3946 (20090318) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 3946 (20090318) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
Back
Top