I am trying to design an application in Visual Studio that will allow a user
to select a spreadsheet, pass in some parameters, and run macros in the
spreadsheet that depend on the parameters. I was able to do this pretty
easily with Access, but I want it to be a standalone app so I'm trying to do
it in VB.NET (I am a VS.NET newbie) So far my code seems to work fine, until
I try to execute the macros, at which point I get a "Type mismatch" error.
The macro in question takes two integer inputs, and both of the variables I
create in VB are integers, I cannot figure out why I am getting this error.
Here is the code up to the point I get an error:

Private Sub Button1_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Button1.Click

Dim XL, FD, OfficeApp As Object

Dim TotalRows As Integer

Dim Prem, Inc, Exp, Profit, Profit5, Increase, TotalInc, LastIncrease As
Double

Dim Iterations, LastBatch, j, Done As Integer

Dim minutes As Double

Dim seconds As Double

Dim StartTime As Double, ElapsedTime As Double

Dim minutesgrammar As String

Dim EndTime As Double

Dim myFileName, vrtSelectedItem

Dim BatchSizeTxt As String

Dim BatchSize As Integer

Dim msoFileDialogFilePicker

Dim openFileDialog1 As New OpenFileDialog()

'User will input the number of policies to run per batch

BatchSizeTxt = InputBox("How many policies do you want to run per batch?")

If BatchSizeTxt = "" Then

'MsgBox ("You must enter a number.")

Exit Sub

Else

If Val(BatchSizeTxt) = 0 Then

MsgBox("You must enter a number greater than 0.")

Exit Sub

End If

End If

BatchSize = Int(BatchSizeTxt)

'Initialize the timer

Dim dtDateTime As DateTime = Now()

StartTime = dtDateTime.Ticks

'Get the file to run a projection with

With openFileDialog1

..FileName = ""

..ShowDialog()

myFileName = .FileName

End With



XL = CreateObject("Excel.Application")

XL.Workbooks.Open(myFileName)

If BatchSize XL.Worksheets("All
data").Range("A1").CurrentRegion.Rows.Count - 1 Then

MsgBox("Your batch amount was greater than the total # of policies.")

Exit Sub

End If

TotalRows = XL.Worksheets("All data").Range("A1").CurrentRegion.Rows.Count -
1

Iterations = Int(TotalRows / BatchSize)

LastBatch = Int(TotalRows - Iterations * BatchSize)

XL.Run("Insert_Data", 0, BatchSize)

Exel is using VBScript so an int is 16 bits. In .net an Integer is 32 bits.
Either make your excel macro take a long (32 bits in VBS like the 32 bit int
in .net) or make batchsize an int16

Change

Dim BatchSize As Integer

to

Dim BatchSize As Int16


"Aaron" wrote in message
...
I am trying to design an application in Visual Studio that will allow a
user
to select a spreadsheet, pass in some parameters, and run macros in the
spreadsheet that depend on the parameters. I was able to do this pretty
easily with Access, but I want it to be a standalone app so I'm trying to
do
it in VB.NET (I am a VS.NET newbie) So far my code seems to work fine,
until
I try to execute the macros, at which point I get a "Type mismatch" error.
The macro in question takes two integer inputs, and both of the variables
I
create in VB are integers, I cannot figure out why I am getting this
error.
Here is the code up to the point I get an error:

Private Sub Button1_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Button1.Click

Dim XL, FD, OfficeApp As Object

Dim TotalRows As Integer

Dim Prem, Inc, Exp, Profit, Profit5, Increase, TotalInc, LastIncrease As
Double

Dim Iterations, LastBatch, j, Done As Integer

Dim minutes As Double

Dim seconds As Double

Dim StartTime As Double, ElapsedTime As Double

Dim minutesgrammar As String

Dim EndTime As Double

Dim myFileName, vrtSelectedItem

Dim BatchSizeTxt As String

Dim BatchSize As Integer

Dim msoFileDialogFilePicker

Dim openFileDialog1 As New OpenFileDialog()

'User will input the number of policies to run per batch

BatchSizeTxt = InputBox("How many policies do you want to run per batch?")

If BatchSizeTxt = "" Then

'MsgBox ("You must enter a number.")

Exit Sub

Else

If Val(BatchSizeTxt) = 0 Then

MsgBox("You must enter a number greater than 0.")

Exit Sub

End If

End If

BatchSize = Int(BatchSizeTxt)

'Initialize the timer

Dim dtDateTime As DateTime = Now()

StartTime = dtDateTime.Ticks

'Get the file to run a projection with

With openFileDialog1

.FileName = ""

.ShowDialog()

myFileName = .FileName

End With



XL = CreateObject("Excel.Application")

XL.Workbooks.Open(myFileName)

If BatchSize XL.Worksheets("All
data").Range("A1").CurrentRegion.Rows.Count - 1 Then

MsgBox("Your batch amount was greater than the total # of policies.")

Exit Sub

End If

TotalRows = XL.Worksheets("All
data").Range("A1").CurrentRegion.Rows.Count -
1

Iterations = Int(TotalRows / BatchSize)

LastBatch = Int(TotalRows - Iterations * BatchSize)

XL.Run("Insert_Data", 0, BatchSize)

Solved my problem!!
Thanks a lot!!!


"Adrian Forbes [ASP MVP]" wrote in message
...
Exel is using VBScript so an int is 16 bits. In .net an Integer is 32
bits.
Either make your excel macro take a long (32 bits in VBS like the 32 bit
int
in .net) or make batchsize an int16

Change

Dim BatchSize As Integer

to

Dim BatchSize As Int16

You may have some problems with the way you dimensioned
the variables.

It's my understanding that you must do it like this:

Dim XL As Object, FD As Object, OfficeApp As Object

rather than:

Dim XL, FD, OfficeApp As Object

etc.

After I had been programming for some time in VBA, someone
pointed that out to me. I was a little embarrassed that I
didn't know. With Excel VBA if you don't define the
dimension, it is assigned as variant by default.

I could be wrong here, but that's what people have told
me. For some reason, the books that I used as a beginner
really didn't clarify or emphasize that very well. You
might review that.

I'm still using VB6 and Excel VBA, so I just took a look
at your code out of curiousity. I don't know enough about
VB.Net to tell you much more, but maybe that will help.


-----Original Message-----
I am trying to design an application in Visual Studio
that will allow a user
to select a spreadsheet, pass in some parameters, and run
macros in the
spreadsheet that depend on the parameters. I was able to
do this pretty
easily with Access, but I want it to be a standalone app
so I'm trying to do
it in VB.NET (I am a VS.NET newbie) So far my code seems
to work fine, until
I try to execute the macros, at which point I get a "Type
mismatch" error.
The macro in question takes two integer inputs, and both
of the variables I
create in VB are integers, I cannot figure out why I am
getting this error.
Here is the code up to the point I get an error:

Private Sub Button1_Click(ByVal sender As Object, ByVal e
As
System.EventArgs) Handles Button1.Click

Dim XL, FD, OfficeApp As Object

Dim TotalRows As Integer

Dim Prem, Inc, Exp, Profit, Profit5, Increase, TotalInc,
LastIncrease As
Double

Dim Iterations, LastBatch, j, Done As Integer

Dim minutes As Double

Dim seconds As Double

Dim StartTime As Double, ElapsedTime As Double

Dim minutesgrammar As String

Dim EndTime As Double

Dim myFileName, vrtSelectedItem

Dim BatchSizeTxt As String

Dim BatchSize As Integer

Dim msoFileDialogFilePicker

Dim openFileDialog1 As New OpenFileDialog()

'User will input the number of policies to run per batch

BatchSizeTxt = InputBox("How many policies do you want to
run per batch?")

If BatchSizeTxt = "" Then

'MsgBox ("You must enter a number.")

Exit Sub

Else

If Val(BatchSizeTxt) = 0 Then

MsgBox("You must enter a number greater than 0.")

Exit Sub

End If

End If

BatchSize = Int(BatchSizeTxt)

'Initialize the timer

Dim dtDateTime As DateTime = Now()

StartTime = dtDateTime.Ticks

'Get the file to run a projection with

With openFileDialog1

..FileName = ""

..ShowDialog()

myFileName = .FileName

End With



XL = CreateObject("Excel.Application")

XL.Workbooks.Open(myFileName)

If BatchSize XL.Worksheets("All
data").Range("A1").CurrentRegion.Rows.Count - 1 Then

MsgBox("Your batch amount was greater than the total # of
policies.")

Exit Sub

End If

TotalRows = XL.Worksheets("All data").Range
("A1").CurrentRegion.Rows.Count -
1

Iterations = Int(TotalRows / BatchSize)

LastBatch = Int(TotalRows - Iterations * BatchSize)

XL.Run("Insert_Data", 0, BatchSize)






.