Page 1 of 2

visual basic problem...

Posted: Sun Jul 13, 2008 11:56 pm
by heebyjeebys
I got a VB problem:

I got a program that generates some numbers (their not random) and i want them to be exported to excell:

This is the code that I have for starting excell:

Code: Select all

   Private Sub exporttoexcell()
        Dim excell As Process = Process.Start("C:\Program Files\Microsoft Office\Office12\EXCEL.EXE")
        excell.Start()
       
    End Sub

(that is obviously called from the button_click event)
I want the numbers here:

Code: Select all


f(x) =  |      x =    
-10   |       382
-9     |       308
-8     |       242
-7     |       184
-6     |       134
-5     |       92
-4     |       58
-3     |       32
-2     |      14
-1     |       4
0      |       2
1      |       8
2      |       22
3      |       44
4      |       74
5      |       112
6      |       158
7      |       212
8      |       274
9      |       344
10    |       422

I was thinking of a macro in the excell workbook something along the lines of this:

Code: Select all

sub insertvalues()
dim y as intger = {how many y's there are from program}
dim x as intger = {how many x's there are from program}
dim xvar (x) as double 
dim yvar (y) as double
for i as intger = 0 to y 
yvar(i) = {gets value[index] from program}
next
for i as intger = 0 to x
xvar(i)={gets value[index] from program}
next
'then insert into program
for i as intger = 0 to x '{x = y}
sheet1.cells(i,a) = yvar(i)
sheet1.cells(i,b) = xvar(i)
next
end sub


ok anyone any ideas?

Re: visual basic problem...

Posted: Wed Jul 23, 2008 1:55 pm
by Majik
Sorry for the late response, have been busy lately.

This might work for you, you are going to have to mod it based on number gen code but you should be able to follow it. This was written in textedit on my mac, so theres no way I could test it (I don't have the tools on my windows VM) but I wrote something like this for exporting data from Active Directory.. so it might work.

Code: Select all

Dim ObjWb 
Dim ObjExcel 
Dim x, zz 

Call ExcelSetup("Sheet1")
x = 1 

On Error Resume Next 

For Each Number In OtherNumber

x = x +1

  objwb.Cells(x, 1).Value = Number 
  objwb.Cells(x, 2).Value = Other Number

Next 

Sub ExcelSetup(shtName) 

Set objExcel = CreateObject("Excel.Application") 
Set objwb = objExcel.Workbooks.Add 
Set objwb = objExcel.ActiveWorkbook.Worksheets(shtName) 

Objwb.Name = "Numbers and Stuff"
objwb.Activate 
objExcel.Visible = True 
objwb.Cells(1, 1).Value = "Number" 
objwb.Cells(1, 2).Value = "Other Number" 

End Sub 
Hope it helps

Re: visual basic problem...

Posted: Wed Jul 23, 2008 7:00 pm
by heebyjeebys
thanks but i need some code to get the values into excell as well .. any ideas?

Re: visual basic problem...

Posted: Wed Jul 23, 2008 8:02 pm
by Majik
This part does that:

Code: Select all

For Each Number In OtherNumber

x = x +1

  objwb.Cells(x, 1).Value = Number
  objwb.Cells(x, 2).Value = Other Number

Next
You just need to write the code to go through.

Somehow you need to populate the Number and Other Number as values, how are you generating the numbers in the first place?

Re: visual basic problem...

Posted: Wed Jul 23, 2008 8:23 pm
by heebyjeebys
Majik wrote:This part does that:

Code: Select all

For Each Number In OtherNumber

x = x +1

  objwb.Cells(x, 1).Value = Number
  objwb.Cells(x, 2).Value = Other Number

Next
You just need to write the code to go through.

Somehow you need to populate the Number and Other Number as values, how are you generating the numbers in the first place?

there is some number generating code:

Code: Select all

 Private Sub calculatetablex()
        Dim x As Double
        Dim y As Double
        For i As Double = QuadraticTable.startval To QuadraticTable.endvalue Step QuadraticTable.stepval
            ammount = ammount + 1
            If QuadraticTable.bvar >= 0 Then

                If QuadraticTable.cvar >= 0 Then
                    x = QuadraticTable.avar * (i) ^ 2 + QuadraticTable.bvar * i + QuadraticTable.cvar
                End If
                If QuadraticTable.cvar < 0 Then
                    x = QuadraticTable.avar * (i) ^ 2 + QuadraticTable.bvar * i - (QuadraticTable.cvar)
                End If

            ElseIf QuadraticTable.bvar < 0 Then

                If QuadraticTable.cvar >= 0 Then


                    x = QuadraticTable.avar * (i) ^ 2 - (QuadraticTable.bvar * i) + QuadraticTable.cvar
                End If
                If QuadraticTable.cvar < 0 Then
                    x = QuadraticTable.avar * (i) ^ 2 - (QuadraticTable.bvar * i) - (QuadraticTable.cvar)
                End If
            End If
            xtable.Items.Add(x.ToString())
            y = i
            ytable.Items.Add(y.ToString())

        Next
    End Sub
That is obviously called via

Code: Select all

calculatetablex()
from form.load

Re: visual basic problem...

Posted: Wed Jul 23, 2008 8:36 pm
by Majik
Call the

Code: Select all

ExcelSetup("Sheet1")
function before your number generator next loop and then at the end of each next segment, populate the cells with the data

So before the next call add:

Code: Select all

objwb.Cells(x, 1).Value = FinalValue
objwb.Cells(x, 2).Value = FinalOtherValue
I don't have VB installed so I can't see what the output of the code looks like

Re: visual basic problem...

Posted: Wed Jul 23, 2008 11:28 pm
by heebyjeebys
And where shall I put the

Code: Select all

Call ExcellSetup("Sheet1")
in my program?
a new private sub() ?

Re: visual basic problem...

Posted: Wed Jul 23, 2008 11:55 pm
by Majik
No you can go ahead and call it from mine.. where the hell else would you call it from?

Re: visual basic problem...

Posted: Thu Jul 24, 2008 12:01 am
by jd2kuk
Yeah, wouldn't you call it from an 'onclick' or similar...? ;)

Re: visual basic problem...

Posted: Thu Jul 24, 2008 12:13 am
by heebyjeebys
Majik wrote:No you can go ahead and call it from mine.. where the hell else would you call it from?
you misunderstand lol