?
Solved

Need help making a VB6 function's output truly comma-quote delimited

Posted on 2007-09-29
3
Medium Priority
?
347 Views
Last Modified: 2008-01-09
The following function (VB 6.0) creates a CSV file which contains the entire contents of an ADODB.Recordset, and the contents of the rows are working.  But I have a problem with the trailing quote, as you'll see in this data snippet.

OpenAccess.csv
"ID", "OrdNum", "Status", "Mgr1", "Mgr2", "POSOrderedDate", "POSOrderedTime", "POS DigMin", "Name", "Address", "City", "State", "
3", "202", "202", "202", "202", "202", "202", "5", "202", "202", "202", "202", "
364", "584149", "POSDeparted", "343", "8388", "10/27/2006", "7:18:19 PM", "1158.3",  "JOHNSON",  "700 MINDY LN", "BOSTON", "MA", "
365", "584150", "POSReturned", "343", "8388", "10/27/2006", "7:18:43 PM", "1158.7",  "FRED",  "316 SHADOW BEND CIR", "BOSTON", "MA", "

Here's the code which created it.

Could someone show me how to change the code to block the final comma from being added to the string and to wrap the quote to the start of the next line, where it belongs?

thanks,
newbieweb


Public Sub CopyRsToCSV(ByVal prstData As ADODB.Recordset, Optional ByVal userFilename As String)
Dim intFileNumber As Integer
Dim lngLoop1 As Long
Dim lngLoop2 As Long
Dim strX As String
Dim pstrFilename As String

    'If Len(Trim$(pstrFilename)) = 0 Then
    pstrFilename = App.Path & "\" & userFilename
   
    'Open data file data to file
    intFileNumber = FreeFile   ' Get unused file
    Open pstrFilename For Output As #intFileNumber
    Print #intFileNumber, userFilename
    'Write data to file
    With prstData
        strX = ""
        For lngLoop1 = 0 To .Fields.Count - 1
            strX = strX & .Fields(lngLoop1).Name & """, """
        Next lngLoop1
        Print #intFileNumber, strX
        strX = ""
        For lngLoop2 = 0 To .Fields.Count - 1
            strX = strX & .Fields(lngLoop2).Type & """, """
        Next lngLoop2
        Print #intFileNumber, strX
        If .RecordCount > 0 Then .MoveFirst
        Do Until .EOF
            strX = ""
            For lngLoop = 0 To .Fields.Count - 1
                strX = strX & .Fields(lngLoop).Value & """, """
            Next lngLoop
            Print #intFileNumber, strX
            .MoveNext
        Loop
    End With
    Close
    On Error Resume Next
    Close #intFileNumber
    If Err <> 0 Then
        MsgBox ("Error: " & Err.Description)
    'Else
     '   MsgBox ("No error")
    End If
    On Error GoTo 0
End Sub
0
Comment
Question by:newbieweb
3 Comments
 
LVL 8

Accepted Solution

by:
MrRobot earned 1000 total points
ID: 19984319
hi,


Public Sub CopyRsToCSV(ByVal prstData As ADODB.Recordset, Optional ByVal userFilename As String)
Dim intFileNumber As Integer
Dim lngLoop1 As Long
Dim lngLoop2 As Long
Dim strX As String
Dim pstrFilename As String

    'If Len(Trim$(pstrFilename)) = 0 Then
    pstrFilename = App.Path & "\" & userFilename
   
    'Open data file data to file
    intFileNumber = FreeFile   ' Get unused file
    Open pstrFilename For Output As #intFileNumber
    Print #intFileNumber, userFilename
    'Write data to file
    With prstData
        strX = ""
        For lngLoop1 = 0 To .Fields.Count - 1
            strX = strX & .Fields(lngLoop1).Name & iif(lngloop1 = .fields.count - 1,"""", """, """)
        Next lngLoop1
        Print #intFileNumber, strX
        strX = ""
        For lngLoop2 = 0 To .Fields.Count - 1
            strX = strX & .Fields(lngLoop2).Type & iif(lngloop2 = .fields.count - 1,"""", """, """)
        Next lngLoop2
        Print #intFileNumber, strX
        If .RecordCount > 0 Then .MoveFirst
        Do Until .EOF
            strX = ""
            For lngLoop = 0 To .Fields.Count - 1
                strX = strX & .Fields(lngLoop).Value & iif(lngloop = .fields.count - 1,"""", """, """)
            Next lngLoop
            Print #intFileNumber, strX
            .MoveNext
        Loop
    End With
    Close
    On Error Resume Next
    Close #intFileNumber
    If Err <> 0 Then
        MsgBox ("Error: " & Err.Description)
    'Else
     '   MsgBox ("No error")
    End If
    On Error GoTo 0
End Sub


rob
0
 

Author Comment

by:newbieweb
ID: 19984496
thanks for the help.

I just noticed, upon opening an Excel spreadeshet containing sample data, that one of the column names in blank.  Does this code need to be changed in that case?

If I recall, there was a problem.   I think when impoted, Excel ignores the column, since the column bane is blank.

Could you do a test (in the first loop) and if the name is blank, insert something as a place keeper?

Perhaps the word "blank" would suffice.

thanks,
newbieweb
0
 
LVL 33

Assisted Solution

by:Robberbaron (robr)
Robberbaron (robr) earned 1000 total points
ID: 19995201
1/ i would do the loops a little different to keep them easy

        strX = ""
        For lngLoop1 = 0 To .Fields.Count - 1
            strX = strX & ","" & .Fields(lngLoop1).Name &  """
        Next lngLoop1
        strX = mid(strX,2)
        Print #intFileNumber, strX

2/  regards blanks, I think you will need to use blank for fieldname
        strX = "": strY=""
        For lngLoop1 = 0 To .Fields.Count - 1
            strY = .Fields(lngLoop1).Name
            if strY="" then
                   strY = format(lngLoop1,"blank000")   'allow for multiple blanks
            end if
            strX = strX & ","" & strY &  """
        Next lngLoop1
        strX = mid(strX,2)
        Print #intFileNumber, strX
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

579 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