Use MS Access VBA to write table data to text file

[tdrBates]

I need to be able to write the dataset below to a text file

"schHwy","TRIPID","SEQUENCE"
0,"1834556",24
0,"1834556",3
0,"1834556",4
0,"1834556",5
0,"1834556",6
0,"1834556",7
0,"1834556",8
0,"1834556",9
0,"1834847",1
0,"1834847",10
0,"1834847",11
0,"1834847",12
0,"1834847",13
0,"1834847",14

I need a VBA macro to write the dataset above to a text file in the format below
where case = "TRIPID"
{Command.seq_num} in "SEQUENCE"
then "schHwy"

Case '1833863':
If {Command.seq_num} in [1] then
8
Else
If {Command.seq_num} in [2] then
10
Else
If {Command.seq_num} in [3,4] then
11
Else
If {Command.seq_num} in [5,6] then
12
Else
If {Command.seq_num} in [7] then
13
Else
If {Command.seq_num} in [8] then
14
Else
If {Command.seq_num} in [9,10,11,12,13,14,15,16] then
15
Else
If {Command.seq_num} in [17,18] then
16
Else
If {Command.seq_num} in [19] then
17
Else
If {Command.seq_num} in [20,21,22] then
18
Else
20

Case '1833875':
If {Command.seq_num} in [1] then
14
Else
If {Command.seq_num} in [2,3,4] then
13
Else
If {Command.seq_num} in [5,6] then
12
Else
If {Command.seq_num} in [7,8] then
11
Else
If {Command.seq_num} in [9,10,11,12,13,14,15,16,17,18] then
10
Else
9

Case '1833878':
If {Command.seq_num} in [1,2,18] then
11
Else
If {Command.seq_num} in [3,4,5,6,7,8,9,10,11,12,13,14,15,16,17] then
10
Else
12

Case '1833881':
If {Command.seq_num} in [1,2,3,4] then
9
Else
If {Command.seq_num} in [5,6,7,8,9,10,11,12,13] then
10
Else
If {Command.seq_num} in [14,15,16,17,18] then
11
Else
If {Command.seq_num} in [19] then
12
Else
If {Command.seq_num} in [20,21] then
13
Else
14

Case '1833913':
If {Command.seq_num} in [1,2,3,4,5,6] then
9
Else
If {Command.seq_num} in [7,8,9,10,11,12] then
10
Else
If {Command.seq_num} in [13,14] then
11
Else
If {Command.seq_num} in [15] then
13
Else
If {Command.seq_num} in [16] then
14
Else
If {Command.seq_num} in [17] then
16
Else
If {Command.seq_num} in [18,19] then
17
Else
If {Command.seq_num} in [20,21] then
19
Else
If {Command.seq_num} in [22] then
20
Else
21

 

[dhookom]

tdrBates,
So the sample data you provided at the top of your post results in the text file that follows later? I don't see any logical connection between the two.

I'm not sure how anyone could help you with what you have provided.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[tdrBates]

Thanks dhookom! The text file at the top of my post was just a sample data set pasted there from a MS Access table that has 89,000 rows.

Basically what I'm trying to do is take the data in the MS Access table (file at the top of my post) and write that data to a text file in the form of case statement
or IF statement.

For example:

If TRIPID = 1234567 and SEQUENCE in [4,5,6]
then schHwy is 10
ELSE IF TRIPID = 89898989 and SEQUENCE IN [1,2,3]
then schHwy is 15

I need if possible a MS Access or Excel VBA macro that looks at each of the 89,000 rows in the MS Access table or Excel spreadsheet
and writes a case statement or if statement.

 

[SkipVought]

Do you mean that for THIS example, YOUR example...
[tt]
"schHwy","TRIPID","SEQUENCE"
0,"1834556",24
0,"1834556",3
0,"1834556",4
0,"1834556",5
0,"1834556",6
0,"1834556",7
0,"1834556",8
0,"1834556",9
[/tt]
...that the desired output is this text string...

[pre]
If TRIPID = "1834556" and SEQUENCE in [24,3,4,5,6,7,8,9]
then schHwy is 0
[/pre]
???



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[tdrBates]

For example in row 1 and row 2 of the text file the top my post would be written

Case '1834556':
If {Command.seq_num} in [3,24] then
0

 

[tdrBates]

Yes Skip!

An If statement is good, but would prefer CASE statement.

Thanks!

 

[dhookom]

Why are you not including 4,5,6,7,8,9? What's the difference between those numbers and 3,24?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[SkipVought]

So using your example, I generate this output...
[tt]
Case WHEN [TRIPID] = '1834556' AND [SEQUENCE] IN [24,3,4,5,6,7,8,9] THEN 0
WHEN [TRIPID] = '1834847' AND [SEQUENCE] IN [1,10,11,12,13,14] THEN 0
END
[/tt]

In Excel with this table using Named Ranges based on your headings...
[pre]
schHwy TRIPID SEQUENCE
0 1834556 24
0 1834556 3
0 1834556 4
0 1834556 5
0 1834556 6
0 1834556 7
0 1834556 8
0 1834556 9
0 1834847 1
0 1834847 10
0 1834847 11
0 1834847 12
0 1834847 13
0 1834847 14
[/pre]

My solution...

Function fOutput() As String
    Dim r As Range, sThisTripID As String, sPrevTripID As String, sSEQ As String, sSchHwy As String
    
    fOutput = "Case "
    
    For Each r In [TRIPID]
        sThisTripID = r.Value
        If sThisTripID <> sPrevTripID Then
            If sPrevTripID <> "" Then
                GoSub DoOutput
            End If
        End If
        sPrevTripID = sThisTripID
        sSchHwy = r.Offset(0, -1).Value
        sSEQ = sSEQ & r.Offset(0, 1).Value & ","
    Next
    
    GoSub DoOutput
    
    fOutput = fOutput & " END"
    Exit Function
DoOutput:
    fOutput = fOutput & "WHEN [TRIPID] = '" & sPrevTripID & _
        "' "
    sSEQ = Left(sSEQ, Len(sSEQ) - 1)
    fOutput = fOutput & "AND [SEQUENCE] IN [" & sSEQ & "] THEN schHwy Is " & sSchHwy & " "
    
    sSEQ = ""

    Return
End Function

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[tdrBates]

Skip, when I put the code above into an Excel macro and run the code
I get Run-time error '424' Object Required.

I tried using Option Explicit but still get the error on
the line For Each r In [TRIPID]

 

[SkipVought]

I did my solution in Excel with your table in a sheet and using named ranges based on the headings in row 1.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[tdrBates]

Skip

I am still getting the

Run-time error '424' Object Required on [TRIPID].

I will study the code more and get back with you.

Thanks

 

[lameid]

Lookup how to make a named range... Make your named range named "TRIPID".

 

[SkipVought]

SELECT your table in Excel

Formulas > Defined Names > Create Names from Selection > TOP Row

After you do this, you will see the three heading names in the Name Box just above Column A.

Selecting any of the references in the Name Box will select the associated Range in your workbook.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[tdrBates]

I haven't work with visual basic for about 10 years, so I am having trouble writing the Case statement to notepad.
Therefore, I changed Skip's function above to a Sub procedure.

In the Sub procedure below I try to write to text file using open file for output and open file for append and
only get one or two lines written to the file.

Can someone explain how I get the sub procedure below to write all contents to the file?
Also, how does Skip's function above write its contents to a text file?

Thanks tdr


Sub trapHeadways()

Dim r As Range, sThisTripID As String, sPrevTripID As String, sSEQ As String, sSchHwy As String

Dim fOutput As String
Dim FilePath As String

FilePath = Application.DefaultFilePath & "\ronTest.txt"

' Open FilePath For Append As #1
Open FilePath For Output As #1


fOutput = "Case "

For Each r In [TRIPID]
sThisTripID = r.Value
If sThisTripID <> sPrevTripID Then
If sPrevTripID <> "" Then
GoSub DoOutput
End If
End If
sPrevTripID = sThisTripID
sSchHwy = r.Offset(0, -1).Value
sSEQ = sSEQ & r.Offset(0, 1).Value & ","
Next

GoSub DoOutput

fOutput = fOutput & " END"
Exit Sub
DoOutput:
fOutput = fOutput & "WHEN [TRIPID] = '" & sPrevTripID & _
"' "
sSEQ = Left(sSEQ, Len(sSEQ) - 1)
fOutput = fOutput & "AND [SEQUENCE] IN [" & sSEQ & "] THEN schHwy Is " & sSchHwy & " "
Write #1, fOutput

sSEQ = ""

Close #1
End Sub

 

[Andrzejek]

[tt]Open FilePath For [blue]Output[/blue] As #1[/tt] will create a new text file every time this line of code is executed.
[tt]Open FilePath For [blue]Append[/blue] As #1[/tt] will create a new file if one does not exist, or write (adds) to an existing file.

The best way for you to know what exactly is going on with your code is to set some break point(s) and step thru your code. You will find out that the [tt]Write #1, fOutput[/tt] code is executed once or twice only and that's why you "get one or two lines written to the file."


---- Andy

There is a great need for a sarcasm font.