opening excel application thru vb.net

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I'm having problems opening up excel thru my code. It will write and
saveas an excel file, but the application won't open on the user's computer,
excel seems to be hidden, because it shows up on the task manager. The
problem is, if this user tries to download the data more than once, it gets
stuck because the file already exists, so I want it to overwrite the old
file...but the user can't overwrite when the excel app won't work! Any
suggestions? This is my code below:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim oDS As New DataSet
Dim oConn As New SqlClient.SqlConnection("workstation
id=CTR3_252A;packet size=4096;user id=sa;data source=CRI;persist security
info=True;initial catalog=gfdgfd;password=gfdgfd")
Dim oSqlCmd As New SqlClient.SqlCommand("SELECT * from
dbo.MRI_HEATHERSTAT_VIEW")
Dim oDA As New SqlClient.SqlDataAdapter(oSqlCmd)

oSqlCmd.Connection = oConn

oConn.Open()

oDA.Fill(oDS)
oConn.Close()


Dim oRow As DataRow

For Each oRow In oDS.Tables(0).Rows
Next
Dim oDSLocal As New DataSet
Dim oSqlCmdlocal As New SqlClient.SqlCommand("SELECT * from
dbo.MRI_HEATHERSTAT_VIEW")
Dim oDAlocal As New SqlClient.SqlDataAdapter(oSqlCmdlocal)
Dim oConnLocal As New SqlClient.SqlConnection("workstation
id=CTR3_252A;packet size=4096;user id=sa;data source=CRI;persist security
info=True;initial catalog=gfdgfd;password=gfdgfd")

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlAELBook As Excel.Workbook
Dim xlAELSheet As Excel.Worksheet
Dim xlSheet As Excel.Worksheet
xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
xlBook = CType(xlApp.Workbooks.Add, Excel.Workbook)
xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)


xlApp.Visible = True
xlSheet.Visible = True

xlSheet.Application.Visible = True
oSqlCmdlocal.Connection = oConnLocal

oConnLocal.Open()

oDAlocal.Fill(oDSLocal)


Dim oRowLocal As DataRow
Dim x As Integer = 2

'Set header
xlSheet.Cells(1, 1) = "SUBJECT_ID"
xlSheet.Cells(1, 2) = "RESERVED"
xlSheet.Cells(1, 3) = "SLICE1_WHOLEARM_CSA"
xlSheet.Cells(1, 4) = "SLICE2_WHOLEARM_CSA"
xlSheet.Cells(1, 5) = "SLICE3_WHOLEARM_CSA"
xlSheet.Cells(1, 6) = "SLICE4_WHOLEARM_CSA"
xlSheet.Cells(1, 7) = "SLICE5_WHOLEARM_CSA"
xlSheet.Cells(1, 8) = "SLICE6_WHOLEARM_CSA"
xlSheet.Cells(1, 9) = "AVG_WHOLEARM_CSA"
xlSheet.Cells(1, 10) = "SLICE1_MARROW_CSA"
xlSheet.Cells(1, 11) = "SLICE2_MARROW_CSA"
xlSheet.Cells(1, 12) = "SLICE3_MARROW_CSA"
xlSheet.Cells(1, 13) = "SLICE4_MARROW_CSA"
xlSheet.Cells(1, 14) = "SLICE5_MARROW_CSA"
xlSheet.Cells(1, 15) = "SLICE6_MARROW_CSA"
xlSheet.Cells(1, 16) = "AVG_MARROW_CSA"
xlSheet.Cells(1, 17) = "SLICE1_BONE_MARROW_CSA"
xlSheet.Cells(1, 18) = "SLICE2_BONE_MARROW_CSA"
xlSheet.Cells(1, 19) = "SLICE3_BONE_MARROW_CSA"
xlSheet.Cells(1, 20) = "SLICE4_BONE_MARROW_CSA"
xlSheet.Cells(1, 21) = "SLICE5_BONE_MARROW_CSA"
xlSheet.Cells(1, 22) = "SLICE6_BONE_MARROW_CSA"
xlSheet.Cells(1, 23) = "AVG_BONE_MARROW_CSA"
xlSheet.Cells(1, 24) = "SLICE1_FAT_CSA"
xlSheet.Cells(1, 25) = "SLICE2_FAT_CSA"
xlSheet.Cells(1, 26) = "SLICE3_FAT_CSA"
xlSheet.Cells(1, 27) = "SLICE4_FAT_CSA"
xlSheet.Cells(1, 28) = "SLICE5_FAT_CSA"
xlSheet.Cells(1, 29) = "SLICE6_FAT_CSA"
xlSheet.Cells(1, 30) = "AVG_FAT_CSA"
xlSheet.Cells(1, 31) = "AVG_WHOLEMUSCLE_CSA"
xlSheet.Cells(1, 32) = "WHOLEARM_VOL"
xlSheet.Cells(1, 33) = "MARROW_VOL"
xlSheet.Cells(1, 34) = "BONE_MARROW_VOL"
xlSheet.Cells(1, 35) = "FAT_VOL"
xlSheet.Cells(1, 36) = "WHOLEMUSCLE_VOL"




For Each oRowLocal In oDSLocal.Tables(0).Rows

xlSheet.Cells(x, 1) = oRowLocal("SUBJECT_ID")
xlSheet.Cells(x, 2) = oRowLocal("RESERVED")
xlSheet.Cells(x, 3) = oRowLocal("SLICE1_WHOLEARM_CSA")
xlSheet.Cells(x, 4) = oRowLocal("SLICE2_WHOLEARM_CSA")
xlSheet.Cells(x, 5) = oRowLocal("SLICE3_WHOLEARM_CSA")
xlSheet.Cells(x, 6) = oRowLocal("SLICE4_WHOLEARM_CSA")
xlSheet.Cells(x, 7) = oRowLocal("SLICE5_WHOLEARM_CSA")
xlSheet.Cells(x, 8) = oRowLocal("SLICE6_WHOLEARM_CSA")
xlSheet.Cells(x, 9) = oRowLocal("AVG_WHOLEARM_CSA")
xlSheet.Cells(x, 10) = oRowLocal("SLICE1_MARROW_CSA")
xlSheet.Cells(x, 11) = oRowLocal("SLICE2_MARROW_CSA")
xlSheet.Cells(x, 12) = oRowLocal("SLICE3_MARROW_CSA")
xlSheet.Cells(x, 13) = oRowLocal("SLICE4_MARROW_CSA")
xlSheet.Cells(x, 14) = oRowLocal("SLICE5_MARROW_CSA")
xlSheet.Cells(x, 15) = oRowLocal("SLICE6_MARROW_CSA")
xlSheet.Cells(x, 16) = oRowLocal("AVG_MARROW_CSA")
xlSheet.Cells(x, 17) = oRowLocal("SLICE1_BONE_MARROW_CSA")
xlSheet.Cells(x, 18) = oRowLocal("SLICE2_BONE_MARROW_CSA")
xlSheet.Cells(x, 19) = oRowLocal("SLICE3_BONE_MARROW_CSA")
xlSheet.Cells(x, 20) = oRowLocal("SLICE4_BONE_MARROW_CSA")
xlSheet.Cells(x, 21) = oRowLocal("SLICE5_BONE_MARROW_CSA")
xlSheet.Cells(x, 22) = oRowLocal("SLICE6_BONE_MARROW_CSA")
xlSheet.Cells(x, 23) = oRowLocal("AVG_BONE_MARROW_CSA")
xlSheet.Cells(x, 24) = oRowLocal("SLICE1_FAT_CSA")
xlSheet.Cells(x, 25) = oRowLocal("SLICE2_FAT_CSA")
xlSheet.Cells(x, 26) = oRowLocal("SLICE3_FAT_CSA")
xlSheet.Cells(x, 27) = oRowLocal("SLICE4_FAT_CSA")
xlSheet.Cells(x, 28) = oRowLocal("SLICE5_FAT_CSA")
xlSheet.Cells(x, 29) = oRowLocal("SLICE6_FAT_CSA")
xlSheet.Cells(x, 30) = oRowLocal("AVG_FAT_CSA")
xlSheet.Cells(x, 31) = oRowLocal("AVG_WHOLEMUSCLE_CSA")
xlSheet.Cells(x, 32) = oRowLocal("WHOLEARM_VOL")
xlSheet.Cells(x, 33) = oRowLocal("MARROW_VOL")
xlSheet.Cells(x, 34) = oRowLocal("BONE_MARROW_VOL")
xlSheet.Cells(x, 35) = oRowLocal("FAT_VOL")
xlSheet.Cells(x, 36) = oRowLocal("WHOLEMUSCLE_VOL")

x = x + 1

Next

xlSheet.Name = "Rapidia Dataset"
xlSheet.SaveAs("C:\MyFile3.xls")
xlSheet.Application.Quit()
xlSheet = Nothing
oDSLocal = Nothing
oSqlCmdlocal = Nothing


Label3.Visible = True
 
if anyone could help me, i'd really appreciate it! I've tried all the
suggestions that were similar to my problem, and nothing seems to be working.
I can't figure out why
1) My excel app is hidden even when I try to make it visible
2) When redownloading the data, I'm assuming that it's trying to overwrite
the old data, but giving an "are you sure" message. (My internet explorer
makes a "ching" sound, which I assume is the msg popping up, then it just
sits there and doesn't move fwd, however I can't see any of it because excel
is hidden) I would like to make it so that this msg does not popup, and it
automatically overwrites the old data. thanks!
 
Back
Top