Automate Excel from VS.NET

[Aaron]

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)

[Adrian Forbes [ASP MVP]]

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" <ab***********@hotmail.com> wrote in message
news:Ov**************@TK2MSFTNGP09.phx.gbl...

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)

[Aaron]

Solved my problem!!
Thanks a lot!!!
"Adrian Forbes [ASP MVP]" <so***@noemail.zzz> wrote in message
news:uT**************@TK2MSFTNGP09.phx.gbl...

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