How to enter and change the connection string and command text of a Querytable in VBA?
I know that for pivot table we can create a variable for the command text and connection then use activeworkbooks.pivotcache.connection/commandtext equal those variables. Is there a way to do the same thing with Querytable?
I tried to do something like
activeworkbooks.worksheets(1).querytables(1).connection, but it did not work. Also, I have two different connections for two different querytables within the same workbook.
See also questions close to this topic
IF Statement with Nested Formula
I am having a devil of a time trying to figure out how to use an IF-statement where I have a nested formula. What I am trying to do is - if a number (B37) is positive extract 2% of its value in the assigned cell and if it's negative I need to assign a value of zero to the cell (B39).
I have included a screengrab of that portion of the spreadsheet. Please let me know what you think - or even if there is a better way that I haven't thought of. I have read other examples, and can't figure this out. I appreciate your insights and time
Excel - WorksheetFunction.Average returns incorrect answer
Given the data in B2:D10 I wanted to find the average in each row use VBA array formula. Following a book example VBA Queue by Bill Jelen.
Name A B C Answer Manual Avg A2 4 34 59 31.5 32.33333333 A3 45 83 74 59.5 67.33333333 A4 98 40 17 57.5 51.66666667 A5 95 8 83 89 62 A6 57 55 98 77.5 70 A7 14 21 17 15.5 17.33333333 A8 55 23 4 29.5 27.33333333 A9 80 15 77 78.5 57.33333333 A10 11 24 40 25.5 25
Answer in Column E is from this formula
Sub QuickFillAverage() Dim myArray As Variant Dim myCount As Integer myArray = Worksheets("Sheet1").Range("B2:D10") For myCount = LBound(myArray) To UBound(myArray) Worksheets("Sheet1").Cells(myCount + 1, 5).Value = _ WorksheetFunction.Average(myArray(myCount, 1), myArray(myCount, 3)) Next myCount End Sub
The answer in Column F is from
(B2+C2+D2) / 3
Done for each row to find the average. As you can see the answers are different. Why?
Excel VBA hard crash on ModifyAppliesToRange with no error message
I'm getting a strange problem in Excel and VBA.
FormatCondition, along with quite a lot of other
It works fine for a while (about 2-300 calls) and then for no reason I can see my Excel (and the VBA development environment) will shut down with no error from either my error handler or excel, no notification, nothing except a 'pause' where nothing happens until it crashes. This happens whether I'm running it normally or with a breakpoint and Step.
Depending on the exact contents of the WorkSheet's
FormatConditionsthe exact number of times it works seems to vary. But if the starting condition is the same then the point of failure is the same (i.e. it fails on the same
FormatCondition). Code failing is:
myCF.ModifyAppliesToRange Union(range1, range2)
myCF As FormatCondition
Dim rng As Range Set rng = Union(range1, range2) myCF.ModifyAppliesToRange rng
rngare all valid as far as I can tell, and show valid
.Addressstrings (for the ranges). For example, it will fail on
"$CI$9:$DN$39", and the resulting
So I tried
Set rng = Union(Range(CFFrom.AppliesTo.Address), Range(CFTo.AppliesTo.Address))
On occasion (under different start conditions) it will get past the above
ModifyAppliesToRangebut the moment I look at
myCF.AppliesTo.Addressthe same crash happens - whether hovering a mouse over it, or showing it in Watches, or displaying it in Immediate. Yet
rng.Addressgives no such problem.
Yet it also seems to work just fine for 2-300 times.
I suspect a memory problem (but Excel is not using much memory or CPU, there's plenty of empty memory on the computer, I've used Option Explicit, and so on) or a hard limit or bug in Excel
Any ideas as to why this might be happening? Any ideas as to how I can find out what is shutting down Excel?
NOTE: To give the exact way to reproduce it I'd have to post my entire spreadsheet and code, which I'm reluctant to do. Still I might be able to send one-on-one if need be.
Copy rows by value
I have a list of room in column a. In column b, I have the number of room. I need to create a list where the room appears as many times in the list as the value of the number:
Room A 4
Room B 2
Room C 3
Room A (2)
How can I do it in excel? Thank you