Solved

Exporting data using VB6 (No DTS)

Posted on 2003-11-07
16
5,733 Views
Last Modified: 2013-12-25
I'm creating a VB6 application that needs to export data to different formats that the end user will define.

The data is comming from an ADO recordset and needs to be exported to any of these formats:

.csv, .txt, .xls

What approach should I use to do these exports? I've played around using the Write statement, but i have to loop through every record and every field, which gets tedious and slow, and doesn't work for the xls :p

Any ideas?
0
Comment
Question by:Krule
  • 4
  • 3
  • 2
  • +6
16 Comments
 

Expert Comment

by:Dan_A
ID: 9703031
Did you try the Print Statment ???

e.g.

Print #1, Field1;Field2,Field3

or the whole record

e.g.

Print #1, THE_WHOLE_RECORD

Dan A.

 
0
 
LVL 3

Author Comment

by:Krule
ID: 9703207
That still doesnt help the xls problem
0
 

Expert Comment

by:Dan_A
ID: 9703232
Works for TXT & CSV ....  Sorry Can't help w/ Excell ......

Dan A.


0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 9703596
you are aware of the fact the Excel can read a CSV file directly, so you have no real need to output as an XLS directly.  If you feel the absolute need to output as an XLS 9not a CSV, to be read by Excel), then you have no choice but to use Office autometion (create an Excel object, in you VB code, and then iterate throiugh each field of each record).  There is not other way to approach the problem.

In turn, if you NAME the file with the XLS extension, but actually load the file as a CSV - (save the data to MyFile.XLS as comma-separated values), then Excel will open the .xls directly, and recognize that it in fact contains the CSV data, and procede.

AW
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 9703597
you are aware of the fact the Excel can read a CSV file directly, so you have no real need to output as an XLS directly.  If you feel the absolute need to output as an XLS 9not a CSV, to be read by Excel), then you have no choice but to use Office autometion (create an Excel object, in you VB code, and then iterate throiugh each field of each record).  There is not other way to approach the problem.

In turn, if you NAME the file with the XLS extension, but actually load the file as a CSV - (save the data to MyFile.XLS as comma-separated values), then Excel will open the .xls directly, and recognize that it in fact contains the CSV data, and procede.

AW
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 9703630
Welcome Krule.  I thought I may see you here soon. :)

The easiest thing to do would be to dump the data into Excel and then save it as either .xls, .csv, or .txt

The process works like this

1. Create a recordset
2. Use CopyFromRecordset Range method method
  Something like (From Excel VBA but similar in VB):  Range(Cells(2, 1), Cells(intRows + 1, rsRec.Fields.Count)).CopyFromRecordset rsRec

3. Save file in desired format

That is all,

Let me know if you need a specific example.

Leon

0
 
LVL 3

Author Comment

by:Krule
ID: 9703772
Yeah..example = good :p

Sup leon..I didn't know this forum existed..i think it'll be better for what i need.

(I'm actually still thinking of using dts..i just want to see how hard it'll be to do it this way instead...and maybe do it in a component that i can just update, as opposed to updating the entire application)
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 9704133
Here is some sample code to retrive the value into Excel.  I am running this directly from Excel (This is Friday afternoon, so I do not want to modify it for VB).  I am using an Oracle database and I am pulling some values like the sql string and the database name from a pre-defined ranges.  I will show you the code to save the file separately.

I have retrieved up to 6,000 rows on a none indexed table using "Select * From table" with this procedure in 5 seconds.

Leon

Public Const EMPTY_STRING = ""
Public strSQL As String
Public strDataBase As String
Public cnConn As ADODB.Connection
Public rsRec As ADODB.Recordset
Public intRows As Integer

Sub GetData()
Dim intCol  As Integer
    Application.ScreenUpdating = False
    strSQL = Range("rngSQL")
    ClearSheet
    If Not OpenRecSet(strSQL, rsRec, intRows) Then Exit Sub
    If intRows > 0 Then
        For intCol = 1 To rsRec.Fields.Count
            Cells(3, intCol).Value = rsRec(intCol - 1).Name
        Next
        Range(Cells(4, 1), Cells(intRows + 3, rsRec.Fields.Count)).CopyFromRecordset rsRec
    Else
        Application.ScreenUpdating = True
        MsgBox "No records returned"
    End If
    CloseConn cnConn
    Application.ScreenUpdating = True
End Sub

Sub ClearSheet()
    Range("A3", Range("A3").End(xlDown)).EntireRow.ClearContents
End Sub

Public Function OpenRecSet(ByVal strSQL As String, _
                            ByRef rsSet As ADODB.Recordset, Optional intRows As Integer = 0) As Boolean
    OpenRecSet = True
    On Error Resume Next
    rsSet.Close
    On Error GoTo ERROR_FUNCTION
    If Not OpenConnOracle() Then GoTo EXIT_FUNCTION
    Set rsSet = New ADODB.Recordset
    rsSet.Open strSQL, cnConn, adOpenStatic, adLockOptimistic
   
    intRows = rsSet.RecordCount
   
EXIT_FUNCTION:
    Exit Function
ERROR_FUNCTION:
    Select Case Err.Number
        Case -2147217865
            MsgBox "Error - Could not connect to database.", vbCritical
        Case 3709
            Err.Number = 0
    End Select
    Err.Clear
    OpenRecSet = False
    GoTo EXIT_FUNCTION
End Function

Public Function CloseRecSet(ByVal rsSet As ADODB.Recordset) As Boolean
    CloseRecSet = True
    On Error GoTo ERROR_FUNCTION
    rsSet.Close
    Set rsSet = Nothing
EXIT_FUNCTION:
    Exit Function
ERROR_FUNCTION:
    Err.Clear
    CloseRecSet = False
    GoTo EXIT_FUNCTION
End Function

Public Function CloseConn(ByVal cnConn As ADODB.Connection) As Boolean
    CloseConn = True
    On Error GoTo ERROR_FUNCTION
    If cnConn.State = adStateOpen Then
        cnConn.Close
    End If
    Set cnConn = Nothing
EXIT_FUNCTION:
    Exit Function
ERROR_FUNCTION:
    Err.Clear
    CloseConn = False
    GoTo EXIT_FUNCTION
End Function

Public Function OpenConnOracle() As Boolean
Dim strConn As String       '/ Connection string to open database
    OpenConnOracle = True
    On Error GoTo ERROR_FUNCTION
    Select Case True
        Case cnConn Is Nothing
            Set cnConn = New ADODB.Connection
            GoTo OPEN_CONN
        Case cnConn.State = adStateClosed
            GoTo OPEN_CONN
    End Select
EXIT_FUNCTION:
    Exit Function
OPEN_CONN:
    strConn = GetConnString()
    With cnConn
        .CursorLocation = adUseClient
        .Open strConn
    End With
    GoTo EXIT_FUNCTION
ERROR_FUNCTION:
    OpenConnOracle = False
    MsgBox "OpenConnOracle error: " & Err.Number & " " & Err.Description
    Err.Clear
    GoTo EXIT_FUNCTION
End Function

Private Function GetConnString() As String
'/ Returns proper connection string based on location
    Select Case UCase(Trim(Range("rngDataBase").Value))
        '/ Default database is Cork
        Case EMPTY_STRING, "CORK"
            GetConnString = "Provider=OraOLEDB.Oracle.1;Persist Security Info=False;" & _
                    "User ID=CORK;Password=CORKIRE;Data Source=TEST"
        Case "USA"
            GetConnString = "Provider=OraOLEDB.Oracle.1;Persist Security Info=False;" & _
                    "User ID=USA;Password=USA;Data Source=TEST"
        Case Else
            GetConnString = EMPTY_STRING
     End Select
End Function
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 29

Expert Comment

by:leonstryker
ID: 9704195
To save your file the code will look something like this

ActiveWorkbook.SaveAs Filename:=strPathName, FileFormat:=strFormat

where
strPathName = Path and name of the file
strFormat = format
for text: xlText
for csv:  xlCSV
for xls:   xlNormal

Leon
0
 
LVL 3

Expert Comment

by:WRNewman
ID: 9707922
The no choice option is just a touch extreme.

You can open an excel spreadsheet as a DB, provided that the spreadsheet has text headers for each column.
The Jet engine then sees each header as the field name.

Years ago I did this in DAO, before ADO was even thought of

Code from an app that has been live for years
ExcelVer$="Excel 5.0"
OpenDatabase(path$ + File1.FileName, False, False, excelver$ + ";")
   Set rc = ex.OpenRecordset("sheet1$")

This was 1996 so the memory could be struggling a bit here but
I think the version string had to be set before opening the spreadsheet, nowadays probaly version 8.0
File1.filename was the name of the xls file
I seem to recollect that in the excel collection "sheet1$" contained the name of the sheet

Step through your sql query table with handle say of RS

RS.Movefirst
rc.addnew
rc("ID")=Rs("ID")
rc.update
rs.movenext

This works

CSV

open "anyfile.csv" for output as #1
step through the table
print#1,RS("id")+",";
print#1,RS("Name")+",";
On the last field omit the trailing ; to get a CR/LF pair

For text you would have to format fields appropriately

print#1, right(space(10)+format(rs("id"),"0"),10)+space(2);
print#1,left(rs("name")+space(30),30);

again on the last field omit the trailing ;

Hope this helps, does give a few more options

Will
0
 
LVL 8

Expert Comment

by:MYLim
ID: 9711644
Do u have crystal report install ?
it seem crystal report have ability to convert your recordset into:
excel,txt,pdf and etc...
check it out.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 9987589
Krule,

Do you still have a problem, or can this question be closed?

Leon
0
 
LVL 3

Author Comment

by:Krule
ID: 10132533
Still waiting to see if any other answers come out..
0
 

Expert Comment

by:KruleOne
ID: 10261342
Nothing here has answered my fundamental problem yet...

And I still haven't found a very good solution
0
 
LVL 1

Expert Comment

by:ayufans
ID: 10458429
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

- PAQ'd and points NOT refunded

Please leave any comments here within the next four days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

ayufans
Cleanup Volunteer
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 10490824
PAQed - no points refunded (of 125)

Computer101
E-E Admin
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now