|
Hi,
Google for "assign array to excel range" and you'll find plenty of people have had the same problem. Apparently the source array, 'a' in your case, should be 2 dimensional.
Alan.
|
|
|
|
|
I swear I was all over google... but using VB.net in my search may have backfired. Better to look at examples of people using VBA writing macros, and then translating. Your suggestion led me to this page[^] which led me to the odd "WorksheetFunction" methods. Here's the new code:
Private Sub ILIHistogramSheet()
xlSheet = ReturnReferenceToNextSheet()
xlSheet.Name = "ILI histograms"
SupplyHeaders()
Dim rows As Integer = mData(0).Trials(0).ILIHistogram.Keys.Count
Dim row As Integer = 7
Dim r As Excel.Range
Dim col As Integer = 1
r = DirectCast(xlSheet.Cells(row, col), Excel.Range).Resize(rows, 1)
r.Value = xlApp.WorksheetFunction.Transpose(mData(0).Trials(0).ILIHistogram.Keys.ToArray)
col = 2
For Each ls As LickometrySession In mData
For Each lt As LickometrySession.LickometryTrial In ls.Trials
r = DirectCast(xlSheet.Cells(row, col), Excel.Range).Resize(rows, 1)
r.Value = xlApp.WorksheetFunction.Transpose(lt.ILIHistogram.Values.ToArray)
Next
col += 1
Next
r = DirectCast(xlSheet.Cells(7, 1), Excel.Range).Resize(rows - 1, 1)
r.Font.Bold = True
End Sub
The key statement is r.Value = xlApp.WorksheetFunction.Transpose(lt.ILIHistogram.Values.ToArray), where xlApp is a reference to an Excel.Application instance and ILIHistogram is the Dictionary(Of Integer, Integer) property that holds my data. The code above:
1. Gets an Excel.Range (r) in column 1 starting at row 7 and sized for the size of my array
2. Adds in one step all of my Keys
3. Starts a loop to circle through all of my custom objects holding all of my data
4. Gets a range each time one column wide and down as many rows as needed
5. Adds the data to the range in one step while transposing using the worksheet function
I'm using a relatively small dataset to test with, but already the advantage is huge. Before seeing your answer, I had coded it looping through my data, adding one cell at a time.
Old way = 233 milliseconds
New way = 32 milliseconds
Since I have 4 sheets to fill with data like this and will usually be dealing with much larger data sets, this is a nice save.
Thanks!
modified on Friday, July 1, 2011 7:55 PM
|
|
|
|
|
My webservice takes in a couple of parameters, some are integers, some are strings and some are self defined structs.
Now the integers' minOccurs and maxOccurs are set appropriately to 1. But with the strings, in both webmethods and structures, the minOccurs are set to 0. Is there a way to set them to 1 without setting <XmlElement(IsNullable:=True)>? I don't want them to be optional and I don't want them to be null-able.
EDIT - In one webmethod, one of the parameters is an array of a self defined structure. Is it possible to set the minOccurs number? I know it can't be done with <XmlElement(IsNullable:=True)> because that gives an error in my webservice.
If I look at the WSDL description of my structures, I see that the minOccurs of the string fields is set to 0. Is there a way to change that?
I can provide code samples if necessary.
modified on Thursday, June 30, 2011 6:16 AM
|
|
|
|
|
Hi
Working in MS Access VBA want to enter a number in textbox and return some text in another textbox.
For example when enter 1 want to return in another textbox Boston.
How to make this possible?
Regards
|
|
|
|
|
KORCARI wrote: Working in MS Access VBA want to enter a number in textbox and return some text in another textbox.
For example when enter 1 want to return in another textbox Boston.
How to make this possible?
Create a form with the two text boxes. Each text box has events associated with it. In the AfterUpdate event of the first text box, check for the value entered and accordingly fill value(s) in the second text box.
HTH
|
|
|
|
|
Would also suggest you use CASE, much easier to follow and edit.
------------------------------------
I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave
CCC Link[ ^]
Trolls[ ^]
|
|
|
|
|
I'm making an installer of my webservices and I'm stuck at the last bit.
I'm trying to change some values in a custom xml file that I'm installing. I don't save my settings in an web.config file, but in either public, protected or private.config.xml file. I want to change the $logPath$ variable in the document with the installation path. I've figured out that with custom code I can do:
MyBase.Install(stateSaver)
Dim xmlDocument As New System.Xml.XmlDocument
Dim strPath As String = System.IO.Path.Combine(Context.Parameters.Item("TARGETDIR"), "public.config.xml")
If System.IO.File.Exists(strPath) Then
xmlDocument.Load(strPath)
xmlDocument.ToString().Replace("$logPath$", Context.Parameters.Item("TARGETDIR"))
xmlDocument.Save(strPath)
End If
Rinse and repeat for the other files. But I get an error: "1001 - parameter path1 cannot be null". That's the first parameter of the System.IO.Path.Combine() function. But it's not null, there should be a path set.
modified on Monday, June 27, 2011 10:35 AM
|
|
|
|
|
|
Hi,
I'm rewriting a old VB6 prog into vb dotnet 2010
I need for mij cddb control the deviceID
In vb6 I get it with this code
MCICtl.DeviceType = "CDAudio"
MCICtl.Wait = True
MCICtl.filename = DriveLetter
MCICtl.Shareable = True
MCICtl.Enabled = True
MCICtl.Command = "Open"
Dim DevId
DevId = MCICtl.deviceId
TocString = Control.GetMediaToc(DevId)
Now in vbdotnet I haven't get it succesfully
the windowsmediaplayer works on other ways
Someone has can help me
I found this on the net and my idee was that SCSITargetId was the attrib that i need but no luck
Function fGetCDroms(ByVal drive As String) As Generic.List(Of Devices)
Dim WMI, Col, Ob As Object
Dim s2 As String = ""
Dim sID As String = ""
Dim l As New Generic.List(Of Devices)
Dim Toestel As New Devices
WMI = GetObject("WinMgmts:")
Col = WMI.ExecQuery("Select * from Win32_CDROMDrive")
For Each Ob In Col
Toestel = New Devices
With Toestel
.Availability = Ob.Availability
.Capabilities = Ob.Capabilities
.CapabilityDescriptions = Ob.CapabilityDescriptions
.Caption = Ob.Caption
.CompressionMethod = Ob.CompressionMethod
.ConfigManagerErrorCode = Ob.ConfigManagerErrorCode
.ConfigManagerUserConfig = Ob.ConfigManagerUserConfig
.CreationClassName = Ob.CreationClassName
.DefaultBlockSize = Ob.DefaultBlockSize
.DeviceID = Ob.DeviceID
.Description = Ob.description
.Drive = Ob.Drive
.DriveIntegrity = Ob.DriveIntegrity
.ErrorCleared = Ob.ErrorCleared
.ErrorDescription = Ob.ErrorDescription
.ErrorMethodology = Ob.ErrorMethodology
.FileSystemFlags = Ob.FileSystemFlags
.FileSystemFlagsEx = Ob.FileSystemFlagsEx
.InstallDate = Ob.InstallDate
.id = Ob.id
.LastErrorCode = Ob.LastErrorCode
.manufacturer = Ob.Manufacturer
.MaxBlockSize = Ob.MaxBlockSize
.MaximumComponentLength = Ob.MaximumComponentLength
.MaxMediaSize = Ob.MaxMediaSize
.MediaLoaded = Ob.MediaLoaded
.MediaType = Ob.MediaType
.MinBlockSize = Ob.MinBlockSize
.Name = Ob.Name
.NeedsCleaning = Ob.NeedsCleaning
.NumberOfMediaSupported = Ob.NumberOfMediaSupported
.PNPDeviceID = Ob.PNPDeviceID
.PowerManagementCapabilities = Ob.PowerManagementCapabilities
.PowerManagementSupported = Ob.PowerManagementSupported
.RevisionLevel = Ob.RevisionLevel
.SCSIBus = Ob.SCSIBus
.SCSILogicalUnit = Ob.SCSILogicalUnit
.SCSIPort = Ob.SCSIPort
.SCSITargetId = Ob.SCSITargetId
.Size = Ob.Size
.Status = Ob.Status
.StatusInfo = Ob.StatusInfo
.SystemCreationClassName = Ob.SystemCreationClassName
.SystemName = Ob.SystemName
.TransferRate = Ob.TransferRate
.VolumeName = Ob.VolumeName
.VolumeSerialNumber = Ob.VolumeSerialNumber
End With
l.Add(Toestel)
Next
Col = Nothing
WMI = Nothing
Return l
End Function
|
|
|
|
|
Never mind,
It was the players ID that I needed not that for the cdromplayer
Jan
|
|
|
|
|
I'm hesitant to ask this question since I may not understand the answer. My VB.NET (2008) skills are weak and my use of LINQ has so far been non-existent. I believe that I could get the result I want using other methods than LINQ, but this may be an opportunity for me to learn it a bit.
I have a List(Of Integer) that holds a bunch of numbers that represent durations of events in milliseconds. I need to generate a frequency table from these durations. For example, the list (usually containing about 500 elements) might be:
67, 85, 110, 112, 83, 130, 99, 105
And I might want a frequency table that looks something like this:
Duration --> Count
61-80 --> 1
81-100 --> 3
101-120 --> 3
121-140 --> 1
Here, the BinSize is 20, but this would be a user-specified value, so I don't know what my "bins" are at design-time. Also, the MaxDur might be 140 in this example, but that is also a user-specified value, so I don't know how many bins there will be at design-time. (The minimum will always be 1, so there should also be bins above for 1-20, 21-40, and 41-60 which have counts of 0).
I think it would be most useful to store the results in a Dictionary(Of Integer, Integer) where the key is the upper limit of the bin (20, 40, 60, 80, 100, 120, and 140 in this example) and the value is the counts.
Consider the following code (I'm writing off of the top of my head here):
Sub TheCaller()
'Assume we have a exampleData = List(Of Integer) with the above 8 elements
Dim count As Integer = GetFrequencies(exampleData)
End Sub
Function GetFrequencies(data As List(Of Integer)) As Integer
Return (From i In data Where i>60 And i<=80).Count()
End Function
I'm not sure I'm right, but would this return the frequency count of bin 61 to 80 milliseconds? I presume I could make 61 and 80 variables and send those as parameters to the function, but what I really want to learn how to do is generate a single function that returns a Dictionary(Of Integer, Integer) with the entire table generated. I'm thinking this requires a Group By clause. I've seen examples out there of frequency tables for a known category (e.g., count the number of orders from a particular company or the number of times a particular word appears), but I'm struggling with the fact that the number and size of my categories would be unknown.
Thanks in advance for any leads.
EDIT: In fact, I think I've got a good compromise working now. I have the caller running a Do... Loop Until iteration, retrieving the Count each time from the GetFrequencies function, which is called in the Add method of a Dictionary(Of Integer, Integer) instance. It's working well.
However, I'm still intrigued by the method ToDictionary of Enumerable. I think there is probably a more efficient (or at least concisely written) way to do this, but I'm not sure.
modified on Saturday, June 25, 2011 3:07 PM
|
|
|
|
|
How do I add a reference at runtime ?
thanks!
|
|
|
|
|
You can't; if you want something from an assembly without referencing it, you'll have to load it dynamic. You'd have to dig into the subject called "Reflection" to use the classes in that assembly.
Bastard Programmer from Hell 
|
|
|
|
|
Hi all,
We are working on a financial project which uses excel sheets extensively.
Instead of entering values into an excel sheet, we thought of using Visual Basic to create a front end form to input values(basically numerical values) which will get stored in specific cells of a excel sheet..
we wish to keep the excel sheets as a backend datasource and just have a front end form ..
using these inputted values .. the results( charts ) will get generated as it is getting generated currently .. at a later stage we also wish to plot the charts on VB forms ..
we are unsure as to which versions of VB and excel are compatible .. what drivers to use .. and how to proceed on this task ..
I hope i was able to put across my project details .. if any doubts please let me know..
Please guide,
Thanks..
Gauti..
|
|
|
|
|
Consider this ...
How about storing the data in a database like SQL-Server and use Excel for your charts and graphs ? Excel can access SQL datasources and you won't be tied so tightly.
Keep your data in a database and use whatever presentation / reporting system you like.
Keep these issues in mind:
1) backup
2) sharing data accross multiple users
3) performance / scalability
Take some time to consider this approach.
Good luck. 
|
|
|
|
|
Thank you David.. this is great and exciting ..
We wish to keep excel sheets completely in the background.. i.e. we don't want to keep these excel sheets open while this application is running .. will the values stored in the database be automatically updated in the excel sheets ..??
I connected excel sheet and mysql database using the data->connection option .. but i am not sure if the values will get automatically updated without the excel sheets been kept open ..
Please suggest..
Thanks,
Gauti
|
|
|
|
|
If I understand you correctly, you will need to perform something like "Refresh Data" to keep the Excel document current with the most recent data.
You might be able to configure Excel to get a fresh copy of the data each time the sheet is opened.
|
|
|
|
|
thank you david..
yes .. that setting can be made .. but while running the application we don't want to keep the excel sheets open at all ..
we were thinking if some executable be made (windows forms application) in which a user enters numerical values and these values gets stored at the desired cells of the sheet .. which is stored at location say 'C:\FinExcel.xls' .. the results that are generated because of the user inputted values are later pulled out and showed on another form in the form of charts
we are just beginning to understand these languages/software .. We may be over enthusiastic for the whole process but we hope that you would understand us. ..
thanks
gauti..
|
|
|
|
|
Unless you have a completely ancient version of Excel, they all allow this sort of thing.
Certainly 2003 onwards can handle.
Check here[^]
------------------------------------
I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave
CCC Link[ ^]
Trolls[ ^]
|
|
|
|
|
Thank you Dalek .. that was a good note ..
|
|
|
|
|
Hi,
we made a small Windows application to input values into specific cells of our financial excel sheet ..
we used Visual Studio 2008 and MS Excel 2003 version
Now our task is to to plot the results in the form of charts on VB forms when we click on a 'Result button'
Please guide as to how this can be accomplished ..
Thanks,
Gauti.
|
|
|
|
|
Weaning financial people off Excel is bloody impossible, dammed thing is too useful to them.
Take a look at SpreasSheetGear, there should be a link to them from this site, they are advertisers here. SSG will allow you to embed an excel like form into your app, the cells can be mapped to a data table and the UI is very excel like.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
thanks for sharing the information Mycroft ..!!
|
|
|
|
|
As one of them there 'Financial People', I concur.
There is nothing better available, and it can do just about everything I need, automating a lot of it.
------------------------------------
I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave
CCC Link[ ^]
Trolls[ ^]
|
|
|
|
|
I am a hobbyist programmer, and this may be a newbie question that has been asked before, but I was unsuccessful searching through the forums.
I am writing a data analysis program that reads disk files and parses the information. Each file is one "session" and each session contains many "trials". Trials are identified by a number of items (trial length, trial stimulus, etc.) and also holds data which are a bunch of integers.
So, I am building a trial class that has a number of properties, but one of those properties will be this array of integers (of unknown number). I'm wondering if I should represent that in the class as a simple array which heavily makes use of ReDim statements as the numbers are read and added to the array, or if I should instead represent them as a List(Of Integer) using the Add method to grow the collection.
This is a philosophical question, not a coding question. I'm sure I will be able to code it either way. What I don't know is whether one or the other is preferable considering performance or other issues. I understand that using a collection would be a bad idea because of boxing/unboxing issues, but I was given to believe List(Of T) does not suffer this penalty.
If List(Of Integer) is preferable, would you go on to say that one should always use generics and that arrays of values are always less preferable? Or perhaps a List(Of Integer) and an array of integers are the same under the hood?
Thanks.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
