Exporting data using VB6 (No DTS)

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.

 

That still doesnt help the xls problem

Works for TXT & CSV ....  Sorry Can't help w/ Excell ......

Dan A.

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

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

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

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)

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

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

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

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.

Krule,

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

Leon

Still waiting to see if any other answers come out..

Nothing here has answered my fundamental problem yet...

And I still haven't found a very good solution

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