Solved

Exporting data using VB6 (No DTS)

Posted on 2003-11-07
16
5,735 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Prevent user closing word document opened with VB6 6 74
MsgBox 4 61
Advice in Xamarin 21 79
Excel Automation VBA 19 71
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…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

809 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