?
Solved

How to export DBGRID data to MSExcel

Posted on 1998-09-24
17
Medium Priority
?
495 Views
Last Modified: 2010-04-30
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
0
Comment
Question by:deveshb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 3
  • +1
17 Comments
 
LVL 1

Expert Comment

by:khaledc
ID: 1436535
When you save save it in MSExcel, do you mean save the data to a file or save the data using OLE?
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;",";Field3;",";...... (depends on the number of the fields)
close #1

Have more questions, give me a yell

0
 

Author Comment

by:deveshb
ID: 1436536
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.

0
 
LVL 1

Expert Comment

by:khaledc
ID: 1436537
I will prepare some and send it to you ASAP
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Expert Comment

by:edjohn
ID: 1436538
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.Workbooks("temp.xls").Worksheets("data").Range("a2")
                For col = 0 To data1.recordset.Fields.Count - 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).Visible = True
        xlfile.Application.Workbooks("test").Save
        xlfile.Application.Workbooks("test").Close
0
 

Expert Comment

by:edjohn
ID: 1436539
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.Workbooks("temp.xls").Worksheets("data").Range("a2")
                For col = 0 To data1.recordset.Fields.Count - 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).Visible = True
        xlfile.Application.Workbooks("test").Save
        xlfile.Application.Workbooks("test").Close
0
 
LVL 1

Accepted Solution

by:
khaledc earned 400 total points
ID: 1436540
The codes can be as followed:
Suppose that the file name you want you want save data to is called MyFile.csv (note that any filename extension should  have a CSV extension). Also, assume that your Grid is called Grid1, then the code should look like the follwing:
Dim i as integer
Dim j as integer
     Open "MyFile.CSV" For Output as #1
     For i = 0 to (Grid1.rows - 1)
         Grid1.Row = i
         j = 0 to (Grid1.clos - 1)
             Grid1.col = j
                   Print #1, Grid1.Text;",";
         Next j
         Print #1,
     Next i
     Close #1

The above code is a quick way to copy all the texts in the grid into a CSV File that can be opened directly by Excel.
The CSV should have exactly the same Row and cols as the Grid.

Give me a yell if you Need more question
0
 
LVL 1

Expert Comment

by:khaledc
ID: 1436541
deveshb,
a small mistake in my code is: clos = cols.
Regards
0
 

Author Comment

by:deveshb
ID: 1436542
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.
0
 
LVL 1

Expert Comment

by:khaledc
ID: 1436543
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
0
 

Author Comment

by:deveshb
ID: 1436544
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.

0
 

Author Comment

by:deveshb
ID: 1436545
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
0
 

Expert Comment

by:mhdhallak
ID: 2365584
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?
0
 
LVL 1

Expert Comment

by:khaledc
ID: 2369074
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
0
 

Expert Comment

by:mhdhallak
ID: 2369709
What if i change my headers at runtime. I want a way to read the captions of the headers and print them.
0
 
LVL 1

Expert Comment

by:khaledc
ID: 2369855
That  will involve you in more coding.
Cheers
0
 

Expert Comment

by:mhdhallak
ID: 2374331
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.
0
 
LVL 1

Expert Comment

by:khaledc
ID: 2379816
I think you need to post it as a new question.
Cheers
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question