Click here to Skip to main content
14,766,373 members
Please Sign up or sign in to vote.
2.00/5 (1 vote)
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.

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