Home > Visual Basic Programming > Creating a comma deliminited list in VB 6

Creating a comma deliminited list in VB 6

I have an Excel spreadsheet what holds data is cells on various tabls (worksheets). I have built some VB 6 code in a macro to propagate through the sheets and build comma deliminted lists in a text file out of the data is the sheets. I can create the files, but I cannot seem to find a good way to get data into those files.

Here is some of the code:

Code:

Function CreateFile(FileName AS Object) As Object

Dim File As Object

Dim TxtStream

Dim Response

Dim Existing File As Object



FileName = "c:\" & FileName

Set File = CreateObject("Scripting.FileSystemObject", "")



If (File.FileExists(FileName)) Then

   Response = MsgBox("The file exists, please remove.")

   End

Else

   Set TxtStream = File.CreateTextFile(FileName)

   Set CreateFile = TxtStream

End If

End Function



Sub Process()

Dim AgentNumber, SheetName, Sheet, Path, Server, FileName As String

Dim File As Object

Dim Response

Dim RowNum, SheetSelectNum As Integer

Dim RowSelect, RangeValue As String

Dim Flag As Integer



Sheets("Instructions").Activate

AgentNumber = Range("A1").Value

Server = Range("A2").Value

Path = Range("A3").Value



Flag = 1

SheetSelectNum = 1

RowNum = 3



Do While (Flag)

    Sheets("Instructions").Activate

    SheetName = AgentNumber & "_" & Range("B" & SheetSelectNum).Value



    Sheets(SheetName).Activate

    FileName = SheetName & ".txt"

    Set File = CreateFile(FileName)

    //Here is where I want to write the data from the sheet to the text file

Some of the code listed above isn't being used because I am testing on a local machine.

I want to have the text file to be something like this:

"<SheetName>", "1"

"<Value>","<Value>","<Value>"

All dependant on what the sheet name and values are.

Any help is greatly appreciated. Thanks.

Guest

Start here

http://msdn.microsoft.com/library/e...eProcessing.asp

Was this answer helpful ? Yes No

Guest

I've gotten the VB macro to write to the file by using the following:

Set File = CreateFile(FileName)

File.Close

Open FileName For Output AS #1

Write #1, Sheet,"1"

Close #1

Everything I've read seams to put a new line character at the end.

I may not want this as in when entering data from the sheet.

I would like to take one value, put that on a line, then a comma, then get another value and write that. I can't quite seem to come up with a solution for that.

Something like this:

RangeNumber = 1

Do While (1)

Write #1, Range("A" & RangeNumber).Value

RangeNumber = RangeNumber +1

Loop

Have all that on one line.

Was this answer helpful ? Yes No

Guest

Use a semicolon on your write # until you get to the last item to write.

This code:

Code:



Sub WriteFieldsText(strFileName As String)

    Dim FileID As Integer

    Dim iLoop1 As Integer, iLoop2 As Integer

    Dim lngValueToWrite As Long

    FileID = FreeFile

    Open strFileName For Output As #FileID

  ' // We'll write 50 records

    For iLoop1 = 1 To 50

      ' // We'll write ten fields

        For iLoop2 = 1 To 10

            lngValueToWrite = Int(Rnd * 100000)

            If iLoop2 < 10 Then

              ' // Keep the first nine fields on the same line

                Write #FileID, lngValueToWrite;

            Else

              ' // Last field to write, move to next line

                Write #FileID, lngValueToWrite

            End If

        Next iLoop2

    Next iLoop1

    Close #FileID

End Sub

Produces this (sample):

Quote:

89425,86623,38431,96549,50314,98414,44684,77913,30857,7884

87976,57924,86162,80959,76786,92398,5029,82486,22814,27484

5263,90305,98794,26226,53983,34305,21312,20124,12940,60487

58610,85697,8823,20030,63403,46048,28577,83350,30694,97422

39714,79164,80581,60773,35622,58553,66074,58324,5884,84021

5903,15130,31585,43565,42336,85640,5626,89728,23988,5261

78038,3670,13632,66918,26292,84224,21017,22621,50615,61964

60674,10021,94298,38262,34734,3978,89014,49355,40738,93122

25124,55405,4447,81412,46994,54564,37660,14706,73309,22031

21024,32316,42379,74123,11843,43574,82457,57329,76897,29394

7052,62379,50249,68836,35236,63302,16060,90665,36961,81490

16402,49916,3372,74763,84070,29458,42116,51427,392,66551

97657,9339,78045,4416,96588,99972,68868,68789,17944,17471

96669,69493,88513,12721,15043,26992,3150,32409,90184,23958

70089,83083,8693,81833,81253,20219,10397,62391,7574,62279

46155,94300,19295,29777,60677,24684,31291,9341,331,95689

73377,42773,45957,46042,85624,41115,70682,37614,38668,23709

22730,6983,79022,82260,33235,41998,99394,69899,49053,83337

33994,15245,90576,51076,78848,61470,34545,96246,37111,30355

21857,77399,89382,11361,90387,62166,89010,75685,32791,53827

82413,15326,79324,42864,54386,7144,5652,83534,98418,1927

Was this answer helpful ? Yes No

Guest

Quote:

Originally Posted by medialint

Use a semicolon on your write # until you get to the last item to write.

This code:

Code:



Sub WriteFieldsText(strFileName As String)

    Dim FileID As Integer

    Dim iLoop1 As Integer, iLoop2 As Integer

    Dim lngValueToWrite As Long

    FileID = FreeFile

    Open strFileName For Output As #FileID

  ' // We'll write 50 records

    For iLoop1 = 1 To 50

      ' // We'll write ten fields

        For iLoop2 = 1 To 10

            lngValueToWrite = Int(Rnd * 100000)

            If iLoop2 < 10 Then

              ' // Keep the first nine fields on the same line

                Write #FileID, lngValueToWrite;

            Else

              ' // Last field to write, move to next line

                Write #FileID, lngValueToWrite

            End If

        Next iLoop2

    Next iLoop1

    Close #FileID

End Sub

Produces this (sample):

I've got the code to write to the file using the semicolons...now I'm having trouble in moving to the next column. I've tried the following:

Code:

Column = "A"

RowValue = 1



Do While (1)

   RangeValue = Column & RowValue

   'Put Value in Txt File

   Write #1, Range(RangeValue);

   RowValue = RowValue + 1

   Column = Column +1

Of course the Column assignment (last line) doesn't work. Is there a way to code this into the macro to have it move to the next cell? (Without using sendkey)

I will have to add some validation in there to make sure there is a value and I'm not at the end of the sheet.

Was this answer helpful ? Yes No

Guest

Answer it

Ask Question About Visual Basic Programming

Creating a comma deliminited list in VB 6

Programming

Related Pages