Automate Excel from VS.NET

A

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

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 said:
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
Click to expand...
data").Range("A1").CurrentRegion.Rows.Count -
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help needed! Extracting Heading 3 and Contents from a Word Doc to Excel 0
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Export specific fields from access to Excel 0
Macro VBA coding, Copying content from one workbook to a new one 0
type mismatch error in macro in excel 0
Convert code to lookup three criteria return fourth 15
How to call excel function with a byref array parameter from VB.Ne 0
Copy from one workbook to another failing 8

Top