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)
.