Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parameter Query executed from a Macro
Hi,
I am trying to get a Macro, to execute a parameter query, that will prompt a user to enter a variable. Every time that I try to record my macro, my session abends. My query works perfectly, when i execute it independent of my macro. Can I do this in Excel. I know this is easy in Access. Thank You very much |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parameter Query executed from a Macro
Hello,
One of my kind colleagues in this forum taught me how to hide an unhide columns using a parameter query (run by a macro) by creatin VBA. See the text in quotes below right after "InputBox". Sub hidecolumns() Columns("d:en").Hidden = False x = InputBox("Enter 1 for DHS Recommendation. Enter 2 for PROJEC Recommendation Enter 3 for FINAL Recommendation Enter 4 for Project/DH Matches") For Each c In Range("d1:en1") If Right(c, 1) < x Then c.EntireColumn.Hidden = True Next End Sub However, I have another question about this please. I would like to be able to have the Enter 1, Enter2 text etc. eac appear on a new line so that they are not jumbled up. See attached fil and run the "Show some detail" macro. I suppose there is sme way t insert a"new line" or "carriage return", but since I don't know VBA, would appreciate any help. Thx. lindas Attachment filename: calwin fs training analysis 4-19-04-forum.xl Download attachment: http://www.excelforum.com/attachment.php?postid=51725 -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parameter Query executed from a Macro
lindasf
It will be easier to manage if you put your prompt in a variable. Then use vbNewLine to insert a carriage return. Dim sPrompt As String sPrompt = "Enter 1" & vbNewLine & "Enter2" & vbNewLine & "Enter3" x=InputBox(sPrompt) -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "lindasf " wrote in message ... Hello, One of my kind colleagues in this forum taught me how to hide and unhide columns using a parameter query (run by a macro) by creating VBA. See the text in quotes below right after "InputBox". Sub hidecolumns() Columns("d:en").Hidden = False x = InputBox("Enter 1 for DHS Recommendation. Enter 2 for PROJECT Recommendation Enter 3 for FINAL Recommendation Enter 4 for Project/DHS Matches") For Each c In Range("d1:en1") If Right(c, 1) < x Then c.EntireColumn.Hidden = True Next End Sub However, I have another question about this please. I would like to be able to have the Enter 1, Enter2 text etc. each appear on a new line so that they are not jumbled up. See attached file and run the "Show some detail" macro. I suppose there is sme way to insert a"new line" or "carriage return", but since I don't know VBA, I would appreciate any help. Thx. lindasf Attachment filename: calwin fs training analysis 4-19-04-forum.xls Download attachment: http://www.excelforum.com/attachment.php?postid=517255 --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parameter Query executed from a Macro
David
It sounds like you already have a query in a worksheet. Is it a parameter query? If so, you only need to refresh it to get a prompt with code like Sheet1.QueryTables(1).Refresh If it's not a parameter query, you can either make it one, or prompt the user for a value using InputBox and adjust the SQL to reflect that new value. See here http://www.dicks-clicks.com/excel/Ex....htm#ChangeSQL and here http://www.dicks-clicks.com/excel/Ex...htm#Parameters -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "David" wrote in message ... Hi, I am trying to get a Macro, to execute a parameter query, that will prompt a user to enter a variable. Every time that I try to record my macro, my session abends. My query works perfectly, when i execute it independent of my macro. Can I do this in Excel. I know this is easy in Access. Thank You very much |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parameter Query executed from a Macro
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro get executed twice | Excel Discussion (Misc queries) | |||
Convert hard coded query criteria to Parameter Query | Excel Discussion (Misc queries) | |||
How to choose if I use a parameter or not in a parameter query | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) | |||
Macro not executed Excel -- PowerPoint | Excel Programming |