.IQY loop in VBA?
I am using Excel 2000 and XP. I have just learnt how to use an .iqy
file to extract data from the web. To really benefit from automation
I want to create a loop using slightly different web adresses each
time. I have recorded the query as a macro and get:
.Connection = "URL;http://www.skyscanner.net/gbp/flights/ABZ/
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = Fa...Option Box radio button wont select
I'm must be missing something very simple but...
I have an option box in the footer of a continous form. I'll use the option
box to set the filter property on the continous form. The problem is the
first radio buttton in the group is selected when the form opens and I'm not
able to select any of the options.
The option group is unbound, nothing in the Control source.
Also if I remove the default value of 1 I am not able to select any radio
buttons in the group.
I would create a second option group next to the 1st, make it just basic, ie
don't go to fancy ...Is it possible to create and publish Outlook Custom Form through VBA
I have created COM-Addin for Outlook in VB. But there are lot of
limitations in VBScript like to handle all events(other than click
event) of controls(ListView,Textbox) on outlook custom form. Can we
handle these events using VBA so that i can distribute that
application? Or Is it possible to create and publish that form through
Why don't you ask "Sue Mosher's" Forum - www.outlookcode.com - dedicated to
OL add in programmers.
Hope this helps you more
> Hi All,
> I have created C...Select top 10 in a list
I'm looking for a way to search a range in a list and pick the top 10
from that list. Once this has been done, I then need to copy the data
to another sheet elsewhere
For example, I have a sheet called "MainList" (http://
joemaldon.googlepages.com/), I would like to pick the top 10 from the
totals colum and then take each entry and copy into another sheet
called "Top10". So, copy ID, Name and Total to sheet called "Top10".
Also then add rank positions including the joint places.
Can anyone please offer a solution how this can be done please?
Note there...How can I minimalize the difference between extreme values on a c.
I'm making a chart with two values for class. One of the values is very
small (3) and the other is very large (317). How can I make the axis values
"break" so to speak, so that the difference between them is minimalized. For
example, I need a portion that goes from 1-7 and another that goes from
300-700. I can't put them on different charts.
I describe one technique on this page, and include links to others:
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutio...sort and select based on criteria
I have a spreadsheet with the following data, what I would like to do is:
1. sort this in descending order by number of sales (got the macro doing this)
2. Determine the total sales ( a simple SUM in an adjacent cell. (done)
3. Calculate what 90% of total sales are. ( done in another adjacent cell)
4. Determine which parts make up the 90% of sales. (HELP)
5. Graph only the 90% parts. Graph is already created but how do I capture
the 90% dataset. (HELP)
The idea is to take the entire dataset, sort by number of sales and then
graph only those parts that make up 90% of the sales.
...Show all fields from table in select query ?
I have 3 tables - INVENTORY, IN and OUT
I made a select query to calculate the quantity of inventory on stock, but I
only see the inventory items that are in both IN or OUT tables.
If I select to show all fields from Inventory and only those related from
other tables a get null values.
Is there a way to have a field showing 0 (zero) instead of empty field ?
You can use an outer join instead of an inner join (the default.)
The Query Lost My Records! (Nulls)
A better solution might be to combine the IN and OUT into one...How do I lookup several values and concatenate the results?
I have data that comes out of the system of record like this.
Item # Description
3456 With Buttons
How do I combine the variable number of descriptions into one field for each
item #? Each item doesn't have the same amount of descriptions.
I want the output to be like this.
Item # Description
3456 "Gizmo,Red,Large,Square,With Buttons"
You could use a macro.
I'm guessing that you reall...negative time values
not to worry, have found the answer but thanks anyway
...Total columns Based on another cells value
I trying to use VBA to calculate totals in multiple columns when it see
uniques values in another Column..in the example below column "A" contains
user names while columns "B, C, D" contain the values I want totaled based
on the user name in column "A"...An additional thing would be have it place
the values with the user name on a new worksheet.
Col A Col B Col C Col D
John Smith 1.0 1.0 1.0
John Smith 1.25 1.25 2.0
Mary Johnson .75 1.0 2.5
Jack Johnson ...OL2003: Selecting Multiple "Blocked Senders"?
In Outlook 2000, I was able to Ctrl-click multiple messages, then click on "Junk
Senders List" and all the senders would be added to my spammers file. Under
OL2003, I can't figure out if it's possible to designate more than one message
at a time to be added to the Blocked Senders list. Is it? If so, how?
...Excel 2003: In a Macro,how to select a variable row length table
I wish to create a macro that will perform functions upon multiple rows of
data. The number of rows of data will change every time.
Problem : I create a macro by recording. I select rows of data using
CTRL/down arrow on the last column and then selecting back to the first
column of the first data row. This appears to 'hard code' the number of rows
of data to be the number of rows that exists at the time of macro creation.
When I run the macro another time it then selects the same number of rows as
when the macro was created, which will then not match the new current data.
i...How move a picture by code (VBA)?
Hi, I want to move a picture to a position, say, ColumnB and Row2 (or Cell
B2) with its up-left coner the same as the cell. And I need resize the
picture to fit in cell(B2:C3). What the VBA code to do this? I tried using
record Macro, but the code is not easy to use.
The following code will move picture 1 to fit over cells B2:C3 --
Dim ws As Worksheet
Dim rng As Range
Set ws = Sheets("Sheet1")
Set rng = ws.Range("B2:C3")
With ws.Shapes("Picture 1")
.LockAspectRatio = msoFalse
.Top = rng.Top
.Left = rng.L...faq: How to obtain the select plain text?
Just like the notepade.exe in windows, I type some plain text
and select it using mouse with left button pressed, and copy it to
the clipboard by select copy command in the menu Edit.
How to do that?
Thanks very much.
Also, take a look at CEdit::Copy() CEdit::Paste() CEdit::Cut(). They should
do what you'd like.
"fcvcnet" <email@example.com> wrote in message
news:fhb5as$ghh$1@ne...VBA return all objects
I am trying to use an if...then statement to show a
message box. I want to say "if the chart legend contains
this legend entry, then show MessageBox X." What I need
is a way to include all legend entries in my if...then
statement, instead of referencing each one seperately. In
the example below, instead of "...LegendEntries
(1).Font..." I want something like "...LegendEntries
(all).Font..." Is there a way to do this? Many thanks!!
.Legend.LegendEntries(1).Font.Italic = True
How about using some...Selecting a namespace-prefixed node
I am pretty much an xml beginner; hopefully someone can easily answer this one... I want to select a node representing a worksheet in an xml document for the Office Web Components spreadsheet. The file uses xml namespaces, of which I know very little. But it does seem clear that I can't select the <ss:Worksheet> element by name only; the xpath "//Worksheet" does not match any nodes
Extract from the file, in case this is needed to come up with the solution
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"><!-- ... there's more, but...Using Functions in Queries/Assigning Values to Non-numerical Entri
I would like to sum values across multiples fields but the values in the
fields I need are letters. So, is it possible to assign a value to each
letter and then take the sum?
For example, if this was an attendance record.
Week 1 = A
Week 2 = P
Week 3 = P
Week 4 = TA
And I would want to count the number of Absences (A) for these four weeks so
I would need to assign A=1, P=0, and TA=.5 (since 2 Tardies = 1 Absence).
Can I do this? And how?
How many values do you have? And how is the data structured?
If you have many values, I would build a table of equivalence.
Lette...in VBA Sheets("mysheet").Copy Before:=Sheets(1) how do i get a reference to the newly created copy of this sheet?
in VBA Sheets("mysheet").Copy Before:=Sheets(1) how do i get a reference to
the newly created copy of this sheet?
Try something like:
Dim WB As Workbook
Dim WS As Worksheet
Set WB = ActiveWorkbook
Set WS = ActiveSheet
"Daniel" <firstname.lastname@example.org> wrote in message
> in VBA Sheets("mysheet").Copy Before:=Sheets(1) how do i get a reference
> th...Uniqueness only for some attribute value
I want to add uniqueness constraint for an attribute, but only for a
certain value, for example, the attribute can be 'true' or 'false' or
none, I want to allow only one 'true' value. How should I do? I tried
to use like this:
<xs:selector xpath="./MyElement[@myAttribute = 'true']"/>
but this gives validation error in xsd.
Any help is appreciated.
> I want to add uniqueness constraint for an attribute...Selecting unique record from duplicate
Hi, please help
I've just run a 'find duplicates' query on a table. Now I'm trying to select
a single unique record from each duplicate. One of my fields is called
'OBSERVER' - this contains either a persons name OR 'unknown' if the persons
name is not known. If one of the duplicate records contains a persons name
and the other duplicate is 'unknown', how do I select the record with the
persons name? If both duplicates contain 'unknown' or both contain a name, i
don't mind which record gets selected. See example below...
In crm 3.0, we can choose different fields in different entities for
some lookup objects. For example, we can choose either contact or
account for parent customer field in contact form. In the same way,
for recipient field in a phone call activity, we can choose lead,
contact, user or account.
My problem is that i dont want to see some entities for lookup
objects. For example, i just want to choose contact or lead records
for recipient field, and i dont want to see the others(account or
How can i do this? Thanks for all suggestions.
Michael Hoehne has a script you can add...Equal list values.
I have a list of names in column A with numerical values in column B
Is it possible to equally split the list into 2 columns so that the number
totals (B) are the same (or as near as)?
Sort the list by ColB and assign subsequent rows to each set.
Jacob (MVP - Excel)
> I have a list of names in column A with numerical values in column B
> A B
> Alan 1234
> Darren ...Assigning Ascii value to CString
I need to send an ascii value (specifically ascii(26) - Ctrl+z) to a
CString in order to send it as a command through a serial port.
Could someone please tell me how I can do this?
Steph Sharp wrote:
> I need to send an ascii value (specifically ascii(26) - Ctrl+z) to a
> CString in order to send it as a command through a serial port.
> Could someone please tell me how I can do this?
Scott McPhillips [VC++ MVP]
CString str((TCHAR)('Z'-'@')) or
CString str(&q...Operation of Asian text font selection in the Font dialog
I have a document that has been translated into Japanese, in which all of the
translated text appears in MS Gothic font. This displays most of the
characters correctly, but not all. Apparently, they are correct if the font
is changed to MS PMincho.
I'm trying to do this globally by changing the Font settings in the various
styles, but no matter what I set in the Asian Text Font and Font boxes, the
Japanese text remains stuck on MS Gothic, although any latin text changes to
I can apply PMincho as a character style, but that's tedious.
How do I get the required ...how to invoke select names form
i want to invoke the form(Select Names form that will be opened when
we click the To button in the new mail message) programatically in my
form ... how can i do this ...
I don't think you can as it is not a form but a cache of addresses =
previously used in the TO: field - however, you can always ask in a =
programming group "down the hall."
Milly Staples [MVP - Outlook]
Post all replies to the group to keep the discussion intact. All
unsolicited mail sent to my personal account will be deleted without
After furious head scratching, Malli asked...