Exporting Dataset/datagrid to Excel
Hello,
I'm looking for a way to export a dataset inside of a datagrid into Excel, that can work in Excel XP (2001) or lesser (i already found code on how to export it and let it be readable w/ excel 2002/2003 here:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q307021
But it cannot be read in previous excel versions because the columns are all messed up.
I just found this piece of code from our area last year:
https://www.experts-exchange.com/questions/20629837/Export-datagrid-to-csv-file.html?query=exporting+dataset&searchType=topic
That works except the columns don't show up.
Would you happen to know how the columns can be added in?
Thanks in advance!
Chris
I'm looking for a way to export a dataset inside of a datagrid into Excel, that can work in Excel XP (2001) or lesser (i already found code on how to export it and let it be readable w/ excel 2002/2003 here:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q307021
But it cannot be read in previous excel versions because the columns are all messed up.
I just found this piece of code from our area last year:
https://www.experts-exchange.com/questions/20629837/Export-datagrid-to-csv-file.html?query=exporting+dataset&searchType=topic
That works except the columns don't show up.
Would you happen to know how the columns can be added in?
Thanks in advance!
Chris
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
iboutchkine,
Awesome code.....
For the interest of sharing code this works too: (make sure you add a reference to Excel objects)
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
oExcel = CreateObject("Excel.Applic
oBook = oExcel.Workbooks.Add
oSheet = oBook.Worksheets(1)
'Set up the Query Table and tell it where to find the data.
Dim oQryTable As Object
oQryTable = oSheet.QueryTables.Add("OL
oQryTable.RefreshStyle = 2 ' x1InsertEntire Rows = 2
oQryTable.Refresh(False)
'sPath is a string containing the location to save the file to
oBook.SaveAs(sPath)
'Clear everything so you can display it to the user
oQryTable = Nothing
oSheet = Nothing
oBook = Nothing
oExcel.Quit()
oExcel = Nothing
'This will open Excel with the new workbook you just created. This step is not necessary, but I find it nice.
Dim xlApp As Excel.Application
Dim xlMappe As Excel.Workbook
xlApp = New Excel.Application()
xlApp.Visible = True
xlMappe = xlApp.Workbooks.Open(sPath
Thanks all of you.
Chris
Awesome code.....
For the interest of sharing code this works too: (make sure you add a reference to Excel objects)
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
oExcel = CreateObject("Excel.Applic
oBook = oExcel.Workbooks.Add
oSheet = oBook.Worksheets(1)
'Set up the Query Table and tell it where to find the data.
Dim oQryTable As Object
oQryTable = oSheet.QueryTables.Add("OL
oQryTable.RefreshStyle = 2 ' x1InsertEntire Rows = 2
oQryTable.Refresh(False)
'sPath is a string containing the location to save the file to
oBook.SaveAs(sPath)
'Clear everything so you can display it to the user
oQryTable = Nothing
oSheet = Nothing
oBook = Nothing
oExcel.Quit()
oExcel = Nothing
'This will open Excel with the new workbook you just created. This step is not necessary, but I find it nice.
Dim xlApp As Excel.Application
Dim xlMappe As Excel.Workbook
xlApp = New Excel.Application()
xlApp.Visible = True
xlMappe = xlApp.Workbooks.Open(sPath
Thanks all of you.
Chris
thank you
In this code you are using the late binding. You don't need to add reference for Excel It will work for any version of Excel
In this code you are using the late binding. You don't need to add reference for Excel It will work for any version of Excel
Assuming your DataSet is named ds
Simply ds.WriteXML("PathAndFileNa
Excel will be able to read this file with no major problem
Dabas