Export Array into Excel

[dvj357]

Greetings all,

I am currently learning visual basic .net on the fly for a project at work and I am having some issues. I have learned most of what (little) I know from posts on these forums, but I cant seem to find any help for my particular problem.

I have arrays of doubles from reading data files, and all i want to do is export the arrays to excel. I am currently looping through each cell, but it is taking quite a long time with sizes of the arrays.

I have attempted the method outlined by microsoft (How to transfer data to an Excel workbook by using Visual Basic .NET) but I cant get it to work. From what I can tell, the command to export the array is :
'Transfer the array to the worksheet starting at cell A2.
oSheet.Range("A2").Resize(100, 3).Value = DataArray

but i am doing something wrong because it isnt working.

One thought I have is that the data array on that page is defined as an object, and I define mine as an array of doubles. I tried to dimension the array to an object, but got the same results.

I have exhausted my google skills searching for help, but have only come across variations on the microsoft method above and large programs that I cant understand.

any thoughts anyone may have with what I am doing wrong would be greatly appreciated. below is the chunk of code I tried without success.

Thanks!


Dim dblPhiPelvis() As Object 'I get the values in this array from external file
'I know the array is built properly because i can export into excel with a basic for loop

''start a new excel sheet called Pelvis
objSheets = objBook.Worksheets
objSheet = objSheets(2)
objSheet.Name = "Pelvis" 'one chart for all ATD's?

'populate spreadsheet with Time, Force(Phi), Force above threshold,

objSheet.Range("D4").Resize(100, 1).Value = dblPhiPelvis

 

[JohnH]

I tried the code and it is working. How it is not "working" for you?

 

[dvj357]

Thank you for your reply!

when i run the code returns an array of "0" in all the cells I populate in this method. this snippet of code doesnt include the other arrays i am sending to excel, and those come through fine. the one array i use this method for gives me an array of zeros.

is it possible that my for loop may be messing w/ it? I will go back and start with a single array to pass and work from there. this is part of a big program (to me at least), so maybe something else in there is messing with the function. At this point I am completely guessing... That is why i stopped in and asked for advice.

any other trouble shooting tips or thoughts on this issue would be greatly appreciated.

again, thanks for taking the time to help!

 

[JohnH]

One thought I have is that the data array on that page is defined as an object, and I define mine as an array of doubles.

You can declare the array as Double, it don't have to be 'Object'.

is it possible that my for loop may be messing w/ it?

Quite possible, you should place a breakpoint and use the debugger to see what values are in the array. Just mouse hover the array variable and click + to expand and see the values.

the other arrays i am sending to excel, and those come through fine

Which should also indicate that it is not the way you're sending data to Excel that is the problem, but rather the data in that specific case.

 

[dvj357]

JohnH-
Thank you again for taking the time to help me with this issue.

I think I figured out what is happening- only the last value in the array is being exported. The final value is "0", so it is exporting "0" for all the cells I call out.

My full "for loop" method is below, but it takes quite a while:
For intCounter7Pelvis = 1 To intCountTimeSteps_nodout
objSheet.Cells(intCounter7Pelvis + 3, 3) = dblTime1_nodout(intCounter7Pelvis - 1)
objSheet.Cells(intCounter7Pelvis + 3, 4) = dblPhiPelvis(intCounter7Pelvis - 1)
objSheet.Cells(intCounter7Pelvis + 3, 5) = strThresholdPelvis(intCounter7Pelvis - 1)
objSheet.Cells(intCounter7Pelvis + 3, 6) = strThresholdTimePelvis(intCounter7Pelvis - 1)
objSheet.Cells(intCounter7Pelvis + 3, 7) = strDeltaTime2Pelvis(intCounter7Pelvis - 1)
Next intCounter7Pelvis

Like I said, the above code works, but takes a while. Below is what I have coded in an attempt at using the command I found:
objSheet.Range("C4").Resize(intCountTimeSteps_nodout, 1).Value = dblTime1_nodout
objSheet.Range("D4").Resize(intCountTimeSteps_nodout, 1).Value = dblPhiPelvis
objSheet.Range("E4").Resize(intCountTimeSteps_nodout, 1).Value = strThresholdPelvis
objSheet.Range("F4").Resize(intCountTimeSteps_nodout, 1).Value = strThresholdTimePelvis
objSheet.Range("G4").Resize(intCountTimeSteps_nodout, 1).Value = strDeltaTime2Pelvis

The above code returns the final value of the array in all the cells I call out.

Again, thank you for your time, and I apologize for my lack of comprehension...