EDN Admin
Well-known member
Dear all,
I have tried run the following code before, and it worked fine. But then today, I try to run again, it couldnt work if I just write either the MsgBox function, or even MessageBox.Show function. I have tried to import the system.windows.form, but still nothing
display and the program just ran and have no further response. However, if I delete all the MsgBox lines, it works. I understand that MsgBox is really really a very basic function, and I am so struggling why there is nothing appeared when writing the MsgBox
function. My running platform is Visual Studio 2012.
Please help me.
The code is as following:
<pre class="prettyprint lang-vb Imports Microsoft.Office.Interop
Imports System.Windows.Forms
Class Form1
Dim xlapp As Excel.Application
Dim xlwb As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim xlcht As Excel.Chart
Dim dirFileName As String
Private Sub PictureBox2_Paint(sender As Object, e As PaintEventArgs) Handles PictureBox2.Paint
PictureBox2.BackgroundImage = System.Drawing.Image.FromFile("C:VBPaint_TSI.PNG")
PictureBox2.BackgroundImageLayout = ImageLayout.Stretch
PictureBox2.SizeMode = PictureBoxSizeMode.AutoSize
PictureBox2.Visible = True
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Run the Open Dialog
Dim strFileName As String
Dim strPath As String
Dim msgtext As String
Dim row, columnwrite, columnFirerd, columnIARrd, columnt As Integer
Dim total As Long
Dim totaln As Long
Dim totalne As Long
Dim totalc As Long
Dim totalw As Long
Dim totale As Long
Dim totals As Long
openFD.Title = "Load Policy Data"
openFD.FileName = "CTFM_Template"
openFD.Filter = "2010 Excel Files(*.xlsm)|*.xlsm|2003 Excel Files(*.xls)|*.xls"
Dim DidWork As Integer = openFD.ShowDialog
If DidWork = DialogResult.Cancel Then
MsgBox("Hi")
MsgBox("Cancel Button Clicked", MsgBoxStyle.OkOnly + MsgBoxStyle.Information, "CFRM")
Else
strFileName = openFD.FileName
MessageBox.Show(strFileName)
strPath = Path.GetDirectoryName(strFileName)
MessageBox.Show(strFileName)
To open the excel file directed
To run all the range of Fire + IAR exposure
If Dir("C:VBrecord.bz") = "" Then
Creat Excel Object
xlapp = CreateObject("Excel.Application")
xlapp.Visible = True
xlwb = xlapp.Workbooks.Open(strFileName)
xlsheet = xlwb.Worksheets(1)
xlsheet.Activate()
row = 3
Do While row <= 79
columnwrite = 11
columnFirerd = 3
columnIARrd = 7
Do While columnwrite <= 14
xlsheet.Cells(row, columnwrite) = xlsheet.Cells(row, columnFirerd).value + xlsheet.Cells(row, columnIARrd).value
columnFirerd = columnFirerd + 1
columnIARrd = columnIARrd + 1
columnwrite = columnwrite + 1
Loop
row = row + 1
Loop
To sum up the last row in the exposure sheet, which is to find the total
row = 3
columnt = 3
total = 0
Do While columnt <= 14
Do While row <= 79
total = total + xlsheet.Cells(row, columnt).value
row = row + 1
Loop
xlsheet.Cells(row, columnt) = total
columnt = columnt + 1
total = 0
row = 3
Loop
To copy the cells into the summary page and sum up by partition
row = 3
columnt = 3
Residential
xlsheet = xlwb.Worksheets(1)
xlsheet.Activate()
xlsheet.Range("K3:K80").Copy()
xlsheet = xlwb.Worksheets(2)
xlsheet.Activate()
xlsheet.Range("D3
80").PasteSpecial(Excel.XlPasteType.xlPasteValuesAndNumberFormats)
Commercial
xlsheet = xlwb.Worksheets(1)
xlsheet.Activate()
xlsheet.Range("L3:L80").Copy()
xlsheet = xlwb.Worksheets(2)
xlsheet.Activate()
xlsheet.Range("E3:E80").PasteSpecial(Excel.XlPasteType.xlPasteValuesAndNumberFormats)
Industrial
xlsheet = xlwb.Worksheets(1)
xlsheet.Activate()
xlsheet.Range("M3:M80").Copy()
xlsheet = xlwb.Worksheets(2)
xlsheet.Activate()
xlsheet.Range("F3:F80").PasteSpecial(Excel.XlPasteType.xlPasteValuesAndNumberFormats)
Warehousing
xlsheet = xlwb.Worksheets(1)
xlsheet.Activate()
xlsheet.Range("N3:N80").Copy()
xlsheet = xlwb.Worksheets(2)
xlsheet.Activate()
xlsheet.Range("G3:G80").PasteSpecial(Excel.XlPasteType.xlPasteValuesAndNumberFormats)
Do the subtotal column
Do While row <= 80
xlsheet.Cells(row, columnt) = xlsheet.Cells(row, columnt + 1).value + xlsheet.Cells(row, columnt + 2).value + xlsheet.Cells(row, columnt + 3).value + xlsheet.Cells(row, columnt + 4).value
row = row + 1
Loop
Update the TSI by Partition
North
row = 10
columnt = 3
totalc = 0
totale = 0
totaln = 0
totalne = 0
totals = 0
totalw = 0
row = 3
columnt = 3
North
Do While row <= 11
totaln = totaln + xlsheet.Cells(row, columnt).value
row = row + 1
Loop
North East
Do While row <= 31
totalne = totalne + xlsheet.Cells(row, columnt).value
row = row + 1
Loop
Central
Do While row <= 53
totalc = totalc + xlsheet.Cells(row, columnt).value
row = row + 1
Loop
West
Do While row <= 58
totalw = totalw + xlsheet.Cells(row, columnt).value
row = row + 1
Loop
East
Do While row <= 65
totale = totale + xlsheet.Cells(row, columnt).value
row = row + 1
Loop
South
Do While row <= 79
totals = totals + xlsheet.Cells(row, columnt).value
row = row + 1
Loop
row = 3
xlsheet.Cells(row, 10) = totaln
xlsheet.Cells(row + 1, 10) = totalne
xlsheet.Cells(row + 2, 10) = totalc
xlsheet.Cells(row + 3, 10) = totalw
xlsheet.Cells(row + 4, 10) = totale
xlsheet.Cells(row + 5, 10) = totals
xlsheet.Cells(row + 6, 10) = totaln + totalne + totalc + totalw + totale + totals
Label2.Text = "$ " & Format(xlsheet.Cells(row + 6, 10).value, "##,##0.00")
To export the TSI chart and to present it into the software
xlsheet = xlwb.Worksheets(2)
xlsheet.Activate()
xlsheet.ChartObjects("Chart 3").chart.Export(Filename:="C:VBClick_TSI.PNG", FilterName:="PNG")
To change the TSI Pie Chart
PictureBox2_Click(sender, e)
xlwb.RunAutoMacros(Excel.XlRunAutoMacro.xlAutoOpen)
xlapp.Run("pp")
xlapp.DisplayAlerts = False
xlwb.Save()
xlwb.Close()
xlapp.DisplayAlerts = True
xlapp.Quit()
MsgBox("Policy Data Loaded", MsgBoxStyle.OkOnly + MsgBoxStyle.Information, "CFRM")
MessageBox.Show(strFileName)
Else
msgtext = "Error: Excel File already haven been Opened" & vbNewLine & "Please Close the Excel File first"
MsgBox(msgtext, MsgBoxStyle.OkOnly + MsgBoxStyle.Information, "CFRM")
End If
End If
End Sub
Private Sub PictureBox1_Click(sender As Object, e As EventArgs)
PictureBox1.Visible = False
End Sub
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
EP Curve Simulation
End Sub
Private Sub PictureBox1_Paint_1(sender As Object, e As PaintEventArgs) Handles PictureBox1.Paint
PictureBox1.BackgroundImage = System.Drawing.Image.FromFile("C:VBFlood_Map.BMP")
PictureBox1.BackgroundImageLayout = ImageLayout.Stretch
PictureBox1.Visible = True
End Sub
Private Sub PictureBox2_Click(sender As Object, e As EventArgs) Handles PictureBox2.Click
PictureBox2.Image = New Bitmap("C:VBClick_TSI.PNG")
PictureBox2.SizeMode = PictureBoxSizeMode.AutoSize
PictureBox2.Visible = True
End Sub
Private Sub PictureBox3_Click(sender As Object, e As EventArgs) Handles PictureBox3.Click
PictureBox1_Click(sender, e)
PictureBox3.Image = New Bitmap("C:VBEP Curve.BMP")
PictureBox3.SizeMode = PictureBoxSizeMode.AutoSize
PictureBox3.Visible = True
End Sub
End Class
[/code]
<br/>
Thank you again, cause I really have no where to start to look where goes wrong, and I have tried searching this problem on the internet, no one has the same problem as I am having, so yea.
Yours,
Wilson
View the full article
I have tried run the following code before, and it worked fine. But then today, I try to run again, it couldnt work if I just write either the MsgBox function, or even MessageBox.Show function. I have tried to import the system.windows.form, but still nothing
display and the program just ran and have no further response. However, if I delete all the MsgBox lines, it works. I understand that MsgBox is really really a very basic function, and I am so struggling why there is nothing appeared when writing the MsgBox
function. My running platform is Visual Studio 2012.
Please help me.
The code is as following:
<pre class="prettyprint lang-vb Imports Microsoft.Office.Interop
Imports System.Windows.Forms
Class Form1
Dim xlapp As Excel.Application
Dim xlwb As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim xlcht As Excel.Chart
Dim dirFileName As String
Private Sub PictureBox2_Paint(sender As Object, e As PaintEventArgs) Handles PictureBox2.Paint
PictureBox2.BackgroundImage = System.Drawing.Image.FromFile("C:VBPaint_TSI.PNG")
PictureBox2.BackgroundImageLayout = ImageLayout.Stretch
PictureBox2.SizeMode = PictureBoxSizeMode.AutoSize
PictureBox2.Visible = True
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Run the Open Dialog
Dim strFileName As String
Dim strPath As String
Dim msgtext As String
Dim row, columnwrite, columnFirerd, columnIARrd, columnt As Integer
Dim total As Long
Dim totaln As Long
Dim totalne As Long
Dim totalc As Long
Dim totalw As Long
Dim totale As Long
Dim totals As Long
openFD.Title = "Load Policy Data"
openFD.FileName = "CTFM_Template"
openFD.Filter = "2010 Excel Files(*.xlsm)|*.xlsm|2003 Excel Files(*.xls)|*.xls"
Dim DidWork As Integer = openFD.ShowDialog
If DidWork = DialogResult.Cancel Then
MsgBox("Hi")
MsgBox("Cancel Button Clicked", MsgBoxStyle.OkOnly + MsgBoxStyle.Information, "CFRM")
Else
strFileName = openFD.FileName
MessageBox.Show(strFileName)
strPath = Path.GetDirectoryName(strFileName)
MessageBox.Show(strFileName)
To open the excel file directed
To run all the range of Fire + IAR exposure
If Dir("C:VBrecord.bz") = "" Then
Creat Excel Object
xlapp = CreateObject("Excel.Application")
xlapp.Visible = True
xlwb = xlapp.Workbooks.Open(strFileName)
xlsheet = xlwb.Worksheets(1)
xlsheet.Activate()
row = 3
Do While row <= 79
columnwrite = 11
columnFirerd = 3
columnIARrd = 7
Do While columnwrite <= 14
xlsheet.Cells(row, columnwrite) = xlsheet.Cells(row, columnFirerd).value + xlsheet.Cells(row, columnIARrd).value
columnFirerd = columnFirerd + 1
columnIARrd = columnIARrd + 1
columnwrite = columnwrite + 1
Loop
row = row + 1
Loop
To sum up the last row in the exposure sheet, which is to find the total
row = 3
columnt = 3
total = 0
Do While columnt <= 14
Do While row <= 79
total = total + xlsheet.Cells(row, columnt).value
row = row + 1
Loop
xlsheet.Cells(row, columnt) = total
columnt = columnt + 1
total = 0
row = 3
Loop
To copy the cells into the summary page and sum up by partition
row = 3
columnt = 3
Residential
xlsheet = xlwb.Worksheets(1)
xlsheet.Activate()
xlsheet.Range("K3:K80").Copy()
xlsheet = xlwb.Worksheets(2)
xlsheet.Activate()
xlsheet.Range("D3
80").PasteSpecial(Excel.XlPasteType.xlPasteValuesAndNumberFormats)Commercial
xlsheet = xlwb.Worksheets(1)
xlsheet.Activate()
xlsheet.Range("L3:L80").Copy()
xlsheet = xlwb.Worksheets(2)
xlsheet.Activate()
xlsheet.Range("E3:E80").PasteSpecial(Excel.XlPasteType.xlPasteValuesAndNumberFormats)
Industrial
xlsheet = xlwb.Worksheets(1)
xlsheet.Activate()
xlsheet.Range("M3:M80").Copy()
xlsheet = xlwb.Worksheets(2)
xlsheet.Activate()
xlsheet.Range("F3:F80").PasteSpecial(Excel.XlPasteType.xlPasteValuesAndNumberFormats)
Warehousing
xlsheet = xlwb.Worksheets(1)
xlsheet.Activate()
xlsheet.Range("N3:N80").Copy()
xlsheet = xlwb.Worksheets(2)
xlsheet.Activate()
xlsheet.Range("G3:G80").PasteSpecial(Excel.XlPasteType.xlPasteValuesAndNumberFormats)
Do the subtotal column
Do While row <= 80
xlsheet.Cells(row, columnt) = xlsheet.Cells(row, columnt + 1).value + xlsheet.Cells(row, columnt + 2).value + xlsheet.Cells(row, columnt + 3).value + xlsheet.Cells(row, columnt + 4).value
row = row + 1
Loop
Update the TSI by Partition
North
row = 10
columnt = 3
totalc = 0
totale = 0
totaln = 0
totalne = 0
totals = 0
totalw = 0
row = 3
columnt = 3
North
Do While row <= 11
totaln = totaln + xlsheet.Cells(row, columnt).value
row = row + 1
Loop
North East
Do While row <= 31
totalne = totalne + xlsheet.Cells(row, columnt).value
row = row + 1
Loop
Central
Do While row <= 53
totalc = totalc + xlsheet.Cells(row, columnt).value
row = row + 1
Loop
West
Do While row <= 58
totalw = totalw + xlsheet.Cells(row, columnt).value
row = row + 1
Loop
East
Do While row <= 65
totale = totale + xlsheet.Cells(row, columnt).value
row = row + 1
Loop
South
Do While row <= 79
totals = totals + xlsheet.Cells(row, columnt).value
row = row + 1
Loop
row = 3
xlsheet.Cells(row, 10) = totaln
xlsheet.Cells(row + 1, 10) = totalne
xlsheet.Cells(row + 2, 10) = totalc
xlsheet.Cells(row + 3, 10) = totalw
xlsheet.Cells(row + 4, 10) = totale
xlsheet.Cells(row + 5, 10) = totals
xlsheet.Cells(row + 6, 10) = totaln + totalne + totalc + totalw + totale + totals
Label2.Text = "$ " & Format(xlsheet.Cells(row + 6, 10).value, "##,##0.00")
To export the TSI chart and to present it into the software
xlsheet = xlwb.Worksheets(2)
xlsheet.Activate()
xlsheet.ChartObjects("Chart 3").chart.Export(Filename:="C:VBClick_TSI.PNG", FilterName:="PNG")
To change the TSI Pie Chart
PictureBox2_Click(sender, e)
xlwb.RunAutoMacros(Excel.XlRunAutoMacro.xlAutoOpen)
xlapp.Run("pp")
xlapp.DisplayAlerts = False
xlwb.Save()
xlwb.Close()
xlapp.DisplayAlerts = True
xlapp.Quit()
MsgBox("Policy Data Loaded", MsgBoxStyle.OkOnly + MsgBoxStyle.Information, "CFRM")
MessageBox.Show(strFileName)
Else
msgtext = "Error: Excel File already haven been Opened" & vbNewLine & "Please Close the Excel File first"
MsgBox(msgtext, MsgBoxStyle.OkOnly + MsgBoxStyle.Information, "CFRM")
End If
End If
End Sub
Private Sub PictureBox1_Click(sender As Object, e As EventArgs)
PictureBox1.Visible = False
End Sub
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
EP Curve Simulation
End Sub
Private Sub PictureBox1_Paint_1(sender As Object, e As PaintEventArgs) Handles PictureBox1.Paint
PictureBox1.BackgroundImage = System.Drawing.Image.FromFile("C:VBFlood_Map.BMP")
PictureBox1.BackgroundImageLayout = ImageLayout.Stretch
PictureBox1.Visible = True
End Sub
Private Sub PictureBox2_Click(sender As Object, e As EventArgs) Handles PictureBox2.Click
PictureBox2.Image = New Bitmap("C:VBClick_TSI.PNG")
PictureBox2.SizeMode = PictureBoxSizeMode.AutoSize
PictureBox2.Visible = True
End Sub
Private Sub PictureBox3_Click(sender As Object, e As EventArgs) Handles PictureBox3.Click
PictureBox1_Click(sender, e)
PictureBox3.Image = New Bitmap("C:VBEP Curve.BMP")
PictureBox3.SizeMode = PictureBoxSizeMode.AutoSize
PictureBox3.Visible = True
End Sub
End Class
[/code]
<br/>
Thank you again, cause I really have no where to start to look where goes wrong, and I have tried searching this problem on the internet, no one has the same problem as I am having, so yea.
Yours,
Wilson
View the full article