deveshb
asked on
How to export DBGRID data to MSExcel
I am using DBGrid (Bound with a Data Control) in VB5.0. The recordsource of the DataControl is changed dynamically and DBGrid is refreshed accordingly.
I want to save the data displayed in the DBgrid as an MSExcel file. How to do that?
Please help. Look forward to an early and complete answer.
Thanks
I want to save the data displayed in the DBgrid as an MSExcel file. How to do that?
Please help. Look forward to an early and complete answer.
Thanks
ASKER
In fact I want DBgrid data to be saved in a file which can be opened in MSExcel with same columns as it appears in the DBGrid.
You have mentioned about saving as CSV format. Could you please give some VBCode to do what I want.
You have mentioned about saving as CSV format. Could you please give some VBCode to do what I want.
I will prepare some and send it to you ASAP
You could use the excel engine. I like this method because it enables me to do formatting if I want. This code probably has errors(I am not going to write the routine for a few points), but itI shows the idea. It loops through each column and row in the recordset and puts them in the corresponding excel cell:
Set xlfile = GetObject("c:\temp.xls") 'open an existing already formatted file
data1.recordset.movefirst
While Not data1.recordset.EOF
'this awkward line allows it to work with excel 7 or excel 97
With xlfile.Application.Workboo ks("temp.x ls").Works heets("dat a").Range( "a2")
For col = 0 To data1.recordset.Fields.Cou nt - 1
.Offset(row, col).FormulaR1C1 = rsData(col) & ""
Next col
row = row + 1
data1.recordset.MoveNext
End With
Wend
xlfile.Application.Visible = True
xlfile.Parent.Windows(1).V isible = True
xlfile.Application.Workboo ks("test") .Save
xlfile.Application.Workboo ks("test") .Close
Set xlfile = GetObject("c:\temp.xls") 'open an existing already formatted file
data1.recordset.movefirst
While Not data1.recordset.EOF
'this awkward line allows it to work with excel 7 or excel 97
With xlfile.Application.Workboo
For col = 0 To data1.recordset.Fields.Cou
.Offset(row, col).FormulaR1C1 = rsData(col) & ""
Next col
row = row + 1
data1.recordset.MoveNext
End With
Wend
xlfile.Application.Visible
xlfile.Parent.Windows(1).V
xlfile.Application.Workboo
xlfile.Application.Workboo
You could use the excel engine. I like this method because it enables me to do formatting if I want. This code probably has errors(I am not going to write the routine for a few points), but itI shows the idea. It loops through each column and row in the recordset and puts them in the corresponding excel cell:
Set xlfile = GetObject("c:\temp.xls") 'open an existing already formatted file
data1.recordset.movefirst
While Not data1.recordset.EOF
'this awkward line allows it to work with excel 7 or excel 97
With xlfile.Application.Workboo ks("temp.x ls").Works heets("dat a").Range( "a2")
For col = 0 To data1.recordset.Fields.Cou nt - 1
.Offset(row, col).FormulaR1C1 = rsData(col) & ""
Next col
row = row + 1
data1.recordset.MoveNext
End With
Wend
xlfile.Application.Visible = True
xlfile.Parent.Windows(1).V isible = True
xlfile.Application.Workboo ks("test") .Save
xlfile.Application.Workboo ks("test") .Close
Set xlfile = GetObject("c:\temp.xls") 'open an existing already formatted file
data1.recordset.movefirst
While Not data1.recordset.EOF
'this awkward line allows it to work with excel 7 or excel 97
With xlfile.Application.Workboo
For col = 0 To data1.recordset.Fields.Cou
.Offset(row, col).FormulaR1C1 = rsData(col) & ""
Next col
row = row + 1
data1.recordset.MoveNext
End With
Wend
xlfile.Application.Visible
xlfile.Parent.Windows(1).V
xlfile.Application.Workboo
xlfile.Application.Workboo
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
deveshb,
a small mistake in my code is: clos = cols.
Regards
a small mistake in my code is: clos = cols.
Regards
ASKER
Hello Khaled
Could you please verify that the above code can handle the dynamic contents of the DBGrid.
In fact The column names and values are changing depending on user's choice for columns in the associated datacontrol. Obviously the columns are not fixed.
Please confim for this.
Thanks for your suggestion.
Could you please verify that the above code can handle the dynamic contents of the DBGrid.
In fact The column names and values are changing depending on user's choice for columns in the associated datacontrol. Obviously the columns are not fixed.
Please confim for this.
Thanks for your suggestion.
deveshb,
The above code will certainly handle the dynamic changes of the DBGrid given that the number of cols and rows are fixed at the execution time of the above code. In other words, the code will always check for the cols and rows number at any time, and then read the text at each cols and rows and print this text to a CSV File.
Regards
The above code will certainly handle the dynamic changes of the DBGrid given that the number of cols and rows are fixed at the execution time of the above code. In other words, the code will always check for the cols and rows number at any time, and then read the text at each cols and rows and print this text to a CSV File.
Regards
ASKER
Hello Khalid
Basically the application is used for custom report where we have around 30 columns available as option to the user. User can select any number of columns; and 'select statement' consisting of these selected columns are passed to the datacontrol as recordsource.
I hope you to get my point. In short, the column names as well as number of columns are not fixed. Thanks for you comments. Further suggestion please.
Basically the application is used for custom report where we have around 30 columns available as option to the user. User can select any number of columns; and 'select statement' consisting of these selected columns are passed to the datacontrol as recordsource.
I hope you to get my point. In short, the column names as well as number of columns are not fixed. Thanks for you comments. Further suggestion please.
ASKER
Hello Khaled
Your answer helped me alot. I succeeded in reaching to the final solution. I accept your answer, however the runnable code is as follows :
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim Myfile As String
Myfile = InputBox("Please Enter The File Name with Path", "MyapplMain", "c:\myfile")
Myfile = Myfile + ".csv"
Open Myfile For Output As #1
For j = 0 To (DBGrid1.Columns.Count - 1)
DBGrid1.Col = j
Print #1, DBGrid1.Columns(j).Caption ; ",";
Next j
Print #1,
Data1.Recordset.MoveFirst
While Not (Data1.Recordset.EOF)
For j = 0 To (DBGrid1.Columns.Count - 1)
DBGrid1.Col = j
Print #1, DBGrid1.Text; ",";
Next j
Print #1,
Data1.Recordset.MoveNext
Wend
Close #1
End Sub
Your answer helped me alot. I succeeded in reaching to the final solution. I accept your answer, however the runnable code is as follows :
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim Myfile As String
Myfile = InputBox("Please Enter The File Name with Path", "MyapplMain", "c:\myfile")
Myfile = Myfile + ".csv"
Open Myfile For Output As #1
For j = 0 To (DBGrid1.Columns.Count - 1)
DBGrid1.Col = j
Print #1, DBGrid1.Columns(j).Caption
Next j
Print #1,
Data1.Recordset.MoveFirst
While Not (Data1.Recordset.EOF)
For j = 0 To (DBGrid1.Columns.Count - 1)
DBGrid1.Col = j
Print #1, DBGrid1.Text; ",";
Next j
Print #1,
Data1.Recordset.MoveNext
Wend
Close #1
End Sub
khaledc,
how do we include the column headers in the CSV file. I mean first print a row of the column headers caption and then start printing the contents of the grid control?
how do we include the column headers in the CSV file. I mean first print a row of the column headers caption and then start printing the contents of the grid control?
Say you want 3 headers named A, B and C. All you have to do is print the following to the file:
Open "FileName.CSV" for ouput as #1
Print #1, "A,B,C"
....
...
Close #1
Open "FileName.CSV" for ouput as #1
Print #1, "A,B,C"
....
...
Close #1
What if i change my headers at runtime. I want a way to read the captions of the headers and print them.
That will involve you in more coding.
Cheers
Cheers
But I got do it this way because my GRID really does change headers at runtime. So I need a way to find out the captions of these headers.
I think you need to post it as a new question.
Cheers
Cheers
If you want to save the data to an excel file format then all what you have to do is save the data to a CSV Format.
CSV stands for Comma Separated Variables.
What to do is:
FIrst, save the data to an ASC File, the fileds shoud be separated by commas. eg:
Second, the extension of the ASC File should be csv.
Example
If you are saving two filed then, do the following:
Open "SomeFileName.CSV" for output as #1
Print #1, Field1;",";Field2;",";Fiel
close #1
Have more questions, give me a yell