×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Use MS Access VBA to write table data to text file

Use MS Access VBA to write table data to text file

Use MS Access VBA to write table data to text file

(OP)
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

RE: Use MS Access VBA to write table data to text file

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

RE: Use MS Access VBA to write table data to text file

(OP)
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.

RE: Use MS Access VBA to write table data to text file

Do you mean that for THIS example, YOUR example...

"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

...that the desired output is this text string...

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Use MS Access VBA to write table data to text file

(OP)
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

RE: Use MS Access VBA to write table data to text file

(OP)
Yes Skip!

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

Thanks!

RE: Use MS Access VBA to write table data to text file

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

RE: Use MS Access VBA to write table data to text file

So using your example, I generate this output...

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


In Excel with this table using Named Ranges based on your headings...
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
 

My solution...

CODE

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Use MS Access VBA to write table data to text file

(OP)
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]

RE: Use MS Access VBA to write table data to text file

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Use MS Access VBA to write table data to text file

(OP)
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

RE: Use MS Access VBA to write table data to text file

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

RE: Use MS Access VBA to write table data to text file

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Use MS Access VBA to write table data to text file

(OP)
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

RE: Use MS Access VBA to write table data to text file

Open FilePath For Output As #1 will create a new text file every time this line of code is executed.
Open FilePath For Append As #1 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 Write #1, fOutput 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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close