Click here to Skip to main content
15,879,095 members
Please Sign up or sign in to vote.
2.00/5 (1 vote)
See more: , +
Hi i am trying to fill an excel sheet from my sql server database and i get a message with code HRESULT: 0x800401A8.Here is my code please assist me.

VB
Dim file As String = "C:\Techform.xlsx"


     Dim xlApp_2 As New Excel.Application
     xlApp_2.Visible = False
     xlApp_2.DisplayAlerts = False
     Dim oBook As Excel.Workbook
     oBook = xlApp_2.Workbooks.Open(file)

     Dim oSheet As Excel.Worksheet


     oSheet = oBook.Worksheets(1)
     objConnection.Open()
     adapter_view.SelectCommand = New SqlCommand()
     adapter_view.SelectCommand.Connection = objConnection
     adapter_view.SelectCommand.CommandText = "SELECT * FROM TECHNICAL_AMS_CALC_GARGANTUA "
     adapter_view.SelectCommand.CommandType = CommandType.Text
     adapter_view.Fill(dataset, "GARGANTUA_AVERAGE_Data")
     dataTable_CALC = dataset.Tables("GARGANTUA_AVERAGE_Data")
     adapter_view.Dispose()
     For h As Integer = 0 To dataTable_CALC.Rows.Count - 1
         ' lets modify a1 and a2

         SoG_AVG = dataTable_CALC.Rows(h).Item("VPM_SPEED_THROUGH_WATER_AVG")
         oSheet.Range("E3").Value = dataTable_CALC.Rows(h).Item("VPM_SPEED_THROUGH_WATER_AVG")
         oSheet.Range("F3").Value = dataTable_CALC.Rows(h).Item("VPM_SPEED_THROUGH_WATER_MAX")

         ' save

         oBook.SaveAs(file + " " + dataTable_CALC.Rows(h).Item("ACTION_DATE") + ".xlsx", CreateBackup:=False)
         oBook.Close()
         xlApp_2.Quit()
     Next
Posted
Comments
Richard Deeming 13-Jul-15 12:50pm    
Does your query return more than one row? Because you're closing the workbook and quitting Excel after you've processed the first row, instead of waiting until you've processed all of the rows.
Member 10059109 14-Jul-15 5:02am    
Indeed it returns more that one row.The logic is that 1 row can fill my excel sheet so i wont for every row that comes from my query to create a new excel sheet

1 solution

I found the solution to my problem i had to close the Workbook and the applciation outside of my loop thank you so mucj @Richard Deeming.
 
Share this answer
 
Comments
Patrice T 30-Jul-15 16:26pm    
Since you found a solution, it good practice to close the question.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900